Locked lesson.
About this lesson
Learn to create and modify basic calculated fields for PivotTables.
Lesson versions
Multiple versions of this lesson are available, choose the appropriate version for you:
Exercise files
Download the ‘before’ and ‘after’ Excel workbooks from the video tutorial and try the lesson yourself.
Calculated Fields in PivotTables.xlsx37.4 KB Calculated Fields in PivotTables - Completed.xlsx
37.9 KB
Quick reference
Topic
Creating PivotTable calculated fields.
Description
Creating and modifying basic calculated fields for PivotTables in Excel.
Where/when to use the technique
Calculated fields are used to build mathematical and logical formulas in a PivotTable, rather than relying on adding those calculations to the data source. This is useful, as there are occasions where we cannot modify our source data but need to add extra calculations to our PivotTables.
Instructions
Setting the stage
- Create a PivotTable
Creating Calculated Fields
- Select a cell in the PivotTable
- Go to PivotTable Tools --> Analyze --> Fields, Items & Sets --> Calculated Field
- In the Name field, replace Field1 with the name you’d like to use for your field
- Highlight the 0 in the formula field, scroll down the Fields list
- Build your formula by selecting fields and performing the appropriate math
- Click Add, then OK, and notice that the new field is on the PivotTable
Modifying Calculated Fields
- Go back to Fields, Items & Sets --> Calculated Field
- Choose the name of your field from the drop down list at the top
- Change the formula as desired
- Click Modify, then OK
- The PivotTable has updated to show your revised formula.
- 00:04 In this video, we're gonna continue our journey with PivotTables.
- 00:08 And we're gonna explore what we should do when we want to actually add a calculation
- 00:12 between columns, but for whatever reason,
- 00:15 we're not able to modify the original source data.
- 00:18 We're not able to add a column to it.
- 00:21 So, in this particular case, you can see that we have a Cost, Price, and
- 00:24 Commission.
- 00:25 But let's assume right now that we're not allowed to modify this worksheet in any
- 00:28 way or add any more data to it.
- 00:30 And we'd like to work out what the profit is, or the profit percentage is for
- 00:34 these particular items.
- 00:36 We have a PivotTable that's already created.
- 00:38 It has Price, that's showing on it right now.
- 00:40 And what I'd like to do is I'd like to actually build something that shows me
- 00:43 the profit.
- 00:44 So how can I do that?
- 00:45 Well, what we're gonna do is we're gonna go to PivotTable Tools > Analyze.
- 00:49 And we're gonna go to Fields, Items, & Sets, and
- 00:52 we're gonna make a Calculated Field.
- 00:56 This is the calculated field dialogue, and what it asks us for first is a name.
- 01:00 So we're going to go and give a name of Profit.
- 01:03 When we hit Tab, it comes down, and says, what's the formula?
- 01:06 = 0, so I'm gonna grab my 0 here.
- 01:08 And I'm just gonna delete it, cuz I do want the =.
- 01:11 What I'm going to say is Profit is worth Price, so
- 01:15 I'm gonna double-click on that,- Cost.
- 01:19 -, we'll double-click on Commission.
- 01:22 The reason I double-click these files is if I ever use one,
- 01:25 like Inventory Item, you'll notice that it has some syntax,
- 01:28 where it actually has put quotes around it.
- 01:29 So, rather than type them, I just go and double-click them.
- 01:33 Once we're done with Price- Cost- Commission,
- 01:36 what we can do is we can click Add.
- 01:39 And you'll notice that this adds this to the list of fields right here.
- 01:44 When I click OK, it immediately adds it to the PivotTable.
- 01:48 So it adds it automatically, and it calls it Sum of Profit.
- 01:51 Now of course, I could go back and say, I really don't need that.
- 01:55 So we'll put in Profit space, there we go.
- 01:58 The important thing that I want you to recognize here is that
- 02:00 this actually is the Price, minus the Cost, minus the Commission.
- 02:04 Do you see those fields on the PivotTable?
- 02:05 No, you don't, you don't need them, which is really kind of cool.
- 02:09 So let's go back, and let's add another one.
- 02:10 We'll go back into Fields, Items, & Sets, and we'll go to Calculated Field.
- 02:14 And this time here, we're gonna call this one Profit %.
- 02:20 And for this one, what we'll do is we'll go with Price/ Profit.
- 02:28 And we'll say Add, and we'll drop it onto our PivotTable.
- 02:32 And then we look it and we go, wait a minute, that's not right.
- 02:35 I got that calculation backwards, that's no good at all.
- 02:38 So how do I modify it?
- 02:40 We actually end up going back into Fields, Items, & Sets, back into Calculated Field.
- 02:45 This time, we're going to select our fields from this drop-down list.
- 02:49 And we'll say, okay, it's Profit.
- 02:50 So Profit %, so we'll grab that.
- 02:52 So I didn't mean to go this way around.
- 02:54 What I meant to do was say Profit/ Price.
- 03:02 Now I click Modify.
- 03:04 You'll also notice, if you wanted to just remove your measure altogether,
- 03:07 you can click Delete right here.
- 03:10 Now we'll say OK, and it will modify this to 0.52.
- 03:13 Which of course, I can go to Value Field Settings > Number Format.
- 03:19 We can format this as a percentage.
- 03:21 Maybe we only want 1 decimal place on here.
- 03:24 Say OK, and we'll call this one Profit % with a space after it,
- 03:30 in order to make it show up properly here.
- 03:33 So there we go, we've got our calculated fields on the PivotTable
- 03:37 without even touching our source data.
- 03:39 And this is kind of important, because when we go back and
- 03:41 we look, can we say, yeah, those fields, they don't exist here.
- 03:45 But they certainly do inside our PivotTable.
- 03:49 Sometimes, when you're building calculations,
- 03:51 you're gonna wanna go back and actually build it against the original data set.
- 03:55 Other times, the calculated field will work for you very, very well.
- 03:58 So one thing I would say, though, is always,
- 04:00 when you're building a calculated field,
- 04:02 check the calculation when it lands on the table and make sure that it looks correct.
- 04:06 Because sometimes,
- 04:07 you'll be able to build them, where they look completely out to lunch.
- 04:11 And in those cases, it's better to go back to the source table and
- 04:13 do your calculations there.
Lesson notes are only available for subscribers.