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!
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!
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.
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.
