Show month names in sequence in a pivot table when financial year runs from April to March

Question

Show month names in sequence in a pivot table when financial year runs from April to March

Steve Bavis 60

Hi

I need to report on a financial year that runs from April to March. using month numbers means users have to perform mental adjustments to understand that month 5 is actually August, not May.

How can I show the month names in the correct sequence in pivot table reports rather than using month numbers? The source data has a MonthName column that has the correct labels in it.

TIA.

Ashish Mathur 102.4K Reputation points Volunteer Moderator

2026-08-31T23:09:52.2666667+00:00

Hi,

You may use Custom sorting within Pivot Tables. To receive further help, please share some data to work with.
Steve Bavis 60 Reputation points

2026-09-01T15:54:06.1733333+00:00

Hi Ashish

Thanks for your answer. It does work, although it means manual intervention for every pivot table. I was hoping for a more automatic one. Until I can find such an automatic one, I will use custom sort.

Steve
Rory Archibald 19,055 Reputation points Volunteer Moderator

2026-09-01T16:42:37.7866667+00:00

If you are using the data model, you can specify another column to sort by, so you can create any sort order you like, which will apply to any pivot tables created from that model.
Steve Bavis 60 Reputation points

2026-09-08T14:01:27.94+00:00

I have a column with month name, but it defaults to sorting January to December. I'm looking for a way for it to start at April, without having to manually move Jan to March to the end of the pivot table.
Rory Archibald 19,055 Reputation points Volunteer Moderator

2026-09-08T17:42:28.4066667+00:00

What I usually do is add a fiscal month column, load the data to the data model and then make the month name sort based on fiscal month. Or use a full calendar table.

For example, your fiscal month can be calculated using a formula like (assuming a table with a column called Date):

=IF(MONTH([@Date])<4,MONTH([@Date])+9,MONTH([@Date])-3)

which will return 1 for April through to 9 for December, then 10 for January, 11 for Feb and 12 for March.

Then load the table into the data model and using the Manage data model option, you can set the 'Sort By Column' for your month name as shown below

Any pivots created from the model will then sort as you want.
Steve Bavis 60 Reputation points

2026-09-09T15:49:46.37+00:00

Thanks, Rory. Unfortunately, I am not doing anything as sophisticated as using data extraction. The data source is a table in the Excel file, going straight to pivot table. That data table has a column with the report month in (1 for April, etc.). The only way I get them in sequence is to use the month number, which means the user has to translate 1 to April etc., which is inconvenient.

Other than manually setting a custom sort sequence in each pivot table (clunky, especially for ad hoc enquiries) I don't see how to get this automatically.

Unless you know a trick to do that?

Steve
Steve Bavis 60 Reputation points

2026-09-09T15:52:47.73+00:00

Thanks, Rory. Unfortunately, I am not doing anything as sophisticated as using data extraction. The data source is a table in the Excel file, going straight to pivot table. That data table has a column with the report month in (1 for April, etc.). The only way I get them in sequence is to use the month number, which means the user has to translate 1 to April etc., which is inconvenient.

Other than manually setting a custom sort sequence in each pivot table (clunky, especially for ad hoc enquiries) I don't see how to get this automatically.

Unless you know a trick to do that?

Steve
Rory Archibald 19,055 Reputation points Volunteer Moderator

2026-09-09T15:53:44.53+00:00

If the data is already in a table, it's one click to load it to the data model. :)

Other than that, you could prefix the name with a 2 digit fiscal month number - e.g. '01 - April'
Steve Bavis 60 Reputation points

2026-09-09T17:00:37.3233333+00:00

Hi Rory. I'm using a MacBook and it seems for Apple the data models don't save. I can create a query, but when the data is updated, I have to rebuild the query, which is a pain. Unless I'm doing something wrong.

So I've edited the month names to prefix with a number to help the sorting. Thanks for that simple idea (doh!).

Steve
Rory Archibald 19,055 Reputation points Volunteer Moderator

2026-09-09T17:03:03.54+00:00

Ah - you posted in the Windows section not Mac. Data model won’t be an option for you sadly.

1 answer

Your answer

Ashish Mathur 102.4K Reputation points Volunteer Moderator

2026-08-31T23:09:52.2666667+00:00

Hi,

You may use Custom sorting within Pivot Tables. To receive further help, please share some data to work with.
Steve Bavis 60 Reputation points

