How do I graph a quarterly time-series in Excel?

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

Guest

How do I graph quarterly data which appears in Excel with gaps between the
mon this, like the first, and second columns below (the third column is
monthly):

Do I have to clean the data first, or is there a trick?

579.836 721.12 567.86 117.45 95.398 23412
_ _ _ 120.08 _ 23913
_ _ _ 120.83 _ 25196
586.415 732.82 575.79 121.24 _ 34446
_ _ _ 121.7 _ 26057
_ _ _ 122.04 _ 31790
584.511 738.72 580.24 122.16 _ 28815
_ _ _ 122.27 _ 30638
_ _ _ 122.29 _ 15793
594.252 744.45 587.69 122.44 _ 22563
_ _ _ 122.28 _ 27764
_ _ _ 122.25 _ 24114
597.912 750.01 589.68 122.12 100 18023
_ _ _ 124.94 _ 22988
_ _ _ 125.24 _ 23650
602.364 743.68 590.22 125.24 _ 23919
_ _ _ 125.22 _ 24943
_ _ _ 125.27 _ 24158
614.051 748.56 593.71 125.15 _ 21822
_ _ _ 125.06 _ 31242
_ _ _ 125.21 _ 16168
620.186 753.08 595.89 125.17 _ 20747
_ _ _ 125.43 _ 25912
_ _ _ 125.41 _ 24793
622.375 754.77 600.05 125.35 102.623 22105
 
Do you have a column with month names and a row with series names? Make
the data look like this (assuming "_" means a blank cell):

Item 1 Item 2 Item 3 Item 4 Item 5 Item 6
Jan 05 579.836 721.12 567.86 117.45 95.398 23412
Feb 05 _ _ _ 120.08 _ 23913
Mar 05 _ _ _ 120.83 _ 25196
Apr 05 586.415 732.82 575.79 121.24 _ 34446
May 05 _ _ _ 121.7 _ 26057
Jun 05 _ _ _ 122.04 _ 31790

The top left cell should be blank. Select this whole range, including
months and series names, and make a chart with the chart wizard. The
data is spaced appropriately. To connect the quarterly markers, go to
Options on the Tools menu, click on the Chart tab, and select Plot Empty
Cells As: Interpolated.

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