• WORK
• TOOLS
• TRAINING
• VIEWS
• CONTACT

Arrows—when added to charts—can help to emphasize the direction and amount of change in the data. The chart below, for example, shows the total difference arrow to highlight how sales increased between 2014 and 2020.

1. Highlight the range A1:B8 and then, on the Insert tab, in the Charts group, click Insert Column or Bar Chart > Clustered Column. The chart should look like this:

HOW TO CREATE A BAR CHART WITH TOTAL DIFFERENCE ARROW IN EXCEL

Next, we need to prepare the setup for the horizontal reference line, the arrow and the label. For that we'll use scatterplots.

2. Enter the following labels "Reference line", "Arrow", "Label position" and "Label" in cells D2, D4, D6 and D8 respectively.
3. Enter the following formulas in the respective cells:
E2 --> 1.33

F2 -->  = B2
E3 -->  = COUNTA(A2:A8) + 0.6
F3 -->  = B2
E4 -->  = E3 - 0.1
F4 -->  = B2
E5 -->  = E4
F5 -->  = B8
E6 -->  = E4
F6 -->  = (F4 + F5) / 2
E8 -->  = TEXT(B8 / B2,"#,###.0") & "x"

The worksheet should look like this:

4. Next, select cell E2, press Ctrl + C to copy the cell into the clipboard, then select the chart and press Ctrl + V three times in a row to paste the data into the chart. The chart should look like this:

5.Right-click the Series2 data series in the chart and then, on the shortcut menu, click Change Series Chart Type.
6. In the Change Chart Type dialog box, under the Choose the chart type and axis for your data series set the Chart Type to Scatter with Straight Lines and Markers for the Series2, Series3 and Series4 data series and uncheck their Secondary Axis check boxes as per the below screenshot:

8. On the worksheet, right-click the chart and then, on the shortcut menu, click Select Data.
9. Next, In the Select Data Source dialog box, select Series2 and click on the Edit button under Legend Entries (Series).
10. In the Edit Series dialog box, refer to cell D2 in the Series name edit box.
11. Next, edit the Series X values to refer to the range E2:E3 and the Series Y values to refer to the range F2:F3. Press the Ok button to close the Edit Series dialog box.
12. Repeat steps 9-11 above to edit Series3 and Series4 by referring to D4, E4:E5 and F4:F5 for Series3 and to D6, E6 and F6 for Series4 respectively.
13. Press the Ok button to close the Select Data Source dialog box and the chart should look like this:

Lets do some cleaning.

14. Delete the horizontal gridlines and the vertical axis.
15. Turn off the border of the Chart Area by setting its Shape Outline to No line.

16. Turn off the line of the Horizontal Axis by setting its Shape Outline to No Line.
17. Change the chart font to Trebuchet MS, its color to black and size to 9.
18. Change the fill color of the Sales data series to light blue and its Gap Width to 50%. The chart should look like this:

19. In the chart, right-click the Reference Line data series, and on the shortcut menu select Format Data Series.
20. In the Format Data Series pane, with Series Options selected, click on the Fill & Line icon. Under Line, set Color to Black, Width to 1.25 pt and Dash type to Dash. The chart should look like this:

Let’s build this chart in Excel.

The dataset for the graphic above is in the range A2:B8 as shown in the screenshot below.

Views > Excel charts > How to create a bar chart with total difference arrow in Excel

HOW TO CREATE A BAR CHART WITH TOTAL DIFFERENCE ARROW IN EXCEL

21. In the chart, right-click the Arrow data series, and on the shortcut menu select Format Data Series.
22. In the Format Data Series pane, with Series Options selected, click on the Fill & Line icon. Under Line, set Color to Red, Width to 2 pt, End Arrow type to Arrow and End Arrow size to Arrow R size 3. The chart should look like this:

23. In the chart, right-click the Label position data series, and on the shortcut menu select Add Data Labels.
24. In the chart, right-click the newly added data label and on the shortcut menu select Format Data Labels.
25. In the Format Data Labels pane, with Labels Options selected, click on Value Form Cells under Label Contains.
26. In the Data Label Range dialog box set the Select Data Label Range to refer to cell E8. Click the Ok button to close the Data Label Range dialog box.
27. Under Label Contains, uncheck the Y Value and Show Leader Lines.
28. Next, under Text Options, click the Textbox icon and set all Margins to 0 and uncheck the Wrap text in shape option.
29. While the label is still selected, set its font color to red and make it Bold. The chart should look like this:

Thank you for reading, and I hope you found this useful. If you have any questions, find me on Twitter and ask me anything.

7.Press the Ok button to close the Change Chart Type dialog box and the chart should look like this:

30. In the chart, right-click the Sales data series, and on the shortcut menu select Add Data Labels.
31. Select the newly added data labels and set their font to Bold.
32. Finally add the chart title and data source and the chart should look like this:

You’ll notice that the chart is dynamic and can adjust automatically. Try it. Replace 2020 sales value with lower value such as 15.1. As expected, the chart will adjust and will look like this: