Email:      Pass:    Pass?
Close Window   
Subscribe for Email Updates!
Excel tips, help, and more!


Free Excel Forum

Grand Totals For Rows Not Displaying

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

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,


Similar Excel Video Tutorials

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
Reverse Row or Column Order in a Worksheet
- This macro reverses the order of a selected row or column in excel. It will completely reverse the data.
Complete Guide to Printing in Excel Macros - PrintOut Method in Excel
- This free Excel macro illustrates all of the possible parameters and arguments that you can include in the PrintOut Meth
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
Delete Rows in Excel if Completely Empty
- This macro will delete only completely blank rows in an excel spreadsheet. It allows you to make a selection of rows, ru

Similar Topics

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?

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 One and All

I have a pivot table with 9 differing column criteria and I want to graph the total column, using the references from the first column of the pivot table.

To do this, I have created a formula that looks at the first column for the reference (if(a1="Grand Total","",A1) and looks at the total column (using "Getpivotdata").

I have then created a pivot chart from the two columns (reference and total), ignoring the Grand Total row.

Now if I remove a column from the pivot table, the number of lines may reduce and Grand Total moves up a row or two.

I can use indirect and match to get either the Grand Total cell or the G/t value in another cell in my spreadsheet, but I cannot seem to be able to get the data for the pivot chart to refresh to not include the Grand Total, which then skews the graph.

I hope that I have given enough explanation such that someone might be able to help me.

Thanks in advance.



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

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'm pretty much a Pivot Table idiot, so I might be overlooking something
horribly obvious, but I'm trying to have a "Grand Average" column and row in
a Pivot Table instead of a "Grand Total." I see the Grand Total checkboxes
in the Options dialog box, but I've yet to stumble upon a way to specify a
function other than Sum.

Is this possible? If so, can someone let me know how?


If I didn't explain that clearly enough, let me know and I can give an
example. Thanks for the help.


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

can someone help me w this:
someone just sent me a pivot table that has rates by province and by month. the rows are by months and the columns are by province. each row and each column has a "Grand Total" at the end. im trying to figure out how the Grand Total is calculated, since it doesn't seem to be the sum or the average of the rates in that column. is there any way i can find the properties of the pivottable such that i can find out what is calculated at the Grand Total? thanks so much.


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

Hi, If I use average to display values in powerpivot table, can Grand total show total of those averages? So if my "Sale of product X" is on average 5 in Day1 column, 6 in Day2 Column, Grand total column would show 11? Normal query would use Average function in Grand Total column too..., but I need to summarice values in pivot.

I have added the file, where it is easier to see what I try to achieve. I show there pivot, that uses average also for Grand Total column and then I added yellow column with excel formulas, but I would like that to be part of pivot or powerpivot. Is it possible?


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


Hi all,
I have one issue when i get the data through the pivot table
option. after creating a pivot table, i enabled the sub-total and the
grand total in the pivot table properties. after enabling that the
sub-total and the grand total columns appear as #VALUE!.

I have office 2003 in OS 2000 proffesion.

Please help me


Hi all,
I have one issue when i get the data through the pivot table
option. after creating a pivot table, i enabled the sub-total and the
grand total in the pivot table properties. after enabling that the
sub-total and the grand total columns appear as #VALUE!.

I have office 2003 in OS 2000 proffesion.

Please help me



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

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

Hi there - i have a pivot table, created using MS Excel 2000, that looks
like this:

Prod: Widgit




01 Total..............200.....200.....200



02 Total.............700.....700.....700

GRAND TTL.......900.....900.....900

what i would like to show is % CONTRIBUTION FOR EACH TERRITORY based on
their AREA, not the % of GRAND TTL.

What i am trying to produce would look like this:

Prod: Widgit




01 Total...........100%.....100%.....100%



02 Total...........100%.....100%.....100%

When I use the "% of column" function in the Field Settings, it calculates
the individual territory % contribution against the GRAND TOTAL, so i get
something like this:

Prod: Widgit




01 Total............22%.....22%....22%



02 Total............78%.....78%....78%

GRAND TTL......100%....100%...100%

the only way i can figure to do this is to create separate pivot tables for
each different AREA...using AREA as the PAGE FIELD and selecting SHOW ALL
PAGES...then it works...

but what i would like is ONE table, on ONE page which shows % Contribution
of each TERR to the AREA...

i was hoping for a one-stop solution - without having to adjust my

is this do-able ?? appreciate your assistance!


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?


Hello All,
I have an Excel 2007 (2003 compatible) workbook that creates a pivot table and chart/graph from data linked on a network share drive. I would like to have the Grand Total rows data show up in the graph title which I can do well with a vlookup and concatenate on the daily totals. The problem occurs when someone clicks the macro to show the data monthly versus daily as another column is added to the pivot and the column source in the vlookup no longer applies.


=VLOOKUP("Grand Total",$A$10:$B$3000,2,0)

This gives me the daily Grand Total which I can then place in the graph.

When the monthly button is pressed and the pivot table changes, a new column is added with months and column 2 is now blank and the Grand Total data is in column 3.

I believe an array with index and match may work but I am not sure. Is there a way to find Grand Total in column A and look across that row for the first non-blank value, regarless of how large or small the pivot table is?



I know that when adding a formula column to a table you simple enter the formula in the first data cell and it auto-populates the column. What can I do with a pivot table to get a similar result? I want to add a column after the grand total to double the grand total without having to alter the data. Example, I have Apples and Grapes as my columns and red and green as my rows. I want to take the grand total of everything that's red and multiply it by 2 and have it become part of the pivot table. I tried adding a calculated field but that drops in an extra column for each fruit.

Any ideas?



Thanks for all your help previously - i have yet another question

I have a pivot table with grand totals at the end. due to the structure of other formulas referencing that pivot table, i cant use a "grand total for rows" option.

However, i would like to sum the values in the "Grand total for columns" row - however, as i choose different options in the pivot table, that row can move up or down.

How can i use a dynamic cell reference to sum the values in this row?

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.

Hey guys,

I'm working with a pivot table with many different names in the leftmost column. "Grand Total" is one of the columns and I want to do a vlookup so it will look up the name in the left most column and find the column that says "Grand Total," and return to me the value in that respective column.

Reason for me wanting to do this is that I want to be able to choose multiple weeks based on the month of data I want to analyze so the column number that "Grand Total" is actually under will change constantly.



I have a very simple pivot table that shows me the sales volume of different stores (in the rows) for the months (Jan, Feb, Mar - in the columns). The table also shows me the Grand Total of Volumes for Jan, Feb, Mar, which I want to display as well. My goal now is to have an extra column at the end that puts a $ Value to the Volume of the Grand Total, using prices I have in my source data (different for each store). I have tried to just calculate the $ figures in my source data and then show it but this always ends up showing for every single month. However, I only want the $ figure for the sum of Jan, Feb, Mar. And that does not work.

Two solutions I came up with ( but did not work, maybe you have an idea):
1) I do add an extra column in the source data and somehow tell the pivot table to show only the grand total of that column summed up over the months.

