Peltier Technical Services, Inc.
 

Excel Chart Add-Ins | Training | Charts and Tutorials | Peltier Tech Blog


Peltier Tech Chart Utility

 

Excel Dashboards

 

Books at Amazon.com

 

Arbitrary Gridlines


 

Don't you wish you had more control over Excel's chart gridlines? Wouldn't you like Excel to draw gridlines where you want them? 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 gridlines, 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.
  • Generate log or probability gridline scales with your own scale parameters.
    See "Flexible Log Axes" at http://www.tushar-mehta.com

This technique is closely related to that in Arbitrary Axis Scale, which demonstrates how to put axis ticks and labels at arbitrary positions along an axis. I use a slightly different approach in Add a Horizontal Line to a Column Chart.

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

The data I will use to generate my gridlines (and labels, while I'm at it) 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 make gridlines parallel to the X 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 gridlines parallel to the Y axis, put the values under X and zeros--or Y axis minimums--under Y.)

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

In Chart 3, I have added positive X Error Bars to my dummy series, long enough to stretch across the plot area. I also manually set the X axis maximum; adding the error bars causes Excel to extend the axis scale. (The error bars are magenta for clarity).

In Chart 4, I have added dummy labels to the gridlines, using the technique described in Arbitrary Axis Scale. The easiest way to get the grid 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 the dummy series added above, 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 original (default) horizontal gridlines. I formatted my custom gridlines so they were hairline width (like the default ones) and I removed the crossbar (the "T") at their ends. 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, and formatted the dummy series ("Grid Y") to have no marker. 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 arbitrary gridlines, or an arbitrary axis (see Arbitrary Axis Scale).

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

Probability Chart

The Reciprocal Axis Chart is another implementation of these techniques.

This zipped Excel file has yet another example of a chart using an arbitrary axis scale and gridlines. The data is sparse for early times and dense later, so neither a Line chart nor an XY Scatter chart will do.

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

Page copy protected against web site content infringement by Copyscape

 

Peltier Tech Chart Utility

 

Create Excel dashboards quickly with Plug-N-Play reports.


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

Microsoft Most Valuable Professional

My MVP Profile