Not Show Grand Total For One Row In Pivot Table

I generated a pivot table using financial data for clients/by year/by sales rep. The table successfully calculated the Individual totals by rep by year as well as the grand totals by rep and by year. The only grand total it did not calculate is the overall grand total by client. I know I can simply generate a sum field/cell manually, but am unable to label that column as the grand total. How can I get this grand total to calculate and have it become part of the pivot table itself?

Thank you.

Using Excel 2007 & Windows XP.

I've made a Pivot Table that filters pages by Years, then gives me monthly transaction totals by categories. Months across the columns and categories down the rows, and gives me the Grand Totals for each Month at the bottom and the Grand Totals for each category at the right. I want to insert a Calculated Field beside the category Grand Totals at the right that will give me the monthly average for each category. NOT the average transaction amount per month but the Average Total = the Grand Total per category / the # of months contributing to the Grand Total per category. I have data for May through Dec for 2009 & data for Jan through the current date in Aug for 2010. That means that I can't just divide the Grand Total per category by 12 months I need to figure out how to make it count the # of months of data available in each year.

I can't figure out how to count the # of months of data available in each year or how to even calculate the average by dividing the Grand Total per category since it doesn't appear the field list.

Any advice or insight would be greatly appreciated! Thanks!

Hello!
Using pivot table with more the one field in the row area adds total lines.
How can I hide these lines?
Copy and paste to deferent area will not do good, since I need to change the table all the time.
The end result should look like:

Sum of count Month
model SN march april Grand Total
aa1 1 4 2 6
2 5 5 10
3 2 3 5
4 8 6 14
bb1 1 5 4 9
2 8 7 15
3 6 5 11
4 3 8 11
Grand Total 41 40 81

And NOT like:

Sum of count Month
model SN march april Grand Total
aa1 1 4 2 6
2 5 5 10
3 2 3 5
4 8 6 14
aa1 Total 19 16 35
bb1 1 5 4 9
2 8 7 15
3 6 5 11
4 3 8 11
bb1 Total 22 24 46
Grand Total 41 40 81



Thank you!
AlonS.

To solve the problem of not being able to use an IF statement in a calculated field I used the pivot table options to make errors return N/A instead of #DIV. Now my grand total line for this field which is a percentage returns the same N/A.

I have fields A, B, and C.

Calculated field is C/B in which B is sometimes 0.

In essence the grand total line for the calculated item should still equal grand total of column C/B but it returns the N/A as stated.

Any suggestions?

Hi,

I have a pivot table with a lastrow for grand total and lastcolumn for grand total. Now I need a code which selects only the data inside the table excluding both the row with grand total and column with grand total.

thanks buddy(s),
cheers

Hello,

I have created a pivot table ... naturally, there will be a Grand Total at the bottom of it. I applied a filter to show the top 10 customers. Automatically, the grand total will change to show sum of the top 10 customers only.

what I am trying to do is this:

Applying the filter without effecting the grand total ... I want to the grand total of all customers even after applying the filter. Is that possible?

Thnnx

Please, Is there anybody that knows how to calculate in pivot table, % on grand total column only, not all of cells
type1 type2 type3 type4 Grand Total
agent1 4 4
agent2 1 5 129 135
agent3 6 5 9 904 924
agent4 6 1 3 87 97
Grand Total 12 7 17 1124 1160

Thaks a lot

Hello,

i have a pivot table showing sales per month then sum the 6 month into a grand total column, now i need also to have an everage sales as another summary beside the grand total by dividing this grand total by 6 , how can i do that?
i know i can do it outside the pivot but i want it inside in case users change the pivot lauout or change number of displayed monthly sales , then the this average sales i want to add will divide grand total sum by number of months displaged

can this be done?!

Thank you
Bassam

First of all I want to express my sincere thanks to this site, every problem and/or question I have ever had has been answered. So I am coming back with a problem I am having now...

I have a number of tables, one table is called Enforcements. This table is broken down to each enforcement by hour. So I will have a field called Dog 8, which means Dog off leash at 8 o'clock... I have 15 Enforcements I am tracking and 15 Hours I am tracking as well... With me so far?

