Excel graph to show months with data instead of full year

[Djani]

Dear all,

Goal is to let my graph only display the months with data on the (X) axis instead of showing the full year. See image down below.

I have played with the "Format Axis" button --> changed to text/date axis or whatever, but I am not able to find a solution for this challenge.

I can solve this by deleting the data and dragging the formula to the next month whenever I have the data for it, but that's not what I want.

Does any of you have any tips/tricks?

Yours sincerely,

Djani

 

[thisoldman]

Try using an Excel date for the month. Instead of the text, "APRIL", in the month header cell, enter a real date for the first of that month, then change its display by using the custom number format "mmmm" (without the quotes).

 

[Djani]

Thanks for the quick reply Thisoldman. I replicated what you suggested me to do, but it's not working unfortunately!

 

[thisoldman]

Which are you using, a line or a scatter(xy) chart?

I used this simple data set for a line chart when I tested:
[TABLE="class: grid, width: 250, align: left"]
<tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64, align: right"]April[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD="align: right"]10[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD]Berry[/TD]
[TD="align: right"]20[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD]Cherry[/TD]
[TD="align: right"]30[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
</tbody>[/TABLE]

 

[Djani]

I'm using a line chart. The data of the months that are not yet "available", will also be displayed as "#N/A". I believe that covers step 1.
However, the headers are indeed the problem, not the actual data itself. For some reason I am unable to manipulate it so the "X" axis only shows the month(s) with data.

This is what I get after following your advice:

 

[thisoldman]

I'm sorry for not replying sooner.

I assume the month headers start in F4. With an Excel date in F4, in G4 try
=IF(ISERROR(G5),"",DATE(2015,MONTH(F4)+1,1))
then copy and paste the formula to the right.

Or
=IF(ISERROR(G5),"",EDATE(F4,1))

 

[Djani]

Good morning Thisoldman,

Appreciate your time and effort that you put in this. The formulas work - they give the appropriate monthnames instead of the numbers. However, this doesn't change the axis. It still shows all months of the fiscalyear, even though there is only data for April?

E.g. if I have the data of May, I want the graph (axis) to only show April until May. It can be done by removing the headers manually, but that's not what I'm looking for.

Is it even possible to do that?

Yours sincerely,

Djani