In last week’s video I covered how to use Timelines to filter Pivot Tables by months and quarters. After seeing that video I received an email from a viewer who said…“One thing that bugs me is when I have a column of individual dates in the source data, I can’t create a Slicer that shows months or quarters.”
That’s what I’m going to cover in this blog post and accompanying video…creating 2 Slicers from a dataset that includes a column of dates. One Slicer will display month names and the other will display quarters (i.e. Qtr 1, Qtr 2 etc)
One way to solve the problem would be to add a couple of additional columns to the source data, one that displays the months and one that displays the quarters, include those columns in the Pivot Table source and then base the Slicers on those columns. But sometimes you can’t amend the source data so that’s not the way I’m going to do it in this tutorial.
Step By Step Instructions
- Select a cell in the Pivot Table
- Drag the heading containing the dates from the Field List in the Pivot Table Panel into the Rows box. Don’t worry this is just temporary
- The Rows box now contains Months and Days
- Right click a cell in the Pivot Table containing a month name
- Select Group
- Deselect Days and select Months and Quarters
- Notice that Months and Quarters are added to the fields list in the top part of the Pivot Table Panel.
- In the top part of the Pivot Table Panel, untick Quarters and Months
- Insert a Slicer
- Tick Months and Quarters and click OK
To hide the month names and quarter names that are dimmed out because there is no data for those months/quarters, right click the Slicer and select Slicer Settings and tick the Hide Items with no Data box
Download a copy of the file(s) used in this video: https://share.zight.com/2NueywNB