Tame your computer – slice and filter data

6 Feb

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!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: