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

The dataset contains sales information about some grocery items. We will show you how to add this amount as a target line in a Pivot Chart.

add target line to pivot chart in excel


Method 1 – Applying a Target Value 

Steps:

  • Enter the required sales amount and convert your data into a table. Select your range and go to Insert >> Table.
  • Select ‘My table has headers’.

  • Select the table and then go to Insert >> PivotTable.
  • You will see a dialog box showing whether you want your PivotTable in the existing worksheet or a new worksheet. (The screenshot below shows New Worksheet)
  • Click OK.

add target line to pivot chart in excel

  • You will see a Pivot Table in a new worksheet.
  • In the sheet, you will also see the PivotTable Fields.
  • Drag the Product Field to the ‘Rowsarea.
  • 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.

  • Click on the Sum of Required Sales Area and select Value Field Settings…

add target line to pivot chart in excel

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

  • Select any cell in your Pivot Table and then go to PivotTable Analyze >> Tools >> PivotChart.

add target line to pivot chart in excel

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

  • 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

  • Change the chart type for the Average of Required Sales from a Clustered Column to a Line Chart.
  • Click OK.

  • You will see the target line in your Pivot Chart.

add target line to pivot chart in excel

You can add a target line to the Pivot Chart in Excel by applying a target value.


Method 2 – Using Excel Pivot Table Analyze Tab 

Steps:

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

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

add target line to pivot chart in excel

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

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

add target line to pivot chart in excel

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

  • You will see a target line in your Pivot Chart.

add target line to pivot chart in excel

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

  • You will see the target line elevated to $400.

add target line to pivot chart in excel

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

Read More: How to Filter a Pivot Chart in Excel


Practice Section

Here is the dataset so you can practice these methods on your own.


Download the Practice Workbook


Related Articles


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

Get FREE Advanced Excel Exercises with Solutions!

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

4 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

  2. You guys are awesome, please create more pivot table for data analysis and power pivot, thank you!!!

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo