Chart Axes

I haven’t posted in quite a while. I have a few long tutorials that I’m working on, but there hasn’t been enough uninterrupted time to get them done.

I’ve also been working on a new edition of my Chart Utility which will run in PowerPoint. This will allow those users who spend most of their time in PowerPoint to run the utility from there, switching to Excel for long enough to select their data, then build their chart as a native PowerPoint chart. I mean, it’s still really an Excel chart, but it will behave as if it were created in PowerPoint (which it was), and the data will sit in its data sheet in PowerPoint. Tentative plans call for the new utility to be released a month and a half ago. I’ll probably release it in stages, starting in a couple weeks.

In the meantime, a customer asked me to help with a certain topic, and I thought it made a great tutorial, so here it is.

Secondary Axis as Percentage of Target

A customer had a stacked chart showing contributions to a fundraising effort by source (below) and wanted to show percentage toward the target. He had sketched something with labels and tickmarks and arrows everywhere, but I thought that was a bit too intrusive and cluttered. Instead I decided to use a secondary axis scale showing the percentage.

I’ve written before about how Secondary Axes in Charts can be more confusing than enlightening, adding to the cognitive load required to understand a chart. But I’ve also written about Secondary Axes that Work – Proportional Scales, and I’ve showed how to Add Percentages on the Secondary Axis. This is really another illustration of that last tutorial.

The only way to get a secondary axis is to have a series that is plotted on the secondary axis. I modified his data range by adding a series named “Blank” with a value of zero. The chart below is selected, and you can see from the highlighted ranges in the worksheet that it includes all series including the new Blank one. To the right I’ve done some calculations that we’ll be using momentarily. Essentially, given a target of 300 and a primary Y axis maximum of 160, I know that the corresponding secondary Y axis maximum should be 53.333%.

The charts below show the steps in constructing the improved chart. Left, I moved the Blank series to the secondary axis. Excel added a secondary vertical axis with a scale ranging from zero to one. Center, I deleted the gridlines, and made all three visible axis lines the same medium-light gray color, and gave the vertical axes major tick marks outside of the axis. Right, I rescaled the axes. I hard-coded the min and max of the primary axis to 0 and 160, just so they didn’t change without me knowing it. I set the min and max of the secondary axis to 0 and 53.333%, and changed the number format to Percentage with No Digits.

Finally I made the chart a little bit taller to allow for a descriptive title. Probably some indication of currency in the primary axis tick labels would also be useful. I also hid the row with the blank data and the columns with my secondary axis calculations, in case the entire sheet would be made public.

Every so often the default axis labels aren’t quite what you want. You might want to highlight one of the labels by bolding it or using a different color. However, the built-in axis labels can only be formatted en masse. We’ll get around this limitation by hiding the built-in labels, then add a dummy series and add data labels, which can be individually formatted.

There are two techniques, an easy one for charts with horizontal category axes (line, area, and column charts), and a more involved one for charts with vertical category axes (bar charts).

It’s probably a good idea to review your old blog entries every so often. This is an upgraded article from five years ago.

Horizontal Category Axis (Line or Column Chart)

We’ll use this simple data to make simple line and column charts to illustrate this process. This same trick can also be applied to area charts.

The dummy series will use zero values. Add this data to the chart using any convenient method. You can select the chart and drag the outlines of the highlighted data range, if the data range is simple enough and you put the dummy data right next to it. You can copy the data and use Paste Special to add it to the chart. Or you can add the series using the Select Data dialog.

You can’t see the new series in the column chart, because the added columns have zero height. But there is a space for the bar next to the blue bars above the labels.

In the column chart, you need to change the chart type of the added column series to a line chart.

Format the category axis (horizontal axis) so it has no labels.

Add data labels to the the dummy series. Use the Below position and Category Names option.

Format the dummy series so it has no marker and no line.

To format an individual label, you need to single click once to select the set of labels, then single click again to select the specific label, then apply your formats.

But go easy with the formatting, which was overdone in these charts. You want to highlight one or two labels, not keep Crayola in business.

Vertical Category Axis (Bar Chart)

We’ll start with the same simple data to make simple bar charts to illustrate this process. The left hand chart in the pairs below is a plain bar chart. The right chart has had its categories reversed following the protocol in Excel Plotted My Bar Chart Upside-Down, which adds a couple steps to this approach. This approach works fine for clustered or for stacked bar charts.

The dummy series will use slightly negative values. Add this data to the chart using your favorite method. Select the chart and drag the outlines of the highlighted data range. Copy the data and use Paste Special to add it to the chart. Or add the series using the Select Data dialog.

Move the new series to the secondary axis, and make sure it’s a clustered, not a stacked, bar chart type. Never mind how ugly the overlap looks.

If you’ve reversed the order of the primary category (vertical) axis of your bar chart, and moved the horizontal axis to the maximum category (right hand chart of the three above), there are a couple more steps to be done here. First you’ll have to add the secondary category (vertical) axis to the chart.

Then reverse the category order of the secondary vertical axis and move the horizontal axis to the automatic position, i.e., zero (see Excel Plotted My Bar Chart Upside-Down).

Delete the secondary horizontal axis and the secondary vertical axis if you had to reverse it.

Format the category axis (vertical axis) to have no labels.

Add data labels to the secondary series (the dummy series). Use the Inside Base and Category Names options.

Format the value axis (horizontal axis)  so its minimum is locked in at zero. You may have to shrink the plot area to widen the margin where the labels appear.

Single click once to select the set of labels, then single click again to select a specific label, then format away.

But pay attention to the rules. Use formatting sparingly, to highlight only one or two items in the chart. Remember, if you highlight everything, you highlight nothing.