| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table Grand Total Plus Calc Field or GT Running Balance
Good Morning All, XL2007
I have a basic Pivot Table summing vales across a row of months. A B C D Category Month1 Month2 Month3 Overheads 10 10 10 Expenses 20 20 20 Grand Total 30 30 30 Now I wish to insert another Grand Total Row which will show the Running Balance ie A B C D Category Month1 Month2 Month3 Overheads 10 10 10 Expenses 20 20 20 Grand Total 30 30 30 Grand Total(2) 30 60 90 I know I could add a seperate row outside the Pivot Tabe to reflect the Running Total, but would rather keep it included, via a Calculated Field or such like. I have managed to contrive a Calculated field, but this returns Subtotal rows for the Running Balance for all my Subdivisions, eg Overheads, Expenses etc. I wish just the Grand Total(2) to show the Running Balance. Is there a way? As usual thank you for any input. Cheers Mathew |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Pivot Table Grand Total Plus Calc Field or GT Running Balance
This requires a number of steps (Assume that the name of the field in the
column area is Months and the data field is Dollars: 1. Right-click in the Data area and choose Field Settings, Options, Running Totals In, and pick the Months field and click OK twice. This will replace the data area and totals with running totals. 2. Drag the Dollars field into the Data area a second time. You wil now have a Sum of Dollars2 field. 3. Click the lable (row area) for one of the Sum Of Dollars2, place your mouse along the edge of the selected cell and drag it up 1 row. This will put the regular sums on top row and the running totals on the row below that. 4. Hide the running totals in the data area but leave it displayed in the Totals area. Cheers, Shane Devenshire "Mathew P Bennett" wrote: Good Morning All, XL2007 I have a basic Pivot Table summing vales across a row of months. A B C D Category Month1 Month2 Month3 Overheads 10 10 10 Expenses 20 20 20 Grand Total 30 30 30 Now I wish to insert another Grand Total Row which will show the Running Balance ie A B C D Category Month1 Month2 Month3 Overheads 10 10 10 Expenses 20 20 20 Grand Total 30 30 30 Grand Total(2) 30 60 90 I know I could add a seperate row outside the Pivot Tabe to reflect the Running Total, but would rather keep it included, via a Calculated Field or such like. I have managed to contrive a Calculated field, but this returns Subtotal rows for the Running Balance for all my Subdivisions, eg Overheads, Expenses etc. I wish just the Grand Total(2) to show the Running Balance. Is there a way? As usual thank you for any input. Cheers Mathew |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Pivot Table Grand Total Plus Calc Field or GT Running Balance
Cheers Shane,
Haven't tried yet, but thank you for suggestion - first viable one yet, enjoy your rest of day at work! Mine finished (UK)- dinner now! Mrs cooking! Cheers again, I will post again, once tried out. Mathew "ShaneDevenshire" wrote in message ... This requires a number of steps (Assume that the name of the field in the column area is Months and the data field is Dollars: 1. Right-click in the Data area and choose Field Settings, Options, Running Totals In, and pick the Months field and click OK twice. This will replace the data area and totals with running totals. 2. Drag the Dollars field into the Data area a second time. You wil now have a Sum of Dollars2 field. 3. Click the lable (row area) for one of the Sum Of Dollars2, place your mouse along the edge of the selected cell and drag it up 1 row. This will put the regular sums on top row and the running totals on the row below that. 4. Hide the running totals in the data area but leave it displayed in the Totals area. Cheers, Shane Devenshire "Mathew P Bennett" wrote: Good Morning All, XL2007 I have a basic Pivot Table summing vales across a row of months. A B C D Category Month1 Month2 Month3 Overheads 10 10 10 Expenses 20 20 20 Grand Total 30 30 30 Now I wish to insert another Grand Total Row which will show the Running Balance ie A B C D Category Month1 Month2 Month3 Overheads 10 10 10 Expenses 20 20 20 Grand Total 30 30 30 Grand Total(2) 30 60 90 I know I could add a seperate row outside the Pivot Tabe to reflect the Running Total, but would rather keep it included, via a Calculated Field or such like. I have managed to contrive a Calculated field, but this returns Subtotal rows for the Running Balance for all my Subdivisions, eg Overheads, Expenses etc. I wish just the Grand Total(2) to show the Running Balance. Is there a way? As usual thank you for any input. Cheers Mathew |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Pivot Table Grand Total Plus Calc Field or GT Running Balance
Sorry Shane, cannot get to work, do not get the Sum of Dollar2 field to
show. Mathew "ShaneDevenshire" wrote in message ... This requires a number of steps (Assume that the name of the field in the column area is Months and the data field is Dollars: 1. Right-click in the Data area and choose Field Settings, Options, Running Totals In, and pick the Months field and click OK twice. This will replace the data area and totals with running totals. 2. Drag the Dollars field into the Data area a second time. You wil now have a Sum of Dollars2 field. 3. Click the lable (row area) for one of the Sum Of Dollars2, place your mouse along the edge of the selected cell and drag it up 1 row. This will put the regular sums on top row and the running totals on the row below that. 4. Hide the running totals in the data area but leave it displayed in the Totals area. Cheers, Shane Devenshire "Mathew P Bennett" wrote: Good Morning All, XL2007 I have a basic Pivot Table summing vales across a row of months. A B C D Category Month1 Month2 Month3 Overheads 10 10 10 Expenses 20 20 20 Grand Total 30 30 30 Now I wish to insert another Grand Total Row which will show the Running Balance ie A B C D Category Month1 Month2 Month3 Overheads 10 10 10 Expenses 20 20 20 Grand Total 30 30 30 Grand Total(2) 30 60 90 I know I could add a seperate row outside the Pivot Tabe to reflect the Running Total, but would rather keep it included, via a Calculated Field or such like. I have managed to contrive a Calculated field, but this returns Subtotal rows for the Running Balance for all my Subdivisions, eg Overheads, Expenses etc. I wish just the Grand Total(2) to show the Running Balance. Is there a way? As usual thank you for any input. Cheers Mathew |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Pivot Table Grand Total Plus Calc Field or GT Running Balance
Shane, cheers, got it to work. Magic. Have to pul from data firld - not from
PT- Cheers Mathew "Mathew P Bennett" wrote in message ... Sorry Shane, cannot get to work, do not get the Sum of Dollar2 field to show. Mathew "ShaneDevenshire" wrote in message ... This requires a number of steps (Assume that the name of the field in the column area is Months and the data field is Dollars: 1. Right-click in the Data area and choose Field Settings, Options, Running Totals In, and pick the Months field and click OK twice. This will replace the data area and totals with running totals. 2. Drag the Dollars field into the Data area a second time. You wil now have a Sum of Dollars2 field. 3. Click the lable (row area) for one of the Sum Of Dollars2, place your mouse along the edge of the selected cell and drag it up 1 row. This will put the regular sums on top row and the running totals on the row below that. 4. Hide the running totals in the data area but leave it displayed in the Totals area. Cheers, Shane Devenshire "Mathew P Bennett" wrote: Good Morning All, XL2007 I have a basic Pivot Table summing vales across a row of months. A B C D Category Month1 Month2 Month3 Overheads 10 10 10 Expenses 20 20 20 Grand Total 30 30 30 Now I wish to insert another Grand Total Row which will show the Running Balance ie A B C D Category Month1 Month2 Month3 Overheads 10 10 10 Expenses 20 20 20 Grand Total 30 30 30 Grand Total(2) 30 60 90 I know I could add a seperate row outside the Pivot Tabe to reflect the Running Total, but would rather keep it included, via a Calculated Field or such like. I have managed to contrive a Calculated field, but this returns Subtotal rows for the Running Balance for all my Subdivisions, eg Overheads, Expenses etc. I wish just the Grand Total(2) to show the Running Balance. Is there a way? As usual thank you for any input. Cheers Mathew |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Pivot Table Grand Total Plus Calc Field or GT Running Balance
Hi Mathew,
Am working, but glad I could help. FYI you should rate the answer so that others know your question was answered. -- Thanks, Shane Devenshire "Mathew P Bennett" wrote: Shane, cheers, got it to work. Magic. Have to pul from data firld - not from PT- Cheers Mathew "Mathew P Bennett" wrote in message ... Sorry Shane, cannot get to work, do not get the Sum of Dollar2 field to show. Mathew "ShaneDevenshire" wrote in message ... This requires a number of steps (Assume that the name of the field in the column area is Months and the data field is Dollars: 1. Right-click in the Data area and choose Field Settings, Options, Running Totals In, and pick the Months field and click OK twice. This will replace the data area and totals with running totals. 2. Drag the Dollars field into the Data area a second time. You wil now have a Sum of Dollars2 field. 3. Click the lable (row area) for one of the Sum Of Dollars2, place your mouse along the edge of the selected cell and drag it up 1 row. This will put the regular sums on top row and the running totals on the row below that. 4. Hide the running totals in the data area but leave it displayed in the Totals area. Cheers, Shane Devenshire "Mathew P Bennett" wrote: Good Morning All, XL2007 I have a basic Pivot Table summing vales across a row of months. A B C D Category Month1 Month2 Month3 Overheads 10 10 10 Expenses 20 20 20 Grand Total 30 30 30 Now I wish to insert another Grand Total Row which will show the Running Balance ie A B C D Category Month1 Month2 Month3 Overheads 10 10 10 Expenses 20 20 20 Grand Total 30 30 30 Grand Total(2) 30 60 90 I know I could add a seperate row outside the Pivot Tabe to reflect the Running Total, but would rather keep it included, via a Calculated Field or such like. I have managed to contrive a Calculated field, but this returns Subtotal rows for the Running Balance for all my Subdivisions, eg Overheads, Expenses etc. I wish just the Grand Total(2) to show the Running Balance. Is there a way? As usual thank you for any input. Cheers Mathew |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Pivot Table Grand Total Plus Calc Field or GT Running Balance
Hi again, nearly there but no banana, will persevere. I think I may need
some VBA, bu at least you have given me a lead about introducing the data feilds more than once. Cheers M "Mathew P Bennett" wrote in message ... Shane, cheers, got it to work. Magic. Have to pul from data firld - not from PT- Cheers Mathew "Mathew P Bennett" wrote in message ... Sorry Shane, cannot get to work, do not get the Sum of Dollar2 field to show. Mathew "ShaneDevenshire" wrote in message ... This requires a number of steps (Assume that the name of the field in the column area is Months and the data field is Dollars: 1. Right-click in the Data area and choose Field Settings, Options, Running Totals In, and pick the Months field and click OK twice. This will replace the data area and totals with running totals. 2. Drag the Dollars field into the Data area a second time. You wil now have a Sum of Dollars2 field. 3. Click the lable (row area) for one of the Sum Of Dollars2, place your mouse along the edge of the selected cell and drag it up 1 row. This will put the regular sums on top row and the running totals on the row below that. 4. Hide the running totals in the data area but leave it displayed in the Totals area. Cheers, Shane Devenshire "Mathew P Bennett" wrote: Good Morning All, XL2007 I have a basic Pivot Table summing vales across a row of months. A B C D Category Month1 Month2 Month3 Overheads 10 10 10 Expenses 20 20 20 Grand Total 30 30 30 Now I wish to insert another Grand Total Row which will show the Running Balance ie A B C D Category Month1 Month2 Month3 Overheads 10 10 10 Expenses 20 20 20 Grand Total 30 30 30 Grand Total(2) 30 60 90 I know I could add a seperate row outside the Pivot Tabe to reflect the Running Total, but would rather keep it included, via a Calculated Field or such like. I have managed to contrive a Calculated field, but this returns Subtotal rows for the Running Balance for all my Subdivisions, eg Overheads, Expenses etc. I wish just the Grand Total(2) to show the Running Balance. Is there a way? As usual thank you for any input. Cheers Mathew |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Duplicate Running Total Grand Total In Pivot Table | Excel Discussion (Misc queries) | |||
| Pivot Table Calculated Field using Grand Total | Excel Worksheet Functions | |||
| How do I create a running total (balance) in a pivot table? | Excel Worksheet Functions | |||
| Missing Grand Total for a Calculated Field in a Pivot Table | Excel Worksheet Functions | |||
| Pivot Table Grand Total - Calc Item | Excel Worksheet Functions | |||
Linear Mode
