| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
PivotTable Grand Totals By Item
(Im hoping the table format is somewhat preserved in this post, so that the
question is readable...if not, I apologize!) If I have a table structured like this: Product Line Jan Feb Mar Apples Revenue 500 600 600 Apples Profit 50 60 60 Oranges Revenue 400 500 400 Oranges Profit 25 35 25 Pears Revenue 200 200 100 Pears Profit 40 40 20 I can create a Pivot Table like this: Product Line SumofJan SumofFeb SumofMar Apples Revenue 500 600 600 Profit 50 60 60 Apples Total 550 660 660 Oranges Revenue 400 500 400 Profit 25 35 25 Oranges Total 425 535 425 Pears Revenue 200 200 100 Profit 40 40 20 Pears Total 240 240 120 Grand Total 1215 1435 1205 But I would also like to have the pivot table include rows near the bottom for €śGrand Total Revenue€ť and €śGrand Total Profit€ť (rather than giving me the Grand Total of Revenue + Profit, which isn't meaningful). Is this possible? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
PivotTable Grand Totals By Item
Right click somewhere in the pivot table and select Table Options. In the Pivot Table Options dialog uncheck Grand totals for rows
and press OK. Now click Pivot Table on the Pivot Table toolbar and select Formulas and Calculated Field. In the Name field write for example Total. in the Formula field clear the 0, press Jan in theFields area and press Insert Field, enter a +, press Feb, press Insert Field, enter again a +, press Mar and press Insert Field and OK. Hans "TMore" skrev i en meddelelse ... (I'm hoping the table format is somewhat preserved in this post, so that the question is readable...if not, I apologize!) If I have a table structured like this: Product Line Jan Feb Mar Apples Revenue 500 600 600 Apples Profit 50 60 60 Oranges Revenue 400 500 400 Oranges Profit 25 35 25 Pears Revenue 200 200 100 Pears Profit 40 40 20 I can create a Pivot Table like this: Product Line SumofJan SumofFeb SumofMar Apples Revenue 500 600 600 Profit 50 60 60 Apples Total 550 660 660 Oranges Revenue 400 500 400 Profit 25 35 25 Oranges Total 425 535 425 Pears Revenue 200 200 100 Profit 40 40 20 Pears Total 240 240 120 Grand Total 1215 1435 1205 But I would also like to have the pivot table include rows near the bottom for "Grand Total Revenue" and "Grand Total Profit" (rather than giving me the Grand Total of Revenue + Profit, which isn't meaningful). Is this possible? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
PivotTable Grand Totals By Item
Thank you for the response.
When I follow those steps, I get a Total column for Jan/Feb/Mar at the far right of the table. What I'm looking for is a "Grand Total Revenue" and "Grand Total Profit" at the bottom....so the left side of the PivotTable would look something like: Product Line --------- ----- Apples Revenue Profit Oranges Revenue Profit Pears Revenue Profit Grand Total Revenue Grand Total Proft The bottom two lines are the crux of what I'm looking for. I can only seem to get one Grand Total line that adds both Revenue+Profit, which isn't meaningful. "Hans Knudsen" wrote: Right click somewhere in the pivot table and select Table Options. In the Pivot Table Options dialog uncheck Grand totals for rows and press OK. Now click Pivot Table on the Pivot Table toolbar and select Formulas and Calculated Field. In the Name field write for example Total. in the Formula field clear the 0, press Jan in theFields area and press Insert Field, enter a +, press Feb, press Insert Field, enter again a +, press Mar and press Insert Field and OK. Hans "TMore" skrev i en meddelelse ... (I'm hoping the table format is somewhat preserved in this post, so that the question is readable...if not, I apologize!) If I have a table structured like this: Product Line Jan Feb Mar Apples Revenue 500 600 600 Apples Profit 50 60 60 Oranges Revenue 400 500 400 Oranges Profit 25 35 25 Pears Revenue 200 200 100 Pears Profit 40 40 20 I can create a Pivot Table like this: Product Line SumofJan SumofFeb SumofMar Apples Revenue 500 600 600 Profit 50 60 60 Apples Total 550 660 660 Oranges Revenue 400 500 400 Profit 25 35 25 Oranges Total 425 535 425 Pears Revenue 200 200 100 Profit 40 40 20 Pears Total 240 240 120 Grand Total 1215 1435 1205 But I would also like to have the pivot table include rows near the bottom for "Grand Total Revenue" and "Grand Total Profit" (rather than giving me the Grand Total of Revenue + Profit, which isn't meaningful). Is this possible? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
PivotTable Grand Totals By Item
If you are interested (and you give me an e-mail address - can't use the one below) I can send you a workbook.
Hans "TMore" skrev i en meddelelse ... Thank you for the response. When I follow those steps, I get a Total column for Jan/Feb/Mar at the far right of the table. What I'm looking for is a "Grand Total Revenue" and "Grand Total Profit" at the bottom....so the left side of the PivotTable would look something like: Product Line --------- ----- Apples Revenue Profit Oranges Revenue Profit Pears Revenue Profit Grand Total Revenue Grand Total Proft The bottom two lines are the crux of what I'm looking for. I can only seem to get one Grand Total line that adds both Revenue+Profit, which isn't meaningful. "Hans Knudsen" wrote: Right click somewhere in the pivot table and select Table Options. In the Pivot Table Options dialog uncheck Grand totals for rows and press OK. Now click Pivot Table on the Pivot Table toolbar and select Formulas and Calculated Field. In the Name field write for example Total. in the Formula field clear the 0, press Jan in theFields area and press Insert Field, enter a +, press Feb, press Insert Field, enter again a +, press Mar and press Insert Field and OK. Hans "TMore" skrev i en meddelelse ... (I'm hoping the table format is somewhat preserved in this post, so that the question is readable...if not, I apologize!) If I have a table structured like this: Product Line Jan Feb Mar Apples Revenue 500 600 600 Apples Profit 50 60 60 Oranges Revenue 400 500 400 Oranges Profit 25 35 25 Pears Revenue 200 200 100 Pears Profit 40 40 20 I can create a Pivot Table like this: Product Line SumofJan SumofFeb SumofMar Apples Revenue 500 600 600 Profit 50 60 60 Apples Total 550 660 660 Oranges Revenue 400 500 400 Profit 25 35 25 Oranges Total 425 535 425 Pears Revenue 200 200 100 Profit 40 40 20 Pears Total 240 240 120 Grand Total 1215 1435 1205 But I would also like to have the pivot table include rows near the bottom for "Grand Total Revenue" and "Grand Total Profit" (rather than giving me the Grand Total of Revenue + Profit, which isn't meaningful). Is this possible? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
PivotTable Grand Totals By Item
Can you send me a workbokk with the solution to problem.
TIA Oscar Hans Knudsen ha escrito: If you are interested (and you give me an e-mail address - can't use the one below) I can send you a workbook. Hans "TMore" skrev i en meddelelse ... Thank you for the response. When I follow those steps, I get a Total column for Jan/Feb/Mar at the far right of the table. What I'm looking for is a "Grand Total Revenue" and "Grand Total Profit" at the bottom....so the left side of the PivotTable would look something like: Product Line --------- ----- Apples Revenue Profit Oranges Revenue Profit Pears Revenue Profit Grand Total Revenue Grand Total Proft The bottom two lines are the crux of what I'm looking for. I can only seem to get one Grand Total line that adds both Revenue+Profit, which isn't meaningful. "Hans Knudsen" wrote: Right click somewhere in the pivot table and select Table Options. In the Pivot Table Options dialog uncheck Grand totals for rows and press OK. Now click Pivot Table on the Pivot Table toolbar and select Formulas and Calculated Field. In the Name field write for example Total. in the Formula field clear the 0, press Jan in theFields area and press Insert Field, enter a +, press Feb, press Insert Field, enter again a +, press Mar and press Insert Field and OK. Hans "TMore" skrev i en meddelelse ... (I'm hoping the table format is somewhat preserved in this post, so that the question is readable...if not, I apologize!) If I have a table structured like this: Product Line Jan Feb Mar Apples Revenue 500 600 600 Apples Profit 50 60 60 Oranges Revenue 400 500 400 Oranges Profit 25 35 25 Pears Revenue 200 200 100 Pears Profit 40 40 20 I can create a Pivot Table like this: Product Line SumofJan SumofFeb SumofMar Apples Revenue 500 600 600 Profit 50 60 60 Apples Total 550 660 660 Oranges Revenue 400 500 400 Profit 25 35 25 Oranges Total 425 535 425 Pears Revenue 200 200 100 Profit 40 40 20 Pears Total 240 240 120 Grand Total 1215 1435 1205 But I would also like to have the pivot table include rows near the bottom for "Grand Total Revenue" and "Grand Total Profit" (rather than giving me the Grand Total of Revenue + Profit, which isn't meaningful). Is this possible? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Dynamic parameterized PivotTable update? | Excel Discussion (Misc queries) | |||
| PivotTable Calculated Item error | Excel Discussion (Misc queries) | |||
| Need to sum up numerous columns in different worksheet into 1 | New Users to Excel | |||
| Linking Several Worksheets to One Worksheet | Excel Discussion (Misc queries) | |||
| PivotTable toolbar, Formulas, Calculated Item | Excel Discussion (Misc queries) | |||
Hybrid Mode
