Line Graph Dates

[Tiffany1]

Hello,
Does anyone know how I can get line graph in excel to chart different date intervals?

For example, for a growth of $10K line chart starting on June 2012-June 2015, I would like the horizontal axis to read starting and ending date with calendar year ends for the other periods:
June 2012, Dec 2012, Dec 2013, Dec 2014, June 2015.

Currently in excel it just reads when I set it to 1 year intervals:
June 2012, June 2013, June 2014, June 2015

Thanks in advance for the help.

 

[Luke M]

Hi Tiffany, and welcome to the forum!

Can you post an example of the data you are working with? Are you using a true line chart, or is it an XY chart?

 

[Tiffany1]

Please see the attached example of my chart and what I am trying to duplicate. Thank you.

 

[Luke M]

Hi Tiffany,

We can do what you want, but it takes a little trickiness.
Instead of using the built-in axis, we can create an extra series, which has y value of 0, and a marker at each point along X axis where we want an axis label. You can set this up manually, or you could use formulas (I did the latter in attached). To do this, we also need to change the chart from line chart to XY.

After it's added, format the new line to now line, and a "+" marker symbol. Add a data label, and hide the labels on the existing horizontal axis. Voila! It looks like we have a nice custom axis.


Currently, the chart's axis is set to have automatic start/end points, which is why you see a small space to the left and right. If you don't like that, we can set the min/max points to be 4/1/2007 and 7/1/2015

 

[Tiffany1]

Thank you for your help and example, Luke. It is much appreciated. A couple of follow up questions:

Where do I go to change this?
Currently, the chart's axis is set to have automatic start/end points, which is why you see a small space to the left and right. If you don't like that, we can set the min/max points to be 4/1/2007 and 7/1/2015.

Also, is there a way to change the "+" marker symbol to just a vertical line "|" like it would appear on a normal chart?

Thanks,
Tiffany

 

[Luke M]

To change it, need to edit the horizontal axis. Easiest way to get there is to select the chart, then from Layout ribbon, change selection in top left

Then hit Ctrl+1 (shortcut key to format selected item)
Then you can set the min/max by typing in desired dates.



For the symbol, there's no build in vertical line, but if you insert a straight black line (from insert - shapes), you can then copy that shape, select the dummy series, and hit paste, and it will paste the shape as a new marker

 

[Tiffany1]

Thank you, this has been so helpful!