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.xlsx46.4 KB Calculated Fields in PivotTables - Completed.xlsx
46.7 KB
Quick reference
Calculated Fields in Pivot Tables
Creating and modifying basic calculated fields for Pivot Tables in Excel.
When to use
Calculated fields are used to build mathematical and logical formulas in a Pivot Table, 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 Pivot Tables.
Instructions
Setting the stage
- Create a Pivot Table
Creating Calculated Fields
- Select a cell in the Pivot Table
- Go to Pivot Table 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 Pivot Table
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 Pivot Table has updated to show your revised formula.
- 00:04 In continuing your journey with pivot tables, we need to recognize
- 00:08 that pivot tables actually aren't just able to be created against Excel tables.
- 00:12 You can actually create a pivot table directly against a database.
- 00:16 And while I'm not gonna show you in that course,
- 00:18 that does bring up a potential problem.
- 00:20 And that is, if you've connected correctly to a database,
- 00:22 you don't have the ability to add any new columns to your source data because
- 00:27 you're pulling directly from another source.
- 00:29 It's not like pulling it into Excel, where you can write a formula.
- 00:33 So in this case, you'll notice we have a price, cost, and commission but
- 00:36 we don't have a profit field.
- 00:38 So what if I wanted to create one?
- 00:40 Well, if I'm working from an Excel table it's easy.
- 00:42 I write my formula down the side, refresh my pivot table, I can pull it in.
- 00:46 But if I'm connected to another data source, the good news is,
- 00:49 if we actually select our pivot table and go to Pivot Table Tools Analyze,
- 00:54 under Fields, Items, & Sets we have the ability to create calculated fields.
- 00:59 Now, I might wanna create a calculated field, for example, for profit and
- 01:04 that field is going to be, Price -, and
- 01:08 you'll notice I'm double clicking these fields Cost- Commission, why double-click?
- 01:15 Well, because if you get something like inventory item,
- 01:18 is gotta actually do something like wrap it in quotes, so
- 01:20 if you double-click, you will always have the syntax correct.
- 01:23 Once we've done this, it's very important to click Add so
- 01:27 that it actually gets added into the field's list.
- 01:29 We'll click Add, and there it is, there's my profit, and that's good.
- 01:32 If I just clicked OK, that wouldn't necessarily happen.
- 01:35 And now we'll click OK,
- 01:35 and you'll notice that automatically adds to the pivot table.
- 01:40 And here's the really cool thing, price doesn't even need to be here.
- 01:43 Cost certainly isn't here, commission certainly isn't here, and yet
- 01:46 our profit actually shows up.
- 01:48 Now, having a name of sum of profit, that's a little silly.
- 01:51 So let's go in and rename it to profit space in order to get it show correctly.
- 01:56 Now, what if I like to add another field?
- 01:59 Well, I can do that.
- 02:00 Let's go back to Fields Item Sets in Calculated Field.
- 02:03 And what I'd like to do this time is I'll like to make a field for the Profit %,
- 02:09 and for this, I'm gonna take Price and
- 02:13 I'm gonna divide it by the Profit measure that I created earlier, there we are.
- 02:19 So I can actually create a field in here that then feeds something else,
- 02:22 which is pretty neat.
- 02:24 We'll say Add, and we'll say OK.
- 02:28 I think I made that calculation backwards,
- 02:32 there's no way I should be having 193% in my profit.
- 02:37 I'm gonna have to fix this now.
- 02:39 So how do we actually fix this?
- 02:41 Well, we'll go back to Fields, Items and Sets, Calculated Field.
- 02:46 And inside here, now, I really want to actually fix this guy, but
- 02:49 when I click on him, nothing really happens.
- 02:52 So I need to remember that I can actually pull it here from the list, and
- 02:55 here's all of the custom fields that we made.
- 02:57 So here's profit percentage and when I pull that up,
- 03:00 it brings up, the formula that I wrote.
- 03:02 Notice that I have the ability to delete this field right here as well,
- 03:05 if I need to, if I want to clean it up and get rid of it.
- 03:08 But what I'm looking for right now is I'm saying, you know what?
- 03:10 No, this should actually be Profit divided by Price, there we go.
- 03:17 And now we'll click Modify to lock that in, and OK.
- 03:21 And just like that, we're at 52%.
- 03:24 Now of course this isn't great because the name is not good.
- 03:29 So we'll go and we'll make this Profit % space.
- 03:32 And now we can go and change the number format to go and reflect as
- 03:37 a percentage put one decimal place on it and we'll say OK, so there you have it.
- 03:44 The cool thing here is that while I would usually encourage you if you have access
- 03:48 to the Excel table to write your formulas there and then pull them in so
- 03:52 you're not using calculate fields.
- 03:55 If you are connecting to an external data source, you can,
- 03:58 because you cannot manipulate that source data.
- 04:00 You should, however, always check these, because sometimes when you write
- 04:03 a formula here, you get wonky results and you wanna make sure that they're right.
- 04:07 Ideally, do it at the source, but if you can't,
- 04:09 calculated fields are available for you.
Lesson notes are only available for subscribers.