Excel Logo on a gray background

Include captions in your Microsoft Excel graphs to provide rich and meaningful labels. The labels can be used to display extra information that is not plotted on the graph. By linking them to cell values, you can make these captions dynamic.

Why Include Captions in Excel Graphs?

When you create a chart in Excel, you are provided with label elements. These include the chart title, data labels, and axis titles. These labels can be very useful for displaying extra information in the chart, especially when you use cell values for Excel chart labels.

The following chart uses a link to a cell value to show the total cells in the chart title.

Dynamic chart title using cell values

However, you are not limited to these built-in labels. You can include captions in Excel graphs by adding text boxes.

This chart was created using the following set of data.

Sample data for the chart

Create the Caption Text

Let’s add a caption to tell more of the story of this data. We will add a caption to convey the top product and its sales total.

First, we need to calculate the data we want to display. In cell D2, the following formula is used to return the maximum sales value.

=MAX(B2:B7)

Calculate the maximum value

We can then use a formula in cell D3 with the INDEX and MATCH combination to return the name of that product.

=INDEX(A2:A7,MATCH(D2,B2:B7,0))

INDEX and MATCH to return product name

In cell D4, we can make a creative caption from these calculated values.

=D3&" is the top product with "&D2&" sales."

Creative text for a caption

Add Captions to an Excel Graph

Before we add the caption, we need to resize the plot area of this chart to make some space for it.

Click on the plot area to select it, then drag the resize handle to make room between the chart title and the chart values.

Resize the plot area

We will include the caption by inserting a text box. Click Insert > Text Box and then select the chart to insert it.

Insert a text box

Next, click in the Formula Bar, type “=” and then select cell D4 (the cell containing the caption text).

Refer to the caption text cell

Press the Enter key.

The caption text is shown in the text box and can be moved and resized into an appropriate position on the chart.

Caption on an Excel chart

To finish the caption, format it to a light grey so that it is not as impactful as the chart title. Click Home, the list arrow for “Font Color,” then select a light grey.

Formatting with a light grey font colour

This is one example of including captions, but it is up to you to be creative. You can show whatever information you want your chart to convey to go beyond the standard charts.

Completed Excel chart with caption

Alan Murray Alan Murray
Alan Murray has worked as an Excel trainer and consultant for twenty years. On most days, he can be found teaching Excel in a classroom or seminar. Alan gets a buzz from helping people improve their productivity and working lives with Excel.
Read Full Bio »

The above article may contain affiliate links, which help support How-To Geek.