How to make chart showing year over year, where fiscal year starts July?

Question

Our fiscal year starts in July. I want to show year over year data as a chart below, but I want the month to start at July.

I created a Pivot Chart from the raw data.

I looked at More Sort Options and sorting manually, but I don't see a way I can drag chart axis labels around.

I had to create an axis called FYM which called:

• July (7) a value of 0,
• August (8) a value 1,
• September (9) a value of 2

But really, I'd like to start from July (7) and end at June (6).

Is there a way to do this?

Solution 1

Pivot charts don't allow much customization. They're useful for a quick look at the data, but for a presentation quality chart, you really should create a custom chart, using the pivot data as your source.

To get your Monthly axis labels, you just need a column containing the labels as part of your data table. Not knowing how your data is arranged, I put together a simplified data table to build a graph from. You might have all these columns in your pivot table. If not, it should be easy to create a similar table.

I used the short names of the months as the x-axis labels, but you could use numbers, if that's what you need.

To make the graph, select the data in the table, leaving out the headers. That would be A2:E13 in the table above. Then choose Clustered Column from the Column section of the Charts tab. Resize the graph to make it bigger, and right-click on the various elements of the graph to format them.

In the graph above, I made the text of the axis labels and legend bigger, added axis and chart titles, and changed the series names from Series1, etc. to the Fiscal Year.

It also improves readability to format the bars of each series and remove the shadow that Excel adds by default.

If you decide to go down this path, and run into any problems, please feel free to comment back here and I'll see what I can do to help.

I hope you find this useful, and best of luck.

Solution 2

You can group the dates in a pivot table in different increments, such as by year and quarter. However, if you want to group the dates by your company’s fiscal year, which starts in July, there’s no built-in way to do that.

You can use a workaround solution, by adding in the pivot table source data a column with a formula that calculates the fiscal year, quarter or month, and then adds that field to the pivot table.

If the date is in cell A2 :

Use the following formula to calculate the fiscal year

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

Use this formula to calculate the fiscal quarter

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

Use this formula to calculate the fiscal month

=CHOOSE(MONTH(A2),7,8,9,10,11,12,1,2,3,4,5,6)

References :

• Grouping Pivot Table Dates by Fiscal Year
• Grouping Dates in a Pivot Table VERSUS Grouping Dates in the Source Data

Comments

[Sun]

I see. So just listing July first, you can set up the chart to look like your image? I'm pulling the results from SQL output, open to Excel, create pivot table, then make chart. When I make PivotTable, it is ordered by number 1 (jan), 2 (feb), etc. How do I make PivotTable show 7 (july) first?

[Bandersnatch]

That's right, Excel plots the data in the order that it is given. Pivot Tables are useful, but they add an abstraction layer that makes it harder for users to really understand what is going on. I would un-pivot your table, or create a new one.

[Bandersnatch]

If you need to keep your table as a pivot table, you could add another column with: 7...12,1...6, and sort the data on that column. That will order your data by the months of your fiscal year, and you should be able to get the graph you want that way.

[Bandersnatch]

@Sun, are you going to use either of these solutions?

[Sun]

Not really. Issue for me is that I have 4 years of data I'm summarizing, so I want to use a Pivot Table & Chart. I'm not sure if I can do what you suggest with the pivot table and chart. I ended up creating another column called fym so that July is 1, June is 12. I ended just hiding those numbers with a white box. Hacky but worked.

[Sun]

I did something similar in the SQL, but couldn't figure out the "More Sort Options" in Pivot Chart for a column. Maybe its not something you can manually order on the Pivot Chart (and was meant for Pivot Table).