Calculated Fields in PivotTables

Quick reference

• 00:04 In this video, we're going to look at calculated fields.
• 00:07 And what a calculated field is, is it's a way to do certain mathematical operations
• 00:13 against fields inside the pivot table.
• 00:16 Now sometimes and often it's easiest to actually come back to your
• 00:21 original source table and just add new columns with some basic calculations.
• 00:25 So if you wanted profit here, you could take price minus cost minus commission,
• 00:30 and run a form all the way down.
• 00:31 That would then get pulled directly into your pivot table when you refreshed it and
• 00:35 everything will be good.
• 00:37 But there are occasions, especially when you're pulling from a database,
• 00:39 where you can't manipulate the original source data.
• 00:42 And that's where a pivot table can actually be really, really helpful.
• 00:46 So what we're gonna do is we're gonna set up a calculated field here,
• 00:49 and the way that we do it is we click somewhere inside the PivotTable.
• 00:52 It doesn't matter.
• 00:54 We're gonna go to Fields, Items and Sets on the PivotTable Analyze tab.
• 00:58 And in here, there is the option to create a calculated field.
• 01:02 The first thing you are going to want to do is give your calculated field
• 01:05 a name that is a little bit more descriptive than field 1.
• 01:07 So I am going to call this one Profit.
• 01:10 Then what I am gonna do is I am gonna come down to where it says equals zero, and
• 01:13 what I am going to do is I am gonna select the price minus the cost
• 01:20 minus the commission, and when I am done with that I am gonna click Add.
• 01:25 And that will add a new field to my list of fields here.
• 01:30 And when I say okay, you'll notice that it adds it immediately to my pivot table.
• 01:35 And if I call it my field list, you'll see that Profit is now a new field in
• 01:41 this list, and it shows inside the pivot table, so that's kind of a nice thing.
• 01:45 But what if I wanted to get a profit percentage of percentage of revenue?
• 01:50 Well, why not?
• 01:50 Let's go back in here and we'll say calculated field and
• 01:54 this one here we'll call Profit Percent.
• 01:58 And what I'm gonna do is I'm going to take Revenue or, sorry, my price,
• 02:05 divide it by profit, and I'm gonna say add and we'll say okay.
• 02:13 When I look at that this is when I realize oh, I made a horrible mistake,
• 02:16 I actually should've divided the profit by the price in order to get the profit
• 02:21 percentage, not the other way around, so I need to fix this.
• 02:24 So how do I fix a calculated field?
• 02:27 Well, we go back into the same area with calculated field.
• 02:30 Only this time, instead of creating a new formula here, what we're gonna do
• 02:34 is we're gonna actually go back and pick our new calculated field off the list.
• 02:38 There's profit percentage, we'll grab it we'll say, okay,
• 02:42 it should have been price minus profit.
• 02:44 What we're gonna do is we're gonna go and we'll delete this and we'll come
• 02:49 back over here and we'll say that this was supposed to be Profit divided by price.
• 02:54 Now, you'll notice that we don't have an add button anymore,
• 02:57 we have a Modify button.
• 02:58 We also have a Delete button.
• 02:59 So if you wanted to get rid of your calculated fields, you just highlight them
• 03:02 from this little drop-down list and click Delete, and they would go away.
• 03:06 They'd be pulled off your pivot table as well.
• 03:07 In this case, I'm gonna click Modify.
• 03:10 We'll say Okay.
• 03:11 And there we go.
• 03:13 That looks a lot better.
• 03:14 So at this point in time, I could right click, I could go into my field settings,
• 03:19 and I could go to number, and change this to a percentage with.
• 03:24 Well, let's say one decimal place is probably better.
• 03:27 We'll say OK.
• 03:30 And OK. And that looks way better.
• 03:33 The only thing that I may want to do differently at this point in time is do
• 03:36 a little bit of reformatting with my column headers.
• 03:38 If I wanted to go back and rename this one to something different, so
• 03:42 we'll put a space in front of it, because that can't conflict with the field names.
• 03:46 And same with this one here, we'll put a space in front of it,
• 03:48 but what's nice is that I've now managed to calculate these things
• 03:52 without ever actually modifying the underlying table, and
• 03:56 every time I refresh the pivot table, this data will refresh.
• 04:00 If I were to go, and you know change this kind of stuff out, and say you know what,
• 04:05 I really don't care about the sales person,
• 04:06 I could take the sales person right off the table.
• 04:09 And these things still work, the commission and the profit, or for
• 04:13 that matter, I could go the other way around too and say you know what lets
• 04:16 bring sold by onto to my pivot table and take inventory item off.
• 04:20 It still works perfectly fine.
• 04:22 So that is the really cool thing about calculated fields when you are working
• 04:26 with them is that they will work nicely there.
• 04:29 The other thing that you should probably know as well is that I don't actually have
• 04:33 to have my commission or my cost on here in order to have this work.
• 04:39 I can see the price and the profit without the precedent columns
• 04:42 that fed that formula as well, so that's kind of a cool thing about these too.
• 04:47 That's calculated fields and how they can add some value to your pivot table too.

Lesson notes are only available for subscribers.