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

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