GoSkills
Help Sign up Share
Back to course

Calculated Fields

Compact player layout Large player layout

Locked lesson.

Upgrade

  • Lesson resourcesResources
  • Quick referenceReference
  • Transcript
  • Notes

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.xlsx
83.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
Login to download
  • 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.

Top and Bottom x Items
05m:17s
Calculated Items
05m:51s
Share this lesson and earn rewards

Facebook Twitter LinkedIn WhatsApp Email

Gift this course
Give feedback

How is your GoSkills experience?

I need help

Your feedback has been sent

Thank you

Back to the top

© 2023 GoSkills Ltd. Skills for career advancement