Tip of the week # 474
27 January 2016


Filter data in a PivotTable and PivotChart using Slicers (Excel 2010-2013) *

If you've been using PivotTables you probably have had the need to temporarily remove unnecessary and unrelated data. (If you've never worked with PivotTables, but use Excel as a database, you're missing out. Don't be put off by its complicated name!)

In earlier versions of Excel, you could only use report filters to filter data in a PivotTable report, but it was never easy to spot what you were actually filtering on. Since Excel v2010, you have the option to use Slicers, providing simple, intuitive buttons. In addition to filtering, slicers also make it easy to understand what exactly is shown in the report or chart. You can also use the slicers as "dashboards" on a separate sheet, which can be very helpful if you work with large workbooks.

Here's how:

  1. Click anywhere in the PivotTable report for which you want to create a slicer.
  2. On the Options tab, in the Sort & Filter group, click on the Insert Slicer button. (In Excel 2013 the button can be found on the new Analyze tab.) If the button is greyed out, you might have opened an Excel file that was created in an earlier version of Excel.
  3. In the Insert Slicers dialog box, select the check box(es) of the PivotTable fields you might want to filter by.
  4. Click OK.
  5. In each slicer window, click the field name you want to filter by. (To select more than one field, hold down CTRL, and then click the fields on which you want to filter.)

To clear the filter, click on the Clear Filter button in the top right hand corner of the filtered slicer window(s). (Look out for a funnel with a red x next to it.)

Finally, if you'd like to learn more about analysing your data using Excel's PivotTables, rather than complicated formulas, why not join me for the next half day Excel Further Use course, planned for the afternoon of 22 February... 97 pounds only! See msexcfu.php for full details.


* Unless stated otherwise, these tips were written for Microsoft Office 2010.