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 about you?
Recently in Charting:
- Use Paste Special to Speed up Chart Formatting
- Use Script font to make better incell charts
- Make a Thermo-meter Chart in Excel
- and many more …
My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.
Thank you and see you around.
Leave a Reply
|« How to Get “Either-Or” Condition in Data Validation?||Data Tables & Monte Carlo Simulations in Excel – A Comprehensive Guide »|