Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!
Subscribe for Free Excel tips & more!

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,


View Answers     

Similar Excel Tutorials

Complex Structured References (Table Formulas) in Excel
How to use complex structured references, table formulas, in Excel. If you don't already understand how structured ...
Using Formulas with Tables in Excel
Easy way to reference data tables and make formulas within a data table. This allows you to create formulas that a ...
NOW() - Display The Current Time in Excel
This Excel tip will show you how to display the current time within any Excel spreadsheet. This is a useful functio ...
Group Data Together for Increased Readability in Excel
How to group data together or collapse it in order to focus only on the important data in Excel. This allows you t ...

Helpful Excel Macros

Excel Macro to Save a Specific Worksheet as a New File
- This Excel Macro allows you to save a specific worksheet within the Excel Workbook to its own new file. You will be a
Macro to add a New Line to Message Box Pop-up Windows in Excel
- This is a very simple Message Box, pop-up window, macro for Excel that illustrates how to put new lines, the same thi
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
Save the Current Worksheet as a New Excel Workbook File
- This Excel Macro will save the currently visible/active worksheet (the one that you see when you run the macro) to 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.

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?

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.

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


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


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

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!

Hi. I have a sheet where I use VBA to perform a few calculations with a pivot table.

1) I would like the GRAND TOTAL ROW of the pivot table to be copied and pasted 5 rows below the grand total row. Problem is the grand total row is dynamic and dependent on the data in column A of the pivot table. I need the VBA to copy the "dynamic and moving" grand total row wherever it is

2) I would like the second (third and forth, etc....) set of results to be pasted below the first row. Then the third results below the second results. The first row of results is also "dynamic and moving" since it is 5 row below the grand table.

I am very new to VBA and normally I use the recording function.

In this example, The grand total is at row 52 but VBA to select the grand total row. Row 102 is where I pasted first the data but I want it to be 5 rows below the grand total. The second set of results need to be pasted 1 row below the first set of results.

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

GETTING SECOND OF RESULTS to be pasted below the first set of results.

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

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!

Is it possible to reference the "Grand Total" column in a pivot table using VBA? Here is a simple example of what I'm trying to do. Based on the grand total I would like to use VB to hide rows. Let say if "Hr" are less than 59, the rows are hidden. I have not been able to find a way to do this. I'm a newbie to VBA in excel. Thanks for any help

Sum of Hr Column Labels Row Labels 2/1/2007 2/2/2007 2/3/2007 2/4/2007 2/5/2007 2/6/2007 2/7/2007 Grand Total jim 8 8 8 11 8 8 8 59 Mark 8 8 8 8 8 8 8 56 tom 8 8 10 8 8 8 8 58 Grand Total 24 24 26 27 24 24 24 173

I created a pivot table showing basic sales (revenue, cost, and margin) by years in my columns and customers by rows. The issue is I'm getting a #DIV/0! in some of my grand totals but not in others. The year to year data is similar (there are some #DIV/0! in other years but the grand total still computes correctly). Why would some years compute whhile others return an error?

Huge road block here; a dead end.

Knowing that a pivot table fluctuates in size (meaning that a Grand Total can be shown in B4,B7,B19,etc. depending on number of identified entrys), is it possible to reference the Grand Total of a pivot table in a formula and make it where the formula is always referencing the correct cell containing the Grand Total???