Using Excel custom chart labels is a great way to create a more insightful chart without having to show a whole other series. Just take this chart below with custom labels showing the year on year % change:
Custom Chart Labels Excel 2013
In Excel 2013 we can easily insert custom chart labels using the new ‘Value From Cells’ option found in the Label Options menu:
Unfortunately if you’re using Excel 2007 or 2010 you’re not so fortunate but fear not, I have a workaround.
Custom Chart Labels Excel 2010/2007
The Old Way:
I used to hijack the regular labels and replace them with links to cells containing the label I wanted, however there were three problems with this approach:
- They could only display numbers, which means I couldn’t do anything fancy like the up/down triangles in the chart above.
- It was tedious as they had to be linked one at a time and when you’ve got loads of labels….well, it gets boring fast.
- They didn’t dynamically update i.e. if the period of my chart changed I had to manually change my labels…double boring.
The workaround for this was to insert text boxes and link them to cells, however this also fell afoul of problems 2 and 3 above.
Bonus problem: aligning the text box labels wasn’t too bad if you wanted them along a straight line as you could just use the Alignment Tools, but if you want them staggered in line with the column height then you’ll be wasting valuable hours tediously moving boxes up and down. Yawn.
The New Improved Way:
Last week Karen asked me how to insert custom labels that dynamically update, so I spent a bit of time experimenting with the tools buried in the chart menus and figured out that I could hijack the horizontal category axis for my custom labels, and puff, just like that problems 1, 2, 3 and 4 are gone.
Here’s how: Set up your chart source data:
Take special note of columns D and E as these are required for the labels.
A brief word on the Max column: this column simply returns the MAX from columns B and C for each row. We use Max as a dummy series in our chart to dynamically position the labels just above the columns.
Step 1: Select cells A26:D38 and insert a column Chart
Step 2: Select the Max series and plot it on the Secondary Axis: double click the Max series > Format Data Series > Secondary Axis:
Step 3: Insert labels on the Max series: right-click series > Add Data Labels:
Step 4: Change the horizontal category axis for the Max series: right-click > Select Data > select ‘Max’ from the Legend Entries and then click ‘Edit’ under ‘Horizontal Axis Labels’:
Select the Labels in cells E27:E38 and click OK (image below):
Don’t worry if the chart doesn’t look any different yet.
Step 5: Replace the default labels with your custom labels: right-click the labels > Format Data Labels:
From the ‘Label Contains’ list choose ‘Category Name’:
Step 6: hide the Max series columns by formatting them with ‘No fill’: double-click the Max columns in the chart to open the ‘Format Data Point’ dialog box and under the ‘Fill’ tab choose ‘No fill’:
Step 7: Tidy up the chart:
- Hide the secondary axis – double click it to open the Format Axis dialog box > Axis Options > Axis Labels, Major and Minor tick marks > set all to ‘None’:
- While in the Format Axis dialog box go to the ‘Line Colour’ tab > select No Line
- Move the legend to the bottom: double click the legend > legend position > Bottom
- Get rid of the gridlines – just select them and press the Delete key.
- Format the legend point for Max to pick up the value in cell E26: right-click the columns > Select Data > select Max from the Legend Series list > Edit:
In the Series name field click on cell E26 > click OK:
Celebrate! Your custom chart labels are complete:
Thanks
Thanks to Karen for prompting me to discover this workaround. Although, I'm sure I'm not the first to use this technique, I've not stumbled upon it before.
Please Share
If you liked this or know someone who could use it please click the buttons below to share it with your friends on LinkedIn, Google+, Facebook and Twitter.
Steve Tainton says
Hi Mynda just revisiting theis post and have made some modifications to your formula so that a third result could b shown to illustrate no change i.e. 0%
=IF(C27>B27,$G$26&TEXT(ABS((C27/B27)-1),”#%”),IF(C27<B27,$F$26&TEXT(ABS((C27/B27)-1),"#%"),$H$26&"0%"&$I$26))
Mynda Treacy says
Lovely, thanks Steve.
lea cohen says
And in the ABS formula how does it give the answer of 8%
I copied the formula that was written in the sheet you attached and all received a score of 0
I’d love to understand the calculation that makes up the percentage, thank you
Mynda Treacy says
The formula that calculates the percentage is just the C28/B28-1. The ABS function just removes the negative sign for any negative percentages.
Mynda
lea cohen says
thank you
lea cohen says
I would appreciate it if you could tell me how to create the icon of the 2 triangles in the F26 G26 cells and can I create more shapes?
Mynda Treacy says
Hi Lea,
The triangles are symbols (Insert tab > Symbol). The font is Arial and they are found in the Geometric Shapes group.
Mynda
lea cohen says
Thanks
The shapes there are very limited
The question is whether it is possible to use with the shapes from the webdings font
Because when I added them and then I did = and clicked the sign I added
He gave me another sign.
What trick can you do to use a name with a larger selection of icons? there is something?
Mynda Treacy says
You can’t use Wingdings or Webdings because you’d have to format the labels in the Wingdings font and that would turn your % numbers into Wingdings, which would be unreadable. You can use any shapes that aren’t generated by a font.
Sara S says
I was so excited to see this post, this is something I absolutely need! However I gotten stuck on Step 4. All of my options are grayed out except for “switch row/column”. What am I doing wrong?
Mynda Treacy says
Hi Sara,
It sounds like you might have a Pivot Chart, not a regular Chart?
Mynda
Jeff Weir says
Hi Mynda. An even simpler work-around for pre 2013 users is to simply download Rob Bovey’s excellent – and free – XY chart labeler. It’s misnamed really…it works on any chart type.
http://www.appspro.com/Utilities/ChartLabeler.htm
Mynda Treacy says
Great tip! Thanks, Jeff.
b. lawrence says
love article – how do you get the symbols in the column E formula?
Mynda Treacy says
Thanks! The symbols can be inserted into a cells (in my spreadsheet they’re in cells F26 & G26), via the Insert tab of the ribbon then Symbol. You’ll find them under the Symbols tab, arial font, geometric shapes.
You then use an IF statement in column E to choose which symbol is required. If you download the workbook you’ll be able to inspect the formula.
Mynda
MF says
or it can be input by ALT + 30, ALT + 31 on the numpad. 🙂
given you are working on PC.
Mynda Treacy says
Suppose you can, if you happen to remember the numbers for them… which I never can 🙂
MF says
You are absolutely right. I only remember a few, less than 5 indeed. 🙂
James Michael Perry says
I’m using excel 2013 so many thanks for sharing
#happy2015
Mynda Treacy says
Cheers, James. Happy 2015 to you too 🙂
MF says
Hi Mynda,
I like that new feature in Excel 2013. Thanks for sharing… I wish I have Excel 2013 to play around. 🙂
As a lazy guy, I normally put the “% change” as part of the X-axis by putting column E to column A, followed by Month/2013/2014. In this way, same chart can be plot in normal way; and the “% change” will appear under the “Month” on the same X-axis.
Here’s a screenshot using your sample data:
https://wmfexcel.files.wordpress.com/2015/01/capture.png
Cheers,
Mynda Treacy says
Cheers, MF. I like that option too.
Asif Hafeez says
Nice article! So easy in Excel 2013 and thanks Mynda learning us such a nice trick in Excel 2007/2010 in a comprehensive and easy way.
Mynda Treacy says
Cheers, Asif. Glad you liked it.
Mynda
Gonzalo Torres says
Dear Mynda, how we can automatically change color in custom chart label. For example -3% (red) and +10% (blue).
Thanks
Gonzalo T.
Mynda Treacy says
Hi Gonzalo,
The only way to format them different colours is to have two series on your secondary axis; one for the negative labels and one for the positive labels. You can then format each series in a specific colour. The negative labels will have blanks where the positive labels are and vice versa. You may also have to format the label box to have ‘no fill’ so that the labels at the bottom show through.
Hope that makes sense.
Mynda