presenting quarterly time series data

[Nicky]

I chart many time series of data, usually annual, financial year,
monthly or quarterly.

Monthly data chart well with time series on the x axis. For example,
setting the major unit to 12 months and minor to 1 generates a neat x
axis with a minor tick mark next to each data point, and a major tick
mark plus label every 12 months (eg chart 1 in attached).

I want to replicate this effect for quarterly data, with a minor tick
mark next to each data point, and a major tick mark and label at
regular intervals (mot necessarily annual).

I understand that it is not possible to chart quarterly data in a
column chart with the x-axis set as time series without excel skipping
the “missing” 2 months, so you end up with very skinny bars (chart 2 in
attached).

However, setting the x-axis format to ‘category’ means that the tick
marks no longer correspond to the data points, unless every point or
every second point is a major tick mark, which looks very busy for long
time series.

I’d guess there are two possible solutions – find a way to vary the
number of minor tick marks between major tick marks on the ‘category’
setting for the x axis, or find a way to customise the ‘base unit’ in
the ‘time series’ setting to quarters, rather than the day/month/year
options currently offered (I’d also find financial years helpful).

Any suggestions?

Attachment filename: quarterly.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=416014

 

[Ed Ferrero]

Hi Nicky,

I chart many time series of data, usually annual, financial year,
monthly or quarterly.

Try using a pivot chart. Pivot charts allow you to group date data
into quarters and years - they do a good job of formatting also.

To prepare a pivot chart, select the data you want to chart (make
sure that there are no blank dates, and make sure that all columns
have a heading), then use menu item Data - Pivot Table and Pivot
Chart report...

then check the option for Pivot Chart, click next, click next
again, click the layout button, drag the field button with your
date data to the row field area, drag the filed button with
your values to the data area...

then click OK, and click Finish on the next dialog.

You will get a pivot chart with the date heading in a button
below the chart.

Now to group the dates - right-click the date button and from
the drop-down menu select Group and Outline - Group...

Select Quarters and Years. Done.

If the above is not too clear you can see a full tutorial on pivot
tables at my website http://edferrero.m6.net/tutorials.html

 

[Nicky]

Thanks Ed,
A bit more complicated than I'd like, but it does the job