The FIRST part of this problem I am having is ... I want to have a REPORT that will give me the TOTALS of Each Enforcement... And to make things MORE Difficult I have NULL VALUES... I was able to use the REPORT WIZARD to make a Report that will take Enforcements A-M give me the total of each hour for each enforcement. The formula for the hourly total is...

=Sum([Dog 8]) This formula was built by the wizard...

Then I added a TOTAL column to add up all the hourly enforcements and give me a total for that enforcement... The formula for the TOTAL is...

=NZ([Dog 8 Grand Total Sum])+NZ([Dog 9 Grand Total Sum])+NZ([Dog 10 Grand Total Sum])+NZ([Dog 11 Grand Total Sum])+NZ([Dog 12 Grand Total Sum])+NZ([Dog 13 Grand Total Sum])+NZ([Dog 14 Grand Total Sum])+NZ([Dog 15 Grand Total Sum])+NZ([Dog 16 Grand Total Sum])+NZ([Dog 17 Grand Total Sum])+NZ([Dog 18 Grand Total Sum])+NZ([Dog 19 Grand Total Sum])+NZ([Dog 20 Grand Total Sum])+NZ([Dog 21 Grand Total Sum])+NZ([Dog 22 Grand Total Sum])

This formula I built...Based on what the Wizard Made

I have a second report that will give me enforcements N-Z... Because of all the HOURS and all the ENFORCEMENTS I had to break it up into 2 reports. With me so far??? I know its complicated...

So Question Number 1... Is there an easier method to give me ONLY the Total of each Enforcement???

Now... Once I get the TOTALS of Each Enforcement... I would like be able to put in a percentage of how many of those enforcements are going to be Citations... Each enforcement will have a different percentage...

AND FINALLY I would like to assign a dollar amount to each enforcement to calculate income of each citation and a GRAND total of Citations...

Since this is Budget FORECASTING I would need to enter the Percentage and the Dollar Amount for EACH enforcement when I run the report

So in my mind the report would look some thing like this....


Enforcements Total Citation % Amount Total

Dog off leash 100 25% $50 $1250

I hope my math is correct... 100 is the total of enforcements, 25% of those will become citations or 25. So 25 X $50 = $1250...

Please help me with this problem... and i would really appreciate STEP-by-STEP instructions...

Thank You in advance

Richard K.

Hello - this seems simple but been searching for about an hour and can't figure it out. I have a simple pivot table with line items in the rows and months in the columns. I have grand totals turned on (annual number), but would like to move it to the left and for the life of me can't figure it out. I could do a calculated field and add the months but that seems like overkill. Any help is appreciated!

Sample:

Jan Feb Grand Total
a 10 10 20
b 10 10 20

Would like it to be:
Grand Total Jan Feb
a 20 10 10
b 20 10 10

I have the following pivot table:

Division 35 Sum of SumOfPaid Or Resv Amt Year HO 2008 2009 Grand Total 165 8,131,967 8,131,967 313 1,970,564 1,970,564 371 3,450,000 299,000 3,749,000 490 1,106,217 11,735,342 12,841,559 497 112,608 112,608 649 9,125,000 1,720,722 10,845,722 654 2,295,722 2,295,722 861 357,602 357,602 Grand Total 13,681,217 26,623,526 40,304,743

How can I change the Grand Total Column formula from a sum of 2008 and 2009 to the difference between the two.

I need to find the grand total label in a pivottable. The cell where the heading Grand Total is.
The Grand Total will move depending on data in labels in preceeding column headings.

cheers
ziggy

Hi all,

I've searched around and can't seem to find an answer that I understand to this.

Here's an example data set that I'm working with:

Code:

Sum of entry		
Data set 1	Data set 2	Total
A	Item a1	63
	Item b1	22
	Item c1	21
	Item d1	20
	Item e1	15
A total		141
B	Item a2	217
	Item b2	152
	Item c2	135
	Item d2	89
	Item e2	83
	Item f2	64
	Item g2	60
	Item h2	55
	Item i2	50
	Item j2	48
	Item k2	46
	Item l2	42
	Item m2	36
	Item n2	35
	Item o2	34
	Item p2	26
	Item q2	26
	Item r2	25
	Item s2	24
	Item t2	19
	Item u2	15
	Item v2	7
	Item x2	7
