Locked lesson.
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.xlsx517.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
- 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.