- 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
Adding Slicers to Drive All Pivot Elements
The PowerPivot team did a beautiful job improving upon the Excel 2010 Slicer. In regular Excel, slicers are a way to visually filter a pivot table. In Excel, if you create multiple slicers, they start out as single-column slicers that are tiled to overlap each other. You will find yourself constantly rearranging the slicers in regular Excel.
In PowerPivot, you drag the slicer fields to either the Slicers Vertical or Slicers Horizontal field. If you have something with a long list of values, put it in Slicers Vertical. Items with short lists should go into Slicers Horizontal.
When you add slicers using the PowerPivot Field List, the PowerPivot add-in actually shifts the pivot tables down and/or right to make room for the slicers. PowerPivot decides how many columns would look best in the slicer. It does not choose a different color for each slicer, but you can easily do that using the Slicer Tools Options ribbon tab.
Be default, PowerPivot makes the slicers filter all the pivot tables and pivot charts. Someone who is very proficient in Excel might figure out how to achieve this in regular Excel, but PowerPivot does it automatically.
In the following figure, four slicers are driving both the pivot chart and the pivot table.
)
To build the figure, follow these steps:
- Drag Mall Developer to Slicers Vertical. Both pivot elements are shifted right for the slicer.
- Drag Region, Division, and DM to Slicers Horizontal. Both pivot elements are shifted down for the slicer.
- You will notice a bounding box around the three slicers at the top. Go to the right edge of the bounding box, click and drag to the right to make more room for the slicers.
- Click each slicer and choose a different color from the Slicer Styles gallery on the Slicer Tools Options ribbon tab.
As you choose from the slicers, both the pivot chart and the pivot table will update.