The labels used with the chart category axis are probably the most ignored part of the entire chart making process. You see, the labels on the value axis can be modified, the bars, the lines and the plot area – all of them can be embellished – colors added, widths adjusted, descriptions applied – the works you know ! Even the poor little gridlines can afford a makeover. But what about the chart’s category axis label – what do we do with it. Nothing. Once it is in place, it just sticks around like a dead possum while other elements tom-tom their colors and hues. Look at the this slightly dated NY Times chart. One look at the chart category axis labels and you probably begin to agree with me !

So…this one is for the the chart axis label – the unsung hero of the charting world ! Now let’s put the chart axis label to some good work.

What we’ve tried to do here is to pack a great deal more information in the label along the chart’s category axis than we normally do. (The same result can be achieved by adding the descriptions in adjacent cells.)

Source Data for the Chart

Let’s get some data for the chart. For this example, we take the sales figures for the various regions of a company. The table below shows sales figures for the present month, the month before and the same month an year ago.

Creating the Proper Labels for the Chart

Now one by one, we take each of the category labels and modify them so that they provide additional information. We make use of symbols (▲ ▼) and the TEXT formula. Let’s assume the sales figures for a particulkar region are values are placed in cells C3, D3 and E3. We can write something like:

=”Region 1 ” &” ” & TEXT(ROUND((C3-D3)/C3,2),”+????.00%▲;-????.00%▼”)& ” ” & TEXT(ROUND((C3-E3)/C3,2),”+????.00%▲;-????.00%▼”)

which gives us the final string as:

Region 1 + 27.00%▲ + 27.00%▲

All that we’ve done in the previous step is to simply write a formula that combines the name of the region with the % increase or decrease in sales this month over the previous month and the same month and year ago. The slightly complicated formula is to format the up/down (▲ ▼) symbols so that they appear when the sales are up or down respectively. The TEXT formula helps us shape or format numbers are desired. It has two parts – the first is the expression/string/number that needs to be formatted and the second is specification of the format to use. Ex. =TEXT(100.23, “$ ##.##”)

Let’s extend this all the remaining cells. We are now free to use these as chart axis labels.

Insert a Chart

All that needs to be done is to simply insert a chart and provide the values as chart axis labels. Remove the chartjunk and provide proper width to the chart area. Play around with the chart & chart axis titles so that you get the final desired result.

You can download a sample worksheet containing example of chart category axis label here or click on the button below: