Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Nested Sub-Totals in Excel

I'm having problems with nested sub-totals in Excel. The original sub-totals
works well, but when I try to do a nested sub-total, the nested function
doesn't appear to be working. Surprisingly, I didn't have a problem until
this past September. I'm still using the same version of Excel 2003 and have
used several different computers with the same result.
Has anyone else encountered this? And, do you have a solution for me?


Post your answer or comment

comments powered by Disqus
User has Excel 2002 (10.6834.6830) SP3

We have applied patch KB892097 and have done a Registry fix that this patch
appears to require.

Problem: Before this patch was done to correct the way Sub Totals work, the
client would do 5-nested sub totals and Excel would put 5-Grand Total rows at
the bottom, all with the same number. Since the patch, there is only one
Grand Totals row at the bottom. Is this working as designed? Why the change?

Thank you for any responses.
--
Brenda

I have up to four nested sub-totals however my last nested sub-total is
printing after the category sub-total. Both sub-totals are correct just
displayed. The first grouping has two nested sub-totals and is correct the
second grouping only has one nested sub-total and the category sub-total and
starts the incorrect display. From then on all categories are incorrectly
diplayed. I've never had this happen before and now I'm confused how to let
the system do its job. I could cut and paste but I shouldn't have to.

Hi all,

I have a pivot table which has headings "primary category", "Sub category", and "Totals".

I hide the Sub totals in the pivot table for each primary category and I now want to put them back into the table but I cannot figure out how to do so.

I know their is a simple answer to this but for the life of me an unable to figure it out.

Does anyone know the answer?

Many thanks,

Nicko

Pls help me to find the solution to do sub total in my work sheet which I am using regularly in my work.
Pls go thru attached file. In this work out sheet is my working sheet in which i used to calculate tax, margin & freight. When I submit it to client I have to submit in the form of BOQ sheet. So each item should be done sub total and that should not affect my grand total at bottom. How to do it>

Regards

Khaleel Ahamed (omdkhaleel@gmail.com)

I want a macro i can run on a selected Pivot table, to set all pivot table field sub-totals to "None".
Excel has no default setting that i am aware of to set sub-totals to none, before or after creating a pivot table. Without individually selecting each field and setting to none.

The next level would be to show all feilds in a combo box, and select any or all to set to nothing etc.

Any help would be appreciated.

Hi,

Any help/soloutions for this query would be gratefully recieved.

I am using excel to create monthly payslips from information gathered from an SQL database program used by a transport company.

I am able to export the necessary data into excel (i.e. Employee Name, date, etc..) and then sepearte the data onto 1 page per employee using the sort and then subtotal tools.

This then gives me a "monthly payslip" per employee, which shows in each row an individual daily date column, a job description column, an employees name column and 5 rate columns. (all the employees are paid daily hence the need to display each days rate on a seperate row). There are 5 rate columns that correspond with the possible 5 rates of pay that an employee can earn depending on which job/site they are working on on any given day.

When using the subtotals tool to split each employee onto a seperate page (payslip) excel displays the monthly total for each rate column at the bottom of the page.

This is all fine but my query is how to then take the sub-total (as generated by excels subtotals tool) for each rate column, add them together and then display a grand total for the months earning at the very bottom of the page (payslip). I obviously wish to be able to do this automatically rather than using a sum and manually creating the grand-totals for each employee (+200!)

Please see below a link for an image of what i am trying to achieve. The bright green lines indicate a page break for each individual employees pay slip. The red box highlights the sub-totals that i want to add together to gain a grand-total which can then be displayed at the bottom of each page.

Any help would be fantastic, thankyou

http://img411.imageshack.us/img411/5...celhelpbl4.jpg

Hello,

I have created a nested function within Excel 2010 that works fine in 2010 and 2007. It does not work in 2003 and now I know that it is because I have more than 7 nested formulas in my original file. I have tried several alternatives, but nothing works. My original formula in 2010 is:

=IF(O7=1%,"0.0101011",IF(O7=2%,"0.0204082",IF(O7=3%,"0.0309279",IF(O7=4%,"0.0416667",IF(O7=5%,"0.0526316",IF(O7=6%,"0.0638298",IF(O7=7%,"0.0752689",IF(O7=8%,"0.0869566",IF(O7=9%,"0.0989",IF(O7=10%,"0.11111", IF(O7=11%,"0.1235956",IF(O7=12%,"0.1363637",IF(O7=13%,"0.1494253",IF(O7=14%,"0.1627907",IF(O7=15%,"0.1764707",IF(O7=16%,"0"))))))))))))))))

Essentially in cell "O7", I change the percentage ranging from 1% to 16% and the cell next to it, "P7" is where I have this formula. Based on the percentage in "O7", the decimal equivalent shows up in "P7", so 1% in "O7" gives me 0.0101011 in cell "P7". All the rest of the calculations in the sheet are generated from this formula in P7 and have to be multiplied by this decimal.

I tried to create a custom VBA function called CalcValue, per another page's suggestion. I copied their suggestion and wrote the macro as follows:

	VB:
	
 
     
    If pVal = "1%" Then 
        CalcValue = 0.0101011 
         
    ElseIf pVal = "2%" Then 
        CalcValue = 0.0204082 
         
    ElseIf pVal = "3%" Then 
        CalcValue = 0.0309279 
         
    ElseIf pVal = "4%" Then 
        CalcValue = 0.0416667 
         
    ElseIf pVal = "5%" Then 
        CalcValue = 0.0526316 
         
    ElseIf pVal = "6%" Then 
        CalcValue = 0.0638298 
         
    ElseIf pVal = "7%" Then 
        CalcValue = 0.0752689 
         
    ElseIf pVal = "8%" Then 
        CalcValue = 0.0869566 
         
    ElseIf pVal = "9%" Then 
        CalcValue = 0.0989 
         
    ElseIf pVal = "10%" Then 
        CalcValue = 0.11111 
         
    ElseIf pVal = "11%" Then 
        CalcValue = 0.1235956 
         
    ElseIf pVal = "12%" Then 
        CalcValue = 0.1363637 
         
    ElseIf pVal = "13%" Then 
        CalcValue = 0.1494253 
         
    ElseIf pVal = "14%" Then 
        CalcValue = 0.1627907 
         
    ElseIf pVal = "15%" Then 
        CalcValue = 0.1764707 
         
    Else 
        CalcValue = 0 
    End If 
     
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I did this in Excel 2003 as a workaround for having more than 7 nested formulas in my worksheet in Excel 2010, because when I opened this file in 2003, the formula stopped working. But I'm still having problems in 2003; in "P7", the formula reads "=CalcValue(O7)", but the value stays at 0 in "P7" even though I change the percentage in "O7". Is there another way to do this or a reason why what I typed isn't working for me?

Excel Picture.jpg

I appreciate your help!!!!

Thanks,
faasmitty

Hello,

I have searched several threads and have not found quite what I'm looking to do.

In the attached file, you will find the data table structure that I'm basing the PivotTable on, as well as the PivotTable that I have created. To the right of the PivotTable are my calculations and formulas of what I'm trying to accomplish within the PivotTable so the results can be seen.

I am trying to use a PivotTable to see how different people's time are allocated across different workstreams (just a code, such as DR29 or DR36). So for a given month, a person will have reported actual time worked to any number of workstreams. For example, in my spreadsheet, Bob spent part of his time on DR29 and part of his time on DR36. He spent a total of 12 hours on these tasks, which shows up correctly in the Bob -> April sub-total.

What I want to do now is to calculate the % of time in APRIL that BOB spent on each DR. So Bob's April % will total 100%, matching the sub-total. For each of the DRs that were worked on, it would be a percentage of only BOB's time.

Currently, I can only figure out how to get it to calculate the percentages using the Sum of Actual Days and Showing Values as a % of column. This is almost what I want to do, but it's showing me the % of the TOTAL time spent, not broken down by sub-total.

Please help! Thanks!

Hi, I am trying to use the "show data as" in the pivottable field setting to do % of colum, this is fine but I have sub totals and want the % of these, not the whole colum. Does any one know if this is possible

I can do this with no problems out side a piv table, but want to include it in the table as a calculation.

thanks

Dan

I have a pivot table with its data in an Access table.
When I choose table options to sum or sub total 2 colums at the bottom the
words Sub Total show up at the far left but my columns are not summed?? When
i add the data to the data area for summing it shows them in colum with data
listing downward in a column like i want but no sub at the bottom.
For example to add rows 1-25 in the hours colum it wont sum them up.

HOURS FEES
3 2.00
2 5.00
1 3.00
---------------
6 10.00

I have a pivot table with its data in an Access table.
When I choose table options to sum or sub total 2 colums at the bottom the
words Sub Total show up at the far left but my columns are not summed?? When
i add the data to the data area for summing it shows them in colum with data
listing downward in a column like i want but no sub at the bottom.
For example to add rows 1-25 in the hours colum it wont sum them up.

HOURS FEES
3 2.00
2 5.00
1 3.00
---------------
6 10.00 <------This doesnt happen unless
I do it outside the pivot table
ANd if i do that then when ever my
records increase in the underlying
table it pushes the sum formula out and
i'm bakc to square 1.
ANY HELP??

