When working with large pivot tables, you often have to forcefully simplify them, filtering some of the information so as not to drown in numbers. The easiest way to do this is to put some fields in the filter area (in versions before 2007 it was called the page area) and select only the necessary values from the drop-down lists:
The disadvantages of this method are obvious:
- When multiple items are selected, they are not visible, but the text “(multiple items)” is visible. Never user friendly.
- One report filter is hardwired to one pivot table. If we have several pivot tables (and usually the matter is not limited to one), then for each (!) You will have to create your own filter and for each you will have to open it, tick off the necessary elements and press OK. Terribly inconvenient, I even saw enthusiasts who wrote special macros for this purpose.
If you have Excel 2010, then all this can be done more gracefully – using slices (slicers). slices is a convenient button graphical representation of interactive report filters for a PivotTable or Chart:
The slicer looks like a separate graphic object (like a chart or a picture), is not associated with cells, and is displayed above the sheet, which makes it easy to move it around. To create a slicer for the current pivot table, go to the tab Parameters (Options) and in a group Sort and filter (Sort and filter) click the button Paste Slice (Insert slicer):
Now, when selecting or deselecting slicer elements (you can use the keys Ctrl и Shift, as well as swiping with the left mouse button pressed to select in bulk) the pivot table will display only the filtered data for the selected items. An additional nice nuance is that the slice in different colors displays not only selected, but also empty elements for which there is not a single value in the source table:
If you use multiple slicers, this will allow you to quickly and visually display the relationships between data elements:
The same slicer can be easily linked to multiple PivotTables and PivotCharts using the Parameters (Options) button PivotTable Connections (Pivot table connections)which opens the corresponding dialog box:
Then the selection of elements on one slice will affect several tables and diagrams at once, perhaps even on different sheets.
The design component has not been forgotten either. To format slicers on a tab Constructor (Design) There are several inline styles:
…and the ability to create your own design options:
And in the “pivot table – pivot chart – slice” combination, all this looks absolutely wonderful:
- What are pivot tables and how to build them
- Grouping numbers and dates with the desired step in pivot tables
- Building a PivotTable Report on Multiple Ranges of Source Data
- Set up calculations in PivotTables