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

Get FREE Advanced Excel Exercises with Solutions!

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.


How to Add Target Line to Pivot Chart in Excel: 2 Ways

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 height 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 Add Grand Total to Stacked Column Pivot Chart


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: How to Filter a 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.


Download Practice Workbook


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.


Related Articles


<< Go Back to Pivot Chart | Pivot Table in Excel Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.

Tags:

Meraz Al Nahian
Meraz Al Nahian

Md. Meraz Al Nahian has worked with the ExcelDemy project for over 1.5 years. He wrote 140+ articles for ExcelDemy. He also solved a lot of user problems and worked on dashboards. He is interested in data analysis, advanced Excel, statistics, and dashboards. He also likes to explore various Excel and VBA applications. He completed his graduation in Electrical & Electronic Engineering from Bangladesh University of Engineering & Technology (BUET). He enjoys exploring Excel-related features to gain efficiency... Read Full Bio

2 Comments
  1. OMG, I searched for this solution off and on for months, this was the first one that completely worked!!!!!! THANK YOU VERY MUCH!

    • Dear Micah,

      Thanks for your kind words. ExcelDemy is dedicated to provide solutions to help you.

      Regards
      Shamima Sultana | Project Manager | ExcelDemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo