Graphing same data with different frequencies on same graph

[rar145]

I have from 1972-2015 adjusted to reflect annualized monthly and quarterly growth rates. How can I create a line graph that shows both the quarterly and monthly frequencies together? I am using Excel 2013. Please be thorough. I have tried extrapolating from responses given by others but keep having issues (for example, I tried creating the quarterly chart and then adding the monthly data by creating a secondary axis but this caused my monthly data to truncate. That is, i brought in the first 1/4 of data points.)

[MrShorty]

It would probably help us help you if you could post a sample file with some of those failed attempts so we could see what you have tried and better understand why it is not working.

The first question that usually comes up in these is how are the data arranged in the spreadsheet? It seems that a lot of charting questions really end up being about how best to arrange the data in the spreadsheet. One common way to arrange data in the spreadsheet would look like this:

Please Login or Register  to view this content.

Select this block and insert a line (or scatter) chart and format as desired.

If the dates in the first column are actual Excel dates (and not text strings that look like dates), a line chart could take advantage of Excel's "date axis". You will also want to look at your setting for "hidden or empty cells" to make sure that formats the way you want.

A few links to resources that may help further:
http://peltiertech.com/Excel/ChartsH...ValueAxis.html
http://peltiertech.com/line-charts-vs-xy-charts/
http://peltiertech.com/mind-the-gap-...g-empty-cells/

[rar145]

I have attached the data file here.

Whenever I use monthly data as a base and then try adding the quarterly data, Excel is treating the quarterly data points like monthly points so that my quarterly line "shrinks" to 1/4 of the appropriate length.

If I use quarterly as a base and then add the monthly data, Excel is truncation the monthly data and bringing in about 1/4 of the data points so that I have the first 1/4 of the monthly points being treated as quarterly points.

[MrShorty]

I don't use line charts and/or date axes very often. My preference is to use XY scatter plots.

A scatter plot might be an easy solution. I find that scatter plots are a lot easier to work with than other chart types when I want a different set of "x values" for each data series. Since the "x-values" field is a part of the select data dialogs in a scatter plot, adding the second data series is straightforward, and the x values should line up without much, if any, effort. The main disadvantage in this case to using an XY scatter plot is that it will not have the nice "date axis" option that works so well for date values.

If you insist on using separate x and y data for each data series, you should be able to use the secondary axis system to get what you want. Adding the second series is not quite as obvious. You will need to select the two columns -> copy -> paste special -> horizontal x values in 1st column. Then add the secondary axis system (both x and y axes). This will work. The main challenge with this is keeping the secondary and primary axis systems tied together so they make sense, since Excel does not offer a simple "link axis limits of the secondary axis system to the primary axis system" option.

I still think the best approach would be to arrange the data as I suggested in post #2. You did not comment on this idea, so I don't know how open you are to it. Using your existing quarterly data list as a lookup table, it should be as easy as adding a column to your monthly list with a simple vlookup() function. Something like =VLOOKUP(A13,$E$13:$G$213,3,false) copied down that column. FALSE in the 4th parameter will cause Excel to look for those exact dates in the quarterly list, return the appropriate value when that date is found, and return N/A if not found. If you read the "Mind the Gap" article, you will see that this is a good way to indicate points that the chart should ignore. Columns C and D then become the data series for you line chart, with column A as the horizontal axis data.

An additional note -- you might check your quarterly data. There appears to be several entries at the beginning that are monthly, and a large gap between 1972 and 1988. I wonder if that is a mistake, and those monthly entries are suppposed to be quarterly entries spanning that gap in time.

[rar145]

Yes - that dating issue was in an old file. I've fixed it. I will try both the options you have outlined (in your two posts) and let you know how it goes. Thanks for the input!