Glide Training Logo

A Clever Use of Slicers in an Excel Pivot Table

  

Written by Jane Hames

I have posted a how-to guide about slicers before, but Excel Pivot Tables are such a popular tool for data analysis and people can become a bit attached to them!

Using the Excel Pivot Table Slicer with Groups

On a recent Advanced Excel training course, I was asked by a buying assistant how to use a slicer to filter based on a date. However, instead of the actual date of the order, they want to be able to filter on the month, quarter or year without the date, month, quarter or year being displayed in the Pivot Table.

I love to solve these kind of problems, and with a bit of creative thinking and our trusty slicer tool, this is what I came up with:

Ensure that you are working in the latest Excel format (if you are in Compatibility Mode, you will not be able to use Slicers).

    1. Create your PivotTable with a date field in the row area. Mine looks like this:

excel pivot tables

    1. Click on the Group button.

group button

    1. Select two groups such as months and quarters or quarters and years. (You need to create two groups in order to have the second one appear as a field in the field list.) Our Slicer will be based on this second field in the following steps.

excel pivot tables

    1. Refresh the Pivot Table and click into the date field on which you wish to base the Slicer. (I clicked into the Quarters field.)
    2. Click on the Insert Slicer button.

excel pivot tables

    1. On the Slicer, select the item to filter by (I clicked on the Quarter I was interested in).

excel pivot tables

  1. You can now use the field list to remove the date fields from the PivotTable if required. Your second “group by” field will still be available in the field list, so you can still use its Slicer.

previous post: Splitting Text Into Separate Columns

next post: Confused by the symbols used in Excel formulas?

Why choose Glide?

Our Unique Approach

Our Unique Approach

We work on a bespoke, requirements-led basis rather than offer a set range of courses. We will work with you to identify your specific requirements and objectives before any training is delivered. That way you can be sure that training will always deliver exactly what you're looking to achieve.

View details »

Our Low Prices

Our Low Prices

We deliver all our on-site training at our customers' premises. Not only does that avoid any travel inconvenience for your team, but also it means we don't have the overheads required to maintain a dedicated training centre - and in turn we are able to pass these savings on to you.

View details »

Our Expertise

Our Expertise

Our trainers have designed and delivered IT training for a wide range of organisations and people including accountants, lawyers, data analysts, sales consultants and buyers. Clients benefit from our our ability to find creative solutions to their problems based on many years of experience.

View details »

Balderton Logo
Kreston Reeves Logo
National Institute of Health Research
England Rugby
Caravan and Motorhome Club Logo
Bird and Blend Logo
Ropetackle Logo
RBLI Logo
Farnborough Airport Logo
Kroll Logo
The Apprenticeship College Logo
Shoreham Port Logo
Adur and Worthing Councils Logo
Balderton Logo
Kreston Reeves Logo
National Institute of Health Research
England Rugby
Caravan and Motorhome Club Logo
Bird and Blend Logo
Ropetackle Logo
RBLI Logo
Farnborough Airport Logo
Kroll Logo
The Apprenticeship College Logo
Shoreham Port Logo
Adur and Worthing Councils Logo