Adding a Benchmark Line to a Graph

Adding a Benchmark Line to a Graph

This simple line packs so much power. Adding a benchmark line to a graph gives loads of context for the viewer. Here’s how to make one right inside Excel. It’s so easy you might pass out.

My data table looks like this:TargetLine1

I have my data and I have the benchmark value listed next to each.

Highlight the group names and their data and insert a simple bar graph:TargetLine2


Then right-click on the graph and click Select Data. In that box that pops up, click the Add button to add a new series. In *that* dialogue box, select your Benchmark data. It’ll look like this, but don’t freak out:

TargetLine3Now it’s time to move that benchmark data from bars to a line.

In Excel 2013, I right-click on the orange benchmark bars and click Change Chart Type and then choose Line. You can do this in 2010, too, just click on the benchmark bars and then click the Change Chart Type button in your Layout tab and select a line graph. (This is a good time for me to mention that if I ever open a pub for data nerds, I’m going to call it The Benchmark Bar.)


I also added the word “Benchmark” to the line by adding a data label to just the left most point. Of course, at first Excel tried to give me the value of the point, but just right-click on the point again and click Format Data Label and then select Series name and unselect Value.

So this is cool but if you’d like your benchmark line to be a bit longer, you can just fiddle with the data table a little and select blank cells to add some space to the set of bars and associate the benchmark 65% with each blank spot. Did that sentence make any sense? It was heavy on the nerd-speak. Just make it look like this table here:


And now the graph is WAY more powerful! We’ve added so much interpretation to the data visualization, helping the viewer understand how far the first two groups are from the target and by how much Group C exceeded it. See a benchmark line in the wild in this dashboard report from Oregon Health Authority (scroll to, like, page 10). And think of other ways you could use this line, such as for targets, averages, national standard, yes keep dreaming!

This is *exactly* the kind of thing we were referring to in the Data Visualization Checklist when we said “Contextualized or comparison data are present.” Yer viewers want to be able to interpret the data – is it good? bad? the worst in history? best in the nation? A benchmark line gives that necessary context. BOOM!

This post is an excerpt from my latest book, Effective Data Visualization. It has loads of advice on the best chart type to use and how to make it in Excel.

