Keeping chart text horizontal: Part 3 - Tweaking double-barrelled axis labels

What are double-barrelled axis labels when they're at home?

Individual categories are often grouped into convenient and/or analytically useful over-arching categories:

• Some U.K. councils group electoral wards into 'area committees' or some synonymous term
• Within census data, detailed ethnic group categories are aggregated into broader categories

Sometimes analysts chart individual categories - but also  show how they fit within overarching categories. If they use Excel defaults to do so, double-barreled axis labels will result. Here's an example of double-barreled axis labels in a bar chart.

Figure 1: Double-barreled axis labels

This is a reasonably simple, straightforward chart (although there are quite a lot of bars) - but what's that mess at the left side?

The same thing can also happen with bar charts: there it's worse because the x-axis labels for individual categories end up being vertical. Here's an example with vertical x-axis text and a bonus vertical y-axis title.

Figure 2: Double-barreled x-axis labels

The basic idea of chunking individual categories into overarching groups is great. People find it easier to make sense of and remember lots of bits of individual information if they are grouped together in meaningful ways.  So how can we keep the 'chunking' but lose the vertical text that is making these charts harder to read?


Turn column charts into bar charts

Transforming column charts into bar charts will immediately reduce the amount of vertical text that has to be removed. Right-click on chart area, select 'Change Chart Type' and swap the column chart out to a bar chart.

Add blank data series

Once you've got a bar chart, take a look at the data used in the chart. First off, if you have any merged cells in your 'overarching categories' series, unmerge these. Then insert a couple of empty rows between each category.  Finally, reselect your data so that you have an empty row at the top of the chart. You should now have a data selection that looks a bit like this...

Figure 3: Sample data with empty data series added

and a chart that looks something like this:

Figure 4: Adding blank data series

Reorganise axis labels
Next, copy the overarching category labels into the same column as your individual data series, and delete the column they were previously in (or reselect the chart data without that column). Your data should look something like the sample data below.

Figure 5:  Data for chart without vertical axis text. 

At this point, there is no more vertical text in the chart - but the overarching category headings look like any other category label on the axis. They need to be distinguished in some way. The simplest way to do this (there are others) is to add spaces after the category heading, such that all the headings appear left justified. If category header names are long, it may also be necessary to use Alt-Enter to split text over two lines.

The final chart should look something like this.

Figure 6: No more vertical text

This chart takes up a little more space than the original, but getting rid of the vertical text has the nice side effect of visually grouping related bars together. This makes it easier to compare values within categories.

Summing up

In this 'Keeping chart text horizontal' series, I've looked at how to get rid of three varieties of vertical text in charts:

Y-axis title
This is both the most common and (fortunately) the most innocuous type of vertical text. It's the sandfly of vertical chart text: mostly harmless and irritating, but enough of them will ruin a day at the beach.

But, like sandflies, they're easily swatted. If the y-axis title is painfully obvious, just remove it. If it's only very obvious (or not particularly obvious at all or actually quite obscure), then resize the chart and plot areas, so there's a little space at the top and change the text orientation to horizontal. Save the chart as a template - next time the y-axis will be in the right place.  Here's our video showing how to do it: https://youtu.be/zL6l3Rv2vqs and here's a link to 'Keeping chart text horizontal: Part 1, Reformatting y-axis titles'

Vertical x-axis text
These usually happen in crammed column charts. The solution is to make a bar chart instead, so that the category labels are set out vertically. There's a walk-through of how to do this in 'Keeping chart text horizontal: Part 2, Getting rid of vertical x-axis text in charts'.

Double-barrelled axis labels
These happen in column and bar charts with sub-categories grouped into larger categories. See above.