2) I have a price list in my source data and using the grand total of the volume, i insert a calculated field that multiplies volumes and price.

Both did not work. I hope I explained the problem well enoguh and you can help me.


I have a pivot table pulling information from a data tab...and need some expertise/advice.

My only "row label" - I filter by top 15 based on one of my three "Values" options.

All is fine, except the results returned filter out information from my "Grand Totals."

In my example I have 26 employees whom have a combined 192 sales for a value of $30825. I also have a calculated field for taking the "% of Total" based on $$$ sold so I can see what % that salesperson brought in as whole for the company. In my example it goes from 25.95% down to 0.32% of the total.

So, I filter for the top 15 employees, and the Grand Totals change, along with the % of Totals. Myy report now shows only 15 employees for 101 sales and $27925. Likewise, my calculated field now uses the new Grand Totals to calculate saying my top employee at 28.65%...

I don't disagree that the information returned is correct, but I want my grand totals to always display where it includes all employees (regardless if I filter), and also in the calculated field calculating using all employees rather than the fitlered ones.

I hope that makes sense. My actual spreadsheet has many copies of similar pivot tables including charts, etc. and I am trying to make these dynamic as I would only have to paste in the data, and all of these tables show the correct information. I have varying departments and each department wants their actual GRAND TOTALS and then only show the top 15 employees. Again, hope that makese sense, and thanks in advance to any help.

Hello, here is what my data looks like in the pivot

---------------Jan -- Feb -- Mar -- Grand total
Frank Boss
--------- Bob --10 -- 15 --- 20 ------45
--------- Jen -- 15 -- 60 --- 10 ------ 85
--------- Bill -- 20 -- 12 --- 55 -------87
Frank Boss Total 45 -- 87 --- 85 ------ 217

I want to be able to have the grand total column to show averages. The problem is that when I change it to averages, the other columns of data like Jan - Bob - 10, changes to averages from "sums". Is there a way to change the grand total column independant of the other columns? Or any other way around this? The goal would be to have the main data on the pivot to "sum" and have the grand total "avg".