Add Calculated Column to Excel Pivot table 1

[sskgraham]

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.

 

[SkipVought]

hi,

Please post a representative sample of your base table. Your table structure is not clear.

Then post what result you expect from that table in your pivot.

 

[sskgraham]

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.

 

[SkipVought]

In this case I'd make my own Pivot Table since the only way I'd know to accomplish your requirement on the sheet, is via formulas.

Second, using Strings for your implied dates is a bad idea. You cannot sort this as expected (ie in the sequence implied by date). It would be much better to enter a full date and DISPLAY the month abbreviation. I'd use the first of the month.

Third, SELECT all your base table, use FORMULAS > Defined Names > Create from Selection > Create names from values in the TOP ROW.

That being said, on a new sheet make headings, Month, Male, Female, Difference in row 1 starting in A1, and list your dates on 12 lines under the Month heading, using the same date format you used in the base table.

Use this formula in B2 and copy to thru the 12 rows under Male & Female.
[tt]
=SUMPRODUCT((Month=$A2)*(Gender=B$1)*(Cost))
[/tt]
The difference is obvious.

In the PT there would be no way to specify Male & Female. You could do the difference in an external column, but that may not work well.

My results (note I added some data rows to your data)
[pre]
Month Male Female Difference
Jan 500 200 300
Feb 300 200 100
Mar 100 300 -200
Apr 0 100 -100
May 100 0 100
Jun 0 0 0
Jul 0 0 0
Aug 0 0 0
Sep 0 0 0
Oct 0 0 0
Nov 0 0 0
Dec 0 0 0
[/pre]
And one final thing. You have never responded to any solution posted in your threads. If you've read other threads here at Tek-Tips, you will notice that this is the custom as it help other members know how well the solution works.

 

[combo]

Create basic pivot table (Month as row field, Gender as column field, Cost as data). Select Gender. Add calculated item to the table, in your case with formula [tt]=Male-Female[/tt]. See (calculated items part)

https://support.office.com/en-us/ar...e20-4a39-acea-8128b5e904b8#bmcreate_a_formula

combo

 

[SkipVought]

@combo, in my 20 years of using Excel and Pivot Tables, I have never used Calculated Items. Nice and Easy in this case.
[pre]
Sum of Cost Gender
Month Male Female Difference
Jan 500 200 300
Feb 300 200 100
Mar 100 300 -200
May 100 100
Apr 100 -100

[/pre]

 

[combo]

@SkipVought, nice to hear this from t-t MVP.

combo

 

[SkipVought]

@combo, you're right up there too! I really respect the quality os your responses!