Locked lesson.
About this lesson
Understanding how to create calculated fields in a PivotTable.
Lesson versions
Multiple versions of this lesson are available, choose the appropriate version for you:
2016, 2019/365.
Exercise files
Download the ‘before’ and ‘after’ Excel workbooks from the video tutorial and try the lesson yourself.
Calculated Fields.xlsx83.3 KB Calculated Fields - Completed.xlsx
91.6 KB
Quick reference
Calculated Fields
Understanding how to create calculated fields in a PivotTable.
Where/when to use the technique
Creating simple calculations between fields in a PivotTable when the output doesn’t reside in your Pivot Source.
Instructions
Creating a calculated field
- Excel 2010: PivotTable Tools > Options > Fields, Items, & Sets > Calculated Field
- Excel 2013: PivotTable Tools > Analyze > Fields, Items, & Sets > Calculated Field
- Type a name for your new field
- Construct the formula by double-clicking the fields from the list to use them in calculations
Modifying a calculated field
- Return to the dialog to create a new calculated field
- Select your existing field from the Name drop-down
- Make your modifications
- Click Add
Removing a calculated field
- Return to the dialog to create a new calculated field
- Select your existing field from the Name drop-down
- Click Delete
Key points to remember
- Underlying field data must be based on numbers in order to perform math on them
- Calculated fields can refer to other calculated fields
- 00:04 In this video we're going to look at how to perform custom calculations inside
- 00:08 a pivot table.
- 00:09 And this is super useful because sometimes you can't manipulate the source data and
- 00:13 you don't have the columns you need.
- 00:15 Other times of course we can.
- 00:16 Before we jump into that, I just want to talk a little bit about the vernacular of
- 00:19 the data set that we're actually using here.
- 00:22 This is a food and beverage data set.
- 00:23 And when we talk about things like chits, what that is is the piece of paper
- 00:27 your server hands you when you actually want to be paying for your meal.
- 00:32 So you could have multiple chits per a table if somebody wanted to separate their
- 00:36 food and alcohol under two separate bills, that would be two chits.
- 00:40 We also have this concept here of covers.
- 00:42 And that's the number of humans that we have sitting at a table.
- 00:45 So one table could have one chit with four covers if there was four people there and
- 00:50 they asked to pay the bill all on one because one person was picking up the tab.
- 00:54 Likewise, there could be four covers and eight chits if everybody said I want my
- 00:58 bill separate and can you split my food and alcohol on a two different bills.
- 01:02 In this particular setup of data,
- 01:03 we also have a units column specific to beverage and food.
- 01:06 And we have sales dollar columns specific to beverage and food as well.
- 01:10 This is kind of interesting because now we can see with our individual chit here
- 01:14 that there was three beverages that were consumed with two plates of food.
- 01:19 Now, let's go and take a look at how this sums up in the pivot table.
- 01:23 So we have our dates, we have a count of how many chits were actually put out,
- 01:26 how many covers were actually there, and the sum of beverage and food.
- 01:30 The problem is, I want to add this together to get the total sale.
- 01:33 And unfortunately in my data set, I don't actually have it.
- 01:36 So what I'm going to do is I'm going to go and
- 01:38 create myself a custom calculation to do this.
- 01:41 And to do that we go to pivot table tools analyze and we'll look for
- 01:45 the button fields items and sets.
- 01:47 And it may be bigger on your screen than mine because my screen is a little bit
- 01:50 compressed right now.
- 01:51 We're going to go and use a calculated field.
- 01:54 Now in the calculated field it brings up the listing of all the different fields
- 01:57 that we have here.
- 01:58 And what I'd like to do is I'd like to make a nice little field here for
- 02:01 total sales dollars.
- 02:04 This is going to be equal to a custom calculation which is going to be my
- 02:07 sales beverage plus my sales of food.
- 02:12 Once I've created it, I'm now going to say Add.
- 02:15 And just like that, it pops into the list down here.
- 02:18 It also will show up in the field list on the right-hand side.
- 02:21 So I'm going to say okay, and just like that, you'll see that it adds it to
- 02:24 the pivot table, and it's adding these two things together, which is fantastic.
- 02:29 What's even better about this, I don't even need to keep my beverage and
- 02:32 food list sitting around here at all.
- 02:34 I can take sum of sales beverage, pull this right off the pivot table.
- 02:38 And my sum of sales food and
- 02:40 even though there's a calculations driving across two individual precedent columns,
- 02:44 they don't need to be in scope to be landed on the pivot table.
- 02:47 So that's pretty darn cool.
- 02:49 Now what if I wanted to do some other things.
- 02:52 What if I wanted to figure out what my average chit was.
- 02:55 How much was actually sold on the average piece of paper that gets handed out, and
- 02:58 what was my average number of covers.
- 03:01 So here's what we're going to do.
- 03:02 We're going to go back up to Fields Item Sets > Calculate field, and
- 03:06 let's figure out if we can make one for Average Chit.
- 03:12 Well, the average chit is going to be based on my total sales dollars.
- 03:16 So the field that I just created previously divided by, and
- 03:20 obviously we've got the counterpoint of ales chits here, so
- 03:22 we'll go with the count of the POS Chit Number.
- 03:26 So there we go, that should give me my average chit.
- 03:27 We'll say OK.
- 03:29 And now I'm going to figure out what is my average cover.
- 03:31 To do that I'm going to make a new column.
- 03:34 We can already verify that my new one just went in here, so that's good.
- 03:37 So now I'm going to overwrite what's showing up here with average cover.
- 03:41 And this one will be total sales divided by covers.
- 03:47 And we can see right here we've got our sum of covers, so
- 03:49 that's what we're looking for.
- 03:50 We'll say Add, and we'll go to say OK.
- 03:55 And what you'll see now is that we end up with one that works and one that doesn't.
- 04:00 We say, well, what's going on?
- 04:02 Well, the sum of covers has information in it that works quite nicely by taking
- 04:07 our total sales and dividing it by whatever's there.
- 04:10 Great, why then doesn't this one?
- 04:13 Because we've got a number over here as well.
- 04:15 Except that when we go back over to this side, we can see that Covers was
- 04:20 a numeric field, and the POSChitNumber is actually a text-based field.
- 04:25 So that's not going to work for us because we can't divide a number by text.
- 04:29 So here's the work around for dealing with this.
- 04:32 I'm going to go and just mark down a nice little field here called Chit, and
- 04:36 I'm going to say equals one.
- 04:38 This formula will now run all the way down the pivot table.
- 04:42 I'm now going to come back over here, right click, and
- 04:45 I'm going to do a little refresh on this to get my new chit field in place.
- 04:50 And I wish I didn't have to do this to be honest with you.
- 04:52 It'll be nice to be able to actually deal with this a little easier.
- 04:55 But we're going to go back into calculated field here.
- 04:58 We now have chit which has a value of one which will be summed up.
- 05:02 What that means is that I can come down and
- 05:04 I can modify my average chit right here.
- 05:07 And instead I'm going to say let's go and find him.
- 05:10 There's average chit.
- 05:12 And instead of dividing it by a POSChipNumber which is actually text,
- 05:17 we'll divide this by the chit column.
- 05:20 We'll click Modify and we click OK.
- 05:22 And at that point, we come back to what we actually need.
- 05:26 So we have our average chit, how much is actually pushed out on each bill?
- 05:29 And what is the average cover?
- 05:31 What's the average human paying?
- 05:33 Now what if I decide I didn't want this in my pivot table at all?
- 05:36 Maybe I say hey, I don't have it, forget it.
- 05:38 I want to get rid of it.
- 05:39 Well, obviously I can come back and say let's just pull it off the pivot table.
- 05:43 But the thing is it still lives field list.
- 05:46 And if I don't want to surface that someone, I might want to get rid of it.
- 05:50 How?
- 05:50 Well, we go back to calculate fields.
- 05:54 We find the average chit right here.
- 05:58 We'll pull it out from our list, it's the proper place to go.
- 06:00 And then we'll say Delete, and we'll say OK.
- 06:04 And now you'll see it disappears from my field list on the right hand side as well,
- 06:08 and it's not listed in my table anymore.
- 06:11 So calculated fields are super useful if you can't manipulate your data set if you
- 06:15 want to do calculations on the fly.
- 06:17 Oftentimes though, I'd prefer to go back to the original data set and
- 06:19 make things easier by putting the source data in there.
- 06:22 Because sometimes it's easier to just build things that way.
Lesson notes are only available for subscribers.