# Is it possible to have both Grand Total and Grand Average in a Pivot Table?

If so, how?

Thanks!

2. ## Re: Is it possible to have both Grand Total and Grand Average in a Pivot Table?

Can you attach your excel workbook and re-submit...so that I can work on it and send you back.

3. ## Re: Is it possible to have both Grand Total and Grand Average in a Pivot Table?

Pivot Table Example.xlsx

attached
thank you

4. ## Re: Is it possible to have both Grand Total and Grand Average in a Pivot Table?

see if this helps

5. ## Re: Is it possible to have both Grand Total and Grand Average in a Pivot Table?

Hi Nitin,

This is nice, thank you.

1) Is there a way to do so without doubling the number of columns? That is, just add one additional column on the right with the grand average?
2) I should have been clearer as to what I wanted to achieve. I appologize. By "Grand Average" I meant having a monthly average, that is just the grand total devided by the number of months (in this case, it's divided by 12, but i have other pivot tables with less than 12)

Can anything be done now?

Thank you!

6. ## Re: Is it possible to have both Grand Total and Grand Average in a Pivot Table?

Revert to your orignal table and in T4 use

=S4/COUNT(H4:R4)

Note your data contains only 11 months of data (No Sep-11!)

7. ## Re: Is it possible to have both Grand Total and Grand Average in a Pivot Table?

Hi,

Yes - of course that's the easy part.
What I wanted to have is an average within the pivot table (right now you can have either a grand total column OR a grand average column - I want both)
The reason for wanting it inside the PT is that with time I'll update the data it runs on to include more and more months - so the size of the PT will change, and I'll have to constantly update the average column manually. (and this is just an example... I have a file with about 50 pivot tables!)

Nice catch re Sep!

8. ## Re: Is it possible to have both Grand Total and Grand Average in a Pivot Table?

you can't have just the grand total average - you have to have the data field twice for all columns.

9. ## Re: Is it possible to have both Grand Total and Grand Average in a Pivot Table?

Usually I can have either the Grand Total or the Grand Average (using the "summarize values by...."). I was hoping there's a way to display both

Originally Posted by JosephP
you can't have just the grand total average - you have to have the data field twice for all columns.

10. ## Re: Is it possible to have both Grand Total and Grand Average in a Pivot Table?

not unless your source data is olap based. if you can use the powerpivot add-in (since you are using excel 2010) then you can make your data into a table, load it into powerpivot then create an olap pivot table and create a named set to display the data the way you want

11. ## Re: Is it possible to have both Grand Total and Grand Average in a Pivot Table?

I see. That's a bit diasspointing because it's very much within reach. I was hoping there's some way to get excel to do it.

Thanks all

Originally Posted by JosephP
not unless your source data is olap based. if you can use the powerpivot add-in (since you are using excel 2010) then you can make your data into a table, load it into powerpivot then create an olap pivot table and create a named set to display the data the way you want

12. ## Re: Is it possible to have both Grand Total and Grand Average in a Pivot Table?

there is-either use the powerpivot add-in or add the data field twice as nitin.asalkar showed and hide the columns you don't want ;-)

13. ## Re: Is it possible to have both Grand Total and Grand Average in a Pivot Table?

Originally Posted by JosephP
there is-either use the powerpivot add-in or add the data field twice as nitin.asalkar showed and hide the columns you don't want ;-)
thanks .

14. ## Re: Is it possible to have both Grand Total and Grand Average in a Pivot Table?

1. You are creating a new formatting rule which will be used to format a PivotTable report. Which of the following formatting styles is NOT available while formatting all cells of PivotTable report based on their values in an MS Excel 2010 worksheet?
a. 2-Color Scale
b. 3-Color Scale
c. 4-Color Scale
d. Data Bar

2. Which of the following statements is true regarding PowerPivot in Microsoft Excel 2010?
a. One limitation of PowerPivot is that it does not allow access to Slicers when working on data in PowerPivot.
b. A connection to a server running SQL Server Analysis Services is required to answer PivotTable queries by the PowerPivot engine.
c. Version control and tracking is supported by PowerPivot.

3. Which of the following statements is NOT true regarding import of data into PowerPivot?
a. Data can be imported into PowerPivot by either selecting list of tables or by writing SQL query.
b. The data being imported into PowerPivot can be filtered before the import.
c. The PowerPivot Import Wizard provides you with an SQL Query builder.

4. Which of the following statements are true regarding PowerPivot in Microsoft Excel 2010?
a. PowerPivot streamlines the process of integrating data from multiple sources like databases, spreadsheets, reports and text files.
b. PowerPivot does not allow access to slicers when working on data in PowerPivot.
c. Version control and tracking are NOT supported by PowerPivot.

5. Deleting a PivotChart report automatically deletes the associated PivotTable report.
a. True
b. False

15. ## Re: Is it possible to have both Grand Total and Grand Average in a Pivot Table?

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.

16. ## Re: Is it possible to have both Grand Total and Grand Average in a Pivot Table?

There are currently 1 users browsing this thread. (0 members and 1 guests)