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

Adding A Total Row To A Table.

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

I'm trying to add a total row to a table. Nothing seems to work. The only time I can get the "table styles option' to appear is when I select all the columns. (ok, I can live with that) but when I click the 'total rows' checkbox, nothing happens. Again my book says that it should create a total row, adding the word 'total' in the first column and that I should be able to select the first blank cell at the bottom of the column I want to total and it should give me a drop down. None of this happens.

What I'm trying to do...
I have a worksheet that I have a column as a table (so the user can easily filter), I want to have the amount column total based on the filter (if that makes sense) ie Eastern region (for example) shows total for that region. Filter on Western, and only see that reigion total.


edit (book meaning the EXCEL 2007 book I bought at Border's Book Store)

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 ...
Display Filter Arrows in a Table or Data Set in Excel - AutoFilter
This free Excel macro allows you to apply filter settings to a data set, list, or range of data in Excel. This wil ...
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 ...
Remove All Filtering From a Worksheet in Excel
This Excel macro removes all filtering from a worksheet in Excel. This allows you to completely remove any filter ...

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 to Show the Bottom 10 Percent of a Data Set in Excel - AutoFilter
- This free Excel macro filters a data set to display the bottom 10 percent of the data set in Excel. This is a great mac
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.
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

Similar Topics

I have a spreadsheet of data collected for a store chain from transactions completed at multiple locations.

My headings a

Store #, Cashier #, Items scanned, Total Amount, Date

Then there are several entries for each store where the store number is the same (obviously). So I wanted to be able to have a drop down filter that you can pick out the store number you want, and only see the entries for that store.

I also want to show the total number of items scanned, and total amount for each store. I did that by doing using the subtotal menu. This worked fine when I wasn't trying to use the drop down filter. But the column that has the store number in it is the same column that has the words "146 total, 215 total, etc" in it. So my drop down filter has each store, and then each stores total as an option. I want to eliminate the totals from the drop down list and only show each store number, but I can't figure out how to remove items from the list.

Right now it looks like this:

I just need to get rid of the totals from the box, that seems simple, but I can't figure out how.

Right now you can pick out the store from the drop down filter, and it will show only the information for that store and on the bottom under all the data it shows the total, which is what I want. But if you pick the option for "146 total" from the drop down filter for example, it just shows all zeros for the information, so I don't even want to have that as an option.

I'm a newb, help!


I was wondering if there was a neat trick for doing this in Excel 2007?
I can get a total row perfectly easily via the Table Tools, but the table contains a lot of rows and it is a pain having to scroll down to see the total row each time. Is it easy to move the total row to the top above or below the header row?

very grateful for any help!

Hello - I wonder if anyone would be able to help with a formula I'm stuck on

I have a spreadsheet for accounts, and a running total at the bottom. I assume I'm doing it the right way, I keep inserting new rows to push the "total row" down...

So In Column F the formula i have is =SUM(F2:F93) (So it doesn't try to add the words in the F1 cell)

The problem is when I sort the A column by date - ie looking at a particular month, the total still shows the overall amount since day one. Is there a way to link the total to what is selected on the screen?

hope that makes sense!?

I have an Excel 2002 spreadsheet where there are several records. I would
like to shade certain rows from Col A: Col J where the following occurs: if
current cell has the word total and the cell above has the word total in it,
I would like to shade two rows below current cell (with say green -- code 35).

Thus, if E42 = Book Total and E43 is June Total, I would like to shade A:J
in row 45.

If E42 = Book Total and E43 does not contain total then do nothing.

Tom Ogilvy and Jim Cone helped me in a previous problem similar to this but
I am stumped and don't know how to proceed from the point below. Any help is
really appreciated. Thanks.

Private Sub mcr18Shade()

Dim RngCell As Range
Dim lngCounter As Long

For lngCounter = 1337 To 1 Step -1
Set RngCell = ActiveSheet.Cells(lngCounter, 5) 'Col E

''If current cell in E has word total in it and cell above has total in it
then shade A:J

If RngCell.Value Like "*total*" And _
InStr(1, LCase(RngCell.Offset(-1, 0).Value), "total") > 0 Then

'Shade two rows below green (interior.color=35)
End If
End Sub

hi guys wondering if anyone can help?
i am making a book keeping / vat workbook, my cells are in the order below

i have it set up so the TOTAL is % by 17.5 and the VAT and the NET are automatically calculated, the TOTAL amount is automatically Duplicated into the Cheque Column (as most purchaces are by cheque) - i have managed to do this by =$B$3 formula, however what i need to do is, .... if i add a sum amount into the CASH column, it would need to to show in the CASH column and the sum amount to be removed from the CHEQUE column, and added to the TOTAL column.???
so by adding an sum in TOTAL .....its Duplicated to the CHEQUE column.
If i Add an sum into the CASH column......the CHEQUE sum is deleted and the amount is added to the TOTAL column
does enyone have any idea what i can do to make this work?
i would be greatfull for any help whatsoever ...... thanks for looking


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?



I currently use Excel 2007 and in the earlier versions of Excel it was possible to create a stacked column chart, with an included data table, that had a Total Row in the Data Table that wasn't graphically represented in the chart. The Total Row was at the bottom of the data table and didn't have a data legend. I cannot replicate this with Excel 2007. With a bit of work I can include a total row in the source data, move it to the top of the data table and ensure that there is no fill for the Total Data Series so it isn't reflected in the stacked column chart above the data table. The problem is that the Total row is now at the top of the data table when I need it to be at the bottom. If I move it to the bottom then all the stacked columns in the above chart appear to be levitating as the base of the column is the Total Data series which has no fill. I have attached an example of what the old excel generated and what I have been able top do with excel 2007.

Any ideas?



I Have rows of data that looks roughly the same, and all end the same, for example:

01 total
02 total
03 total
03gdd total
04 total
05 total
05edi total
06 total
09 total
12 total
12as total
12gs total
12edi total
12ddrh total

Theres a lot of this recurring data. Is there a formula that i can use that will show only the left part of the info and not 'total'?

i would have thought


But it's not that simple

I am familiar with Excel. I need some help with the manner in which I should organize my spreadsheet.

Currently, I am doing manual accounting. I maintain two books -- Bill (Sales) Book and Account Book.

A sample bill from the Bill book looks like this:

Date: 24/11/09

Party Name: ABC
Bill No: 123
Items Qty Rate Amt
Total Amt .........
Add:Previous dues* .....
Total dues .........

*Previous dues must be the total of all dues receivable from the party.

A sample page from the account book looks like this:

Party Name:

Bill Date Bill No. Bill Amt Amt Recd Total dues

(The total dues column above is the total of all dues till date which is equal to "Total dues" in sample of Bill Book shown above)

1. None of the payments are received bill-wise. So there is no need to cancel out a bill after payment is received.
example, if bill 1 is of amount 13,400 and bill 2 is of amount 12,400, the party will never bother to look at either 13,400 or 12,400. The party will only look at the "Total dues" and pay some amount.

How do I put the above manual system in Excel ?

I am using Excel 2007. I have a data set that will change from month to month. I am taking the data set and creating a Pivot Table. The Pivot table has account #s in my rows, cost centers in my columns and net expense as my data. The Pivot table accurately give me a total of the expenses by Cost Center. Once I have the total expenses by cost center, I am calculating as a % the Total Expense By Cost center to the Total of all the Cost centers. I then want to add up the % amounts and have the total reflected in my Grand Total Column. I have been able to do all this without any issue.
My problem is I want to create a Macro which will automatically take my data set, create the Pivot Table and then create the % row and finally add up the % amounts in my Grand Total column.
Again, I have figured out how to deal with my data set changing from month to month (using the Table function). I have even been able to get the Pivot Table to get reflected.
What i can't seem to figure out is how to get my Grand Total % sum, when the Pivot Table results will be different each month (more columns and less columns). I thought I could try sokmething with the sum(offset..) but I can't get that to work.
I hope this makes sense. Any suggestions or ideas would be appreciated. Also, if there is an easier way to do this that would be great.


I want to use pivot table and attaching a file containing data

Plz make a suitable pivot table as I want following data

-Region-wise sum of all columns such as Qty, Inv. Value, TOTAL RMC, TOTAL VC, TOTAL IWC
-Category-wise sum of all columns such as Qty, Inv. Value, TOTAL RMC, TOTAL VC, TOTAL IWC

Please also suggest any free tutorial available for pivot table



I am using MS Excel 2007. My problem is with inserting rows in a table when a filter is applied.

I created a table in excel 2007 with no Excel 2007 specific table styles assigned. I applied an auto filter. The table automatically adds a new row when I hit the tab key irrespective of any filter applied. However, when I add a "Total Row" to the table, and apply a filter, I get the following message when I try to add a new row by hitting the TAB key "Cannot shift cells in a filtered range or table".

This would not be a problem only if I did not have other tables that I personally created returning this error message. Now even the new tables from scratch return this error unless I remove "Total row". Without the "Total row", the table acts as expected. I can add rows by hitting the tab button whether or not there is a filter applied.

I already checked if there was some kind of protection, hidden cells, or some formatting that would generate such strange behavior. I did not have this issue before. Can anyone assist me fixing this annoying issue?

Dear Sirs,

I have a problem in getting the pivot table to automatically post its monthly Total in Pivot Table 1 to the respective Column C in sheet Bank whenever the data refreshed in Sheet Data.

If MTH = Apr in pivottable1, Apr total amount post to Column C in Sheet Bank
If MTH = May in pivottable1, May total amount post to Column E in Sheet Bank
If MTH = Jun in pivottable1, Jun total amount post to Column G in Sheet Bank
If MTH = Jul in pivottable1, Jul total amount post to Column I in Sheet Bank
If MTH = Aug in pivottable1, Aug total amount post to Column K in Sheet Bank
If MTH = Sep in pivottable1, Sep total amount post to Column M in Sheet Bank
If MTH = Oct in pivottable1, Oct total amount post to Column O in Sheet Bank
If MTH = Nov in pivottable1, Nov total amount post to Column Q in Sheet Bank
If MTH = Dec in pivottable1, Dec total amount post to Column S in Sheet Bank
If MTH = Jan in pivottable1, Jan total amount post to Column U in Sheet Bank
If MTH = Feb in pivottable1, Feb total amount post to Column W in Sheet Bank
If MTH = Mar in pivottable1, Mar total amount post to Column X in Sheet Bank

Original sheet Bank consists of 34 Bank name. Therefore, I hope that I might get some help from you guys to save a bit of time in manually doing the posting.

Do I need to have a vba programming code to perform the above, or maybe there is some function in the PIVOT TABLE that I have no idea. Pls help me.

I have attach a dummy file for your reference.

Hello all after searching through this wonderful site I wasn't able to find any reference to my question.

I am using excel 2007 and what I'm trying to do is find a % of 2 running totals in a pivot table.

say I have a pivot table like this

5/1/09 5/2/09 5/3/09
Count 288 224 320
Running Total 288 512 832

Count 158 152 182
Running Total 158 310 492
Total Count 446 376 502
Total Running Total 446 822 1324

Count is a sum field and running total is a running total field

Is it possible to have another row that is a percentage of the individual running total / total running total?

for example 288 / 446, 512 / 822 ... for Asia Region

The raw data which I don't have much control over looks like

Date Count Region
5/1/2009 2 Asia
5/1/2009 1 Asia
5/1/2009 2 US
5/1/2009 3 US
5/2/2009 7 US

any help is apperciated

Excel 2003.
Can Autofilter be used to filter rows 5 to 99 - where rows 100 to 105 are total rows and so want to remain visible ?
The value of the filter is the word "Complete" in a column - which I'm unable to put into the total rows. So, selecting the filter in row 5 loses the visibility of the total rows.

Ideas ?

I am trying to create a pivot table from a very large data base where it takes alot of employees production for the day and gives it an average efficiency. But i am having problems when the pivot table calculates the percentage. It will calculate it correct goin across the pivot table but it doesnt calculate it correctly going down when it enters the total percentage for the person. All it is doing is adding the combined percentages together when in fact it needs to take the parts ran multiply by the cycle time and add that sum all together and then divide by 480 to give the correct percentage efficiency for the person for the day. I have tried many ways to change the formula but nothing seems to work for me. Here is a sample of the pivot table. This employees 5 should be around 71% if it is calculated correctly but as you can see it is comming up at 106%.

Any Help would be appericated

Sum of CYCLE TIME % EMPLOYEE PART# CYCLE TIME PTS RAN MACHINE Total DARYL WHITE 41002-MAP 1 60 STEALTH 25.0% 60 Total 25.0% 1 Total 25.0% 41002-MAP Total 25.0% 41066-MAP 1 121 STEALTH 25.2% 121 Total 25.2% 1 Total 25.2% 41066-MAP Total 25.2% 41120-MAP 1 84 STEALTH 35.0% 84 Total 35.0% 1 Total 35.0% 41120-MAP Total 35.0% 42066-MAP 1 48 STEALTH 10.0% 48 Total 10.0% 1 Total 10.0% 42066-MAP Total 10.0% 450141-MAP 1 26 STEALTH 10.8% 26 Total 10.8% 1 Total 10.8% 450141-MAP Total 10.8% DARYL WHITE Sum 106.0%

Hi all,

I just wanted to know if it was possible to conditional format and entire row based on 1 of it's cells. e.g. I have a table with 3 columns (total, positive, negative). I want to format the rows of this column based on the overall percentages in the Total Column (i.e. if the total column is greater than 5% of the overall Total then the row should have no fill). So row A has a total at 10, positive 4, negative 6. Row A should then all be filled.

Hope that a) makes sense and b) can be done. If it is posible how do I do it??



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?


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.

