0

I have one monthly data series in one spreadsheet and one quarterly in a different spreadsheet. What is the "smartest" way to plot them together in one graph (in Excel)? Its quite a lot of data, so I would like to avoid fiddling with it manually.

EDIT: The data is laid out in the following way:

Monthly:

2011-12-01  1246.91
2011-11-01  1251
2011-10-03  1131.21
2011-09-01  1219.12
2011-08-01  1292.59
2011-07-01  1320.64
2011-06-01  1345.2

Quarterly:

1947q1  237.2
1947q2  240.4
1947q3  244.5
1947q4  254.3
1948q1  260.3
1948q2  267.3
1948q3  273.8
1948q4  275.1

We can assume that 1947q1 is the same day as the corresponding monthly data for January.

3
  • Can you give us an idea how the data is layed out?
    – CharlieRB
    Dec 30, 2011 at 13:58
  • @CharlieRB, thanks for the suggestion - I added it to the question.
    – Grzenio
    Dec 30, 2011 at 14:13
  • The Community bot randomly stirs up old questions without accepted answers and recirculates them. This one popped up from 3 yrs ago. This is a unique, user-specific requirement. There is no obvious relationship between the monthly and quarterly data, which is half a century apart. "What is the smartest way to plot them together in one graph" depends entirely on what you are trying to illustrate, which only you know. Without something like a mockup or very clear description, nobody can answer this. Do you still need an answer? If so, can you provide clarity?
    – fixer1234
    Dec 8, 2014 at 6:00

3 Answers 3

4

I assume the time span of the quarterly data corresponds to that of the monthly data. I extended the monthly data to include an entire year, and made up corresponding quarterly data, and plotted each separately:

Monthly and quarterly data and charts

I like bars for the quarterly data, because I can make them wide enough to span the three months of monthly data for each quarter.

To make the chart, start by selecting the monthly data and inserting a line chart. Excel automatically sorts the dates, so they are plotted increasing from left to right (Chart 1 below).

Select and copy the quarterly data, select the chart, and use Home tab > Paste dropdown > Paste Special to add the data as a new series, series in columns, series names in first row, categories in first column. The new series isn't visible (yet).

Select the new series (you can't see it, but if you select the visible series and click the up arrow key, you'll select it), and format it to assign it to the secondary axis. You still can't see it, but Excel has added a secondary vertical axis (Chart 2).

Use the "+" skittle floating beside the chart (Excel 2013+) or the ribbon buttons to add the secondary horizontal axis (Chart 3). Now we're getting somewhere.

This axis isn't automatically sorted, since Excel doesn't recognize 2011Q4 as a date. So format the secondary horizontal axis: check Categories in Reverse Order, and check Axis Crosses at Automatic (Chart 4). Don't worry about the different Y axis scales.

Right click on the quarterly data series in the chart, select Change Series Chart Type, and select the clustered column type (Chart 5). Don't worry that the bars are stalactites instead of stalagmites.

Format the secondary vertical axis (right edge of chart) and check Crosses At Automatic. This moves the quarterly axis and its labels to the bottom of the chart (Chart 6).

Hide the quarterly axis: format it so that it uses no line, and its label position is No Labels (Chart 7).

Delete the secondary vertical axis. Now both series use the primary axis for scaling (Chart 8).

Finally format the bars. I used a gap width of 20% so they each span three months of the line chart. I used a transparency of 25% so the bar fill color wasn't too bold, and so you can see the gridlines behind them. I added data labels to the bars, using the Inside Base position and Category Name instead of Value (Chart 9).

enter image description here

1
  • 1
    Thanks this is EXACTLY what I needed! There was only one difference with my data: in the first few quarters there were negative values. This puts the X axis labels in the middle of the chart, which means you can't read the dates. After hunting around I found the solution. Right click the axis labels, choose format axis, scroll down to Label Position and set to Low. This moves all dates to the bottom of the chart.
    – Alex
    Apr 2, 2020 at 23:40
1

Dealing with a similar challenge. What you want to do is build a scatter plot instead of a line plot. Scatter easily graphs time series of different frequencies because you can specify different x axis series for different y-axis time series.

In the insert tab insert a scatter plot (probably a straight line plot in your case). Right click on the plot and select the data series you want setting the time frames associated with each series. You can then right click on the axis and set the incremental unit max and min values. it will take a little trial and error but should be pretty straight forward.

Good luck, posting in part for future users with the same question since it isn't intuitive.

0

First you have to decide what do you want to plot. For instance, do you want a monthly or quarterly chart?

If you want a monthly chart, you need to decide wheter the quarterly data will be paired to the monthly data by repeating the quarter's value each month, by charting just the end of period, or something else.

If you want a quarterly chart, you need to decide wheter the monthly data will be paired to the quarterly data using the quarterly average, the end of period value, or some other transformation.

This decisions will depend on what you need to chart - there is no simple answer.

As on how to pair the data, use a pair a formulas to create an identifier on which you can perform lookups. Like:

=year()*10+month()  and pass a date as parameter
=value(replace("2003q2";"q";""))

both will yield months in the 20031 format, making it easy to use lookup functions.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .