Close Window   
Free Ebook
Got an Excel Question?
Ask it in our Excel forum!
TeachExcel.com
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



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

Create and Manage Tables in Excel
Here, I'll show you everything you need to know to get started using tables in Excel; how to create, edit, and man ...
Remove Table Format in Excel
Convert a table back into a regular set of cells.  This removes any automated table features and I'll show you how ...
Get the Number of Workdays Between Two Dates in Excel
How to calculate the total number of working days between two dates in Excel.  This allows you to exclude all weeke ...
Get a Table Look with Only the Formatting in Excel
How to get the nice formatting of a table without turning your data into an actual table. Formatting data as a tab ...

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


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





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!




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?


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


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,

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



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?

Example

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.


Hello All,

2 Different worksheets to work with

The "Nursery" Worksheet
I already have code that puts the Auto-Sum amount below the last data row in the column named "Nursery Grand Total" in the Nursery Worksheet.

This Auto-Sum amount, however, will always be in a different row because the amount of rows generated from the report is always different as well, therefore, the Auto-Sum cell/row changes with that to be right below the last data row in the "Nursery Grand Total" Column.

I would like to copy (values only) the amount from this dynamically changing Auto-Sum cell and paste it into another worksheet named "Totals".

The "Totals" Worksheet
In my "Totals" worksheet, I have two columns.
"Master Total Description" and "Master Grand Totals".

In the "Master Total Description" column, I have a cell named "Nursery Grand Total" which is exactly the same name as the header row in the "Nursery" worksheet.

So,

In the "Nursery" worksheet/"Nursery Grand Total" column, I would like to copy the auto-sum amount

and paste it into....

the "Totals" worksheet/"Nursery Grand Total" row/"Master Grand Totals" column

Here are some pictures for reference...

From the Nursery Worksheet
Product ID Shrubs Plants Nursery Grand Total ABC00144 $6,853.98 $200.00 $7,053.98 ABC00145 $0.00 ABC00146 $857.60 $857.60 $1,715.20 ABC00147 $18,967.82 $18,967.82 ABC00148 $12,064.80 $12,064.80 ABC00149 $6,526.24 $2,295.00 $8,821.24 ABC00150 $0.00 ABC00151 $1,476.46 $1,476.46 ABC00152 ABC00153 $2,295.00 $7,785.64 $10,080.64 ABC00154 $0.00 ABC00155 $0.00 ABC00156 $9.62 $9.62 ABC00157 $3,082.00 $124.55 $3,206.55 ABC00158 $5,562.61 $5,562.61 ABC00159 $10,000.00 $10,000.00 ABC00160 $1,546.00 $1,546.00 $3,092.00 ABC00161 $0.00 ABC00162 $367.50 $367.50 $735.00 ABC00163 $2,093.71 $344.56 $2,438.27 ABC00164 $75.00 $43.99 $118.99 $54,150.93 $31,192.25 $85,343.18


From the "Totals" Worksheet
Master Totals Decription Master Grand Totals Nursery Grand Total


Can this be done?

Thanks


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


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.

Regards


simon

Hi.. I have added a calculated field in the pivot table for calculating weighted average. The formula is for the calculated field is ((Calls * Value) / Calls) . Here 'value' is the value for fields like AHT, ACW etc.
However, I need to display the grand total also as weighted average which is now showing as total of each parameter. The calculated field is not allowing me to change it to average.
One image has the data format, the other one displays my problem - grand total...






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?

Hi

I have a pivot table wth a field in the column area containing two values,
yes and no. My data is then filterd to these two columns. I have created a
calculated item adding the yes and no for a total people. I created another
calculated field to compute the percentage of 'yes' to the total people. I
have turned off the row grand totals. My column sub totals snd grand totals
are not giving me acurate percentages for the data in the fields. Am I
missing something?
Yes/No
No Yes Total Percentage
Person1
Sub total person1
person2
sub total person2
person3
sub total person3

Thanks for your help in advance.




Hi All,

I have a macro to create a pivot table. The macro works and the pivot table is great, but I would like to create custom grand totals and I do not know how to do this. The pivot table looks like this......

Dept Ledger Jan Feb Mar
100 Actual 50 75 80
Budget 25 90 45
$Over(Under) 25 (15) 35
200 Actual 10 35 20
Budget 40 50 40
$Over(Under) (30) (15) (20)
Grand Total 120 220 200

The $Over(Under) field is a calculated item. What I would like to do is to have 3 grand total rows - total actuals, total budget, and the over(under) budget row, so that it looks like this.....

Dept Ledger Jan Feb Mar
100 Actual 50 75 80
Budget 25 90 45
$Over(Under) 25 (15) 35
200 Actual 10 35 20
Budget 40 50 40
$Over(Under) (30) (15) (20)
Tot Act 60 110 100
Tot Bdg 65 140 85
$ Over(Under) (5) (30) 15

...but I do not know if this can be done. Can anyone let me know if this is possible, and if so, how to do it?

Thank you for reading my topic!

decklun


Hi,

How do i change what value gets displayed in teh Grand Total row in a pivot table.

Currently the Grand Toatal row has no information and I want it to calculate the sum of a specific column.

TIA,
sippon77


Hello,

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.

Cheers