Tame your computer - slice and filter data

In her regular series for Cambridge Network members, software training expert Karen Roem offers handy tips to help you 'Tame your computer'. This week she describes how to 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 dialogue 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 only!

1 February 2016

Karen Roem offers software training and support through her company Roem Ltd.  Contact her by email   (Karen@roem.co.uk) or visit her website at www.roem.co.uk

Roem Ltd is a Learning Collaboration approved supplier, having come recommended by Cambridge Network member organisations. Network membership means you automatically have the right to book on to any LC course at any time, while some categories of membership actually include LC training units.

 



Looking for something specific?