Slicers are hugely popular in both Excel Essentials and Excel Pivot Tables training. We go for standard slicers in both of those sessions, but there is another type that is particularly fun to play with if you are working with dates; the Timeline Slicer.
Exercise File
Download today’s Exercise File to follow along:
This is… what else… some dessert sales! Desserts make their way into a lot of my training exercises (wonder why?).
The Dessert Sales sheet shows entries in checkbook style: date, dessert type, and then income and expense on each day.
The Dessert Pivot sheet is referencing data on the Dessert Sales sheet. You must have a pivot table with dates in order to use the Timeline Slicer tool. If you are not comfortable with Pivot Tables, check out my session Excel: Pivot Tables, Charts and Pictures, listed in MyTraining.
Create the Slicer
- Go to the Dessert Pivot sheet
- Click on the pivot table to activate Pivot Table Tools contextual tabs
- Click on the Pivot Table Tools Analyze tab
- In the Filter group select Insert Timeline
- Select Date and press OK
Look at this nifty Timeline slicer you just created!
Working with the Timeline Slicer
Just like your standard slicers, you can click on a month to narrow down information in the pivot table.
Notice you also have the ability to select a range of months by hovering your mouse between two months and clicking and dragging.
To clear the filter, click on the clear filter button at the upper right of the timeline slicer.
Timeline Tools
Click on the timeline to select it and notice this activates a Timeline Tools contextual tab.
Among other options, you have the ability to adjust the size of the Timeline and change the color to another available theme color.
Troubleshooting
Oh no, I sliced by a month and then accidentally deleted my slicer! How do I unslice my data?
Don’t forget that whenever you create a slicer, you are creating a shortcut to filter. Notice how a filter appears at the top of the row labels.
To clear this filter click on the filter button and select Clear Filter from “Months”.
Thoughts?
What do you think? How will you use the Timeline tool with your workbooks?
Congratulations, Power Users!
Congratulations to our newest Power Users! For the full gallery, and more information about the WSU Microsoft Office Power User Program, please visit: wichita.edu/poweruser
Glenn Gunnels
Jeremy Webster
