So how do you fit multiple scales on a single excel chart? No, we are not referring about the primary and secondary scales on an Excel chart here, we are talking about adding a customized scale for each category in the chart.
Take for example the receipts portion of the U.S. federal budget – a data set with multiple categories such as individual income, social security, excise, estate and other taxes & duties.
Now while all these categories pertain to the same universe and are equally important in their own right, the quantum of each category may differ from others in terms of sheer magnitude. So for example while the receipts from the individual income tax might be $ 1,100 billion in a particular year, the corresponding figure for the estate & gift tax might only be $ 26 billion.
If you put compare both these data points using a single chart scale, it does not really help the reader appreciate the size and the magnitude of change for the smaller data points. Using a log scale is an option worth using in many cases but the log scale does suffer from two shortcomings:
a) Log scales do not lend themselves to easy comprehension.
b) When the difference in size of two data points is disproportionately large, a log scale may not always help.
So what about a chart with customized scales for each category – something like the one shown below. Methinks that a piece of beauty right there. (If you don’t agree with me, go away and don’t come back …. ever )
So open up your spreadsheets and put on your thinkers cap ’cause ladies and gentlemen we are going to ….
Make a Multiple Scale Chart in Excel
The data comes first, each and every time. So let’s generate the data grid for the chart. If you look at it carefully, a number of series have been generated. You can broadly classify them into three groups – the first that help us plot the actual data points, the second that help us plot the scales and the third ones that we use for the legend. The X and Y values for the actual plot values can be structured using formulas so that they fall between 0 and 10 (or any other scale that one may choose to use). (The trick here is to have them converted to a common scale).
Now let’s draw a simple XY chart. Each row will have to be added as a series and has a name, X and Y values. Typically if our original data set had X data points, we would have to plot an additional of 2X the number of series for the labels for the chart scales. (Essentially the highlighted portion gets converted to individual series of the XY chart.)
In order to hide the last two points (we will use them later in the legend), we adjust the vertical scale to start at zero. Since the last two points has a Y-value of -1, the sink below the X-axis. To do this, right click on the chart axis and pick ‘Format Axis’ and then set the minimum value to 0. While at it, we can also adjust the maximum value of the horizontal scale (X-Axis) and set it to 10. This way the rightmost points align up nicely against the secondary vertical axis. We can obviously the values set of whichever level one wants – in this case we simply picked up the ones which work for us.
Adding Scales to the Chart
Let’s turn on the labels. Right click on any point and select ‘Format Data Series’ (Excel 2003) or ‘Format Data Labels’ (Excel 2010). Once you have the labels turned on for the first point, simply use the repeat key (F4) to carry out the same operation on all the other points. Once all the points have been turned on, you will notice that they would typically be positioned towards the right of the point. For the left scale and the first data series, you can have them positioned towards the left (right clicking the first point and on the ‘Alignment’ tab change the orientation to left).
Add Legend To the Chart
Turn on the legend. By default, you will see a number of entries in the legend and we definately don’t need all of them. The only ones of use are the last two. So one by one, place two slow clicks on each of the legend entries and press the delete key.
Once you have reached this point, take a break. Close your eyes for a while. Imagine a good looking girl / guy walking upto, staring deep into your eyes and and complementing you … on your Excel skills
Ok time to wake up and move onto the formatting the chart’s scales. One by one, hide the points on both the vertical scales. That way only the labels remain visible. Now for the interesting part. We can convert the horizontal chart scale (X-Axis) to show ‘min’ and ‘max’ strings rather than values from from 0 to 10. How. Simple. You can use custom format to change the original scale on the X-axis of the chart to show any text /string that you want based on the specified criteria. In our case, the X-axis has a scale that goes from 0 to 10. So writing a custom format string like:
converts a value of 0 on the scale to “Min’ and a value of 10 on the scale to “Max”. The remaining values on the scale are now all hidden.
The end result of all the tinkering is that we have a chart that looks pretty much how we wanted it to be. As you can see, each category in the chart now has a customized scale of its own. From this point, its just a short hop to adding the chart title (of course), coloring gridlines and generally taking care of the placements.
You can download a copy of the chart with multiple scales here or click on the button below
With Excel, it’s easy !!!