Learn Excel 2013 - "Subtract in a Pivot Table": Podcast #1655

If you like this content, please consider visiting the video on YouTube and subscribe to the MrExcel Channel to show your support!
This video has been published on Feb 27, 2013.
Michael has a question: "I have Actual and I have Budget Figures that are combined; what I would like to see Actual Minus Budget, right in the Pivot Table. How can I do this?" Follow along with Bill in Episode #1655 as he shows us how to set up our Table to show the results we'd like our Pivot Table to produce.

Pivot Table Data Crunching: Microsoft Excel 2013 - by Bill 'MrExcel' Jelen and Michael Alexander Excel 2013 Pivot Table Data Crunching (MrExcel Library): Jelen, Bill: 9780789748751: Amazon.com: Books

Crunch any data, from any source, quickly and easily, with Excel 2013 Pivot Tables! Use Excel 2013 Pivot Tables and Pivot Charts to produce powerful, dynamic reports in minutes instead of hours... understand exactly what's going on in your business... take control, and stay in control!
maxresdefault.jpg


Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast, episode 1655.
Subtract in a Pivot Table.
Hey, this is a timely question sent in by Michael. It's timely because for the past couple of days, I've been working on live lessons Pivot Table, Power Pivot, Power View and Geo flow.
A new product that queue is going to be putting out I don't know when it'll come out sometime later this year.
But I'm doing all the videos for it and so this great pivot table question comes in.
Michael says" hey I have data that has Actual and Budget and when I put that in a pivot table, First thing, they give me this completely ridiculous January total adding Actual and Budget.
I don't want that.
And what I'd like to see is a Actual minus Budget.
I'd like to actually do a subtraction right here in the Pivot Table" And I say it's a great question because I just covered this in the live lessons yesterday.
But hey, you're going to get it today.
So, first thing, Design Tab > Report layout > Tabular form.
It's not that I do this just out of spite because I hate Compact Form which I do.
I do it, so that way I actually have month and source as two different headings to make my life just a tiny bit easier.
Okay! So, choose that month heading.
Coming here to Options > Field Settings > Subtotals > Automatic change that to none.
Click Ok.
And we're halfway there.
Now that completely ridiculous Actual plus Budget is gone.
Now, we need to create our own subtraction.
So, you see I move down to either the Actual or Budget heading.
Doesn't matter which one.
And I'm going to try and make Excel smaller here.
On the Options Tab or if you're in Excel 2013, the Analyze Tab.
We're going to go to Calculation > Fields Items and Sets and create a new calculated item.
Calculated in that item.
Now, usually I say calculated items are really, really dangerous.
And we should never ever use them but in this case, this is actually a perfect use for them.
Thanks Michael for sending that question in So, we need to do Actual minus Budget.
I'm going to call it Delta.
I don't know Michael you can call it whatever you want.
And so equals zero.
You need a backspace through the zero.
One of those annoying things and double click on Actual.
minus double-click on Budget and click OK.
Alright, so there we go.
Actual, budget and Delta.
So, the Actual was 80, the Budget was 100 There 20 under.
Woohoo! That's good.
Budget was 200 and again, we're 20 under there and that should work all the way across.
So, February Actual and budget were the same.
We were $20 over there All right there you go.
Calculated item actually does the subtraction right here in Excel.
Wanna thank Micheal for sending that question in.
And I wanna thank you for stopping by.
We'll see you next time, for another netcast from MrExcel.
 

Forum statistics

Threads
1,213,489
Messages
6,113,949
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top