- Trending Categories
- Data Structure
- Operating System
- C Programming
- Social Studies
- Fashion Studies
- Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to add average/grand total line in a pivot chart in Excel
Have you ever attempted to include an average line or grand total line in an Excel pivot chart? It appears difficult to show or add an average/grand total line like you would in a typical chart.
Create Pivot table
Let’s understand step by step with an example.
In the first, we must create a sample data for creating pivot table as shown in the below screenshot.
Now, select the data range from A1:B15. Click on the Insert tab on the toolbar ribbon and then select pivot table option to insert pivot table for the selected data range. Refer to the below screenshot for the same.
In the next step, create pivot table window appears, make sure the data range is selected as A1:B15 under select table/range option. Now, choose new worksheet to create the pivot table in separate sheet then click on OK button.
The pivot table is now created in a separate worksheet as shown below.
In the Pivot table fields check employees and incentives. After those values appear.
Now, select the data range A4:B17 and click on insert chart tab to insert the bar chart>2-D column. As shown in the below screenshot.
And based on the above range, you've created a pivot table and a chart, as shown in the screenshot below.
Add average/grand total line in a pivot chart in excel
Now, you may follow these steps to add an average line or grand total line to an Excel pivot chart.
By selecting Insert from the right-click menu after selecting the Incentives column in the source data, a column will be added before the Incentives column.
Type "Average" in Cell B1 of the new Column, enter the formula in Cell B2 and drag the Fill Handle to Range B3:B15. Refer to the below screenshot.
Click the Pivot Chart, and then under the Analyze tab, click the Refresh button. See the below screenshot.
The Average field (or Grand Total field) has now been added to the PivotChart Fields pane. To add the field to the Values section, check the Average (or Grand Total) box and right click and select add to values. As shown in the below screenshot.
The Pivot Chart now includes the average filed (or Grand Total filed). Refer to the below screenshot.
Then, right-click the average filed (or Grand Total filed) and select Change Series Chart Type. As shown in the below screenshot.
In the Change Chart Type dialog box that appears, click Combo in the left pane, and in the Choose the chart type and axis for your data series box, click the Sum of Average box and select the Line from the dropdown list, then click the OK button. See the below screenshot.
The average line (or grand total line) is now added to the Pivot Chart all at once. Refer to the below screenshot.
In this article, I'll show you how to effortlessly add an average/grand total line to an Excel pivot chart.
- How to Add Percentage of Grand Total/Subtotal Column in an Excel Pivot Table?
- How to add a horizontal average line to chart in Excel?
- How to add moving average line in an Excel chart?
- How to Add Vertical/Average Line to Bar Chart in Excel?
- How to Add Secondary Axis to a Pivot Chart in Excel?
- How to Add Series Line in Chart in Excel?
- How to Add Total Labels to a Stacked Column Chart in Excel?
- How to add dotted forecast line in an Excel line chart?
- How to add arrows to line / column chart in Excel
- How to add drop lines in an Excel line chart?
- How to Add Up/Down Bars to a Line Chart in Excel?
- How to add a single data point in an Excel line chart?
- How to calculate weighted average in an Excel Pivot Table?
- How to add a calculated field to a pivot table in Excel?
- How to add horizontal benchmark/target/base line in an Excel chart?