
Not Show Grand Total For One Row In Pivot Table


Search Excel Forum Posts, Tutorials, Macros, Tips, and More
Not Show Grand Total For One Row In Pivot Table  Excel

View Answers


Is it possible to not show Grand Totals for a particular field while others do.
I have a running total % field, that I have to build using 2 prebuilt running total fields. I do not want to display it's grand total because in reality it is the % of all running totals togther. Thus the grand total is innacurate. The prooper % IF FOUND ON THE MONTHLY % LINES GRAND TOTAL AS WELL AS THE LAST PERIODS running total. So I would like to not display this particular grand total. Is that possible?
Similar Excel Video Tutorials
Running Total Formula or Pivot Table
 See how to create a Running Total with a Formula or a Pivot Table. For formulas, the trick is to use the SUM function and lock one of the cells, ...
Helpful Excel Macros
Bubble Sort
 This macro will perform a bubble sort in excel. You use it simply by selecting one column to sort and then running the
Similar Topics
I have a pivot table that will not calculate the grand totals of rows or columns. The appropriate boxes are selected under the Table Options and Field Options to show the grand totals.
Example data below. I would like the GT to show for each inv, as well as an overall grand total for the complete sheet. For this one the GT should be 960, I on get zero.
Cur
Supplier
Supplier Inv.
Document
Doc. Date
Amount
Balance
CAD
9MA
72496
API 21243541
24Jul07
480
0
API 21243541 Total
0
0
72496 Total
0
0
73362
API 21246113
20Aug07
480
0
API 21246113 Total
0
0
73362 Total
0
0
9MA Total
0
0
CAD Total
0
0
Grand Total
0
0
Hello,
I have created a pivot table and the Grand Total values are correct, except for 1 that keeps showing a value of 0%. My Grand Total row shows 3 sets of values...for 3 different months. Each month has the following:
Capacity  Availability  Total Availability %
I've used the calculated field function to calculate the Total Avail %...therefore, I've used "Avail / Cap" for each of the 3 months of data. In the Grand Total row, it calculates all information correctly, EXCEPT for the last row that shows a value of 0%. So the Grand total row looks kind of like this:
Grand Total  761,292,745  229,221,558  30%  786,669,228  244,528,458  31%  1,547,961,973  473,750,016  0%
Can anyone out there help??
Thanks!!
Excel 2003
I have a pivot table with the "Grand Total For Rows" and "Grand Total For
Columns" enabled. The grand total for columns show up as the last row, but
the grand total for rows does not show up as the last column???? I am pretty
adept with MS pivot tables, but can't figure out how to get them to display.
Any ideas.
Thanks in advance,
Chad
i have an input form in which 6 rows of data intern give a sub total. what i would like to do is have a running / grand total automatically.
p2 =20
z2 =18 Grand total would read = 38
ac2 = 1 Grand total would read = 39
ad2 = 4 Grand total would read = 43
aj2 = 3 Grand total would read = 46
az = 2
Grand total = 48
can any one suggest a solution
Hello Everyone,
is it possible to view the 'Grand Total' data column as a % as well as a number? I can get the whole pivot table to show % but not the grand total on its own.
Count of Type
Quarter
Classification
Q1 2011
Q2 2011
Q3 2011
Q4 2010
Grand Total
A
1
5
1
1
8
B
13
16
3
14
46
C
295
292
60
381
1028
D
1
1
2
Grand Total
309
314
64
397
1084
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
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
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 AM 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 NZ... 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 STEPbySTEP instructions...
Thank You in advance
Richard K.
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 rightclick 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 BA or AB 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

