Change Data Labels in Charts to Whatever you want [Quick Tip]
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:
You can change data labels and point them to different cells using this little trick.
First add data labels to the chart (Layout Ribbon > Data Labels)
- Define the new data label values in a bunch of cells, like this:
- 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.
- Go to Formula bar, press = and point to the cell where the data label for
that chart data point is defined.
- Repeat the process for all other data labels, one after another. See the screencast.
Points to note:
- 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.
- That brings us to Rob Bovey’s Chart Labeler Excel Add-in. This free tool can automate the whole custom chart labeling for you.
- 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 ).
Recently in Charting: