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.xlsx525.1 KB Calculated Items - Completed.xlsx
526 KB
Quick reference
Calculated Items
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
- PivotTable Tools > Analyze > Fields, 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.
- 00:07 Now these are very different than calculated fields,
- 00:10 because a calculated field actually aggregates values to return a number.
- 00:15 In a calculated item, it's actually more similar to grouping.
- 00:19 Now what I'm showing you here on the right-hand side is a picture of a pivot
- 00:22 table that's actually using classic grouping,
- 00:24 which we've shown in prior modules.
- 00:26 So you can see we've got three lunch items that are grouped together and four dinner items.
- 00:31 Let's see how this actually is similar to what we have there.
- 00:35 We're going to go click in our pivot table, go to pivot table tools,
- 00:38 analyze fields, items and sets.
- 00:41 Wait, calculated item is grayed out.
- 00:43 Why is that?
- 00:44 It's because you have to have selected a field from one of your row labels
- 00:48 in order to be able to say, let's create a calculated item.
- 00:53 Now when we get in here, we can go in and
- 00:55 let's give our nice little calculated item a name.
- 00:57 We'll go and we'll start grouping here.
- 00:59 We're going to go with breakfast.
- 01:01 And you'll notice it as soon as I do this, it comes back and
- 01:03 the Add button is grayed out.
- 01:05 It won't let me do it.
- 01:06 And the reason being, I can't use the same name of an existing item.
- 01:10 So it's a little frustrating but you can work past that by doing this.
- 01:13 We're going to go with dash breakfast dash.
- 01:17 Now what I'll do is I'll say great, and highlight the zero here,
- 01:20 select breakfast and say, insert item and we'll add that to the pivot table.
- 01:27 Now for whatever reason, it wipes out my Item's list.
- 01:30 But if I go and take a look at some of the other categories here.
- 01:33 If I bring back Category,
- 01:34 it brings me back all of the different items that I actually have here.
- 01:37 So that's nice.
- 01:38 Now I'm going to go through and build out some of my other categories.
- 01:41 Let's make a new one called Lunch.
- 01:45 For lunch, what I'm going to do is I'm going to say
- 01:49 this one's going to be equal to my burgers.
- 01:52 Plus, I'm going to add my sandwiches and my soups and salads.
- 01:59 So grab those three and we'll say Add.
- 02:04 Now why don't we go and make a dinner category and
- 02:08 the dinner category is going to be equal to.
- 02:12 So we'll take all this and wipe it all out.
- 02:14 For dinner, I think we're going to go on, we're going to have, let me see,
- 02:17 what should we throw in here?
- 02:18 Let's grab our appetizers.
- 02:20 We're going to grab our desserts.
- 02:23 We're going to grab our entrees.
- 02:27 And we're also going to grab our food modifiers as well and say Add.
- 02:33 And then finally, we'll make another category here all together for beverage.
- 02:39 So again, we'll hit tab, I'm going to wipe all this one out, go down click Category.
- 02:43 For some reason, doesn't show up right away.
- 02:45 So we click somewhere else and come back to it.
- 02:47 Now we can go and say, this is going to be bottled beer.
- 02:51 Plus, it's going to be canned beer, cooler cider, draft beer.
- 02:55 What else do we need to pick up here?
- 02:57 Liquor, non alcoholic beverage and
- 03:02 wine and we'll say Add.
- 03:06 At this point, we hopefully have everything in there that we need.
- 03:09 If we don't, we can always come back, pick up the category and
- 03:13 modify this piece if we need to do so.
- 03:16 What's going to happen now, I'm going to click OK and you're going to see and
- 03:20 this is where things get a little bit confusing.
- 03:22 We've now just added four new items to our pivot table.
- 03:26 Now, you'll notice that I've actually turned off grand totals for
- 03:29 the pivot table.
- 03:30 The reason being, if I turn them on,
- 03:32 it actually re-summarizes this information and adds it in twice.
- 03:37 So we don't want to do that,
- 03:38 I'm just going to press Ctrl+Z to undo that right now.
- 03:42 Now the key thing is, in order to actually make sure my data isn't duplicated,
- 03:46 I'm going to now need to filter these items.
- 03:49 I'm going to go to label filters and
- 03:50 say, let's go with does not begin with the dash.
- 03:55 At this point, I can now say OK and
- 03:58 that gets rid of those, which wasn't exactly the way that I wanted to go.
- 04:03 Let's go back and try this again and we'll say, begins with the dash.
- 04:08 And this will keep only those items exactly as we actually want to see them.
- 04:12 Now obviously,
- 04:13 this has a lot less data in it than what we see on the right-hand side.
- 04:16 But you can see how you can actually control the grouping levels and
- 04:19 we can actually come up with the same value.
- 04:21 So breakfast, there we are, the 767.
- 04:23 This guys aren't subtotaled, but we certainly could do that very easily.
- 04:29 Here's a report that's done with classic grouping.
- 04:31 This is the way that we would normally do things.
- 04:33 But what you'll see here is we get some funny little categories like
- 04:36 breakfast breakfast, and then we got beverage beverage.
- 04:39 So we've got this extra repeating pieces.
- 04:41 Where a nice little item set can help you out really well
- 04:45 is where you decide to go a little bit more in depth with what you have.
- 04:49 To actually use some more advanced tricks that we'll show you in later modules in
- 04:52 this course.
- 04:53 But this particular pivot table actually uses calculated items
- 04:58 to create a whole bunch of information.
- 05:00 And then uses conditional formatting to format it up to make it look just right.
- 05:04 How does this actually work?
- 05:06 If I go back to pivot table tools analyze, this is a different data set,
- 05:10 which is why you're not going to see these things right off the bat.
- 05:12 Let me grab this, we'll go and grab our calculated items.
- 05:16 You'll see that I've actually got breakfast, lunch, dinner, total food,
- 05:20 all of them starting with a space, so that I can actually highlight these things.
- 05:24 I've got beer as a specific thing that groups three different items for
- 05:27 these guys here.
- 05:29 And I use a mixture of filtering on this, to actually get rid of the items that I
- 05:33 don't want to see, like the other child items that were in this set.
- 05:37 The key here is working with some of the advanced tricks, we'll show you later.
- 05:40 To really take granular control, and
- 05:43 make your pivot table show exactly as you want.
Lesson notes are only available for subscribers.