2026-09-01T15:54:06.1733333+00:00

Hi Ashish

Thanks for your answer. It does work, although it means manual intervention for every pivot table. I was hoping for a more automatic one. Until I can find such an automatic one, I will use custom sort.

Steve
Rory Archibald 19,055 Reputation points Volunteer Moderator

2026-09-01T16:42:37.7866667+00:00

If you are using the data model, you can specify another column to sort by, so you can create any sort order you like, which will apply to any pivot tables created from that model.
Steve Bavis 60 Reputation points

2026-09-08T14:01:27.94+00:00

I have a column with month name, but it defaults to sorting January to December. I'm looking for a way for it to start at April, without having to manually move Jan to March to the end of the pivot table.
Rory Archibald 19,055 Reputation points Volunteer Moderator

2026-09-08T17:42:28.4066667+00:00

What I usually do is add a fiscal month column, load the data to the data model and then make the month name sort based on fiscal month. Or use a full calendar table.

For example, your fiscal month can be calculated using a formula like (assuming a table with a column called Date):

=IF(MONTH([@Date])<4,MONTH([@Date])+9,MONTH([@Date])-3)

which will return 1 for April through to 9 for December, then 10 for January, 11 for Feb and 12 for March.

Then load the table into the data model and using the Manage data model option, you can set the 'Sort By Column' for your month name as shown below

Any pivots created from the model will then sort as you want.
Steve Bavis 60 Reputation points

2026-09-09T15:49:46.37+00:00

Thanks, Rory. Unfortunately, I am not doing anything as sophisticated as using data extraction. The data source is a table in the Excel file, going straight to pivot table. That data table has a column with the report month in (1 for April, etc.). The only way I get them in sequence is to use the month number, which means the user has to translate 1 to April etc., which is inconvenient.

Other than manually setting a custom sort sequence in each pivot table (clunky, especially for ad hoc enquiries) I don't see how to get this automatically.

Unless you know a trick to do that?

Steve
Steve Bavis 60 Reputation points

2026-09-09T15:52:47.73+00:00

Thanks, Rory. Unfortunately, I am not doing anything as sophisticated as using data extraction. The data source is a table in the Excel file, going straight to pivot table. That data table has a column with the report month in (1 for April, etc.). The only way I get them in sequence is to use the month number, which means the user has to translate 1 to April etc., which is inconvenient.

Other than manually setting a custom sort sequence in each pivot table (clunky, especially for ad hoc enquiries) I don't see how to get this automatically.

Unless you know a trick to do that?

Steve
Rory Archibald 19,055 Reputation points Volunteer Moderator

2026-09-09T15:53:44.53+00:00

If the data is already in a table, it's one click to load it to the data model. :)

Other than that, you could prefix the name with a 2 digit fiscal month number - e.g. '01 - April'
Steve Bavis 60 Reputation points

2026-09-09T17:00:37.3233333+00:00

Hi Rory. I'm using a MacBook and it seems for Apple the data models don't save. I can create a query, but when the data is updated, I have to rebuild the query, which is a pain. Unless I'm doing something wrong.

So I've edited the month names to prefix with a number to help the sorting. Thanks for that simple idea (doh!).

Steve
Rory Archibald 19,055 Reputation points Volunteer Moderator

2026-09-09T17:03:03.54+00:00

Ah - you posted in the Windows section not Mac. Data model won’t be an option for you sadly.

Answer 1

Barry Schwarz 6,106

Since your data sorts by month numerically rather than alphabetically, it appears that you have actual date values but are displaying only the month name associated with the dates. You can sort this data in any order you want using a custom list. To create a fiscal year list for the data you describe:

Select the entire range to be sorted.
Open the Data tab.
In the Sort & Filter group, select the big Sort icon (looks like a box).
Specify the column containing the dates.
Select Cell Values in the Sort On field.
Select Custom List in the Order field
In the List entries box enter your list ("April, May, June, ..., December, January, February, March") and click Add.
Click OK.
The selected data should now be sorted in the desired order.

If you omit any months from the custom list, they will sort at the end.

Steve Bavis 60 Reputation points

2026-09-09T15:52:12.3766667+00:00

Hi Barry. It looks like you are thinking of a data table. I'm trying to sort in a pivot table. But thanks for the reply, anyway.

Steve