Excel Slicers for rolling periods, for example; the last 12 months, requires a column in your source data that looks up the date and compares it to today’s date to see if it falls into the current period.
Download the Workbook
Enter your email address below to download the sample workbook.
Watch the Video
Watch right to the end to see the bloopers!
Creating Excel Slicers for Rolling Periods
Below is my data: in column A I have the date for each record and in column C I use an IF function to compare today’s date (returned from your computer’s clock using the TODAY() function in cell F2), to the date in column A to see if it falls within the last 12 months.
The formula:
=IF(today-[@Date]<365,"Last 12 Mths",">12 Mths")
Note: today is a named range for cell F2 which contains the formula =TODAY(). This formula returns the date from your computer’s clock.
English Translation of the IF formula:
=IF (today’s date minus [the date in column A] ) is less than 365, then enter the text "Last 12 Mths", otherwise enter the text ">12 Mths"
Now we can build the PivotTable and or Pivot Chart and add a Slicer for the Rolling Period:
And because I’ve grouped my data by month I don’t have the day detail so I want to make it absolutely clear which period the chart is displaying by inserting a custom chart title that automatically displays the date range.
Warning: Don’t forget to refresh the PivotTable/Pivot Chart so that it displays the correct ‘last 12 months’.
If your PivotTable is pulling in the data from another system you could turn on the ‘Refresh data when opening the file’ in the PivotTable Options menu:
Please Share
If you liked this or know someone who could use it please click the buttons below to share it with your friends on LinkedIn, Google+, Facebook and Twitter.
Ryan says
My data is usually tracked by Year and Month – I do not track Days. Is there a way to do this with a separate year and month column or does the date have to be formatted year-month-day in the same column?
Mynda Treacy says
Hi Ryan,
You need a numeric field to check if the date in your data is less than 12 months ago and if you only have year and month then my guess is that’s a text field, not a numeric field. You could change it to a numeric field and use that, but you’d have to change the IF formula to suit, or you could just add a column that represents a proper date dd/mm/yyyy or mm/dd/yyyy.
If you get stuck please post your question and sample Excel file on our forum.
Mynda
Phillip says
Excellent tutorial–worked flawlessly. I’ve been using Excel for decades and have never used the Slicer and “structured tables”. Very useful and now I’ll start to use them.
Mynda Treacy says
Great to hear, Phillip 🙂
Rachel says
Hi Mynda, how did you insert a custom chart title that automatically displays the date range?
Mynda Treacy says
Hi Rachel,
I used a dynamic text label as described here: https://www.myonlinetraininghub.com/excel-dynamic-text-labels
Mynda
Rachel says
Hi Mynda,
Thank you for publishing this article and for your prompt reply. Learning how to use slicers to create a rolling 3-week PivotChart / learning how to create dynamic chart titles were both very useful skills to learn. You are a lifesaver!
Best,
RF
Mynda Treacy says
Glad you found them useful, Rachel 🙂
MF says
Hi Mynda,
I ran into a similar situation recently and tried to deploy a similar approach as you presented here. However I gave up and decided to use a not-so-effective approach simply because
“Warning: don’t forget to refresh the PivotTable/Pivot Chart so that it displays the correct ‘last 12 months’.”
Sadly but true, users of my report are not familiar with Excel. I can by no mean assure that they will “refresh” the Pivot Table after they change the variable of date (in my case, last 12 months depends on which dates user selected). Although Macro can do the job easily, the problem is ONLY when the macro is enabled and which requires USER’s co-operation. @_@
My not-so-effective approach is to create a Slicer of months in YYYYMM, so that user can select whatever periods they wish by simply click-and-drag. You can imagine how many icons could be there in a single slicer. This is not perfect but at least users are aware of the changes they made.
Cheers,
Mynda Treacy says
Hi MF,
On the upside, if you set the refresh to happen on File Open then you shouldn’t have this problem. I guess if the user is also adding the new data then you need them to refresh it.
If it weren’t for the Users… 😉
Mynda
MF says
HI Mynda,
Thanks for your suggestion… however that does not work in my case as my “rolling month” is based on the selection made by users, not on =TODAY().
MF