🥳 GOSKILLS TURNS 10: Get 10 days of free access with code 10YEARS

GoSkills
Help Sign up Share
Back to course

Calculated Items

Compact player layout Large player layout

Locked lesson.

Upgrade

  • Lesson resourcesResources
  • Quick referenceReference
  • Transcript
  • Notes

About this lesson

Understanding how to create calculated items 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 Items.xlsx
517.2 KB
Calculated Items - Completed.xlsx
517.2 KB

Quick reference

Topic

Understanding how to create calculated items in a PivotTable.

Where/when to use the technique

You wish to take explicit control over the groups that show up in your PivotTable.

Instructions

Creating a Calculated Item

  • Select a row or column label in your PivotTable
  • Excel 2010: PivotTable Tools > Options > Items, Items, & Sets > Calculated Item
  • Excel 2013: PivotTable Tools > Analyze > Items, Items, & Sets > Calculated Item
  • Type a name for your new Item
  • Select the row/column label field on the bottom left
  • Construct the formula by double clicking the Items on the right side to set up grouping levels
  • Filter the row/column to avoid data duplication by removing the items the calculated item summarizes

Modifying a Calculated Item

  • Return to the dialog to create a new calculated Item
  • Select your existing Item from the Name drop down
  • Make your modifications
  • Click Modify

Removing a Calculated Item

  • Return to the dialog to create a new calculated Item
  • Select your existing Item from the Name drop down
  • Click Delete

Key points to remember

  • Does not allow for expansion/collapsing like standard grouping
  • Does not automatically pull in new categories
  • Auditing requires examining the calculation
  • Does not factor correctly into subtotals/totals
  • Can’t take advantage of PivotTable styles properly as the item is classified as a row, not a total/subtotal
  • Requires creating manual subtotal/total highlighting with conditional formatting
