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.