Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!

Subscribe for Free Excel tips & more!

Free Excel Forum

Not Show Grand Total For One Row In Pivot Table

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

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?

View Answers     

Similar Excel Tutorials

How to Arrange Data within Cells in Excel
In this tutorial I am going to look at cell alignment / arrangement. These features allow you to change how data lo ...
Excel Time - Summing Time Greater than 24 Hours
I'll show you the easy and simple way to sum time past 24 hours in Excel.  All we really have to do is to change th ...
Absolute and Relative Cell References in Excel
In this tutorial I am going to cover the difference between Absolute and Relative Cell References in Excel and show ...
How to use Vlookup Across Multiple Worksheets in Excel
This tutorial shows you how to use the Vlookup function across multiple worksheets within Excel.  This means that y ...

Helpful Excel Macros

Sort Data that Doesn't Have Headers in Descending Order in Excel
- Sort data in Excel that doesn't have headers using a macro. This Excel macro assumes that your data set does not have a
Close a Workbook after a Time Limit is Reached
- This macro will automatically close the workbook in which it is located after that workbook has been opened for a certai
Filter Data Sets on Multiple Columns with Multiple Criteria at Once in Excel - AutoFilter
- This Excel macro allows you to filter a data set on multiple columns and criteria at once. This means that you can filt
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
Delete Entire Rows Based on Predefined Criteria (Text)
- This macro will allow you to specify certain criteria and then to delete rows based upon that criteria. You will choose

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 24-Jul-07 480 0 API 21243541 Total 0 0 72496 Total 0 0 73362 API 21246113 20-Aug-07 480 0 API 21246113 Total 0 0 73362 Total 0 0 9MA Total 0 0 CAD Total 0 0 Grand Total 0 0


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??


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,


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!

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!

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?


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),


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?


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


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

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!


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 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.

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.


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?


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.


Chris Wood

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


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.

I think it's a commom problem in Excel 07. I have the data list as below:

Name Expenses1 Expenses2
Alan 100 200
Alex 120 150
Jack 100 120
Micheal 150 100

PT will be as below, with grand total of Expenses1 and Expenses2, I have both Row and Column Grand total ticked, but I can't get the Row Grand Total.

Name Expenses1 Expenses2
Alan 100 200
Alex 120 150
Jack 100 120
Micheal 150 100
Grand total 470 570

In Excel 03, with PT wizard, the report can be easily done as below:

Name Expenses1 Expenses2 Grand Total
Alan 100 200 300
Alex 120 150 270
Jack 100 120 220
Micheal 150 100 250
Grand total 470 570 1040

In Excel 07, I have tried to play around the settings in PT options, moving around the row and column, change the formats, and tried in both XP and Vista. But still I can't get the Row Grand Total.

Please help me out. Thank you.

I'm using Excel 2007. I need to have a dynamic stacked chart based on a pivot table that has the 'grand total' at the top of each stack. I understand from reading other threads that you cannot include the grand total in a pivot chart. Is this correct? If so, how can I create a regular type chart from the pivot info that will be dynamic that includes a grand total?


Sept Oct Nov TOTAL
Item1 1 2 3 6
Item 2 4 5 6 15

Would like chart that stacks Sept, Oct, Nov for Item 1 with the total on top, etc.

Your help would be greatly appreciated!

I have a chart based on the following pivot table:

Count of Late CRD Weeks Late CRD Weeks Total 0 133 01-07 128 08-14 93 15-21 78 22-28 55 29-35 44 36-42 25 43-49 21 50-56 12 57-63 8 64-70 4 71-77 4 78-84 1 92+ 1 Grand Total 607

So imagine a simple column chart representing everything here except the grand total row of course. Now I have been trying to find a way to add a field that would show me a running CUMMULATIVE total. I have been unsuccessful in doing so. If I had, the table would look something like this:

Late CRD Weeks Total Running Total 0 133 21.91% 01-07 128 43.00% 08-14 93 58.32% 15-21 78 71.17% 22-28 55 80.23% 29-35 44 87.48% 36-42 25 91.60% 43-49 21 95.06% 50-56 12 97.03% 57-63 8 98.35% 64-70 4 99.01% 71-77 4 99.67% 78-84 1 99.84% 92+ 1 100.00%

(I manually created the third column and it stops working if I apply the filter at all)

Anyway, the trendline I would have liked to have added would have followed the third column. Can anyone tell me how I can either add a field that would let me create the third column or a trendline that would represent the data in a third column. I have been totally stumped.

Dear Friends,
I am using PV table for short outs some data's based on our requirement, usually PV Table is giving the sum between two data's ( A + B= Grand Total), but i need an answer in Grand total colume like ( A- B = Grand Total), So

Please find the attached excelsheet here, ex-

AMEX Total is coming by adding BST + Slip, instead of adding i need subtract (BST - Slip).