How to Add a Reference Line to a Bar Chart in Excel

A bar chart shows you the numbers. A reference line shows you what they mean.

In this tutorial we compare average temperatures across the G20 countries and
add a world average line cutting straight across the chart — so you can see at
a glance which countries sit above it and which sit below.

Excel has no “add reference line” button. We build it from a scatter series and
error bars, which sounds odd but takes about three minutes and works on any bar
chart you’ll ever make.

Watch: bar chart with a reference line in 8 minutes

Prefer reading? The same steps are written out below the video.

Step-by-step

A bar chart with a vertical line that marks an average or target puts every bar in context. The example compares the 2022 average temperature of the G20 countries with the world average of 15.2°C.

1. Insert a clustered bar chart

Select the countries and temperatures (A3:B26), go to Insert > Column or Bar Chart and choose Clustered Bar.

2. Move the labels out of the way

Countries with sub-zero averages, such as Canada and Russia, have bars that cover the labels. Right-click the vertical (category) axis, choose Format Axis and under Labels set Label Position to Low.

3. Add the world average as a new series

Set up a small table with a World average label, the average (15.2) as the X value and 0 as the Y value. Copy the average cell, click the chart and paste (Ctrl+V). A second series appears.

4. Change it to a scatter point

Right-click the new series, choose Change Series Chart Type and set it to Scatter. Then go to Select Data, edit the series, link the name to the World average label and set the X value to the average cell and the Y value to the 0 cell.

5. Turn the point into a line with error bars

Select the world average series, click the + button and tick Error Bars. Delete the horizontal (X) error bars. Format the vertical ones: direction Plus, end style No Cap, fixed value 1. Give the line a solid black, 1.25 pt, dashed style.

6. Make the line reach the top

Right-click the secondary vertical axis, choose Format Axis and set the maximum to 1. The line now runs the full height of the chart.

7. Style the marker and label

Format the world average marker as a built-in up arrow, size 10, solid black. Add a data label, place it Below and show only the X Value (untick Y Value and leader lines). Add data labels to the country bars too.

8. Clean up

Delete the gridlines, the secondary vertical axis and the horizontal axis, and remove the chart border. On the category axis tick Categories in reverse order so the first country is at the top. Colour the bars light blue, set their gap width to 30%, and add a title and the data source.

Go further

My Excel Charts, Infographics & Data Visualisation course teaches 20+ advanced charts like this from a blank sheet.