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

1. Open your Excel spreadsheet. To add a target line in Excel, first, open the program on your device. …
2. Enter your data. …
3. Create a bar graph. …
4. Convert your target number to a target line. …
5. Select the line option. …
6. Save your work.

Plotting two different types of data points on the same graph will accomplish the task. Combining two chart types into one was a time-consuming, multi-step process in earlier iterations of Excel. You might be left wondering, “Wow, why hadn’t they done it before,” as a special Combo chart type is offered by Microsoft Excel 2013, Excel 2016, and Excel 2019.

How to Add a Target Line in an Excel Graph

What is a target line in an Excel bar graph?

Someone can add a target or goal line to an Excel bar graph to show how performance compares to their goal. This line runs horizontally for vertical bar graphs and vertically for horizontal bar graphs. A person or organization can use target lines as benchmarks to see the results of their actions and determine whether they need to make any adjustments.

For instance, if a sales team’s daily sales quota is 15, the target line can start at 15 on the Y-axis and extend horizontally. This shows them whether they met their daily sales quota.

What is a bar graph in Excel?

In Excel, a bar graph is a chart that shows data from a spreadsheet. This represents a numerical value by using the height or length of the lines. Usually, the X-axis represents a time period, such as months, and the Y-axis represents an amount, such as sales. Excel bar graphs can be used by people to display trends and changes over time. Additionally, it serves as a helpful visual aid for emphasizing variations in the quantity, size, or volume.

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

By including a new data series, use the procedure below to add a target line to your Excel spreadsheet:

1. Open your Excel spreadsheet

Open Excel on your device first before adding a target line. Then create a new spreadsheet by clicking “New. Additionally, you can access an existing one and open it with the information for your bar graph.

2. Enter your data

Next, enter your data into the spreadsheet columns. The duration you’re measuring, such as months, can be listed in the first column. Enter your activity’s actual numbers in column “B,” and your target numbers in column “C.”

3. Create a bar graph

After entering your data and goal values, select the columns you want to appear in the bar graph by clicking and dragging your mouse over them. After that, select “Charts” from the top menu. Choose between the 2D and 3D options by clicking the “Bar” icon from there.

4. Convert your target number to a target line

Excel adds a graph to your spreadsheet with columns for the actual numbers and target numbers after you choose a bar chart. Move your mouse over the target columns in your graph to alter this format. When the options box appears, press “Change series chart type. This provides you with a chart option for the target columns you chose.

5. Select the line option

Choose the “Line” option from the menu to convert your target column into a target line. You can select a plotted data point or a standard line. Press “OK” after choosing the line you want to use as your target line. With this update, the columns containing the actual data and a target line are displayed in your Excel bar chart.

6. Save your work

You can save your work so you can access it later and share it with others after you update your bar graph to include the target line. To do this, select “File” from the menu at the top of your page. Then select “Save as” and enter the location on your device where you want to save the bar graph. After choosing the location and renaming the file, press “Save. “.

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

To add a target line in Excel using the paste special, follow these steps:

1. Open Excel on your device

Open Excel on your device first before attempting to add a target line. Either select the Excel icon or enter it into the search field of your application. When Excel opens, you can either make a new spreadsheet or make changes to an existing one.

2. Type in your actual data

Begin typing your actual data in the appropriate columns. If you are examining your annual revenue, the first column can include the time period you are evaluating, such as years. Enter the actual number for your activity in the second column. As an illustration, this might be the total yearly income.

3. Create a box with your target data

In two separate columns, enter the data for your target. Fill out the first column with the row where your line should begin. Below that, type the number for it to end. If your actual data falls between rows one and ten, for instance, enter one in the top box and ten in the box below it. Add the target number to the start and end numbers in the following column.

4. Select the target data

Select the columns containing the target data in order to create your target line using the paste special. You can do this by highlighting the column with your mouse while it is hovering over it. Press “Ctrl” and “C” on your keyboard to copy the information. “.

5. Apply the paste special

After choosing your target data, select “Home” from the menu at the top of your spreadsheet. Then click “Paste special” under the “Paste” menu option. Alt, E, and S are the keyboard shortcuts for the paste special. “.

6. Check the new series option

Circles next to “New series” and “Columns” should be checked in the “Paste special” drop-down box. Aside from that, select the checkboxes next to “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

Following the aforementioned steps, Excel produces a bar chart using your new data series. Choose “Change series chart type” from the shortcut menu when you right-click the new series. Choose “Scatter with straight lines” as the chart type from the menu and deselect “Secondary axis.” Press “OK” to save your changes.

8. Format the axis

Right-click the X-axis after a target line appears to change its span. Then select the “Format axis” option from the context menu. Check the box next to “On tick marks” in the format axis pane, located below the “Axis options” tab.

Uses for a target line in a bar graph

In a bar graph, you can use a target line to compare your progress to your original objective. If you work in sales, you can use this feature to determine whether your monthly sales were in line with your sales goals. Bars that extend past the target line indicate activities that went above and beyond your goals, while those that are below the target line indicate activities that fell short of your objectives. If you’re on a marketing team and want to track how many website visitors you receive each month, for example, target lines are useful for tracking a constant target over time.

Please be aware that none of the businesses mentioned in this article suffer from Indeed

FAQ

How do I add a target value Line in Excel?

1. Select the chart and go to Design –> Select Data.
2. In the Select Data Source dialog box, Click Add.
3. Type “Target Line” as the series name in the Edit Series box, and then choose the Target Value cell under Series Value.
4. With this, a bar chart will only be added for the first data point (January).

How do you add a reference Line in Excel?

Click the Insert tab in the Excel ribbon. Click the drop-down arrow next to the Line chart button in the Charts section, then select the first selection under 2-D Line. Select the most effective visualization by clicking the Chart Styles button to the right of the chart.