This chapter is from the book
This chapter is from the book
This chapter is from the book
Build a Pivot Table
One of the advantages of PowerPivot is that multiple pivot tables can share the same data and slicers. Open the PivotTable drop-down on the Home tab of the PowerPivot Ribbon. As shown in Figure 2.20, you have choices for a single pivot table, a single chart, a chart and a table, two charts, four charts, and so on.
)
Figure 2.20 You have many options beyond a single table or chart.
|
|
To read more about layouts with multiple elements, see Chapter 7, "Building Pivot Tables." |
Follow these steps:
- Select PivotTable. You now see the PowerPivot tab back in the Excel window.
Choose to put the pivot table on a new worksheet (see Figure 2.21).
Figure 2.21 Choose the location for the pivot table.
There are many things to notice. The icon for a blank pivot table occupies cell B3:D20. This allows room for slicers to go above and left of the pivot table (see Figure 2.22).
Figure 2.22 The blank pivot table icon appears in B3.
Docked on the right side of the screen is the PowerPivot Field List. This is now a third variation of the PivotTable Field List.
Figure 2.23 The PowerPivot Field List is a third variant of the PivotTable Field List.
Both tables are available in the top of the Field List. The main table is expanded to show the field names, but you can expand the other table and add those fields to this pivot table.
Two new sections in the drop zones offer vertical or horizontal slicers.
Because you are in a pivot table, the PivotTable Tools tabs are available in the Excel Ribbon.
- Select Revenue from the PowerPivot Field List by adding a check box next to it. Because Revenue is a numeric field, it automatically moves to the Values drop zone at the bottom of the field list. Your pivot table now shows Sum of Revenue, which is the total revenue in the entire 1.8 million row file (see Figure 2.24).
Figure 2.24 Choose a numeric field and Excel sums that field.
- Expand the StoreInfo table. Select Region from the StoreInfo table. Excel builds a pivot table showing sales by region. At this point, you have a pivot table from 1.8 million rows of data with a virtual link to a lookup table.
- Drag the Division field from the Demo table to the Column Labels drop zone. Your pivot table is now mashing up data from two different tables (see Figure 2.25).
Figure 2.25 This pivot table summarizes 1.8 million rows and data from two tables.
- Drag the Store Name field to the Row Labels drop zone. Drop Store Name below the Region field.
)
)
)
)
)
To show off some more features of the PowerPivot pivot table, let's add some slicer functionality:
- Drag Year to the Slicers Vertical drop zone.
- Drag Month to the Slicers Vertical drop zone.
- Drag Mall Developer to the Slicers Horizontal drop zone.
To clean up the formatting of the pivot table, follow these steps:
- Go to the PivotTable Tools Design tab in the Ribbon. Select Banded Rows.
- On the same tab, open the PivotTable Styles gallery and choose a light, medium, or dark color scheme.
- Go to the Options tab. The Active Field should say Sum of Revenue. If it does not, choose a numeric cell in the pivot table.
- Click the Field Settings icon in the PivotTable Tools Options tab.
- In the lower left corner of the Value Field Settings dialog, select the Number Format button.
Select the Number category. Select Use 1000 Separator. Select 0 decimal places (see Figure 2.26). Click OK to close the Format Cells dialog. Click OK to close the Value Field Settings dialog.
Figure 2.26 The numeric formatting is hidden two levels deep.
The result is an impressive interactive report, as shown in Figure 2.27.
Figure 2.27 The formatted report with some slicers.
- Select the year 2000 from the Year slicer. The report instantly redraws to show only the stores that were open in the year 2000. The Mall Developer slicer redraws to show only the five developers with whom you had contracts in 2000 (see Figure 2.28).
Figure 2.28 Choose a year and the report updates, as well as the other slicers.
)
)
)