Quarterly data with years on the x-axis

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I wish to know how to produce charts that use quarterly data that shows the
year only along the x-axis. And when the year is shown, have the position of
the year label centered between annual drop lines. Is this possible?
Thank you.
 
Start with some dummy year-based data:

Yearly
2000 0
2001 0
2002 0
2003 0
2004 0
2005 0

Make a column chart. There are no columns, because of the zero values, but
this series defines the axis. Now add your quarterly data (a sample snippet
shown below) to this chart as a new series.

Quarterly
2000 Q1 69
2000 Q2 75
2000 Q3 85
2000 Q4 97
2001 Q1 108
2001 Q2 117
2001 Q3 134

It's going to look goofed up, but let's fix it. Double click the added
series, and on the Axis tab, select Secondary.

Right click the chart and choose Chart Options (or get Chart Options from
the Chart menu). On the Axes tab, check the secondary X axis, and uncheck
the secondary Y axis. Now double click the extraneous axis as the top of the
chart, and on the Patterns tab, select None wherever possible to hide the
features of this axis.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Thank you Jon. I was able to follow your instructions and was successful.
However, I am interested in having drop-down lines appear on the chart with
the x-axis year labels centered between the drop-lines. Also to make things
just that more complicated, I want to include additional series and have the
chart type be a line chart. Is this possible?

DE
 
You should be able to add another series, and change it to a line series.
The trick is to make sure it is associated with the appropriate axis.

To add the series, copy the data, select the chart, and use Paste Special
from the Edit menu to add the data as a new series. Alternatively use Source
Data from the Chart menu, and use the Series tab.

To change the series type, select the series, then use Chart Type from the
Chart menu, and choose a line type for the selected series.

To associate the series with the appropriate axis, double click on it, and
choose Primary or Secondary on the Axis tab.

In order to get drop lines between the dates, you would need to have a
multiple column range for the category data:

2000 Q1
Q2
Q3
Q4
2001 Q1
Q2
Q3
Q4
etc.

But then you are obliged to show both the years and the quarters. The best
you could do is use outside or cross as the axis tick style.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Back
Top