Change Data Labels in Charts to Whatever you want [Quick Tip]

Posted on May 5th, 2010 in Charts and Graphs - 53 comments

We all know that Chart Data Labels help us highlight important data points. When you “add data labels” to a chart series, excel can show either “category” , “series” or “data point values” as data labels.

But what if you want to have a data label that is altogether different, like this:

Custom Data Labels in Charts - an Example

You can change data labels and point them to different cells using this little trick.

  1. Data For Custom Chart Data LabelsFirst add data labels to the chart (Layout Ribbon > Data Labels)
  2. Define the new data label values in a bunch of cells, like this:
  3. Now, click on any data label. This will select “all” data labels. Now click once again. At this point excel will select only one data label.
  4. Go to Formula bar, press = and point to the cell where the data label for that chart data point is defined.
  5. Repeat the process for all other data labels, one after another. See the screencast.
    Set chart data labels to custom values - demo

Points to note:

  1. This approach works for one data label at a time. So if you have a large chart, you are in for a lot of clicks and manic mouse maneuvering.
  2. That brings us to Rob Bovey’s Chart Labeler Excel Add-in. This free tool can automate the whole custom chart labeling for you.
  3. If you want more formatting options, consider adding text boxes and point them to cells instead. See the smart chart legends post.

Share your Chart Formatting Tricks:

What are your favorite chart formatting tricks? My favorite tricks are using text boxes to add rich formatting to charts (example here) and messing with fonts and colors (some rules here).

What about you?

Recently in Charting:

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

