Sort Months by Fiscal Year in Excel

In a long while I thought of writing a post on Excel. It’s nifty, it’s quick and highly useful.

I am pretty sure, if you belong to a country that follows an unconventional Fiscal Year (eg. Apr to Mar or July to June etc..) you must have felt the need to sort your months in the Fiscal Year order rather than the Calendar Year.

In this post, I’ll show how to set the default sorting option for months that belongs to your fiscal year. And for this example I am going to use the sorting order as Apr to Mar (for India) and needless to say you’ll be able to customize this trick to your own fiscal month order.

 

Want to grab the video first?

 

Step 1 – Write Down all the Months in the Fiscal Order

1. Type down all the month names anywhere on the spreadsheet
2. Note that as of now I am using the abbreviated format for months – Jan, Feb so on…
3. In case I’d like to display the full month name (January, February), I also need to type that down in the fiscal year order.

 

Step 2 – Add the Months to Custom Sorting Options

1. Now go to File -> Excel Options
2. In Excel Options -> Advanced -> Scroll right at the bottom -> Edit Custom Lists
3. Add the cell references of the months -> Import -> Add
4. Then click on Done

That’s it and we are all set.. let’s test this out

 

Sorting by Fiscal Months in a Slicer

I just pulled together some random data in table format, where months are from Jan to Dec but when I make a slicer on Months, they get sorted by the Fiscal Year order by Default (Apr to Mar)

Voila 😎

 

Sorting by Fiscal Months in a Pivot Table

1. I created a Pivot Table and grouped the date column by months
2. You’ll see that by default the months will get sorted in the Calendar Year
3. But if you sort the Month in the Ascending Order, you’ll get your custom fiscal sort order – Apr to Mar 😎

 

Some more Pivot Table Awesomeness

1. Pivot Table Time Saving Tricks
2. Get top Values in a Pivot Table
3. Change Pivot Table Calculations with a Slicer
4. Change Pivot Table Calculations with VBA and Slicer
5. Set Default Layout for Pivot Tables
6. Create a Pivot Table in Power BI
7. Pivot Table Formatting Tricks