Calculated Fields in PivotTables

Locked lesson.

Upgrade

About this lesson

Learn to create and modify basic calculated fields for PivotTables.

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.
Login to download
  • 00:04 In this video we're going to introduce you to pivot table calculated fields.
  • 00:08 Now, I have a table of data here which is already summarized by a pivot table here.
  • 00:13 And you'll notice that I've got the price and cost columns already in play.
  • 00:17 ANd what I want to generate at this point in time is profit.
  • 00:20 Now, bar none, the easiest way to do this is actually
  • 00:23 just insert a new column in our table, write the formula there and
  • 00:26 then refresh our pivot table in order to get that field.
  • 00:30 But the reality is that pivot tables can be connected to external data sources, and
  • 00:34 sometimes you don't have the ability to modify the original data.
  • 00:38 So we need to be able to add a formula column inside just the pivot table.
  • 00:43 Now, to do that, here's what's going to happen.
  • 00:46 I'm going to go up to pivot table, analyze.
  • 00:48 And I'm going to look for this field items and sets in here.
  • 00:52 I'm going to click Calculated Field, and this will bring me a nice little dialog
  • 00:57 here where I can actually write my new calculated field.
  • 01:00 So what I'm going to do is I'm going to choose profit and the formula.
  • 01:04 I'm just going to go and select my zero there.
  • 01:06 What I'm going to do is I'm going to take price, so
  • 01:09 just double click on that minus the cost.
  • 01:12 And once I'm done with this I've got everything set up the way that I want,
  • 01:17 I'm going to click Add.
  • 01:18 At this point nothing seems to happen but
  • 01:21 you'll notice that I have a new profit field listed in my fields list.
  • 01:25 And it also shows up in the pivot table field list over here.
  • 01:28 When I now go and say OK, because I added it right inside that pivot table interface
  • 01:33 or the Calculated Fields, it automatically adds it to my pivot table.
  • 01:38 Of course, it puts that horrible sum of profit word on there.
  • 01:41 So I'm just going to go and rename this to profit space.
  • 01:45 All right, now, that's cool, except for one, small problem.
  • 01:51 I realized that the commission it should fit right in the middle here, and
  • 01:54 let's just go and change the name of this one, so we can see it as well.
  • 01:57 So we'll go with commission here, there we go.
  • 02:00 Space, my commission should be subtracted from my price as well.
  • 02:05 So this is not accurate anymore.
  • 02:08 I now need to go back and modify the calculated field.
  • 02:11 So to modify the calculated field to update it, what I'm going to do is
  • 02:16 I'm going to go back to fields item sets, and go into calculated field.
  • 02:20 I'm going to select my calculated field here.
  • 02:24 And yet you'll notice that nothing happens.
  • 02:26 If I double click on this,
  • 02:27 it starts to actually write it into a new formula called Field 1.
  • 02:30 This is not what I want at all.
  • 02:33 So I'm going to click the little drop down right here and choose profit.
  • 02:36 Now, at this point,
  • 02:37 it now brings me back in formula that I had which is price minus cost.
  • 02:40 Now, I'm going to put another space in here just so
  • 02:42 it makes it a little bit more readable.
  • 02:43 I'm also going to come back and say minus, space, and double-click on commission.
  • 02:50 At this point it's very tempting to just click OK.
  • 02:53 The problem is, we haven't actually updated the formula at this point in time.
  • 02:58 So what we're going to do is we're going to hit modify again.
  • 03:01 This will actually change it so that it is written in here.
  • 03:05 And now we can click OK and
  • 03:07 we can see that the profit should be if we actually go and
  • 03:11 check this 2,894 minus 1,310 minus 8,682.
  • 03:16 And we can see that it does indeed come up to 1,497.18.
  • 03:21 The bonus with a calculated field is that the same formula gets used on
  • 03:25 every single row.
  • 03:26 So we know it's always going to add up correctly and that's fantastic and
  • 03:32 just add it right into that pivot table field list ready to go.

Lesson notes are only available for subscribers.

Gift this course