|
|
Peltier Tech
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:
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.
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.
|
