Pivot tables are great at summarising large volumes of data but even then, the data can sometimes still be too huge to deal with.  This is where filtering, slicers and timelines come in enabling you to work with a subset of the original data. They all effectively filter Pivot tables in different ways which are detailed below.

Filter in the pivot tables pane

The pivot table pane on the right-hand side of the screen allows for fields to be dragged into the filters box.  This produces a drop-down filter on the pivot table to view specific information.  Normally only one record type can be chosen at a time but if select multiple items is chosen from the drop-down produced then multiple selections can be made at the same time.  Several fields can be added to the filters box at the same time allowing the criteria for the data to be narrowed down as much as is necessary.

Image of the filter pane

Filter directly in the pivot table

Sometimes you may want a field to be in the pivot table to group by (rows or columns) but may not need to see all the different options for it.  The drop-down on the row or column labels from within the pivot table itself allows you to choose the data to view and the box that appears looks much the same as it does when a filter is applied to a standard table.

Image to show filter by column

Slicers

Slicers are another way to filter data but this time produces a floating dialogue box from which the data to be viewed can be selected.  By default, if a second option is selected the first is deselected but multiple records can be chosen using the CTRL or SHIFT keys. The multi-select button at the top right of the slicer box overrides this so now when you click on a second option the first stays selected too.  To remove the filter use the Clear filter icon at the top right of the slicer box to show all the data again.  To remove the slicer box totally click on the border of the box then hit the delete key.

Did you know that slicers can control more than one pivot table at a time? On the options tab that appears when you are clicked on the slicer choose report connections and select which pivot tables the slicer will control. The slicer can be on a different tab to the pivot tables providing the option of having a dashboard where all the specific data is selected which updates all the other sheets in the workbook if this is required.

Image of the slicer

Timelines

Timelines are specifically for filtering date related data. The start and endpoints can be dragged to change the range of dates selected.  Filtering is done by months by default but can be changed in the top right to Quarters, years or days.  Just like with the slicer the filter can be removed by using the Clear Filter icon.  To remove the timeline box totally click on the border of the box then hit the delete key.

Like slicers, timelines can also control more than one pivot table at a time.

Image of the timeline

Fort more information about how to create pivot tables go to https://www.laramellortraining.co.uk/pivottabletop3issues

Want to learn more about Excel then email lara@laramellortraining.co.uk to discuss how I can help or have a look at the Excel Courses I run.

MEMBERSHIPS

FOLLOW LARA