Excel Chart Tip: Add a CAGR line callout to a column chart

When you are reporting sales, shipments, clients served, or any measure where you want to show the growth rate over the period of time along with the values in each period, a column chart with a CAGR line callout can be a good visual to use. Instead of drawing the line on the chart and adding a text box, build the CAGR line as part of the chart so it updates automatically when the data updates.

In the video below you will see me build this chart. Here are the basic steps.

Calculate the CAGR

In a helper cell on the worksheet I use the RRI function in Excel to calculate the compound annual growth rate (CAGR). I then test the result by calculating the values for each time period using the CAGR to see if I get the final result. I don’t do this because I don’t trust Excel, I do it to reassure myself that I used the function correctly.

Decide where the CAGR line will go

Because the values of the columns can change, I set the % of the height of the column that I want the line to be from the top of the column. I usually use 15-20% because it gives me enough room for data labels above the column. I calculate a helper cell that has the value of this distance based on the tallest column in the chart.

Create the column chart data table

Creating the data table for the column chart is easy because it is a standard column chart. I check to make sure that the vertical axis will start at zero and if needed I include an invisible single point line with a zero value to force it (see this video for more on this technique).

Create the CAGR line data table

The CAGR line is a scatter with straight lines chart so I create the x and y values to position the chart above the columns. By using a scatter with line chart it gives flexibility to place the CAGR line between any two columns.

Create the CAGR label data table

The CAGR data label is also a scatter with straight lines chart that only has one point. This point is the middle of the CAGR line. The series name is text that includes the word CAGR and the value and is used as the data label.

Create the column chart

I use the column chart data table to create a standard column chart. I format it so the columns are wider, it doesn’t have gridlines, and it has formatted data labels above the columns.

Add the CAGR line to the column chart

I add a data series to the column chart. I change it to a scatter with lines chart and add the x values so it appears in the correct position. I then format the line with an arrow at the end to indicate the direction.

Add the CAGR label to the chart

I add another data series to the column chart. Since any added series adopts the chart type of the last series, this is already a scatter with lines chart and I can select the x and y values from the worksheet. I  add and format the series name data label so the CAGR label is in the middle of the line.

Full video