MrExcel.com - Your One Stop for Excel Tips & Solutions

Pivot Table Timelines Forget What Year it Is

March 16, 2018 - by

Pivot Table Timelines Forget What Year it Is

Today's bug in Excel was discovered by Mynda Treacy and involves the timeline slicer in Excel. Mynda was working on videos for one of her awesome courses and found a pivot table where the timeline slicer kept adding new years.

To start, create a data set with dates running from January 1 2017 to December 31 2018. Make sure to include those two dates (the bug is different if your data ends on December 30 instead of December 31.

Start with a data set that spans two complete years
Start with a data set that spans two complete years

Create a new pivot table from this data.

Drag Revenue to the Values area of the pivot table. Drag date to the Rows area.

Depending on your version of Excel and your Excel Options, you will see one of two things.

If you see Years, Quarters, and Date across the top, Excel has auto-grouped your dates. Press Ctrl + Z to undo the grouping.

If Excel groups your dates, ctrl+z to undo.
If Excel groups your dates, Ctrl + Z to undo.

Select any cell in the pivot table. From the Analyze tab, click Insert Timeline. In the Insert Timelines slicer, choose Date.

Add a timeline
Add a timeline

Check out the timeline. Your pivot table ends at 12/31/2018, yet the timeline goes all the way out to December 2019.

The timeline slicer should end in 2018
The timeline slicer should end in 2018

To make it easier to see, open the Months dropdown and change to Years.

The timeline thinks there is an extra year.
The timeline thinks there is an extra year.

Mynda discovered something more. Choose the first date cell in the pivot table. On the Analyze tab, choose Group Field. In the Grouping dialog, choose Days, Months, Quarters, Years.

Visit the Grouping dialog box and change anything
Visit the Grouping dialog box and change anything

When you click OK, the Timeline Slicer grows to include another year.

Where did 2020 come from?
Where did 2020 come from?

Every time you visit the Grouping dialog, the timeline grows by another year.

Kudos to Mynda Treacy for discovering this odd bug. She reported it to the Excel team, and it will likely get patched within a month or so. By the way, check out Mynda's awesome e-mail newsletter at MyOnlineTrainingHub.

Every Friday, I examine a bug or other fishy behavior in Excel.

Excel Thought Of the Day

I've asked my Excel Master friends for their advice about Excel. Today's thought to ponder:

"Structured references pointing to Tables are easier to understand: =[@Profit] makes more sense than =F2. An A1 reference to a cell doesn't ring a bell!"

Title Photo: Freestocks / Unsplash