pivot table

Public Schedule Face-to-Face & Virtual Instructor-Led Training - View dates & book

• Home
• Courses
• Promotions
• Schedule
• Formats
• Our Clients

# Pivot Table

Resolved · Low Priority · Version 2013

Silvia has attended:

### Pivot Table

How can I arrange Months based on financial year April-March instead of Jan to December?

### RE: Pivot Table

Hi Silvia,

Thank you for your question. Here is how you can show years, quarters and months in Fiscal Year order:

First, insert a new column in your original Pivot source data table and write the following formula:

=YEAR(A2)+(MONTH(A2)>=4)

This formula calculates the year of the date in cell A2. Then, it calculates the month of the date in cell A2, and checks to see if the month number is 4 or higher. If the month is less than 4, then zero will be added to the year, otherwise 1 will be added.

If you also want to group the pivot table dates by the fiscal quarter, you can add another column to the pivot table source data. Use this formula to calculate the fiscal quarter, if the fiscal year starts in April.

=CHOOSE(MONTH(A2),4,4,4,1,1,1,2,2,2,3,3,3)

The Choose formula calculates the month of the date in cell A2. Based on the month number, the formula selects the appropriate fiscal quarter number from the numbers that follow. For example, if the month is June, the month number is 6. In the Choose formula, the sixth number is 1, so June is in fiscal quarter 1.

Thirdly, if you want the months to show as text, you could create a lookup table as shown below:

1 January
2 February
3 March
4 April
5 May
6 June
7 July
8 August
9 September
10 October
11 November
12 December

Then, add another column in the pivot source data and use a VLOOKUP to list the months:

=VLOOKUP(MONTH(A2),\$N\$7:\$O\$18,2,1) where Column A holds the dates and \$N\$7:\$O\$18 might be where the above lookup table is entered.

Once you have created all the above, you can bring the new fields into your Pivot Table and you will have the dates arranged in Fiscal order.

I hope this helps.

Kind regards
Marius Barnard
STL

Tue 27 Oct 2020: Automatically marked as resolved.

Training courses

Training information:

 Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions What does 'Resolved' mean? Any suggestions, questions or comments? Please post in the Improve the forum thread.

### Excel tip:

When I scroll a long way down the screen from a selected cell, I can return to that cell with the Ctrl+Back Space shortcut. The active cell now appears in roughly the middle of the screen.

Shift+Back Space does something similar. Scroll down from the active cell and Shift+Back Space returns me to it and puts the active cell at the top of the screen; scroll up from the active cell and Shift+Back Space returns me to it and puts the active cell at the bottom of the screen.

Note also, that while Ctrl+Back Space will return me back to a selected range, Shift+Back Space only ever returns me to the active cell, which is normally at the top left-hand corner of any selected range.

View all Excel hints and tips