I use 2 or 3 levels of sub totals. I'm using about 5000 lines of data. When
there's only 1 line of data being sub totalled it puts the sub total in the
wrong place. This spoils the presentation when using the +. Worked fine with
Excel 2000! Any ideas?

I'm pretty well versed in excel basics and formulas, but I've run amuck trying to figure out how to get it to calculate quantity.

What I want it to do is be able to enter a total quantity (example 14)
In a second box enter the number used (example 4)
and then calculate what this leaves (example 10) but change the first value to that number and clear the second box.

Can someone point me in the right direction?

I am using pivot tables to show summary HR recruiting data. The data columns
are nested at three levels - priority(a, b or c), type(add/replace), number
of reqs and number of positions. The pivot table automatically gives me
sub-totals within the priority so I get number of reqs that are adds of
priority A for example. I also automatically get a total of number of reqs
and total of number of positions. What I'm trying to get is the sub-total
of number of reqs that are adds regardless of priority.

I attach apreadsheet with a list of dat in tab "Evnt". I would like to

1 Insert sub-totals in columns L to Q at the change of Evnt No. in Col "A".
2 Click outline button 2 to hide the details and show only the totals.
3 Sort the rows by column P by descending order. This sorts the entire data including hidden rows.
4 Click outline button 3 to unhide the rows.

I have written VBA for step 1 above. This however takes about 20 secs. Is there a better way of doing this in lesser time?

Regards
N V Bhaskaran

Can you please help me?
I have two columns on my spreadsheet.
Column A is a list of figures
Column B is a column for sub totals

I want to put in sub totals in random places in my Column B. I would like a macro that will automatically add up all the figures AFTER the previous sub total point.

I have attached a file to illustrate this.
Hope it makes sense
Thanks - John Haliday

I can no longer select all the sub-totals in pivot (arrow does not appear).
Can anyone think of the reason why?? Many thanks..

Hi All

I am wondering if it is possible to calculate the %'s for amounts that make up sub totals in a pivot table? Attached is an example. The % I am after is for the "WBS Element" sub totals.

Cheers

Rod

Hi all

Attached is an example of the workbook I am looking for

What I have created so far :

A macro will take the sheet "Data" and split the info across into new separate worksheets labelled "SIM","MOR","RED","CAR","EMB"

The split worksheets contain the same info as "Data" however the column for "Total Arrears" is sorted as descending and the moment values become negative, there is a 5 row gap to seperate the Positive amounts and Negative amounts in a descending order

What needs to be completed:

I need to create Sub Totals in columns E:K for both the negative and Postive balances (and Label them as "Sub - Total" in column D) as well as have them represented as a percentage, and then after that have a grand total value represented at the bottom

I have attached examples of what the final product should look like for each worksheet (they are labelled in blue)

The problem I have is that I am unsure of how to use VBA to recognise where to put the amounts for Sub Totals. I would assume it would use a .End.xlUp.Row and some kind of Offset, but I don'r even know where to begin

The "Data" sheet has a button that runs the macro to split the data into worksheets, but the worksheets SIM/MOR/RED/CAR/EMB must be deleted first since there cannot be duplicate sheets (which is why there is also a button to delete those sheets)

Help with this matter is appreciated

Regards

When I calculate sub-totals in Excel the software adds the new lines for
sub-total totals and the grand totals but I do not see nor have the ability
of select from the outline view that is suposed to appear to the left of the
row numbers. ie I cannot just select 'totals' or 'grand totals' by clicking
on option 1 or 2 or 3

I would like to have help in trying to make a final page that adds up all totals in other tabs in the same workbook,

I have uploaded my document. I would appreciate any help you can offer.

thanks again

Rick


1. Select a cell in the PivotTable report, right click and select Table Options -> Totals & Filters tab.
2. Clear the check box for Show grand totals for rows or Show grand totals for columns.

Select PivotTable Tools Ribbon -> Design tab -> Grand Totals (in Layout Group).

I have a report with sub-totals in Excel 2003 and I have copied the
sub-totals as a paste special values to a new spread sheet and want to remove
the word total and keep a leading zero that is part of our Part Numbers. is
there an easy way to do that? Here's an example cell
Befo
03010042 Total
After I want this:
03010042
When I do a find and replace it removes the leading 0 even with the column
set as a text column.

I have a report with sub-totals in Excel 2003 and I have copied the
sub-totals as a paste special values to a new spread sheet and want to remove
the word total and keep a leading zero that is part of our Part Numbers. is
there an easy way to do that? Here's an example cell
Before:
03010042 Total
After I want this:
03010042
When I do a find and replace it removes the leading 0 even with the column
set as a text column.


No luck finding an answer? You could always try Google.