Adding A Total Row To A Table. 


Adding A Total Row To A Table.  Excel 
View Answers 
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)
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 Tutorials
Vlookup with a Range of Numbers in Excel
How to use the Vlookup function to return a result that falls within a range of numbers, such as a weight or quanti ...
How to use the Vlookup function to return a result that falls within a range of numbers, such as a weight or quanti ...
Combine Multiple Chart Types in Excel to Make Powerful Charts
In this tutorial I am going to show you how to combine multiple chart types to create a single chart in Excel. This ...
In this tutorial I am going to show you how to combine multiple chart types to create a single chart in Excel. This ...
RATE Function  Calculate an Interest Rate in Excel
The Rate function in Excel (RATE) calculates the interest rate for a financial transaction, such as for an annuity ...
The Rate function in Excel (RATE) calculates the interest rate for a financial transaction, such as for an annuity ...
How to Arrange Data within Cells in Excel
In this tutorial I am going to look at cell alignment / arrangement. These features allow you to change how data lo ...
In this tutorial I am going to look at cell alignment / arrangement. These features allow you to change how data lo ...
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
 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
 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
 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.
 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
 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!
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!
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!
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.
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.
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
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
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
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?
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
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 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
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
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.
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 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?
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 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
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
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
Hello,
I know that when adding a formula column to a table you simple enter the formula in the first data cell and it autopopulates 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
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
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
scenario: I have a list of clients in column A with the corresponding debt amount in column B. I made a code to filter the clients with a particular surname which I coded successfully. What I want to do now is to total the debt amount of the clients filtered. I want the word "TOTAL"in column A 3 cells down after the last client in the filtered list and the total debt amount in column B in the same row as the word "TOTAL". The assumption is you don't exactly know how long the list is so it is useless to make a code to put the "total" in a specific cell. My question: how do I select the blank cell 3 steps below the last filtered client? (say the last client filtered is on cell 50 in a total of 800 clients. Thus, when you perform the filter it will hide cells 51 up to 800). I want to select cell 803 to put the word "TOTAL". I tried the activecell offset etc... it still selects cell 53 and not 803. It would have been easy if I am allowed to delete the clients I do not want to filter... but I cant.
Cheers
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:
But it's not that simple
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 ?
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
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
Hi Guys,
I need assistance with VBA for a Pivot Table.
I want a VBA Macro that will filter out only items with the word "Total" in it. All the Items containing the word total have total as the last word in the item.
For example see two Items below:
James Total
Olise Total
I do not want this to show in the Pivot Table.
Thanks.
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
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
Hello,
Up until recently I could copy data like the following from notepad (tab between Total and amount) and paste it into excel 2007.
Total $29.85
Total $58.95
Total $59.85
Total $32.85
Total $103.09
Doing so would put "Total" in the first column, leave the second column blank and put the amount in the third column.
Now when I try to do this the tabs are turning into little squares with question marks [?] and excel is shoving everything into the first colum so it looks like this:
Total[?]$29.85
Total[?]$58.95
Total[?]$59.85
Total[?]$32.85
Total[?]$103.09
Anyone know how to stop this?
Thank you,
Almondmilk
Up until recently I could copy data like the following from notepad (tab between Total and amount) and paste it into excel 2007.
Total $29.85
Total $58.95
Total $59.85
Total $32.85
Total $103.09
Doing so would put "Total" in the first column, leave the second column blank and put the amount in the third column.
Now when I try to do this the tabs are turning into little squares with question marks [?] and excel is shoving everything into the first colum so it looks like this:
Total[?]$29.85
Total[?]$58.95
Total[?]$59.85
Total[?]$32.85
Total[?]$103.09
Anyone know how to stop this?
Thank you,
Almondmilk
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
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
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?
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?