This is what Im doing. I am making a Time Study. So what I have done is: I am using Autosum function to total the individual A1- N1 column and so on. And Autosum to total the A1-A23 column and so on. I know that I can Total it up so that my vertical total is equal to my horizontal total. I hope this makes sense to you. I have looked in the Help but I must not be wording it right. I dont want the cell to say True or False.
Thank You for Help

I have a pivot table grouped by sales groups, under each sales group there is a list of their clients. Values, sales for this quarter, are given for each client. I want to filter the pivot to show only the sales groups whose total sales (a subtotal created by the pivot - this number is not in my data) are greater than a specific value.

Is this possible?

The data looks something like this:

Q1 sales Sector 7 G Star-bellies 26 Whos 77 Total Sector 7 G 103 Moe's Mulberry Street 50 Cats in Hats 25 Total Moe's 75

My goal would be to filter out, let's say, the entire Moe's group because their total is less than 100.

The sales groups whose total sales are greater than the threshhold should still be visible, and the list of their clients and this quarter's sales figures cannot be hidden.

Setting a filter for column A to text containing total and then using value filters on column B is not a solution - lines are hidden. For some reason, everyone I'd talked to give this solution.

In a perfect world, if my overlords came to me and asked to re-filter based on another value, that would be a few clicks.

FWIW, the pivot table I am working with is more than 9,000 lines.

