In Australia our financial year starts in July. Excel is set up to work with calendar years and we need to do some date gymnastics to have our reports start in July. Here is a hack for Custom Lists that can make some things better in Excel.

Custom Lists are hidden (one of my training attendees says buried) in the Options section of Excel.

Custom Lists are great for any lists you use frequently. I have written about them previously here.

Shout out to Alan Murray of https://www.computergaga.com/ I found this technique in his book Advanced Excel Success. His example was based on the days of the week.

I have a formatted table (click here for a post on them if they are new to you) see image below. The tables starts on 1 July 2021.

When I add a Slicer to the table, yes you can add a Slicer to a formatted table (click here for a post on Slicers if they are new to you), notice it starts from January.

Well, that’s frustrating.

There is a solution. Create a Custom List of months that starts in July. I have a separate list of abbreviated months with my preferred sequence.

Select this list (A2:A13) and click the File ribbon tab, then click Options (left-hand side), then click Advanced (left-hand side) and then scroll all the way to the bottom and click on the Edit Custom Lists button. In the old days the button was at the top of the Advanced listing.

Click the Import button in the Custom Lists dialog as below.

Click OK and OK and OK again.

Delete the existing Slicer and add it again and notice the difference.

Woohoo, it starts in July.

Warning

Custom Lists are stored on the computer you use, so if you want others to share you new found power they too must create their own Custom List. You only need to do it once on each PC /laptop you use.