Excel: Timeline Slicer

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

  1. Go to the Dessert Pivot sheet
  2. Click on the pivot table to activate Pivot Table Tools contextual tabs
    Pivot Table Tools Tab
  3. Click on the Pivot Table Tools Analyze tab
  4. In the Filter group select Insert Timeline
    Insert Timeline
  5. Select Date and press OK

Look at this nifty Timeline slicer you just created!

Timeline Slicer

Working with the Timeline Slicer

Just like your standard slicers, you can click on a month to narrow down information in the pivot table.

Month Selected, Pivot table showing only one month

Notice you also have the ability to select a range of months by hovering your mouse between two months and clicking and dragging.

Cursor between two months, ready to click and drag. double headed arrow visible

To clear the filter, click on the clear filter button at the upper right of the timeline slicer.

Clear filter button on slicer

Timeline Tools

Click on the timeline to select it and notice this activates a Timeline Tools contextual tab.  

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.

Filter button

To clear this filter click on the filter button and select Clear Filter from “Months”.

Clear filter option in pivot filter button dropdown

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

Glenn

Jeremy Webster

Jeremy Webster