Email:      Pass:    Pass?


Advertisements


Free Excel Forum

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.

Thanks.

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


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

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

23-Feb-11 24-Feb-11 25-Feb-11 26-Feb-11 27-Feb-11 28-Feb-11 1-Mar-11 2-Mar-11 3-Mar-11 4-Mar-11 5-Mar-11 6-Mar-11 7-Mar-11 8-Mar-11 9-Mar-11 10-Mar-11 11-Mar-11 12-Mar-11 13-Mar-11 14-Mar-11 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 non-accessible 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 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.

Jeff


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

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