Adding Data to Pivot Table

Andy0311

Board Regular
Joined
Oct 16, 2019
Messages
90
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hello,
Below is a simple pivot table. In my pivot table, I need to display both the Grand Total and Average. How do I add the Average (yellow areas) to my pivot table?
1599340453792.png


Thanks for any help you might be able to give me?

Andy
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

PankajMaheshwari

New Member
Joined
Sep 7, 2020
Messages
24
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
  3. Mobile
Hi Andy,

That's not possible within the Pivot table itself. There can be few workarounds though.

1. Copy and Paste your Pivot table and in value field, change "Sum" to "Average". Then add a lookup of Row Averages and Column Averages near the original Pivot table.

2. Second solution can be a little time consuming based on your base data because that's where I guess you should make the changes. But this will best suit your purpose. Example, if this was your base data:
base data_old.PNG

Now your base data should look like this (all the yellow highlighted cells are newly added info.):
base data_new.PNG


Once you make above changes to your base data, here's how your pivot will look like with both Sum and Average info. and you can add any amount of data to your base data and just refresh this pivot:
Final pivot.PNG


Hope this helps.

Thanks.
 

Andy0311

Board Regular
Joined
Oct 16, 2019
Messages
90
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Excellent! Thank you for helping me. Exactly what I needed.

Andy
 

Watch MrExcel Video

Forum statistics

Threads
1,112,771
Messages
5,542,433
Members
410,552
Latest member
Yogesh977
Top