Categorized | excel chart

Multiple Scale Chart – Adding Customized Scale to Each Category in a Chart




TumblrStumbleUponRedditLinkedInAIMDiggEvernoteWhatsAppMySpaceKindle It

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.

data-multiple-scale-chart-revenue-receipts-budget

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.

single-scale-in-a-chart

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 :-) )

multiple-scale-chart

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

multiple-scale-chart-data

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).

xy-chart

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.)

set-minimum-scale-value-to-zeroIn 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.

change-chart-scale-minimum-value

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).

multiple-scale-chart-turn-on-the-labels

Add Legend To the Chart

chart-legend

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:

[=0]“1997″;[=10]“2007″;

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.

hide-chart-scale-label-points

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.

multiple-scale-chart-plot-values

You can download a copy of the chart with multiple scales here or click on the button below

multiple-scale-chart

With Excel, it’s easy !!!

TumblrStumbleUponRedditLinkedInAIMDiggEvernoteWhatsAppMySpaceKindle It

Excel Formula, Excel Chart, Excel Macro, Excel VBA, Pivot Table Excel, Excel Dashboard

What Do You Think ?


XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>


Comments and Trackbacks

  1. Jon Peltier wrote:

    What’s the basis for selecting the customized scales? If I choose 40 for Custom Duties, it compares differently with Estate & Gift Tax. Don’t be lured by having nice round (but entirely arbitrary) scale values.

    It makes sense to choose limits algorithmically, so either the 1997 or the 2007 points are vertically aligned.

    Better yet, if you want to compare the relative changes from 1997 to 2007, you could just plot % change.

  2. Ajay wrote:

    @Jon – It was a toss up between making the scales ‘arbitrary’ and having the points show up all over the place :-) But then within an acceptable range, aren’t all scales arbitrary. I guess you would be the best person to ask the next question:

    Is there a set of rules which one should follow while choosing the scale?

    That way the reader can have something to use as a basis for consistent scale ‘deployment’ across all plot values.

    Regards,

  3. Jon Peltier wrote:

    If you adjust the scales to align the 1997 values or the 2007 values, it’s no longer arbitrary, is it? Or if you normalize the values so all fit on the same percentage scale, that is not arbitrary either.

    You need to think not of how to make the scale values look nice (nice whole number multiples of ten, for example), but how to make possible comparisons between the sets of numbers.

  4. Jeff Weir wrote:

    Hi Ajay. Nice to see someone willing to try new approaches.

    However, at the risk of being told to go away for real, I think the point of graphing data (rather than simply reporting it in a table) is to make possible comparisons between the sets of numbers (as Jon says above).

    To do that, then I’m afraid you’re better off with your original graph. Even better, use a table.

    Essentially, your amended graph above is really a dressed-up table, with quite bad data/ink ratio.

    An alternative with this data set would be to use bullet graphs.

    Please, please don’t block my ICP! I enjoy your blog a lot.

    :-)

  5. Ajay wrote:

    @Jeff

    1. I will not block your ICP and you are always welcome :-)
    2. The part that I really liked about your observation was on Line 1 :-) . :-)

    Regards,

  6. MattWPBS wrote:

    Interesting, but I’d be tempted to view these as two different problems that need to be combined on the same chart. You want people to be able to see the absolute value, but also to see how much they’ve changed.

    I’d do this by figuring out the % changes, and then plotting them on another axis with the same colours, but lighter. Keeps info handily linked, means you concentrate on the absolute, but can see how the changes vary in scale.

    http://yfrog.com/jbmultiscalep

  7. Gerald Higgins wrote:

    I just discovered this blog today (via Excel Hero). Some nice ideas here, well done ! On this chart, I like it alot as a demonstration of what can be done in Excel. Perhaps there’s a great real life application for it. But I’m afraid I’m with Jon, Jeff and the others – I don’t think it’s right for this specific application. Your original chart showing absolute values is fine, PLUS you want something showing percentage change – indexing in other words. As Jeff suggested in comment 1, aligning the 1997 values would have ben good.
    A detail – I originally read the MIN and MAX statements on the chart as implying that you actually had data for many years, not just two, and these represented the minimum and maximum values in your data. That’s slightly misleading in my opinion.
    Carry on with the blog, I’ll be looking back.

  8. Ajay wrote:

    @Gerald – You’re welcome !!! A few kind words would have done my bruised ego a world of good :-(

  9. Andy wrote:

    Not sure if this will provide a solution for me….?

    I have a combined line and bar chart with primary and secondary y-axis – the primary is based on value whilst the secondary is in days. The X-axis is date driven. There are four sets of data in the chart – two in the value and tow in the days

    My problem is trying to improve the visibility of the value primary y-axis in respect that the one value series is circa 15-20 times larger than the other so therefore I do not see the variation in the smaller values series. How can I do this?

  10. Paolo wrote:

    Great solution! i’ll use it for the next monthly expenses review!

Subscribe

Keep up with the latest stories - Delivered right to your inbox
feedburner

Translate

English flagChinese (Simplified) flagPortuguese flagGerman flagFrench flagSpanish flagJapanese flagArabic flagDutch flagHindi flagPolish flagSwedish flagFilipino flagHebrew flagIndonesian flagUkrainian flagThai flagTurkish flag
treeemap software for excel