
Adding A Total Row To A Table.


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.
Thanks.
edit (book meaning the EXCEL 2007 book I bought at Border's Book Store)
Similar Excel Video Tutorials
Summarize Survey Pivot Table
 See Mr Excel and excel is fun summarize survey data with a Pivot Table (Grouping & Report Filter), COUNTIFS function (4 criteria), SUMPRODUCTS for ...
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.
Here's a picture of what it looks like now.
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 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 have in column a many total rows like so 'Total 01/08/2006' and on each total row in column C a total value.
I want to create a column in a separate book that has on the top row a month (example August) with days of the month 131 below. For each day i would like to look up in the other workbook and retreave the total value for each day.
I can do this as a formula but want to consider that easiness of running it in VB
Is there such a thing as a master filter? let me explain.....
I have a large xl book with 70 pages or so. the last page has total figures for all other pages.
i can filter each page to a name (such as sean) to get his total figure. Is there a way i can filter the whole book (to sean) to save me going through all the other pages?
i hope this makes sense!!!
eg....... i filter jan, feb, mar, apr etc to seans name, and the total will give me his totals. this is time consuming, so i want to filter all pages (jan, feb mar ect) to sean without going through each page first.
Hi,
Can you help me in generating a chart (2 axes) for the following table...date wise. The resulting graph shold have HC and TEM with total salary date wise..
23Feb11
24Feb11
25Feb11
26Feb11
27Feb11
28Feb11
1Mar11
2Mar11
3Mar11
4Mar11
5Mar11
6Mar11
7Mar11
8Mar11
9Mar11
10Mar11
11Mar11
12Mar11
13Mar11
14Mar11
Particulars
Units
Total
HC
TEM
Total
HC
TEM
Total
HC
TEM
Total
HC
TEM
Total
HC
TEM
Total
HC
TEM
Total
HC
TEM
Total
HC
TEM
Total
HC
TEM
Total
HC
TEM
Total
HC
TEM
Total
HC
TEM
Total
HC
TEM
Total
HC
TEM
Total
HC
TEM
Total
HC
TEM
Total
HC
TEM
Total
HC
TEM
Total
HC
TEM
Total
HC
TEM
Total Head Count
Number
303
94
209
304
95
209
304
95
209
304
97
207
304
97
207
304
98
206
304
98
206
324
118
206
324
118
206
340
133
207
341
133
208
341
133
208
341
133
208
342
134
208
345
138
207
Total Salary
Currency
227,434,016
121,766,400
105,667,616
227,953,016
123,205,400
104,747,616
227,953,016
123,205,400
104,747,616
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
Hello,
I'm stuck in totals kindly help me out.I did subtotals of the Speed limit column of my sheet.In the result I got , I had subtotals for speeds multiple .But I want the totals for every speed once .These are :
A
B
0 Total
1.4
20 Total
0.333333
30 Total
240.6879
40 Total
70.13167
50 Total
46.06567
60 Total
68.06233
70 Total
21.4
30 Total
4
30/40/60 Total
2
40 Total
3.666667
40/30 Total
3
40/60 Total
4
50 Total
1.333333
60/30 Total
3.333333
60/50 Total
0.333333
n/a Total
79.73327
part 30/ part 40 Total
1.333333
part 30/ part 60 Total
0.66667
part 40/ part 60 Total
2.33333
30 Total
5.333333
40 Total
1
50 Total
1.333333
60 Total
0
30 Total
0.33333
40 Total
2.33333
60 Total
4.666667
70 Total
1
30 Total
5.8
40 Total
1.1
50 Total
4.7
60 Total
1
30 Total
2.666667
40 Total
5.733333
50 Total
3.66667
60 Total
3.4
n/a Total
1
30 Total
28.03333
40 Total
1.66667
50 Total
0.66667
70 Total
1
30 Total
15
40 Total
2
50 Total
8.666667
30 Total
0
40 Total
0
50 Total
0
30 Total
0.3
40 Total
4.3
50 Total
0.3
60 Total
8.966667
70 Total
1.7
20 Total
0.333333
30 Total
68.33333
40 Total
18.33333
50 Total
18.66667
60 Total
1
70 Total
2.333333
n/a Total
78.66667
30 Total
0.666667
40 Total
9
50 Total
6.333333
60 Total
10.96667
70 Total
2.7
30 Total
16.66667
40 Total
10.66667
50 Total
5.666667
60 Total
43.66667
70 Total
11.66667
30/40/60 Total
2
40/30 Total
3
40/60 Total
4
60/30 Total
3.333333
60/50 Total
0.333333
30 Total
30
40 Total
4.333333
50 Total
0
60 Total
2
30 Total
2.666667
40 Total
1.666667
50 Total
0
30 Total
4
40 Total
3.666667
50 Total
1.333333
part 30/ part 40 Total
1.333333
part 30/ part 60 Total
0.66667
part 40/ part 60 Total
2.33333
30 Total
1
40 Total
16.66667
50 Total
5.333333
60 Total
1.333333
30 Total
17.22222
40 Total
8.333333
50 Total
0.666667
70 Total
1
30 Total
30
40 Total
0
50 Total
1
60 Total
3
30 Total
13.33333
40 Total
10
50 Total
0.333333
60 Total
1.333333
70 Total
2
30 Total
1
40 Total
2.333333
50 Total
1
60 Total
1
30 Total
0.999
40 Total
1.335
50 Total
0.999
60 Total
1.329
What formula do I need to apply here so that all individual speeds get added?
Kind Regards,
siddharth
Have a pivot table with a column that shows a % of total based on a sum from my data sheet. I also have a filter on this pivot table. On the side I have a pivot chart. What now is being asked is can I take the column with the % of total and anything less than, let's say 5.0%, they would like it to group and show a total. I know I can highlight it and group  it will total, etc. but when they use the filter that no longer works. Is there a way to have the less than % group and total based on the pivot table's filter?
I am trying to convert some Access 2003 database output into an accessible table with totals. Access reports put out nonaccessible format which cannot easily be converted into an accessible format. So I tried exporting the data to Excel 2003. It's looking like I don't have an easy way to make that accessible without a lot of manual work.
In the attached document, I've created a simplified version of what I have and of what I want to accomplish.
The first tab, source data, simulates the Excel output from the database query.
Columns:
Region  a region code.
Region name  the corresponding region name.
Section name  the corresponding section name.
Section  a section code.
Hours  an amount I want to show/total.
Dollars  an amount I want to show/total.
Quantity  an amount I want to show/total.
The desired result is shown on the second tab:
Section name  the corresponding section name.
Section  a section code.
Hours  an amount I want to show/total.
Dollars  an amount I want to show/total.
Quantity  an amount I want to show/total.
What I want is that:
 The original detail rows could either been hidden or not present in the result.
 The figures for each section in a region are summed to a single row without the row being labeled "total."
 The figures for each region are preceded by a row giving the region name.
 The figures for each region are followed by a row giving the region name followed by the word "total."
 There is a total row named "total."
 All "total" labels must appear in column 1 for accessiblility.
Do you know of anything that will produce this?
Presumably Access could, but its output is not accessible, nor is it easily made accessible, presumably because you essentially "draw" the report.
Hi guys,
Havent some trouble here,
Ive just made a macro to auto create a worksheet and a pivot table based on data from another worksheet in the work book, though, I am trying to make any cell on the pivot table worksheet (in the pivot table, or not) highlight if the string "Total" appears.
For some reason using the below conditional format strang its does not work... any thoughts on what I should do?
FYI: I selected rows 5 through to 358 to be applied this conditional format.
Please note that most of the cells with "Total" has other words in the same cell. Ie: Grand Total, Total, Sarah Total, Smith Total
Code:
=NOT(ISERR(SEARCH("Total",$5:$358)))
I made a table in excel to calculate total (unitprice*quantity=total), the last row is the grand total. This table is inserted in a word doc. The problem is that for every empty row, $0.00 comes in the total column. While it doesnt change anything, it just looks unprofessional (i think).
I know its because the column is formated as Currency with 2 decimal places....well is there a way to ignore the empty rows and just have their total column empty (while displaying the grand total).
My option now is to recreate the table everytime(too much work) :x
Does anybody know a solution to this?
Thanks
Every book I have read tells me NOT to make a TOTAL field in the database because Access can take care of that in the REPORTS. I need to find a method to sort/filter the TOTAL Text Box in the REPORTS and I need to sort/filter by >0 or 0. In my database I purposely use empty text boxes but I handle the nulls with the NZ command.
Everything works they way it should with ONE major exception. In the REPORT when I click on the TOTAL text box the FILTER option does not activate. I only want to see records in the REPORT with the TOTAL is greater than zero (or does not equal zero).
Any suggestions???
Please help.
Thanks so much,
Richard
I have Excel 2007. I have a pivot table. The grand total is adding all columns. Can I change it so it only adds certain columns.
Sheet1
B
C
D
E
F
2
Add
Add
3
Book
Car
Bike
Computer
Grand Total
4
1
2
3
4
4
Excel tables to the web >> Excel Jeanie HTML 4
I would like to add book and bike. It is not an option to exclude the ones that are not being added from the report. They need to remain on the report. The grand total field is called 'sum of amount'. The columns are called 'Item'.
Hi All,
I have the following pivot table below. Is it possible to get a totals of each "Animal" field at the bottom of the pivot table? eg total of cow, cat and dog. I am sure I have seen this done but I can't seem to work it out.
I am using Excel 2007.
Cheers
kim
Sum of Amount
Version
Animal
Colour
Total
A
Cat
Black
15
White
20
Cat Total
35
Cow
Brown
20
Green
5
Cow Total
25
Dog
White
20
Dog Total
20
A Total
80
B
Cat
Black
25
White
40
Cat Total
65
Cow
Brown
40
Green
15
Cow Total
55
Dog
White
30
Dog Total
30
B Total
150
Grand Total
230
I created the file below using subtotal from Excel 2003. To format file, I clicked on no 2 in the top left hand corner of the work area. Excel seems to displays only column A (staff No) and column C (Amt). How can I display what's in column 2 (Name).
Staff No Name Amt
53 Total 7,399.35
34 Total 5,851.20
408 Total 5,468.00
32 Total 1,799.20
343 Total 4,716.40
45 Total 2,917.20
80 Total 2,212.00
59 Total 2,200.00
61 Total 2,706.00
82 Total 946.00
79 Total 4,680.00
90 Total 1,970.40
66 Total 4,782.80
47 Total 2,953.02
46 Total 6,571.20
88 Total 3,894.80
97 Total 6,760.00
77 Total 2,865.20
Grand Total 70,692.77
Thank you.
hello
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!
thanks
jon
I"m trying to work up a spread sheet that has 1 field with names. I want to make the name equate to a 1 in a formula to figure out the % of Contacts to sales.
Example:
NAME BOOK SS WORD OTHER SOLD
KIM KETCHER 1 1
ANDREW RICHARD 1
DAWN DEMARCO 1
Want I want to do is divide the number in column 1 by the number of people. I THINK that an IF command exists that will do this.
Right now what I'm doing is taking the column total for (example) book and manually adding the correct number of names.
to look like =sum(A15/<number of names counted manually>)
Is there a way to make the Name Column total a number at the bottom of the column like I do at the bottom of the columns for all the columns? Or am I stuck manually adding the total names up?
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
Next
End Sub
hi guys wondering if anyone can help?
i am making a book keeping / vat workbook, my cells are in the order below
DATE TOTAL VAT NET CHEQUE CASH
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
Debbie
Hi,
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?
Thanks
Hiya.
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
Code:
=A2(RIGHT(A2,6)
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)
Notes:
1. None of the payments are received billwise. 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.
Jeff
I want to use pivot table and attaching a file containing data
Plz make a suitable pivot table as I want following data
Regionwise sum of all columns such as Qty, Inv. Value, TOTAL RMC, TOTAL VC, TOTAL IWC
Categorywise 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
Thanks
shubhkaran
hi guys wondering if anyone can help?
i am making a book keeping / vat workbook, my cells are in the order below
DATE TOTAL VAT NET CHEQUE CASH
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
please see attached file January 1st page  till sales  im sure this will make it clearer!
does enyone have any idea what i can do to make this work?
i would be greatfull for any help whatsoever ...... thanks for looking
Debbie

