Arbitrary Axis Scale

Don't you wish you had more control over Excel's chart axis ticks and labels? There isn't a lot of built in support for this, but over the years people have learned a few tricks to generate charts like this:

Here is how to get your own custom axis ticks, with arbitrary spacing and arbitrary labels. You can use this technique for many purposes:

Show target or cut off levels for the plotted values.
Show statistics, such as mean, control limits, etc.
Put text labels along the vertical axis of a line or scatter chart.
See my companion page Vertical Category Axis
Generate log or probability scales with your own scale parameters.
See "Flexible Log Axes" at http://www.tushar-mehta.com

Chart 1 is a scatter chart showing 40 randomly generated data points.

The data I will use to generate my ticks and labels is shown below:

       Axis X    Axis Y    Label
         0        0.7      alpha
         0        1.2      beta
         0        1.9      gamma
         0        2.2      delta
         0        3.1      epsilon
         0        3.6      eta
         0        4.2      theta

To fit along the Y axis, all my X values are equal to the Y axis minimum (zero in this case). The Axis Y values correspond to the places I want my axis ticks. (To generate dummy ticks along the X axis, put the values under X and zeros--or Y axis minimums--under Y.)

Chart 2 shows the dummy axis tick data plotted, as a series of magenta squares along the Y axis.

In Chart 3 I have changed the format of the dummy series into crosses, so they look like tick marks (still magenta for clarity).

The easiest way to get the tick labels is to apply data labels to each point in the dummy series, then change the label to display the desired text. To do this easily, you need Rob Bovey's XY Chart Labeler, a free and absolutely must have add-in available from http://www.appspro.com. It's compatible with every version of Excel since 97 (and I think there's also an earlier version, but even I use 97). Use the Labeler to add the 'Labels' to this dummy series you've just added, aligned to the left of the points. See the magenta labels in Chart 4.

In Chart 5 I have cleaned things up for presentation. I removed the horizontal gridlines. In the patterns tab of the Format Y Axis dialog, I set the major ticks, minor ticks, and tick labels to "none". I adjusted the size of the plot and the X axis scales. I changed all the magenta stuff to black. I removed the "Axis Y" entry from the legend. (Single click twice--don't double click--on the legend entry until only the entry is selected, then press delete.)

Now wasn't that easy!? DummySeries.zip is a zipped Excel file with an easy demonstration of the steps to create an arbitrary axis, or arbitrary gridlines (see ArbitraryGridlines.html).

My Simulated Probability Chart Example (below) is an application of the Arbitrary Axis Scale and Arbitrary Gridlines techniques.

The Reciprocal Axis Chart is another implementation of these techniques.

Arbitrary Gridlines and Axis Labels shows how to improve the appearance of an axis and its related gridlines using this technique.

Peltier Technical Services, Inc.

Excel Chart Add-Ins | Training | Charts and Tutorials | PTS Blog

Peltier Technical Services, Inc., Copyright © 2013. All rights reserved.
You may link to this article or portions of it on your site, but copying is prohibited without permission of Peltier Technical Services.

Microsoft Most Valuable Professional

My MVP Profile