|
|
Peltier Tech
Excel Dashboards
Books at Amazon.com
|
Column Chart on Two Axes.
Suppose you need a column chart of data that has greatly different magnitude. The table below shows three series of data in the thousands, and a fourth in percentages.
The column chart can't show the fourth series, because its values are so much smaller than the others.
Excel doesn't offer a built-in custom Columns On Two Axes chart type, but you know how to assign a series to the secondary axis. "That's easy," you say. "I'll just put the last series onto the secondary axis." It's a little tricky, because it's hard to select something you can't see. But you can select a visible series, then use the up and down arrow keys to change the selected series, then choose Selected Data Series from the Format menu, or use the CTRL+1 shortcut. This works great in Line charts, XY Scatter charts, and any combination charts with Columns on one axis and Lines on the other. But in a column chart, this has an unintended consequence.
Note: You can use the Chart Objects dropdown control on the Chart command bar (shown above) to identify which chart element is selected, or to select one which is difficult to click on with the mouse. In addition, the Name Box (above the top left visible cell, in the left edge of the Formula Bar) shows the name of whatever object is selected.
In a column chart, the columns on a given axis are centered over each category. Hence the single column on the secondary axis has obscured the center column on the primary axis. You can get around this problem if you use dummy series in your chart. The trick here is to include a dummy series, using zero values to be invisible, on each axis to maintain proper positioning of each visible series. For each visible primary axis series, you need a hidden secondary axis series, and for each visible secondary axis series, you need a hidden primary axis series. The table below shows how to insert columns to set up the desired chart appearance. The upper case headers denote visible (non zero) series, while lower case headers denote hidden series.
Modify the original data table as shown below to provide for the hidden series.
When this data range is charted, the result is similar to our first chart, with more empty space after the three visible columns.
Now the last four series must be assigned to the secondary axis. Select one of the visible columns, and press the up arrow key until the "Share" series (the eighth series) is selected, then assign it to the secondary axis. Before doing anything else, press the down arrow key to select the seventh series, and press the F4 function key to repeat the axis switch. Two more times press the down arrow key followed by the F4 key, so that the last four series are on the secondary axis. The unwanted legend entries are easily hidden. Select the legend, then select the text of the legend entry (two single clicks), and press Delete. Note: make sure you select the Legend Entry (the text for the entry you want to hide) before you press Delete. If you select the Legend Key (the small square filled with the series color) and press delete, you will delete the series. Fortunately the Undo button (or the CTRL+Z shortcut) can correct this mistake.
Finally, you should include some clues to help the reader determine which axis contains values for which series. In this example, the legend is moved above the plot area, and descriptive axis titles relate to the legend entries. To make the legend more descriptive, I redrew it and only deleted three of the dummy legend entries. For the remaining one, I changed the series name to " " (a series of spaces), and I changed the series formatting to no fill and no border. This leaves a gap between the legend entries for the Markets and the legend entry for Share.
This technique can also be used on bar charts, as in this example:
|
Peltier Technical Services, Inc.Excel Chart Add-Ins | Training | Custom Solutions | Charts and Tutorials | PTS BlogPeltier Technical Services, Inc., Copyright © 2010. All rights reserved. |
