How to Create a Target Line in Excel Charts

When you put data into an Excel spreadsheet, the program provides multiple options for displaying your data. One of those options is a bar graph, a chart that presents data along the X and Y axis. You can use the bar graph to track goals or see how information has changed over time.

With a bar graph, you have several rows of data and may want to see how those rows stack up to your goal. Drawing a target line shows when you met or exceeded the goal.

What is an Excel Target Line?

A target line runs horizontally across vertical bar graphs and vertically across horizontal bar graphs. It intersects the graph at the value you define. For example, if you wanted to gain 100 new customers each month, the target line displays that value, and seeing the other data in the graph reach that line would mean you’ve met your goal.

How to Create a Target Line

Creating a target or goal line in Excel is simple, but there are some steps involved.

1. Input Your Data

Open a new spreadsheet and input your data. You’ll need to add data to three columns on the spreadsheet.

The first column can be the duration of what you’re tracking, such as months. In row 1, type “January,” then in row 2 of the same column, type “February.” Continue until you’ve entered each month.

The second column should contain the numbers you’re tracking, such as the number of new accounts or sales figures. Input the amounts in the row that corresponds to the appropriate month. If you had 10 sales in January, type 10 in row 1, column 2.

The third column should contain your target number for each month. For example, in January, you wanted to make 15 sales. Type 15 in row 1, column 3.

2. Create a Bar Graph

Now, you need to turn your data into a bar graph. Highlight the three columns, then click the “Chart” button. Choose “Bar,” then 2-D or 3-D and horizontal or vertical.

Excel will put a bar graph into your spreadsheet.

3. Convert Your Target to a Target Line

Once you create the bar graph, you’ll have a chart with vertical or horizontal lines for the figures in columns 2 and 3. You want a single horizontal or vertical line for your target data, the figures in column 3.

Hover your mouse over the “Targets” column, then choose “Change series chart type.” Select the “line” option to turn your target data into a single vertical or horizontal line that intersects the bars on the chart.

Contact Agio Today

Agio offers end-user Microsoft 365 support. Contact us today to learn how to make the most of Excel and other Microsoft apps.