52 thoughts on “Adding a Benchmark Line to a Graph
  1. Jason Burkhardt says:

    This was really helpful. We use a system with benchmarks, but we always add it to the chart in word afterword. This works much better. Also, you should call the pub y-bar….

  2. Natalie says:

    This is a VERY effective tool. I’ve been using this trick to measure financial results for years. In my graph, bars represent actuals versus forecasted results (a measure of forecast accuracy) for a given month and then I add two line graphs: 1) to represent average actual run rate and 2) one to represent run rate required to meet year-end target. This shows what we’re doing and what we have to do to finish the year on target. Data visualization is fun.

  3. Francis | Voilà says:

    Thanks Stephanie. Useful trick and clear explanation.

    It’s a wonder Excel still doesn’t have a function where you specify the target and it creates this kind of line. Comparing an actual with a target (average, standard, previous performance…) is such a basic feature of a graph. The amount of workarounds needed to get Excel to make good graphs is excessive, especially after 27 years in existence.

    • Stephanie Evergreen says:

      And I find that people often have benchmarks/targets/whatever but don’t graph it because it isn’t automatic in Excel. While there are a lot of workarounds, I actually find the customizability of Excel to be an attractive feature, so long as you know how to leverage it.

  4. Jenna Boyer says:

    Please open the pub!

    Also, I am passing this post around to my colleagues!

  5. Ashley says:

    Hi Stephanie –
    Thanks so much! I’m having a problem trying this out though. When click Select Data, then Add, and I choose the benchmark data, it only adds the orange bar to Group A…not to Groups B or C. I’m using Excel 2013. Any ideas? I’ve tried it a few times. Thanks!

    • Stephanie Evergreen says:

      When you select the benchmark data, are you selecting for all 3 groups? That should definitely give you 3 orange bars. Send me your file and I’ll take a look.

      • Elizabeth says:

        I am also having the same issue that Ashley described. Was there a solution? Love your blog!

        • Stephanie Evergreen says:

          Make sure you have one benchmark value for each one of your performance values and make sure you select all 3 of them when you are selecting data. Also double check that you are highlighting the values in the Value box, not in the Name box. That’s a common mistake. If you are still running into trouble, send me your file and I’ll take a look.

  6. Yueyue says:

    Hey Stephanie, thanks for my post. I tried to play with this trick. It worked. However, in the graph that I created the line chart and the bar chart positioned the same value differently. I set the bench mark and one of my bar value to be the same. However, they did not overlap causing the misinterpretation of one bar not reaching the benchmark when in fact it does. Can you help me with this? I am willing to send you my file.

  7. Justin P says:

    Hello, do you know how I can include two goal/benchmark lines in a bar graph? For example I want to track the Donations progress…I want to have a 1st goal at $85, then a second goal at $100. I would like to be able to see the current donations progress measured against those two goals. Thank You!!!

    • Stephanie Evergreen says:

      Yes, Justin, you can add two benchmark lines. Just follow these same steps and add another set of data for your second benchmark line. You’ll want to take care to distinguish them from one another. Perhaps make one gray. Or possibly dashed. Another way could be to make it more like a bullet graph, where your goals are gray bars in the background. See that tutorial here.

  8. Craig Young says:

    Hey Stephanie. Thanks for this great article. I am having a small problem with mine. I am using a bar chart that displays 3 bars horizontally not vertically. Is there a way I can make the benchmark line go vertical cutting through the bars at a specific point? For me the line is sitting horizontally as a separate data set (which would be fine if the bars were vertical like most bar charts) Any help greatly appreciated.

    • Stephanie Evergreen says:

      Hey Craig,

      It’s definitely a bit trickier. Basically, to get the benchmark line going vertically, you need to plot it like a scatterplot (no markers, just a line). So you’ll need to fake in a set of y-axis values. See my tutorial on dot plots to get a sense of what that will do for you. The graph may add in an extra set of axis labels but you’ll just delete them. I hope that all makes sense!

  9. Stephanie Means says:

    Hi Steph, Great job!!! I was able to get three lines with no major problems. I plan to use this in my dissertation! Let’s see how it goes!!

  10. Cyndi says:

    Hello. I have a scatter plot with 59 data points. I need to create a quadrant chart (basically splitting the chart into 4 parts) using 2 benchmarks. 1 for X-axis, 1 for Y-axis. Is there any easy way to do this?

  11. Cecilia says:

    Hi Stephanie,
    This is so super helpful and I am almost there. I’m using Excel 2010 and went to the Layout tab but don’t see anywhere to Change Chart Type to change benchmark data set from bar to a line. Am I missing it somewhere?

    • Stephanie Evergreen says:

      Check the Design tab. You can also right-click on the series and you should see a menu option there to change chart type.

      • Mieko says:

        This is very useful and I followed it and worked good until the benchmark line I wanted was at 75% but showed at 100%. How can I adjust? Yes my data has correctly 75% all way across the category.

  12. Rajesh paul says:

    Thanx . it was helpful for me.

  13. JayS says:

    I like this blog. I live a great deal of my pitiful existence within Excel. People being who they are, I am fairly happy to bury myself in the odd spreadsheet and, to that end, am grateful to have found this blog. So here’s one for ya — I’ve built a model that projects volatility ranges for financial time series data — you know, a stock chart… eniway, I’ve managed to set up the stock chart but now I want to plot static price levels on the chart, much like your “benchmark” line. I can do this on a regular bar chart, but cannot for the life of me figure how to do this on a stock chart. I’m beginning to lose hope. Ideas?

    The data looks something like this:
    With the data below.
    The first five columns form the stock chart, The next four columns are horizontal lines at four different price levels, that should be superimposed on the chart, with only one X axis…

  14. JayS says:

    Re: The above — I did it! So…nevermind. But thanks for the blog!
    – From a fellow (expatriated) Oregonian

  15. Pradeep says:

    Brilliant Stephanie 🙂 It worked like a charm…

  16. Kareem says:

    Thanks for the post! Does this also work for pivot charts? When I open the “Select Data” window, add, edit, and remove are all grayed out and I can’t do anything about it.

    • Stephanie Evergreen says:

      Hi Kareem, yes it is possible. I can’t remember the exact process right now but keep experimenting. It’s definitely doable.

  17. Sam Olson says:

    Hey Stephanie, awesome stuff. Working on a productivity analysis for a health system where I’m measuring hours worked vs. productivity for about 30 different departments. I have a calculated productivity (hours worked vs productivity) that I would like to chart for each department with the corresponding productivity benchmark. I have 3 years of data but the benchmark is the same for each year so when I graph it the benchmark doesn’t appear because it’s only for “one of the years. Is there a way to include the benchmark for each year without having 3 lines of the same data for each department? Sorry if that’s terribly confusing. Much appreciated.

    • Stephanie Evergreen says:

      Shoot me an email with the file so I can see what you’re talking about. Stephanie at StephanieEvergreen dot com. I’m sure we can figure out the answer.

  18. Robert says:

    Hi Stephanie,
    I followed your process for Excel 2013, but was not getting the same results. Both bars would turn to lines when switching the chart style to line after right clicking. Only when I chose ‘Combo’ rather than “Line’ did my variable data stay in bar chart form.
    Thanks though. Your process was easier to follow than many others on the internet.

    • Stephanie Evergreen says:

      I’ve done this in 2010 and 2013 and it works in both. Be sure you are clicking on just one series – the Benchmark one – and right-clicking on it. Right-clicking anywhere else in the graph will also get you the option to change chart type but it will change the whole chart, not just the Benchmark series. Or go with Combo if that’s faster for you. Glad to hear you found a solution.

  19. Jay says:

    Thanks for the advice, but it doesn’t seem to be coming up how I want.
    In your example, how would I make it so Groups A,B and C all have different benchmarks?
    Whenever I try, I get one line that goes up and down across the grass, but I would prefer to have a flat straight line for each one individually, if that makes sense. (So 3 benchmark lines, but they only last the length of Group A,B,C respectively.)
    Does that make sense?

  20. Gung eka says:

    Thanks for the tutorial.
    It was helpful.

  21. Jennifer Ramirez says:

    Can you do the same with a Horizontal cluster bar and add a Vertical target line?

  22. Scott Mullett says:

    Hi thanks for displaying the tips here. I am looking to do something slightly different in that I have 8 values for each month and now want to display the previous years value over the same graph, but I cannot figure it out I am assuming that its something to do with how my data is stored so the graph would have bars for this years results by month with the corresponding value in the same column of what the previous years value was as a cross, any ideas on how best to layout my data to get excel to do this?

  23. Jeff says:

    I have items I am trying to graph. The first one is a bar showing numbers from this year, the 2nd one is a line showing this years goal, and the 3rd is showing the average from last year. When I first inserted the graph, I set it up as a bar chart, then I changed the the 2 benchmark bars to lines. Everything looked good until I changed last years average to a line. When I did this, it added this years goal and last years average, and created a line at that point. Any ideas what might be causing this. Like I said the totals looked good when they were all bars.

    • Stephanie Evergreen says:

      Make sure that each one is its own series. Not all part of a single series. Then you can change series chart type.

  24. Rusty says:

    Hi, Stephanie, thank you very much for this entry.
    I have been asked to create a combination chart (vertical bars plus a line) in Excel 2013, very similar to the one you present, but with a data table at the bottom. (If you create the chart and then go to Design > Quick Layout, it’s the second format in the second row.) The problem: we would like to display the data for the bars in the table, but not the data for the benchmark (which will show as the same number over and over.) Do you know of a way to do this?

    • Stephanie Evergreen says:

      Sorry, I don’t know of a quick way – maybe turn other benchmark data white? – cause I try not to show tables.

  25. Chathuranga Somarathna says:

    Thanks, It is very helpful 🙂

  26. Bob says:

    Just used this and it worked nicely. Thanks. PS U R beautiful.

  27. Brij Patel says:

    Why doesn’t microsoft just give us an easy way to do this?

    • Stephanie Evergreen says:

      They make the bar and line combo chart a default graph type but the line doesn’t extend all the way across the graph like I have it here.

  28. Patrick says:

    I haven’t read through all the comments so you may have answered this already but is there a way to make the same “benchmark” line without having to input the 65% 3 or 5 times. I’d like to just place that % once on the excel spreadsheet and have a line go straight across without having to input that % as man times as I have bars. Does this makes sense?

Leave a Reply

Your email address will not be published. Required fields are marked *

RT @LizTweets: Use the free StateFace font from @ProPublica to incorporate tiny US state icons into your text. How cool is that?! https://t…

RT @EvanSinar: Using The Gauge Diagram for Qualitative Data Visualization @evergreendata #dataviz…

@cisey you're welcome! So fun!

5 hours of detention by immigration cannot keep the #dataviz from #Canada.

RT @net2van: #Qualitative Data Visualization: The Gauge Diagram When numbers aren't a fit for your #dataviz @evergr

Audible groans when this slide came up at #evalYOW17

RT @katiedrumm: Excuse me, GMG is hiring a graphics and data viz editor. Please RT and tell your talented friends:

So much fun keynoting & workshopping #dataviz at #evalYOW17