How To Add a Target Line in Excel (Using Two Different Methods)

There are a variety of features you can use on Microsoft Excel to help display your data. A target line is a useful feature that allows individuals to see how their actual data compares to their initial goal. Understanding the various ways to insert a target line in your Excel bar graph can help you understand how you're doing and whether you need to make any adjustments to meet your goals.

In this article, we discuss what a bar graph in Excel is, explain what a target line is, provide steps for how to add a target line in Excel using two methods and describe when you may use this.

What is a bar graph in Excel?

A bar graph in Excel is a chart that displays information in a spreadsheet. This uses the height or length of lines to represent a numerical value. Typically the Y-axis represents an amount, such as sales, and the X-axis represents a period of time, such as months. Individuals can use bar graphs in Excel to show patterns and changes over time. It's also a useful visual aid for highlighting differences in the amount, size or volume.

Related: A Guide to Bar Graphs and 6 Steps on How To Draw a Bar Graph

What is a target line in an Excel bar graph?

A target or goal line in an Excel bar graph is an addition someone can add to show how performance compares to their goal. For vertical bar graphs, this line runs horizontally, and for horizontal bar graphs, it runs vertically. Target lines are benchmarks that help an individual or organization visually see the results of their actions and whether they need to make any adjustments.

For example, if a sales team has a daily sales quota of 15, the target line can run horizontally from the 15 on the Y-axis. This shows them whether they met their daily sales quota.

Related: 25 Best Excel Tips To Optimize Your Use

How to add a target line in Excel by adding a new data series

Use the following steps to add a target line in your Excel spreadsheet by adding a new data series:

1. Open your Excel spreadsheet

To add a target line in Excel, first, open the program on your device. Then create a new spreadsheet by clicking "New." You also can open an existing one with the data you want to use for your bar graph.

2. Enter your data

Next, enter your data into the spreadsheet columns. The first column can include the duration you're measuring, such as months. In column "B," place your actual numbers for your activity, and in column "C," enter your target numbers.

3. Create a bar graph

Once you've entered your data and target numbers, click and drag your mouse over the columns you want to appear in the bar graph to select them. Then go to the top menu and press the "Charts" button. From there, select the "Bar" icon and pick from the 2D and 3D options.

Related: How To Make a Graph in Excel

4. Convert your target number to a target line

After selecting a bar chart, Excel inserts a graph on your spreadsheet with columns representing the actual numbers and target numbers. To change this format, move your mouse over the target columns in your graph. When the options box appears, press "Change series chart type." This gives you a chart option to use for the target columns that you selected.

5. Select the line option

To make your target column a target line, select the "Line" option in the menu. You can pick the standard line or one with data points plotted. After selecting the line you want to use for your target line, press "OK." This updates your Excel bar chart to show the columns with the actual data and a target line.

6. Save your work

Once you update your bar graph to include the target line, you can save your work so you can access it later and share it with others. To do this, go to the top of your page and press "File." Then click "Save as" and designate where you want to save the bar graph on your device. After choosing the location and renaming the file, press "Save."

Related: Advanced Excel Skills: Definition and Examples

How to add a target line in Excel by using paste special

Here are some steps to take to add a target line in Excel using the paste special:

1. Open Excel on your device

In order to add a target line in Excel, first, open the program on your device. Either click on the Excel icon or type it into your application search bar. Once you open Excel, you can either create a new spreadsheet or edit an existing one.

2. Type in your actual data

Begin typing your actual data in the appropriate columns. The first column can include the duration you're measuring, such as years if you're reviewing your annual revenue. In the second column, enter the actual number for your activity. For example, this could be the total amount of money earned each year.

3. Create a box with your target data

In two separate columns, enter the data for your target. In the first column, type the row for your line to start. Below that, type the number for it to end. For example, if your actual data ranges from row one to row 10, enter one in the first box and 10 in the box below it. In the next column, enter the target number next to the start and end numbers.

4. Select the target data

To create your target line with the paste special, select your columns with the target data. You can do this by hovering your mouse over it and highlighting the column. If you want to copy the data using your keyboard, press " Ctrl" and "C."

5. Apply the paste special

Once you select your target data, go to the menu at the top of your spreadsheet and press "Home." Then select the "Paste" option and click on "Paste special." The keyboard option to apply the paste special is "Alt," "E" and "S."

6. Check the new series option

In the "Paste special" drop-down box, check the circles next to "New series" and "Columns." Also, click on the boxes next to the "Series names in first row" and "Categories (x labels) in the first column." Then press the "OK" button.

7. Select the change series chart type

After performing the previous actions, Excel creates a bar chart with your new data series. Right-click the new series and select the "Change series chart type" option. From the menu, select "Scatter with straight lines" for the chart type and uncheck the "Secondary axis" option. Press "OK" to save your changes.

8. Format the axis

Once a target line appears, right-click the X-axis to adjust its span. Then select the "Format axis" option from the context menu. Once the format axis pane appears, check the circle next to "On tick marks," which is below the "Axis options" tab.

Uses for a target line in a bar graph

You can use a target line in a bar graph to compare your achievement with your initial goal. This is a helpful feature if you work in sales because it can show you whether your monthly sales met your sales targets. Bars that extend beyond the target line represent activities that exceeded your goals and those below the target line are activities that didn't meet the goal. Target lines are also helpful if you're tracking a constant target for a duration of time, such as if you're on a marketing team that wants to track how many website visitors you get each month.

Please note that none of the companies mentioned in this article are afflicted with Indeed.