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 fasi pepa (slicers). fasi pepa 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 le faataamilosaga (Filifiliga) ma i se vaega Fa'avasega ma faamama (Sort and filter) kiliki le faamau Fa'apipi'i Slice (Insert slicer):
Now, when selecting or deselecting slicer elements (you can use the keys ctrl и gāoioiga, 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 le faataamilosaga (Filifiliga) faʻamau So'oga Pivot Table (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 Fausia (Mamanu) 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:
- O a laulau pivot ma pe fa'apefea ona fausia
- Fa'avasega numera ma aso fa'atasi ma le la'asaga mana'omia i laulau pivot
- Fausiaina o se Lipoti Pivot Table i le tele o vaega o fa'amatalaga puna
- Seti fa'atatau ile Pivot Tables