|
|
Peltier Tech
Excel Dashboards
Books at Amazon.com
|
Arbitrary Gridlines and Axis Labels
Here is another example of Arbitrary Axis Labels and Arbitrary Gridlines, together in the same chart. I received the following data (columns A and B) and the related chart with a request for special formatting.
We will use a helper series with points along the X axis to serve as anchors for the error bars we will add for our gridlines, and another series, also along the X axis, to serve as anchors for the data labels we will use for our desired axis labels. Although a line chart treats line chart series as only having whole number X values, we can add a series and convert it to an XY type series, and Excel will treat its X values as numerical values. Therefore, we will add our helper series and change them to XY series. The first data point in the main chart series is at 1, and we want a distinct gridline half a unit before it, at 0.5. After this we want gridlines every six units along the X axis, at 6.5, 12.5, etc. We will place a point at each of these X values along the Y axis (Y equals zero). The data for the Gridlines helper series is shown in E1:F7 below. We want year labels in the middle of each year's data, between the third and fourth categories. We will use 3.5 for the first category label, and add 6 for the location of each additional label, so they will lie at 3.5, 9.5, 15.5, etc. We will place a point at each of these X values along the Y axis (Y equals zero). The data for the Labels helper series is shown in H1:J6 below.
Add the Gridline data to the chart. Copy the Gridline data in E1:F7, select the chart, and use Paste Special from the Edit menu to add the data to the chart as a new series, with series in columns, categories in the first column, and series names in the first row.
Now select the added Gridline series so it can be changed to an XY type. The series is not apparent in the chart (except for the legend), so use the dropdown chart element selector in the Chart toolbar, and select the Gridline series.
With the series selected, choose Chart Type from the Chart menu, and select the XY chart subtype with markers and no lines. The markers now appear on the X axis, but not at the exact positions we expected, because Excel has helpfully added secondary axes to the chart.
Like many helpful things Excel does, we have to partially undo it. We will keep the secondary Y axis, so our error bars can remain a constant height even if changes to the main series data causes causes to rescale the axes. We will scrap the secondary X axis, and Excel will use the primary X axis for all series in the chart. Select Chart Options from the Chart menu, click on the Axes tab, and uncheck the box next to Secondary Value (X) Axis.
The secondary Y axis is important to the chart, but not to the viewer. First, set its axis limits so Excel doesn't automatically autoscale it. Double click on the axis, and on the Scale tab, enter 0 and 1 for Minimum and Maximum, and make sure the Auto boxes in front of these values are unchecked. Hide the axis by double clicking on it, click on the Patterns tab of the Format Axis dialog that pops up, and select None for all options.
Add the Labels data to the chart. Copy the Labels data in H1:I6, select the chart, and use Paste Special from the Edit menu to add the data to the chart as a new series, with series in columns, categories in the first column, and series names in the first row. Excel remembers that we changed the previous added series to an XY type, so it adds this one automatically as an XY type, not a Line type. This is a helpful thing that we don't have to undo!
Add custom gridlines by adding error bars to the Gridlines series. Double click on the Gridlines series, click on the Y Error Bars tab, click the Plus icon under Display, and set the Error Amount to a Fixed Value of 1 (the maximum value we locked into the secondary axis). The error bars are shown below in red.
The way to get the custom X axis tick labels is to apply data labels to each point in the Labels helper series, then change the label to display the desired text. This becomes tedious after about two labels. To do this easily, you need Rob Bovey's Chart Labeler, a free and absolutely must-have add-in available from Application Professionals. It's compatible with every version of Excel since 97, and is easy to install and use. It is started through an added command, XY Chart Labels, on Excel's Tools menu. Use the Labeler to add the labels in J2:J6 to the Labels series, aligned below the points. The added labels are shown below in green.
A little more cleanup is needed. Hide the markers for the two helper series: double click on each in turn, and on the Patterns tab, choose None for Markers. Hide the default X axis labels: double click on the axis, and on the Patterns tab, choose None for Tick Mark Labels. This causes the plot area to stretch downward: select the plot area, and drag its lower edge upward until there is sufficient room for the added labels. There is only one relevant series in the chart, so the legend is redundant: select the legend and press the Delete key. If there were two or more meaningful series in the chart, the legend entries for the helper series can be hidden. Single click on the legend, then single click on the text of an extraneious legend entry, and press the Delete key. Don't click on the legend key, the marker in front of the legend entry text, because deleting a legend key removes its series from the chart.
|
Peltier Technical Services, Inc.Excel Chart Add-Ins | Training | Custom Solutions | Charts and Tutorials | PTS BlogPeltier Technical Services, Inc., Copyright © 2010. All rights reserved. |
