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.

add target line to pivot chart in excel


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.

add target line to pivot chart in excel

  • 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 ‘Rowsarea. 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…

add target line to pivot chart in excel

  • After that, select Average and click OK.

Your Pivot Table Fields are all set now.

add target line to pivot chart in excel

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.

add target line to pivot chart in excel

  • 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…

add target line to pivot chart in excel

  • 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.

add target line to pivot chart in excel

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…

add target line to pivot chart in excel

  • 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.

add target line to pivot chart in excel

  • 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.

add target line to pivot chart in excel

  • 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$.

add target line to pivot chart in excel

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.

add target line to pivot chart in excel

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

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

add target line to pivot chart in excel

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

Nahian

Nahian

Hello, Nahian here! I do enjoy my efforts to help you understand some little basics on Microsoft Excel I've completed my graduation in Electrical & Electronic Engineering from BUET and I want to be a successful engineer in my life through intellect and hard-work, and that is the goal of my career.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo