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

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

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