Hi All,

I have a sheet with two tables in it. Both have 16 columns.
The first column is the name of a site, then columns 2-15 are the page views for each day over a two week period, finally column 16 is the total for the two weeks.
The second table is exactly the same except the values in columns 2-15 conform to time on site.
What I want to do is create a macro (i think) that looks in column 1 of table A sees the entry in row 1, attempts to find the same value in column 1 of table B (becasue the sites are not in the same order in both tables) then muliply the value in column 16 of table A with the corresponding entry in column 16 in table B. I would then like to iterate through the rest of the values in column 1 of table A until all entries have been multiplied.
Something like this

Table 1
Site Page View Page View Total 5312 234 5546 1245 23 1268 78 35 113

Table 2
Site Time Time Total 2.1 1.3 3.4 43.1 4.6 47.7 12 43.4 55.4

where we multiply the value for total of in table 1 by the total value of in table 2.


I'm having trouble finding information on how to change the row total in a pivot table using VBA. I wrote (well, copied from Mr. Jelen's book) some code and was able to build a pivot table of a data set, however the row total is automatically set to sum the values of the row. I'd like it to average those values (or any of the other options you can normally choose from).

Every time I search, all that comes back is the way to turn off the row total...

Any ideas?

I am extremely new to Excel so would appreciate any help you may be able to provide.

I am trying to create a form that will enable users to know immediately if there is an error in their data entry. The row total (B50:J50) should equal the column total (K3:K49). I have put in test numbers and the row total and column total do equal one another.

While I could put the row total in K50 and the column total in L50, I would prefer to incorporate everything within the K50 cell, show that total or have it flag "Error" if the two totals do not match.

I know this works with the IF function if I have a single cell value but can't find out how to do it with arrays when they are in the same cell.

Need help with a spreadsheet in getting the the Total Value to Date H3 (10.00) into I3 Previous Total Value before adding another amount in E3 so that I can end up with a Previous Total each time before I add the next weeks data - any alternative option is OK
Thanks in advance

1 A B C D E F G H I J 2 2-Feb-00 4-Mar-00 4-Apr-00 5-May-00 5-Jun-00 6-Jul-00 6-Aug-00 Total Value To Date Previous Total Value Total This Value 3 1.00 2.00 3.00 4.00 10.00