Excel chart data labels are quite boring. While there is not much you can do with these, a bit of excel trickery can add some glamor to it. In this post, I will show you how to show trends in chart labels in a bar chart. Something as shown below:
Show Positive/Negative Trend Arrows in Excel Chart Data Labels
Suppose I have the data as shown below:
If I plot 2013 data only, it won’t tell us how much the sales of a product have increased/decreased over the last year. However, I can do this by changing the chart data label to show the YoY change. To do this, we first need to modify our data set a bit.
Changes made to the data set
- Added YoY change in Column D
- Added an upward arrow in B11 and downward arrow in C11
- To do this, go to Insert –> Symbol
- In the Symbol dialog box, select Arial font (or whatever you are using), and scroll down to find the arrow symbols
- In column E, use the following combination of TEXT and IF formula:
When you have the data set in place, create a bar chart using 2013 values. You will get a chart as shown below:
Now to show the YoY change instead of 2013 values in chart labels, follow the steps below:
For Excel 2013
- Click on any of the data labels
- Right-click and select Format Data Labels
- In Label Option, check Value from Cells, click on Select Range and select the cells the new labels (E3:E9 in this case)
For Excel 2010
- Select the first data label (click on it twice)
- Click on the formula bar and type =
- Select E3
- This will change the first data label to display the value in E3 (along with the arrow). Now you can do this for all the data labels (a bit of manual labor – but a straight forward method)
That’s it!! You would have your chart ready.
- Color Negative Chart Data Labels in Red with a downward arrow.
- Creating Pareto Chart in Excel (Simple + Interactive).
- How to Spot Data Point in Excel Scatter Chart.