Login to download
  • 00:04 In this video, we're going to look at calculated items, and
  • 00:08 calculated items are slightly different than calculated fields.
  • 00:11 Basically, what they are is a way that we can actually start grouping in a pivot
  • 00:15 table using a different technique than our standard grouping levels.
  • 00:19 In a classic grouping scenario, we basically go and we drag our fields and
  • 00:23 group them up, so that they'd look like this.
  • 00:24 We'd end up with breakfast, and lunch, and dinner items that are all grouped and
  • 00:28 organized underneath each other to create a grand total on our pivot table.
  • 00:33 In a calculated item scenario, things are a little bit different.
  • 00:37 We sort of start the same way as calculated fields.
  • 00:39 Although, when we go to pivot table tools analyze, or options in Excel 2010,
  • 00:44 you'll notice that when we go to fields item sets, it's actually grayed out.
  • 00:47 The reason being is because we have to select a row or column header.
  • 00:52 So, if I go ahead and select canned beer, for example,
  • 00:54 now you'll notice that because I'm in my row labels, something from this area,
  • 00:59 or this area here, I can actually now access my calculated items.
  • 01:04 Now, it asks for the same kind of thing, is, would we like to have,
  • 01:09 or what would we like for a name.
  • 01:11 Well, I'm gonna try and group my items by meal times, so
  • 01:14 I'm gonna go with breakfast.
  • 01:16 And it says, all right, what categories would you like to be part of that?
  • 01:19 I'm gonna say, well, this is great, I'll go select this here.
  • 01:21 And I'm going to click Breakfast, and I'm gonna say, OK.
  • 01:25 And it says you can't do that because I've already got a field with that name.
  • 01:29 Wonderful, all right.
  • 01:30 Well, you know what, I can fake this out.
  • 01:31 I'm going to go ahead and
  • 01:33 put little dashes around it just to make it look a little bit nicer.
  • 01:36 So, we've got breakfast with dashes around it, and
  • 01:38 that's gonna include the Breakfast category.
  • 01:40 We'll add that.
  • 01:41 And now, I'm gonna go in and we'll create one for lunch as well, and
  • 01:46 instead of using Breakfast, I'm gonna go to Category here.
  • 01:50 We'll double click on that to open it up, and replace everything that shows up here.
  • 01:54 Inside Lunch, I would like to have, let me see, what would I like to have?
  • 01:59 Burgers are part of lunch, and if I go down a little bit here, sandwiches are.
  • 02:03 And notice as soon as I hit plus, it unfilters, or takes me back to
  • 02:08 the top of this, which is a little bit maddening, but we'll go to Soups/Salads.
  • 02:12 So, those three categories will go for Lunch, and let's go for Dinner.
  • 02:19 And in Dinner we're gonna go back to Category.
  • 02:22 And it just added it to the end, but this formula won't work for us now.
  • 02:25 So we've gotta wipe that one out.
  • 02:27 And in Dinner, we'll have Appetizers, and we'll have Desserts,
  • 02:33 and we'll have Entrees, as well as our Food Modifiers.
  • 02:39 So you get the idea fo what we're starting to build here.
  • 02:43 I can also, of course, go and pick up Beverage, and double click on Category,
  • 02:50 and wipe all of this out, and pick up all the Beverage categories.
  • 02:55 So we'll go with bottled beer.
  • 02:57 And we'll go with Canned Beer, and Coolers/Cider, and
  • 03:02 Draft Beer, and Liquor, and Non-Alcoholic Beverage, and Wine.
  • 03:09 And Add.
  • 03:10 Now, what happens?
  • 03:11 When I add this to my pivot table, you'll notice when I click OK,
  • 03:15 it's added all of these items to my pivot table.
  • 03:20 You'll notice I've turned the grand total off for this, and here's why.
  • 03:23 When I summarize all these values,
  • 03:25 you'll notice down on the bottom here I come up to 18, 515,
  • 03:29 which happens to be the grand total from the classic view of this pivot table.
  • 03:33 This is just a picture, by the way.
  • 03:34 So, I've also got the same thing happening for food, 8000,
  • 03:38 and this one totals up to 5600.
  • 03:40 Oops, hang on, I picked up Wine there.
  • 03:43 Let's try here, 5686, which totals up over here.
  • 03:46 But here's the problem.
  • 03:48 If I went back to my pivot table and
  • 03:49 I turned on my Grand Totals, I've now got duplicated numbers.
  • 03:55 So in order to work with this, I actually have to go and filter out
  • 04:00 all these categories that make up the pieces that I don't want here.
  • 04:04 So what I'm gonna do is, I'm gonna say Label Filters,
  • 04:07 in this case here, begins with.
  • 04:09 And I can just choose to begin it with a dash because all my new categories have
  • 04:13 dashes in them.
  • 04:15 And that allows me to actually filter the table down
  • 04:18 in order to work with the grand total.
  • 04:20 Now, I'm gonna show you a couple of interesting pieces about this, and
  • 04:23 ramifications.
  • 04:25 When I go and use a classic grouping level, and
  • 04:28 this is a real pivot table here, you'll notice that if I group my Beverage items
  • 04:33 I actually get a subcategory of Beverage, because I have Beverage as a class.
  • 04:37 So, I get two levels in here, which kind of looks a little bit funny.
  • 04:40 If I use calculated fields,
  • 04:42 I can actually rebuild a pivot table to do all kinds of interesting things here.
  • 04:46 So all of these totals are based on calculated items,
  • 04:53 and then I had to use some conditional formatting tricks to get it to work
  • 04:55 correctly for the actual totaling up.
  • 04:57 So, if you want really granular control of your pivot table,
  • 05:01 you can use calculated items.
  • 05:03 But you have to apply a lot of filtering and
  • 05:05 grouping to make sure everything gets set up correctly.
  • 05:08 Notice that I've got Beer items that are not checked here,
  • 05:12 because I've got a beer total.
  • 05:13 This is a lot of work.
  • 05:14 But with explicit control, we can get it to look exactly the way we want.
  • 05:18 Using some advanced tricks, we'll teach you in later modules.

Lesson notes are only available for subscribers.

Calculated Fields
06m:31s
Value Field Formats
04m:30s
Share this lesson and earn rewards

Facebook 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