How to Add Target Line to Pivot Chart in Excel (2 Effective Methods)

The article will show you how to add a target line to the Pivot Chart in Excel. Adding a target line will always help you remember what target you need to achieve in any sector of your working or personal life. It can be an educational or health issue or business aspect if you want to use Excel in your daily life. Suppose you are a businessman and you have a target of achieving the best sales and profits in your organization. Using a Pivot Chart will be the best option to visualize the progress of your business. You can add a target line in it so that you can always be aware of your achievement.

Download Practice Workbook

2 Ways to Add Target Line to Pivot Chart in Excel

In the dataset, we have sales information about some grocery items. Suppose the owner of the shop set a target of the sales amount for each product over a period. We will show you how to add this amount as a target line in the Pivot Chart.

1. Applying a Target Value to Add Target Line to Pivot Chart

One of the easiest ways that you can use to add a target line in your Pivot Chart is to set a target or required value of sales amount and use this value in the Pivot Chart as a line chart. Let’s go through the process below for a better understanding.

Steps:

• First, insert the required sales amount and convert your data into a table. For this reason, select your range and go to Insert >> Table.
• Make sure you select ‘My table has headers’.

• After that, select this table and then go to Insert >> PivotTable.
• You will see a dialog box showing whether you want your PivotTable in the existing worksheet or in a new worksheet. In my case, I selected New Worksheet and clicked OK.

• Next, you will see a Pivot Table in a new worksheet.
• In the sheet, you will also see the PivotTable Fields.
• After that, drag the Product Field to the ‘Rows’ area. Also, drag the Sales and Required Sales Fields to the Values Area.

• Instead of taking the Sum of Required Sales, we will use the Average of Required Sales because that will keep the target or required sales value constant while analyzing the Pivot Table. To do that, click on the Sum of Required Sales Area and select Value Field Settings…

• After that, select Average and click OK.

Your Pivot Table Fields are all set now.

In the following image, you will see the Pivot Table analysis for the sales of your grocery items.

• After that, select any of the cells of your Pivot Table and then go to PivotTable Analyze >> Tools >> PivotChart.

• Thereafter, you will see the data information in a Column Chart. There are some columns in the chart that are of the same height. They indicate the Required Sales amount of your products which is 220$.

• Next, select any of the columns of the same heights and right-click on it. Choose Change Series Chart Type…

• After that, change the chart type for the Average of Required Sales from Clustered Column to Line Chart.
• Click OK.

• Finally, you will see the target line in your Pivot Chart.

Thus, you can add a target line to the Pivot Chart in Excel by applying a target value.

Read More: How to Make a Line Graph in Excel with Multiple Lines (4 Easy Ways)

2. Using Excel Pivot Table Analyze Tab to Add Pivot Chart Target Line

Another way to add a target line in the Pivot Chart is to use the PivotTable Analyze Tab. You just need to add a new field for this aspect. Let’s go through the following description to get a detailed view of this scenario.

Steps:

• First, follow the steps of Section 1 to create the Pivot Table analysis sheet, and also follow this link to add the Required Sales amount in your Pivot Chart.

• After that, select the chart and go to PivotTable Analyze >> Fields, Items & Sets >> Calculated Field…

• Thereafter, set a name and add your target value in the Formula section of the Insert Calculated Field In my case, it’s 220$.
• Next, click OK.

• If you go to the PivotTable Fields, you will see the Target field added to it. Uncheck Required Sales and check Target.

• After that, you will see a Column Chart and convert the columns that indicate Target values to a Line Chart following the steps described in Section 1.

• Thereafter, you will see a target line in your Pivot Chart.

• In addition, if you want to change your target line, you can change it in the Calculated Field In this case, I changed it to 400$ and clicked OK.

• After that, you will see the target line elevated to 400$.

Thus you can add a target line to the Pivot Chart in Excel by using the PivotTable Analyze Tab.

There is another way you can add a target line. Although this process is not an efficient one, you can use it if you don’t want to apply the Pivot Table features.

• Uncheck the Target field.

• Next, select the Pivot Chart and go to Insert >> Shapes >> Line.

• Now, draw a line as a target line in your Pivot Chart.

The problem with this process is that the line won’t be fixed in the position if you move your Pivot Chart individually.

Read More: Difference Between Pivot Table and Pivot Chart in Excel

Practice Section

Here, I’m giving you the dataset of this article so that you can practice these methods on your own.

Conclusion

In the end, we can conclude that you will learn some pretty basic ideas of how to add a target line to a Pivot Chart in Excel. If you have any better methods or questions or feedback regarding this article, please share them in the comment box. This will help me enrich my upcoming articles. For more queries, kindly visit our website ExcelDemy.

Related Articles

• How to Edit Pivot Chart in Excel (with Easy Steps)
• How to Refresh Pivot Chart in Excel (4 Suitable Approaches)
• How to Insert a Stacked Column Pivot Chart in Excel
• How to Filter a Pivot Chart in Excel (5 Suitable Ways)
• How to Make a Bar Graph in Excel with 2 Variables (3 Easy Ways)
• Types of Pivot Charts in Excel (7 Most Popular)