New Timeline Slicers in 2013

January 9th, 2013 by datapig Leave a reply »

Happy New Year (nine days late)! A new year means a new version of Excel (at least this year it does). Excel 2013 will be coming out soon, so I’ve switch to working with it exclusively.

.

Over the past few days, I’ve been playing with the new Timeline Slicers in Excel 2013. The Timeline slicer works in the same way a standard slicer does, in that it lets you filter a pivot table using a visual selection mechanism instead of the old Filter fields. The difference is the Timeline slicer is designed to work exclusively with date fields, providing a nifty way to filter and group the dates in your pivot table.

If you have Office 2013, you can place your cursor anywhere inside your pivot table, then go up to the Ribbon and select the Insert tab. There, you will see the Timeline icon.

.

Clicking the icon will activate a dialog box where you can select the date fields for which you want to create slicers.

.

Once your Timeline slicer is created, you’ll see a slicer that allows you to filter the data in your pivot table using a scrollbar-like data selection mechanism.

So clicking on April slices the data in the pivot table to show only April data.

.

You can expand the range on the Timeline slicer to include more data in the filtered numbers.

.

Want to quickly filter your pivot table by quarters? Well that’s easy with a Timeline slicer. Simply click the time period dropdown and select Quarters. You also have the option of switching to Years or Days if needed.

.

.

There are a couple of things to consider when using a Timeline Slicer.

  • A Timeline slicer can only be created with a field where ALL the data is formatted as a date. It’s not enough to have the field contain just a few dates. All the values in the field must be a valid date; formatted as such. If even one value in the field is blank or not a valid date, Excel will not create the Timeline slicer.
  • Timeline slicers are not backwards compatible; meaning they are only usable in Excel 2013. If you open a workbook with Timeline slicers in Excel 2010 or previous versions, the Timeline slicers will be disabled.
  • Like all slicers, they do take up a good bit of dashboard real-estate, but this can be managed by clever formatting.
  • As far as I can tell, there is no way to set Timeline slicers to not print. They WILL print with your dashboard, which may be annoying or helpful (depending on your politics). J-Walk points out that you CAN prevent slicers from printing. Open the Timeline task pane, and expand the Properties section to reveal a Print Object checkbox. Thanks John!

.

All that being said, I actually like the new Timeline slicers in 2013. They are a definite improvement over standard slicers in 2010 which were useless when it came to dates. In 2010, you simply got a list of dates. With Timeline slicers in 2013, users have the ability to dynamically change their time dimensions with ease. Plus, they work continue to work when publishing to Excel Services.

.

I’ll be back tomorrow to talk a bit more about Excel 2013.

Advertisement

11 comments

  1. Jon says:

    Did they fix (finish) the sparklines?

  2. datapig says:

    Jon: Unfortunately, there has been no major improvements to sparklines.

  3. Actually, you can prevent a Timeline from printing. Use the Format Timeline task pane, and expand the Properties section to reveal a Print Object checkbox.

  4. datapig says:

    John: Thanks! I’ve updated the post.

  5. AlexJ says:

    Mike – you were right, it WAS the lamest apocalypse ever.

    My workaround in 2010 is to create an extra field in the dataset which has only the month value (normalize the transaction date to the first day of the month). That is the value I use in my slicers and pivot filters

  6. Hi,

    The timeline slicer can be created with blanks appearing in the Date Field. I just tried it and it works just fine.

  7. Danny says:

    Does the timeline support non-standard month definitions? “Fiscal months” such as the 22nd through 21st?

  8. datapig says:

    Danny: No – Calendar-based paradigms only.

  9. Jon says:

    Those gotchas at the end really get you – I didn’t read the whole post and ran into every problem you mentioned! I wish they would have done something like this a long time ago, it definitely makes it easier to use pivot tables, and more worthwhile. Even the pivot charts are much sleeker.

  10. Brandy says:

    I have found that the Timeline slicer is not functioning in IE 11 in SharePoint. Has anyone else seen this? I am trying to find out if this is an Excel Services issue, and if so, when it might be fixed.

Leave a Reply