These raw data are organized differently from the data in the previous example. Each row in this table represents a specific category of video for a specific store. The number is the count of titles in stock for that category. The goal is to create a PivotTable report that presents this information in an easy-to-read form and to display summary information.
To begin, open the workbook VideoStoreRawData.xlsx. Make sure the cell pointer is on a cell in the table; it does not matter which one. Then click the PivotTable button on the Insert ribbon to display the Create PivotTable dialog box (shown earlier in Figure 1-2). Make sure the following options are selected:
Click OK to create the shell of the PivotTable and display the PivotTable Field List. The list contains Store, Category, and Titles. Select all three field names. Excel will:
The results are shown in Figure 1-8.
Figure 1-8: The initial PivotTable created for the video rental store data.
Although this is a perfectly legitimate PivotTable, it is not what you want. You can see that both the Store and Category fields are used as row headings—you want a report where Category is a column heading. This is easily fixed: In the PivotTable Field List, go to the Row Labels box and click the down arrow next to Category. From the context menu, select Move To Column Labels. The field will move to the Column Labels box and the PivotTable will change to the desired format, as shown in Figure 1-9. This is an example of pivoting the table so the data is arranged differently. If you are working along in Excel, be sure to save your workbook after creating the PivotTable and pivoting it. You’ll use this PivotTable again later in this chapter.
Figure 1-9: The final PivotTable has Store as a row field and Category as a column field.
In Figure 1-9, notice that Excel automatically creates totals for each category and each store, as well as an overall total.
