Sort by Fiscal Month in a Pivot Table

Does your business have a fiscal year that starts in a month other than January? In a pivot table, there’s no built-in way to group or sort the dates by their fiscal month number. See how to calculate those numbers in the source data, and then sort the data by fiscal month in a pivot table, with the month name included.

Fiscal Year Start Month

In Excel, you can use formulas to calculate the fiscal year, quarter or month.

In this example, the fiscal year starts in April, so 4 is typed in a cell is named FYStart. We’ll use that name in the fiscal year calculation formula.

Calculate Fiscal Year

In the source data, there is a column named FY, where the fiscal year is calculated in each row, using this formula

=YEAR([@OrderDate]) + (–MONTH([@OrderDate]) >= FYStart)

The formula calculates the calendar year for the order date
If the order month is greater than or equal to the fiscal year start month (4), 1 is added to the order year.

NOTE: The two minus signs are a double negative, and they convert a TRUE result to a 1, and a FALSE result to a zero.

Fiscal Month Lookup

In this example, there is a fiscal month lookup table. It shows the month name, calendar month number, and fiscal month number. This formula, starting in cell F2, calculates the fiscal month number:

=IF(E2<FYStart,12,0)+(E2-FYStart+1)

The list of fiscal month numbers, in F2:F13, is named FM_List. We’ll use that name in the fiscal month calculation formula.

Calculate Fiscal Month

In the source data, there is a column named FM, where the fiscal month is calculated in each row, using this formula:

=INDEX(FM_List,MONTH([@OrderDate]))

The INDEX function returns the fiscal month from the FM_List range, based on the month number of the order date

Fiscal Month in Pivot Table

After you calculate the fiscal year and fiscal month in the source data, you can use those fields in a pivot table, to summarize the data.

In this example, fiscal year (FY) is in the column area, and fiscal month (FM) is in the row area. Because both fields are numeric, they sort correctly.

Show Fiscal Month and Month Name

Someone asked me how to show both the fiscal month number and the calendar month name in a pivot table, to make things clearer.

To do that, I added another column (FMNm) in the source data. That column has a formula that combines the:

2-digit fiscal month number ( @FM), and
3-digit month name for the OrderDate.

=TEXT([@FM],”00″) & TEXT([@OrderDate],” mmm”)

Sort by Fiscal Month with Name

Next, I refreshed the pivot table, and removed the FM field. I added the new field, FMNm, in the Row area, and it sorts the months correctly.

Sorting Text “Numbers”

Because the FMNm field is text (01 Apr), instead of numeric (1), it’s important to use 2-digit numbers, with leading zeros, where necessary. Otherwise, Excel would sort them like this, with all of the “1” months listed first, followed by the other month numbers

Get the Sample File

To get the sample file to sort by fiscal month in a Pivot table , go to the Fiscal Year in Pivot Table page on my Contextures website.

The zipped file is in xlsm format, and contains macros that let you show a specific fiscal year in one of the pivot tables. Enable macros when you open the file, if you want to test that macro.

_____________________