There’s no built-in way to create Excel Slicers for fiscal years, however the solution is easily achieved by adding an extra column to your source data to classify each date into its relevant fiscal year.
Download the Workbook
Watch the Video
Be sure to watch to the end for the bloopers!
Creating Excel Slicers for Fiscal Years
In Australia our financial year runs from July 1 to June 30 so I’ll use that as my example.
In columns A and B below is the data I want to analyse in a PivotTable and Pivot Chart; it’s the trading volume of some stocks by date.
And in columns E and F is my lookup table (with the named range tbl_fiscal_yr), that I’ll use to map the dates in column A into their Fiscal Period:
Note: both tables are formatted as an Excel Table and my dates are dd-mm-yy.
I'll add a column (C) to classify the dates in column A into their fiscal year. For this we can use a VLOOKUP formula with a Sorted List:
Note: if the formula arguments like [@Date] look odd to you it’s because they’re using the Excel Table’s Structured References.
Now that we have the new column (C) for the fiscal year we can use that field for the Slicer and then choose the fiscal year we want to display in the PivotTable and Pivot Chart:
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.