Chart Label Trick – Converting Chart Axis Labels to Table

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:

What Do You Think ?

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

1. Danièle wrote:

Thank you for very elegant chart tricks using excel.
I am always happy to go back to your site, but there has been no activity for several years. yet, your tricks are still unusual, and very handy, even using excel 2013.
Thanks!

1. Chart Label Trick - Label Last Point in a Line Chart and Offset Axis Crossover | Excel & VBA - da Tab Is On wrote:

[…] floating end points. And now my “value add” to Kaiser’s original chart – if you format and align the labels properly, you can achieve a table like structure. The more the data points, the […]

2. Sparklines in Excel - New Features in Excel 2010 Series | Excel & VBA - da Tab Is On wrote:

[…] the reverse direction (right to left) 3. Customizing the maximum and minimum values on the vertical axis of the chart 4. Ability the change the chart axis from General to the one based on […]