CAGR arrows can be used to display the compound annual growth rate between the first and last points of a bar chart. The chart below, for example, shows the CAGR arrow to highlight how sales increased between 2016 and 2023.

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

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

2. Enter the following labels "Arrow", "CAGR circle" and "CAGR Label" in cells D2, D6 and D8 respectively.
3. Enter the following formulas in the respective cells:
E2 --> 1
F2 -->  = INDEX(B2:B9,E2) + 6
E3 -->  = COUNTA(A2:A9)
F3 -->  = INDEX(B2:B9,E3) + 6
E6 -->  = (E2 + E3) / 2
F6 -->  = (F2 + F3) / 2
E8 -->  = "CAGR" & CHAR(13) & TEXT((B9 / B2)^(1 / E3) - 1,"0.0%")

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 two 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 click on Combo. Under the Choose the chart type and axis for your data series set the Chart Type to Clustered Column for the Sales data series, Scatter with Straight Lines for the Series2 data series and Scatter for the Series3 data series. Next, 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 by referring to D6, E6 and F6 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. Next, select the horizontal axis and set its Shape Outline, under the Format tab, to Black and its Weight to 1.5 pt..
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 Arrow 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 Dark Red, Width to 1.25 pt, Dash type to Dash and End Arrow type to Arrow. The chart should look like this:

Let’s build this chart in Excel.

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

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

CREATE AWSOME CHARTS

21. In the chart, right-click the CAGR Circle 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 Marker, set the Marker Option to Built-in and set its type to circle and its size to 31. Next, Under Fill, set Color to Dark Red, and Border to No Line. The chart should look like this:

23. In the chart, right-click the CAGR Circle 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. Next, select the Center option under Label Position.
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 white 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, adjust the size of the chart and the chart should look like this:

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