47. Videotutorial – Deal with Pivot Tables 7 – Filters, Slicers, Timelines

Another episode of tutorials Deal with Pivot Tables continues with the topic Filters, Slicers and Timelines. You will see how to filter simple conditions, find top values and especially use Slicers and Timelines to find what you are looking for. You can even filter in multiple Pivot Tables at once. If this guide has helped you, become a fan on Facebook and recommend this site to your friends, it can be useful for them too. You can get inspired also in previous tutorials, Slicers in Excel and Timelines in Excel.

Please note that this tutorial is presented in czech language, with english subtitles.

Source file can be downloaded here.

We are creating a pivot table for establishing the aggregate data, which are viewed from different perspectives. Therefore it is important to control options to filter out specific values. For this purpose, we have already showed in previous episode called the Report filter field. Now is the time to delve more deeply into filtering, I will show you simple filtering and Slicers and Timelines.

In our finished PivotTable from previous work we try to filter out few states, for example US and Canada. Simply click the arrow above the first column and tick the appropriate states, these conditions work on the same principle as simple filters in general tables. I’ll come back and filter now useful condition that finds only two countries with the largest value of the cost summary. I use the Filters menu values > Top 10 … and the first 2 items by value of the sum. You see? Restore Filter again.

Filtering can be even more fun with new tools the Slicer and the Timeline. Both of these tools have a similar basis and work with them is easier. Click to the table and find Analysis tab > Insert Slicer. We choose to filter the data by employee name. This is a Slicer that allows you to filter the values ​​just by clicking them in the list. To select multiple conditions, simply hold down the CTRL key and press this button (filter with a cross) to go back to all the possibilities.

Similarly, you can also set the Timeline, back through the Analysis tab, insert a Timeline that works for dates, in our case, only one field named Date of payment. The Timeline can then search for a specific term using simple drag on the axis. You can also choose what term is used as a unit on the axis, whether months, years or days.

Both of these filtering tools have their Options tab where it is possible to e.g. change their design. Interesting is also the ability to filter multiple PivotTables at once. If we had one source worksheet and liked to see more pivot tables, and each would show the data in a different way, Slicer and Timeline is a great way to filter them simultaneously, click on the Options tab > Pivot Table Connections here, then check which PivotTables to filter simultaneously.

You can see that filtering has many options, now you can test it yourself. Next time we will work with PivotTable chart.