How to add average/grand total line in a pivot chart in Excel?
Have you even tried to add an average line or grand total line in a pivot chart in Excel? It seems hard to show or add average/grand total line as that you do in a normal chart. In this article I will share a tricky way to add an average/grand total line in a pivot chart in Excel easily.
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
Supposing you have a sales table as below screen shot shown:
And you have created a pivot table and chart based on the sales table as below screen shot shown:
Now to add an average line or grand total line in a pivot chart in Excel, you can do as follows:
1. Insert a column before the Amount column with right clicking the Amount column in the source data, and selecting Insert from right-clicking menu.
2. In the new Column, type Average in Cell E1, and enter the formula =AVERAGE($F$2:$F$15) into Cell E2 and drag the Fill Handle to Range E2:E15.
(1) In the formula =AVERAGE($F$2:$F$15), $F$2:$F$15 are amount values in the Amount Column.
(2) If you want to add the Grand Total line in the Pivot Chart, type Grand Total in Cell E1, and enter the formula =SUM($F$2:$F$15) into Cell E2 and drag the Fill Handle to Range E2:E15.
3. Click the Pivot Chart, and then click the Refresh button on the Analyze tab.
4. Now you will see the Average field (or Grand Total field) is added into to PivotChart Fields pane. Check the Average field (or Grand Total field) to add the filed to Values section.
5. Now the average filed (or Grand Total filed) is added into the Pivot Chart. Right click the average filed (or Grand Total filed) and select Change Series Chart Type from the right-clicking menu.
6. In the opening Change Chart Type dialog box in Excel 2013, 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 in the drop down list, and click the OK button.
Now you will see the average line (or grand total line) is added in the Pivot Chart at once.