vsuripeddi
asked on
Add a calculated field in Excel Pivot table
I am using Excel 2010. I have a base table which has month, gender and cost columns. Month is like Jan, Feb, Mar... Dec. Gender is Male or Female.
I need to create a pivot table, which can get the sum of cost by Males and Females for each month. Then again, the Female cost should be subtracted from the Male sum each month.
The cost should be in values field, Gender should be in Column Labels and Month is Row label.
How can I do that. Please suggest a method.
I need to create a pivot table, which can get the sum of cost by Males and Females for each month. Then again, the Female cost should be subtracted from the Male sum each month.
The cost should be in values field, Gender should be in Column Labels and Month is Row label.
How can I do that. Please suggest a method.
Upload a sample workbook with enough data and mock the desired output manually to show what output you are expecting.
ASKER
Hi
Please find the excel file pivot.xlsx attached.
It has a raw data by month and also the final pivot format that I am expected. I had highlighted the
expected pivot values in yellow.
Please provide solution.
Please find the excel file pivot.xlsx attached.
It has a raw data by month and also the final pivot format that I am expected. I had highlighted the
expected pivot values in yellow.
Please provide solution.
I think you forgot to attach the file.
ASKER
ASKER
sktneer,
I had attached for my file. I apologize for the mistake earlier .
I had attached for my file. I apologize for the mistake earlier .
See the attached if this is what you are trying to achieve.
pivot.xlsx
pivot.xlsx
Please find the workbook with the steps involved to insert the Calculated Item Field in the Pivot Table.
Steps-to-Insert-Calculated-Item-in-Pivot
Steps-to-Insert-Calculated-Item-in-Pivot
ASKER
Sktneer
Thanks for the post. Unfortunately, my winzip is not working properly. Would you please send me the actual Excel file. Sorry for the trouble.
Thanks for the post. Unfortunately, my winzip is not working properly. Would you please send me the actual Excel file. Sorry for the trouble.
I am confused. I sent you the excel file not the zipped file so you just need to download the file and open it. That's it.
What excel version you are using?
Did you try downloading the second file I uploaded with the steps you will need to follow to achieve the task?
What excel version you are using?
Did you try downloading the second file I uploaded with the steps you will need to follow to achieve the task?
Oh I see. I think there was an error while uploading the file.
I am trying to attach it again, hope it will be ok this time.
Steps-to-Insert-Calculated-Item-in-Pivot
I am trying to attach it again, hope it will be ok this time.
Steps-to-Insert-Calculated-Item-in-Pivot
No. Something wrong.
I will try again. lol
I will try again. lol
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Do you get the pivot.xlsx this time?
ASKER
sktneer
Thanks a bunch for your post.
I did get ur file this time and am trying to follow ur instructions.
As per your suggestion, I am supposed to go into Pivot table Options and then to Fields, Items and Sets and within that click on the Calculated Item.
I tried to follow that instruction, but the "Calculated Item" option is disabled. Only "Calculated Field" option is enabled. Any suggestions?
Thanks a bunch for your post.
I did get ur file this time and am trying to follow ur instructions.
As per your suggestion, I am supposed to go into Pivot table Options and then to Fields, Items and Sets and within that click on the Calculated Item.
I tried to follow that instruction, but the "Calculated Item" option is disabled. Only "Calculated Field" option is enabled. Any suggestions?
That means you didn't read all the steps carefully.
See step 14 where I also mentioned the cell address where you need to click. The Calculated Items will be enabled if you click on either Row headers or Column Headers in the pivot table.
If you click in the values area, you won't see Calculated Items enabled.
See step 14 where I also mentioned the cell address where you need to click. The Calculated Items will be enabled if you click on either Row headers or Column Headers in the pivot table.
If you click in the values area, you won't see Calculated Items enabled.
Did it work for you?
ASKER
Yes.. Thanks
ASKER