- Downloading and Installing PowerPivot
- Getting your Data into PowerPivot
- Linking an Excel Table to PowerPivot
- Importing Data into PowerPivot
- Adding DAX Calculations to the Grid
- Creating Pivot Tables from PowerPivot
- Defining a Relationship Between Worksheets
- Using Automatic Relationship Detection
- Using Explicit Relationships
- Using the RELATED Function in the PowerPivot Grid
- Adding Slicers to Drive All Pivot Elements
- Adding DAX Calculations to the PivotTable
- Entering a Measure
- Using the X Functions
- Using CountRows and Distinct
- PowerPivot Is a Home Run
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Creating Pivot Tables from PowerPivot
The real power of PowerPivot comes in the pivot table–creation process. There are two identical drop-downs that can be used to start a PowerPivot pivot table. One drop-down is in the PowerPivot window, on the Home tab. The other drop-down is on Excel's PowerPivot ribbon tab.
)
The first two menu items let you create a single PivotTable or PivotChart. The last menu item creates a pivot table without subtotals and with item labels repeated. This table is appropriate when you are trying to create a new summary table that will be used as input for further data analysis. The other choices in the PivotTable drop-down are various combinations of tables and charts. As you will see later, the new Slicer filters allow for multiple pivot tables to be filtered at the same time. Thus, PowerPivot is great for creating dashboards of multiple charts or tables.
Choose Chart and Table (Horizontal). You are returned to the Excel workbook. Two new worksheets have been inserted. One of those worksheets is used by PowerPivot to hold the actual pivot table for the chart. You will initially be looking at the worksheet that will hold the PivotChart and PivotTable.
)
The cell pointer in J3 indicates that you will be building the PivotTable first. If you want to work on the PivotChart first, click in B3. One nice feature is that PowerPivot will handle moving the pivot table to a new location if the chart gets larger or smaller.
The PowerPivot Field List is similar to the regular PivotTable Field List, with a few additions. Like a regular pivot table, the field list has four drop zones for Row Labels, Column Labels, Report Filter, and Sum Values. Two new drop zones are used for vertical or horizontal slicers.
One major difference is that each PowerPivot worksheet is listed in the top of the field list. Click the plus sign next to a worksheet to list all the fields available in that worksheet. You can start your pivot table using fields from either worksheet.
){kind=small}
In the following figure, the pivot table shows Revenue by FY and Quarter. Just as with a regular pivot table, you drag fields from the field list to the appropriate drop zone in order to arrange the pivot table.
)
You can format the pivot table using the regular Excel PivotTable Tools ribbon tabs. For example, to format this pivot table, you might do the following:
- Choose Design, Report Layout, Tabular.
- Choose Design, Banded Rows.
- Open the PivotTable Styles gallery on the Design tab and choose a medium style.
- Select one cell that contains Revenue; then use Field Settings on the Options tab to change the numeric format to Number, Thousands Separator, No Decimals.
Also in the same dialog, you can change the field name to Revenue (with a trailing space) instead of Sum of Revenue.
)
At this point, you've built a pivot table using only the fields from one worksheet. Let's see how to bring in the fields from another worksheet.