53 Responses to “Change Data Labels in Charts to Whatever you want [Quick Tip]”

  1. Andy Pope says:

    I sometime use a dummy data series plotted on the secondary axis. You can then set the secondary category labels to use the range with the custom labels text.
    Apply Category data labels to the dummy series.

  2. Tom says:

    Personally, I think this is a great tip. I like the idea of automating the process, although I’m always hesitant to use “add-ons.” It seems like it would be a fairly straightforward VBA project. In fact, you could probably use VBA to automatically make the labels themselves, so you didn’t need the source cells. I’ll look into this the next time I’m needing custom labels.

    • Tammo says:

      Bob Bovey’s Chart Labeler Add-in (linked above) works like a charm. I needed to add alpha labels to the bubbles in a bubble chart and it gives me exactly what I needed.

  3. Alex Kerin says:

    You can use the same trick to relabel axes – this is great if you’re using the offset formula with a dropdown to create a dynamic chart = just change the axes title as well using an offset.

  4. Bill says:

    I know this is but an excel trick, but …

    I think I’d rather see two charts: one with the numbers as displayed and another with the %change between the 2 months. Better yet would be to display the last several months as line charts. Change always needs to be seen in the context of history.

  5. davidlim says:

    hi guys, this works if you have a single series.

    what about stacked bar (multiple series)?

    e.g. i have March and April series stacked-bar chart. i’d like to label the TOTAL of both months, but the data label should be [outside-end] of April’s bar.

    [March]-[April]-[data label of the total for Mar+Apr]

    normal labelling dont offer [outside-end] data labelling. Rob Bovey’s Chart Labeler also doesnt offer this.

    any ideas?

    • Chandoo says:

      @David: You can add a dummy series to the stacked bar and stacked it on top of the last month. Now, make the dummy series transparent (no fill, no border) and set labels to it (at inside end). Use the technique in this post to customize labels and you should be good to go.

      • SNK says:

        dear I got your point, but it will show label at x- axis only , how to raise that to the primary series data level , that height just next to the primary series values.

  6. davidlim says:

    ahhh yes, that’s what I’m thinking of too ;)

    cheers!

  7. Danny says:

    Brill tip….as usual!

  8. teylyn says:

    @David,

    you can also calculate the totals in your data, then add the total series to the chart, move it to the secondary Y axis, change the chart type to a column (not stacked column) chart. Then add data labels for the total series, which you can now place to the outside end of the columns. Finally format the total series to no fill and no line to make it invisible.

    cheers, teylyn

  9. Chandoo says:

    @David… Teylyns tip is better than mine. With that you dont have to fix the axis scales.

    @Andy & @Alex… good additions :)

    @Bill: Of course using separate charts is even better. My intention is to show the trick related to chart label customization.

    @Teylyn… Excellent. Here is your donut :)

  10. davidlim says:

    thx teylyn ;)

    chandoo, i prefer adding another dummy series; BUT with small value (and clickable!) so that it won’t force change the axis scale ;)

    anyhow, both methods have its merits :D

  11. dandanthebassman says:

    Dont know if anybody has come across this before, but I’ve been using it (well, my own slightly modified version of the code) to add labels to charts and it works very very well

    http://support.microsoft.com/kb/914813

    -Dan

  12. [...] Chandoo bring us yet another quick tip on how to add data labels to the chart. [...]

  13. david says:

    chandoo, how do u ‘break’ the data labels into 2 lines (like those displayed on the chart)?

    e.g. 400 (5%) ==> 400 (5%)

  14. Mayank says:

    David,
    You can press Alt+Enter after 400 to get data lables in 2 lines.

  15. Pipo says:

    How do you do to brake labels into 2 lines if the link is a concatenate? Alt+Enter doesn’t work in this case… Any ideas? Thanks.

  16. Pipo says:

    Got it!!!
    I might be of help for someone, so here it goes:
    Just place CHAR(10) were you want the break and that’s it!
    E.g.: =A1&CHAR(10)&A2

    My full formula to keep format is:
    =TEXT(A1,”0,000″)&CHAR(10)&”(“&TEXT(A2,”0.0%”)&”)”, were A1 is my amount and A2 my growth %.

    Thanks Chandoo for such a wonderful tip….

  17. david says:

    wow, that works Pipo ;)

    thanx!

  18. Randall says:

    Thanks dude – making my charts look good – management thinks im the bomb!!

  19. Santanu says:

    really very useful…Thanks a lot….

  20. Andy Fleisher says:

    So, now that there are custom data labels, is there any way to change the text justification? All I ever see is centered text but I would like to have mine left justified. Whatever the formatting is in the linked cell doesn’t get reflected in the chart. In your example it works OK since there is a number (short length) and then a percent change (longer length). The centering looks OK there. I am working on a time line and would like the labels in more of a list format rather than centered. Thanks.

  21. Prabhu says:

    Hello Chandoo.. Great tip.. Would i be able to color the arrow using formula itself??

  22. Fitriadi says:

    Hi Chandoo,
    didn’t know that we can do that ^^. Out of the topic. is it possible to make the axis label interactive as well. We can change the data through name range but I can seem to find the answer for axis label. I am trying to make interactive bar chart with different number of data and different axis label (name of region for example).

  23. Hui... says:

    @Fitriadi
    Put the text in A1 you want as your Axis label
    Select the Axis Label you want to change
    With it selected click in the formula Bar at the top of the screen and type =A1
    Select with the Tick to the left of the formula bar
    Change reference to suit

  24. Fitriadi says:

    @Hui
    Hi Hui, thanks for the quick reply. But I think what you mean is axis title, because i cant seem to click on the formula bar when i click on the axis label. I did able to do it with the axis title though.

  25. John austin says:

    I have a chart with two axis on the X axis I have the date on the two Y axis i have bar and line data. The Line data shows from 30 to 70 in increments of 10. I want the 70 to show a label £ without putting in a text box eg £70 and none of the other figures will have the £ sign. Is there a formula or a quick way in Excel 2003 to do this.

    Many thnaks

  26. Hui... says:

    @John
    Select the X-Axis you want to format
    Right Click, Format Axis
    Add a Custom Number format of “£”0

  27. John Austin says:

    Thanks Hui but that changes all the values to have the pound sign I only want one value to show with the £ sign. And these values are on the y axis. There are two y axis.

  28. Hui... says:

    You can’t format a single entry or component of an axis text
    Easiest way would be to add a text box to the chart

  29. John austin says:

    “Formula used to create £ symbol on secondary scale (i.e. not text box)”
    This is part of the test I have to do Hui so you must be able to do it otherwise it wouldn’t be part of the test.

    I am at a complete loss on this one I have tried all the methods you have described.

    John

  30. Hui... says:

    @John
    Select the X-Axis you want to format
    Right Click, Format Axis
    use the following Custom Number format
    [<70]“”;[=70]“£”0;;

  31. John austin says:

    Brilliant I had to ammend slightly but spot on.
    [<70]general;[=70]£0;;

    Thank you so much Hui.

    John

  32. Kit says:

    What great info! This and other tips on this site are awesome. Thanks Chandoo!

  33. anisa says:

    This is awesome! Saved my day

  34. RR says:

    Has someone figured out how to do this in VBA code? This is exactly what I need.

  35. Yvonne says:

    How do I format labels in a scatter plot with over 200 labels to change. Is there no way of creating a column with the labels you want so that excel automatically includes these labels instead of the ‘series labels’

  36. Veedee says:

    Hi Chandoo

    I used the XY Labeler and it worked for me.

    Thanks
    Vipul

  37. jackie says:

    It’s Very Helpful to me ~ Thank A lot.

  38. Itzel says:

    Hi,
    Great info!! I want to know if it possible to hide a specific data label except when the cursor is in the data.

    For instance, I have a lot of wells plotted in a XY chart with a map as a background, the x and y are the coordinates of each well. However, when I want to know a specific well in the chart is so hard to find it, I need to check the coordinates in the chart and then find to which well correspond those coordinates. If I put all the well label the chart looks messy.
    What do you recommend me??
    Thank you in advance

  39. Jonatan Dahlgren says:

    I’ve used the tip from the tutorial and it works great until now. I have to move the entire xlxs file from my computer onto a flash drive to get it to my computer at work but I loose he Data Labels when I move my project. It just says cell reference instead.
    Anyone know how to move my file and still keep the Data Labels?
    Thank you in advance!

    • Chandoo says:

      This should not be the case. Did you make sure the cells to which data labels refer to contain the data?

      • Jonatan Dahlgren says:

        Well yes. To clarify, everything works perfect on my computer. And all the cells witch the data labels refers to contains data and there in the same workbook as the chart. Just on a different sheet. Then I make a copy of the file, put it on a flash drive and even tjen, when I open the file from the flash drive on the same computer everything works perfect. But when I put my flash drive in another computer (I’ve tried several) the data labels doesn’t refesh! All cells looks the same. The chart works just fine. Text box’s within the chart aswell. Just not the data labels. And I have somewhere between 150 and 200 of hem. I don’t want to update all of the manually everytime I move the file. Please help.

  40. harley says:

    Chandoo, thank you for your wonderful site – I really like the way you present info!

    Is it possible to make the custom data label table dynamic, ie up/down/sideways arrow depending on changing data? I don’t want to use any add-ins.

  41. Sally says:

    Thank you this has helped a ton and saved me time :)

  42. […] Do this for each of the labels and soon you’ll have labels for each of the groups. (here is another explanation for how to add custom labels with clearer step-by-step instructions for this […]

  43. JohnH says:

    Thanks, great tip.
    One problem, I cant seem to conditionally format the data labels now. EG, Red font for minus %, which I can to in a ‘normal’ data label.

    Any ideas how to get around this?

  44. JohnH says:

    @Hui
    Yes this is what I would normally use. But seemingly with the ‘custom data labels’ this doesn’t seem to work. I guess it makes sense as with the custom labelling, you could be putting anything in as a label.

    I’ve worked out a workaround now anyway, its a bit long winded, but it works!
    Cheers

Leave a Reply