Create Convincing Visualizations by Adding Reference Lines to Your Excel Charts

Here is another excel tip from the Microsoft official Excel blog that discusses how to create powerful visualizations in your excel charts:

“by Diego M. Oppenheimer – on July 21

Today’s blog post is brought to you by Cathy Harley a Program Manager on the Excel Chart team.

Have you ever wanted to add a horizontal or vertical line to your chart to indicate a key value, sales threshold, important date, or the average of your data? Are you looking to impress managers and convince clients with your superb charts? Reference Lines (see the dotted black line on the chart below) can be powerful tools in effectively communicating important points in your data. There is currently no specific built-in functionality for creating Reference Lines in Excel 2010, but there are still a few ways to add them to your chart.
Microsoft Office Excel - Screenshot of Chart

Here we’ll give you the steps to make these exciting visualizations—but we also want to hear uses you have for them, so tell us in a comment below!

Method 1: Simply Adding an AutoShape to Your Chart

If your line does not need to be exactly positioned, then the easiest method by far is to use a Shape to add a line to your chart. Simply click the Insert Tab on the Ribbon and select Shapes.

From here, click on the straight line and click and drag the object onto your chart in the location that you desire.

While this method is easy, it does have its downsides. Since the line is separate from your chart, every time you move or resize your chart you must also move or resize your line! Even without physically dragging your chart around alignment problems can arise. Say you add a reference line to a chart like this at 3.5, which is an important target for your data:

Microsoft Office Excel - Screenshot of Chart wth lines

And then a value in your data changes, changing the value of the axis and leaving the line in a not-so right position for you to have to replace manually:

Microsoft Office Excel - Screenshot of Chart wth lines

Never fear, a few extra steps in Method 2 and your line can stay in the perfect position!

Method 2: Creating an Additional Series

Perhaps you need to place your line in a very exact location on your chart. Have no fear—you can simply create an additional data series to plot your line

Let’s say you have a table like this:

Microsoft Office Excel - Screenshot of source data table

And you have a chart like this:

Microsoft Office Excel - Screenshot of Chart

For a year-end review meeting, you’d like to be able to show how all of your salesmen did in comparison to the average target monthly sales goal. I.e., you want to end up with this:

Microsoft Office Excel - Screenshot of Chart

Never Fear! You can be here in a few simple steps.

First, create a new table on a blank portion of your grid that contains the value you want to plot (we’ll call this “Sales Goal” in our example, with a value of 16) and dummy x values of 0 and 1. The header that you use at the top of your second column, which we called “Sales Goal,” is the header that will appear next to your reference line in the chart legend. You also shouldn’t be concerned with constraining the data in your columns to a fixed value like we did—it can also be a formula! Your new table should look like this:

Microsoft Office Excel - Screenshot of source data table

Make sure that if you use a formula in place of a fixed value that you use the same one for both rows.

Now, you’ll want to highlight these 6 cells in the table above and either right click or use a keyboard command to copy them. Then head on over to Select your entire chart so that it is surrounded by the silver band like this:

Microsoft Office Excel - Screenshot of line chart

Next, you’ll want to look up to the Ribbon, and Select the menu from Paste under the Home tab.

ArrowMicrosoft Office Excel - Screenshot of the paste button

Select Paste Special, and you’ll be brought to this new dialogue:

Microsoft Office Excel - Screenshot of the paste special

Make sure you select to Add the cells as New Series and check the boxes for both Series in First Row and Categories (X Labels) in First Column. The Values(Y) will be a default as the same style as your first series (Column, in this case).

You’ll now have two extra bars added to your chart. The next step is to right click the bars of the new series, the part of the chart shown selected below:

Microsoft Office Excel - Screenshot of a line chart

Right clicked the selected area, and choose the Change Chart Series Type option.

From here, select the X Y Scatter option with no data points:

Microsoft Office Excel - Screenshot of  X Y Scatter option

Click ok, and your graph should look like this:

Microsoft Office Excel - Screenshot of Chart

Clearly that purple line is not where we want it to be. Do not fear—we’re only a few steps away from bringing your Sales Goal line to its correct location in the chart.

Select the secondary X-axis (i.e., the one on the right hand side of the chart), and double click:

Microsoft Office Excel - Screenshot of Chart wth lines

You’ll now get this box:

Microsoft Office Excel - Screenshot of Dialog

Set the Minimum to Fixed and 0 and the Maximum to Fixed and 1. Then make sure to select None as the menu option for both Major and Minor tick mark type.

Finally, select the secondary Y axis:

Microsoft Office Excel - Screenshot of Chart wth lines

And simply hit Delete on your keyboard. Magically, your graph should now look like this:

Microsoft Office Excel - Screenshot of Chart wth lines.

Finally, you’ll probably want to get rid of that stubborn secondary axis on the top of your chart. The easiest way to do this is to right click on the axis and change the font color to match your background—which is this case is just white!

Microsoft Office Excel - Screenshot of Chart wth lines

Congratulations- you’ve successfully inserted a horizontal reference line into your chart with a label in the legend! Using this method will maintain your line in the correct position, even if the chart is resized!


About Advanced Network Consulting:

Looking for a local, personable, professional IT Consultant to service your LA or Orange County business?  Advanced Network Consulting offers onsite, ongoing IT support for businesses with 5-75 employees.  Whether your business needs network maintenance, hardware upgrades, or a system redesign, ANC provides customized technology solutions.  Not sure where to start?  Start by giving us a call for a complimentary onsite evaluation.  562. 903.3992  We look forward to meeting you.