chartsmicrosoft excel
I have 3 time series data with wildly different data ranges. I can show two of them using a primary and secondary y-axis but that does not work well with the third data series.
In Excel is it possible to combine two charts such that they share the same x-axis, i.e. two different origins. Is there a limit – could three charts be stacked this way?
Thanks a lot
Well, I found what seems to be authoritative information about how series order is handled in Excel 2003 (http://peltiertech.com/WordPress/order-of-legend-entries-in-excel-charts/). It appears that there's really nothing I'll be able to do to get my secondary axis series to plot with lower z-order than my primary axis series.
I'm going to post and accept this answer to serve as future reference for anyone else who encounters this problem.
It is very easy to make a chart like this in Excel. You'll just need to change your data layout.
List all of your category labels are in a single column.
Create a column for each category type (e.g. equities, corporate, sovreign...)
For each category, just place the value in the appropriate categories column. Your data should look something like this:
Then, create a Clustered Column Chart Insert>Column>Clustered Column.
Select your data series, then Format>Series Option>Series Overlap and set the value to 100%.
Format to taste (e.g. move horizontal axis to low, remove grid lines, adjust percentage decimals to 0, etc...)
Your completed chart should look something like this:
FWIW, I'd consider a Bar Chart laid out in similar fashion (just insert Clustered Bar instead of Clustered Column). Cleaned up a bit, it would make your category labels easier to read (horizontal, rather than vertical) and can be compressed a bit easier into a smaller space.
Best Answer
As discussed briefly in the comments, there are at least two possible solutions:
Assuming three data sets like this:
First, a single chart with a log formatted axis. This will work well if your data are simple and/or your audience is educated/prepared to read and understand it. Simply re-format your axis to an appropriate log and you'll get this:
If you're not sure your audience will understand the log scale, you can use a panel chart approach. Here are three identical charts, each with one data set and the appropriate Y axis scale:
To create this effect, just create/format your first chart with your first series, then duplicate (or copy/paste) the first chart for each series. Then change the Y-series data and make sure the Y axis is formatted properly.