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:
Enter your email address below to download the sample workbook.
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. Note: Excel 2013 onward also requires this step if you have more than one series you want to position your labels above.
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.
Excel Find Tool
Ndebeh soriba
This lesson is so interesting I must apprecite you ‘ but, I’m facing some problem with finding the formula for true or false
Mynda Treacy
You’re welcome, Ndebeh! If you’re stuck please post your question on our Excel forum where you can also upload a sample file and we can help you further.
Ahmed
Thanks Mynda for making our lives easier
i am using excel 2010 and i have a stupid problem in step 4, when i select the max series there is no option to convert into horizontal, am i doing something wrong??
Mynda Treacy
Hi Ahmed,
In step 4 you’re not converting it to a horizontal axis, you’re simply giving it a different range of cells for the horizontal axis. Simply select the Max series, then click on the ‘edit’ button on the Hoizontal (Category) Axis Labels side of the dialog box as shown in the image.
Mynda
Mario
[Color10]_( “â–²”_*0.0%;[Red]_(“â–¼”_*-0.0%
Could you assist with changing the following format where anything over 100% is RED ??
Mynda Treacy
Hi Mario,
You can only colour code numbers based on positive, negative and zero values using custom number formats. If you want to colour code based on different thresholds, you need to use Conditional Formatting.
Mynda
Joe
Hi Mynda,
Always enjoy your lessons in excel and it has helped me tremendously. One question regarding this process: In the change column, my down arrow and percentage seem to be more spaced out than the up arrow and percentage. Is there a way I can get the down arrow and percentage closer together? Thanks Joe
Mynda Treacy
Hi Joe,
Maybe try some different fonts to see if you can get better results. The font in my example is Calibri (Body).
Mynda
Sergey
Good day to you Mynda! Thanks for the post.Made a littlle improvement. There is a way to bypass the IF,ABS and TEXT function and referencing cells with arrow symbols.
I mean creating custom cells format, inserting the needed symbols along with the percentage: [Green]â–²# ##0%;[Red]â–¼# ##0%;[Yellow]# ##0%.
Added some colors just for fun)))
Not sure if I wrote everything correctly, my English is not so good yet.
Thanks again!
Mynda Treacy
Thanks for sharing that tip, Sergey.
Unfortunately the colour coding doesn’t flow through to the chart, but it does remove the need for the formula in the YoY Change column. More on custom number formats here.
Sam
This is a brilliant work and thought process. It is very innovative. I believe that Excel has everything that you need but someone like Mynda needs to show How-To otherwise you will feel lost.
Mynda Treacy
Thanks, Sam! Glad you found it useful 🙂
Steve Tainton
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
Lovely, thanks Steve.
lea cohen
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
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
thank you
lea cohen
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
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
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
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
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
Hi Sara,
It sounds like you might have a Pivot Chart, not a regular Chart?
Mynda
Jeff Weir
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
Great tip! Thanks, Jeff.
b. lawrence
love article – how do you get the symbols in the column E formula?
Mynda Treacy
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
or it can be input by ALT + 30, ALT + 31 on the numpad. 🙂
given you are working on PC.
Mynda Treacy
Suppose you can, if you happen to remember the numbers for them… which I never can 🙂
MF
You are absolutely right. I only remember a few, less than 5 indeed. 🙂
James Michael Perry
I’m using excel 2013 so many thanks for sharing
#happy2015
Mynda Treacy
Cheers, James. Happy 2015 to you too 🙂
MF
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
Cheers, MF. I like that option too.
Asif Hafeez
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
Cheers, Asif. Glad you liked it.
Mynda
Gonzalo Torres
Dear Mynda, how we can automatically change color in custom chart label. For example -3% (red) and +10% (blue).
Thanks
Gonzalo T.
Mynda Treacy
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