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.xlsx75.9 KB Calculated Fields - Completed.xlsx
84.1 KB
Quick reference
Topic
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 a way that we can actually use custom
- 00:08 calculations inside a pivot table.
- 00:11 If you'll notice, I have a list of data here, and I have a couple of different
- 00:14 columns, a sales dollar beverage, and a sales dollar food.
- 00:17 And they're related to the chit numbers that are actually showing up on the left,
- 00:20 the transaction numbers.
- 00:22 Now, I've already made a pivot table that shows the sum of sales beverage and
- 00:26 the sum of food, but I can't sum the total amount of records in this particular
- 00:30 column, because it's not actually in the pivot table.
- 00:34 I can't just drag that on there.
- 00:36 So I'd like to go and
- 00:37 actually create a custom field that will actually allow me to do that.
- 00:40 And the way that I would do that is I'd go select in my pivot table,
- 00:44 go to pivot table tools, and in Excel 2013 it's called analyze,
- 00:49 in Excel 2010 this tab was called options.
- 00:52 And then we'll go to fields items sets, calculated field.
- 00:56 And what you'll see is you get a list of each of the fields that actually show in
- 01:00 my pivot table.
- 01:00 They're all showing here, which is perfect.
- 01:04 What I'd like to do now, is I'd like to create a new measure, or
- 01:07 a new calculated field rather, called Total Sales.
- 01:11 And then I can click in the Formula bar,
- 01:12 and this is really just a double click operation.
- 01:15 We're gonna take Sales Beverage plus Sales Food and then we can say Add.
- 01:21 And you'll notice that it shows up right away.
- 01:24 When I click OK, you'll notice that it actually automatically adds it to
- 01:28 the pivot table sum of total sales.
- 01:31 What's really cool about this is that if you take a look at these fields,
- 01:35 I can actually take sales beverage and
- 01:38 sales food off the table altogether and it still gives me the total sales.
- 01:42 I don't have to have those predecessor columns showing up inside my data.
- 01:47 Now.
- 01:49 What if I'd like to make some calculations, like the average cover, or
- 01:53 the average chit?
- 01:55 I can go back into fields, items, and sets, calculated field,
- 02:00 and I can create a new field called Avg Cover.
- 02:05 And the formula for Avg Cover is going to be Total Sales.
- 02:09 That is the calculator custom column that I created earlier, divided by Covers.
- 02:15 We'll say Add.
- 02:19 And while we're here, why don't we also make another one named Avg Chit,
- 02:22 which will be Total Sales divided by The POSChitNumber.
- 02:28 And add.
- 02:29 There we go.
- 02:32 We can now say OK.
- 02:33 And you can see that one of these worked and one of them didn't.
- 02:38 The average cover is calculated based on total sales divided by the sum of covers.
- 02:43 And that's working quite well and
- 02:44 we can see that it works all the way down the table.
- 02:46 But the average Chit is not working so well at all.
- 02:49 We can see that we have a Count of POSChitNumber.
- 02:52 If I go back to the table, what does count usually indicate?
- 02:55 It indicates that we have text.
- 02:57 So that's not gonna work because in order to be able to actually do any
- 03:01 mathematics it's gotta be based on numeric fields.
- 03:04 So what we'll do, is we'll go and
- 03:06 we'll add a ChitCount field to our table and we're gonna put in =1.
- 03:11 And that way it just sets a value of 1, but
- 03:14 when I hit enter it's a formula so it writes all the way down the table.
- 03:17 I can go back over to Sheet2, right-click, and refresh my data.
- 03:22 And you'll now see that I have ChitCount showing up in my columns.
- 03:26 I've also got a name error that's come up, which is far from inspiring.
- 03:30 So let's go and figure out what's going on with that.
- 03:32 So back into analyzer or options in Excel 2010.
- 03:35 Fields item sets.
- 03:36 We'll go to calculated field.
- 03:38 And let's go take a look at our average cover first, and
- 03:41 figure out what's going on there.
- 03:43 So, for some reason, when we added the new column, it's blown away the logic here and
- 03:48 I'm not 100% sure why that is, but it's certainly easy to fix.
- 03:51 We can just go back and say Total Sales, and
- 03:54 divide that again by the number of Covers, and modify.
- 03:59 And that will actually fix that for us.
- 04:02 We can also go back now and say Avg Chit.
- 04:06 And this time, we'll go in total sales.
- 04:08 Instead of using POSChitNumber, we'll use the ChitCount column that we added and
- 04:13 modify that as well.
- 04:15 And now when we say, okay, they should both calculate correctly.
- 04:20 And this is a beautiful thing for us, we can now actually start taking some of
- 04:23 the fields off the pivot table we don't need.
- 04:25 Maybe we don't need the count of POSChitNumber and
- 04:28 we don't need the sum of covers.
- 04:29 All we're interested in is the total sales, the average cover, and
- 04:33 the average chit.
- 04:34 We could, of course, update our headings appropriately.
- 04:38 What if I decided that I didn't want one of these particular measures?
- 04:42 Maybe I don't want Avg Chit at all after all that hard work.
- 04:45 I can remove it from the pivot table, that's easy.
- 04:48 I just uncheck it or drag it off the field.
- 04:50 But what if I wanted to make sure it was really gone, so
- 04:52 it didn't even show up in the field well anymore at all?
- 04:55 Well again, we go back to Fields Items Sets,
- 04:58 Calculated Field, and what we do is we find it in the list.
- 05:03 Average chit and we click Delete.
- 05:05 And at that point, when we say OK, you'll notice that it completely disappears from
- 05:10 the pivot table all together.
- 05:11 So there you go.
- 05:12 Now, you know how to create, modify, and
- 05:14 remove calculated fields to add a little bit more business logic to your
- 05:18 pivot tables rather than always modifying the underlying data set.
Lesson notes are only available for subscribers.