B Total		1295
Grand Total		1436

What I'd like to see is another row under both A and B Total as well as Grand Total that would display the appropriate Count, Median, and Average, to end up with a rows like:

A total
A median
A Average

If I right-click on the data set and choose to display as Average instead of Sum, my A average is 7.05, my B is 7.573, and the grand is 7.747. However, if I set the data set to display Sum, and use an Average() formula on the range, I get an A average of 28.2 and a B average of 56.3.

So, the final question is:

How do I go from the view above to this:

Code:

Sum of entry		
Data set 1	Data set 2	Total
A	Item a1	63
	Item b1	22
	Item c1	21
	Item d1	20
	Item e1	15
A total		141
A average		28.2
A median		21
A count		5
B	Item a2	217
	Item b2	152
	Item c2	135
	Item d2	89
	Item e2	83
	Item f2	64
	Item g2	60
	Item h2	55
	Item i2	50
	Item j2	48
	Item k2	46
	Item l2	42
	Item m2	36
	Item n2	35
	Item o2	34
	Item p2	26
	Item q2	26
	Item r2	25
	Item s2	24
	Item t2	19
	Item u2	15
	Item v2	7
	Item x2	7
B Total		1295
B average		56.3
B median		42
B count		23
Grand Total		1436
Grand average		51.28
Grand median		35.5
Grand count		28

I'd also want these fields to be set into the Pivot so that, as I change related fields in the top section of the table, the bottom section dynamically updates.

Thanks,
-Rich

(example data view spreadsheet attached to help visualize formatting)

Is there any way to make the Grand Total Column into a "Difference" column without having to paste special and manually calculate the field?

Widgets YEAR Branch 2008 2009 Grand Total 165 199 210 409 295 374 239 613 297 186 401 587 Grand Total 759 850 1609

I have a Pivot table which refuses to display a grand total for each row. It does show the grand totals for the columns, but ticking the checkbox for 'Grand Totals for Rows' has no effect. Any ideas?

Hi,

In Excel 2003, I hid the grand total column. How do I unhide the total? In "Table Options", the "Grand totals for columns" is checked.

Thanks,

Chris Wood

Hi
Is there a method to obtain a subtraction instead of a Grand Total at the end of the pivot table report. In example below to show B-A or A-B instead of A+B. Appreciate any help. Thankyou
Sum of Sales Company Month A B Grand Total Jan 2312 2342 4654 Feb 435 234 669 Grand Total 2747 2576 5323

This should be easy and probably is but I am drawing a blank, I have a pivot table that will grow and shrink with data, all I am trying to do is insert a formula in "A5" to look at a range "B5:BB100" and find "Grand Total" there is a "Grand Total" in row 5 and a "Grand Total" in column "B". I am familiar with vlookup and hlookup but since the row and column that the work "Grand Total" is in will always be changing it won't work.

Hi,

I'm using Excel 2000. My pivot table contains calculated fields, the problem
I'm experiencing is that the grand total for the field is almost 2 times
higher than it should be. However, if I look at the individuals rows that
calculation is correct, if I sum the calculated field column (with exception
to the grand total) the sum is correct but the highlighted column doesn't
match it's grand total....not sure why.

I'm not user if the problem is with my source data worksheet, the only thing
I'm doing which maybe an issue is- for the rate column, I have it equal a
cell that's resides on another work sheet.
--
Linda

Hi
I have a workbook with a number of tabs all numerically sequenced (ie. 1, 2, 3 etc). Each tab is identical to one another (that is, same design but different detail) and has a grand total in cell J51.

I have a summary tab which outlines what each tab details.

What i am trying to do is under the grand total column link to the corresponding tabs grand total. But I want to do this easily, so I don't have to keep flicking to each tab to insert the grand total and each time I add a tab, it will automatically update the sheet!!

I've shown below the summary I wish to create.

Is this formula possible??!

Thanks for all your help.
Sonya

Invoice Number Description Grand Total Paid 001 002 003 004 005