Blog

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.)

TargetLine6

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:

TargetLine7

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.

40 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. http://stephanieevergreen.com/easy-dot-plots-in-excel/ 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:
    [DATE] [OPEN] [HIGH] [LOW] [CLOSE] [EXT H] [MID H] [MID L] [EXT L]
    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…
    Boom!

  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.
    Robert

    • 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:

    Hi,
    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:

    Hi.
    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?

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

RT @BethBrod: @shapr I learned about Tufte today! I heard the best/most practical to start for #eval is "Presenting Data Effectively" by @e

Having fun doing #dataviz with @IDEXX but campus is so large I got lost on the way back from lunch.

RT @SAGE_Methods: Listen to @visualisingdata @albertocairo @camoesjo & @evergreendata discuss their #data viz publishing experiences https:…

RT @Ko_Ver: My New Favorite Graph Type: Overlapping Bars - https://t.co/y07YkEwxPA by @evergreendata #dataviz

National newspaper publishers meeting at my hotel & wanted to #dataviz flash mob somehow https://t.co/NctbI0KxAC

RT @disparityreport: Attended awesome workshop on data visualization yesterday! FF @evergreendata https://t.co/yRm4yxBfOF

The danger of real time dashboards & the need for slow data https://t.co/CdBnjCyY3Y

Into R? Grab the code to make a few of the #dataviz on my latest book here https://t.co/vYgCyR66yZ Thanks @dckallos !