Sales PivotTable

Open the ExtraChapter09_PivotTables file.

The SalesData worksheet contains data from the furniture store’s 2015 transactions. The data on this worksheet is nicely organized, but there is a lot of it! We can use a PivotTable to further summarize the data so that it can be explored interactively.

1. Select the SalesPivotTable worksheet. Notice that cell A5 has a fill color. This is the cell that we are going to choose as the location for the PivotTable.
2. Go back to the SalesData worksheet and select one cell that has data in it.
3. Click on the Insert tab and then click on PivotTable on the far-left side of the ribbon.
4. In the Create PivotTable window that opens, confirm that the range of data is in the box at the top.
5. Click on the button near the bottom, next to Existing Worksheet.
6. Click in the box next to Location.
7. Click the SalesPivotTable worksheet.
8. Click in cell A5. Click OK.
9. Notice the PivotTable Fields list on the right side. This is where we will set up which fields are the columns of the PivotTable and which fields are the rows of the PivotTable as well as which values are summarized and in which way they are summarized.
   • Look at the image below of what your finished PivotTable will look like. Above the PivotTable, in cells A3:B3 is a filter so that the data can be filtered down to different departments. In the Fields List, drag the Department field to the Filters area of the design.
   • Look at the image below of what your finished PivotTable will look like. The categories are each in their own column. Therefore, drag the Category field to the Columns quadrant.
   • Look at the image below of what your finished PivotTable will look like. The rows are related to when the transactions took place. Therefore, drag the Date field to the Rows quadrant. Notice that Months will be added as well so that Excel will group the date by month. We will also group by Quarter later.
   • Look at the image below of what your finished PivotTable will look like. The actual data inside the PivotTable are totals of the volume and revenue amounts. Therefore, bring the Volume field into the Values quadrant. Notice that the name of the field changes to Sum of Volume. Then, bring the Revenue field into the Values quadrant, making sure it is placed under the Volume. Notice that the Revenue field becomes Sum of Revenue.
     • Click on the Sum of Revenue field and select Value Field Settings. Click on the Number Format button. Select Currency as the format of this field. Click OK and OK again.
   • Close the PivotTable Fields list.
10. Change cell B5 to Category by typing over it. It’s ok, it will still remain a drop-down.
11. Change cell A7 to Date by typing over it.
12. Right-click on any one of the months and choose Group. Deselect Days and Select Quarters, then click OK.
13. Change the PivotTable style to Medium 6.
14. Use the PivotTable to answer the questions in M1:O1. After you are finished answering the questions, make sure any filters you used are cleared so that your PivotTable looks like the image below.

The finished PivotTable should resemble this image:

If you are going to stop working here, make sure your file is saved. When you close it, if there is any unsaved work, Excel will prompt you to save. Make sure the most recently worked file is in your IU OneDrive.

Customer Data PivotTable

Open the ExtraChapter09_PivotTables file.

The CustomerData worksheet contains some demographic data about customers as well as how much each customer spent in 2015. You are going to use this data to create a PivotTable that will summarize the data and make it easier to analyze the demographics of the customers.

1. Select a cell that contains data on the CustomerData worksheet.
2. Insert a PivotTable in cell A4 on the CustomerPivotTable worksheet.
3. Set up the PivotTable according to these steps to make it look like the image below the directions.
   1. Filter the data by HomeState.
   2. The columns of the PivotTable contain both the gender and the marital status of the customer. Add both of those fields to the Columns quadrant, making sure that Gender is first and Married? is below that.
   3. The rows of the PivotTable contain the ages of the customers, grouped in ranges of 5 years. Add the Age field to the Rows quadrant.
   4. The PivotTable table should show the number of customers in each age group, for each gender, and for each marital status. Each customer has a unique CustomerID and these can be counted to examine how many customers are in each age group. Add the CustomerID field to the Values quadrant.
   5. In column A, right-click on any of the ages and choose Group. In the window that appears, change the By: value to 5. Click OK. The ages should now be in the intervals shown in the image.
   6. Change cell A4 to # of Customers.
   7. Change cell B4 to Gender/Status.
   8. Change cell A6 to Age Group.
   9. Change the PivotTable style to Dark 6.
4. Use the PivotTable to answer the questions to the right. After you are finished answering the questions, make sure any filters you used are cleared so that your PivotTable looks like the image below.

The finished PivotTable should look like this image:

When you are done, close and save your file. If there is any unsaved work, Excel will prompt you to save. Make sure the most recently worked file is in your IU OneDrive.