🎉 New Year’s Resolution Sale! Get 35% off unlimited courses 🥳 Only 11 hours left!

GoSkills
Help Sign up Share
Back to course

Show Details

Compact player layout Large player layout

Subscriber only lesson.

Sign up to this course to view this lesson.

View pricing

  • Lesson resourcesResources
  • Quick referenceReference
  • Transcript
  • Notes

About this lesson

Using Show Details to extract details from a summarized value.

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.

Show Details.xlsx
203.2 KB
Show Details - Completed.xlsx
192.5 KB

Quick reference

Show Details

Using Show Details to extract details from a summarized value.

Where/when to use the technique

You wish to understand which records contribute to the value you see on the PivotTable.

Instructions

Using Show Details

  • Locate a value from the values area of the PivotTable
  • Double-click it

What shows in the Show Details

  • The listing presented shows all records that contributed to the summarized values
  • Records that are not part of the filter context are not included

Disabling/enabling Show Details

  • Right-click the PivotTable > PivotTable Options > Data > Enable Show Details
  • CAUTION:  It is very easy for a user to turn this back on!

Key points to remember

  • Show Details sheets need to be manually removed
  • Show Details sheets are not removed when the file is closed
  • Because they are very easy to create accidentally, it is recommended to disable this feature
Login to download
  • 00:04 In this video, we're going to look at another really cool feature of
  • 00:07 the Pivot Table, which is the Show Details feature.
  • 00:10 Now you'll notice, I already have a pivot table set up,
  • 00:12 it's showing me Alcohol and Food.
  • 00:13 And if I wanted to go and
  • 00:15 say, maybe review the information before I send it out to someone.
  • 00:19 I might want to try and
  • 00:19 figure out if there's anything looking a little bit strange in here.
  • 00:22 So I'm going to drill into Food for the day and
  • 00:24 maybe I'll drill into my Lunch items and just sort of explore.
  • 00:27 Suddenly, I see that I've got a day here for
  • 00:29 Burgers that seems to be out of scope with everything else.
  • 00:32 Everybody else is around 100 to $200 and this one here is $1,300.
  • 00:35 So I'd like to know what's going on in this.
  • 00:37 So to find out, I'm just going to double-click on it.
  • 00:41 And that will actually bring me back all of the records that went into summarizing
  • 00:44 up that data point.
  • 00:46 And you can actually see here, when I look at things, I say hey wait,
  • 00:48 what's this item name right here?
  • 00:50 Tournament special, okay, I had 144 burgers for $1,100.
  • 00:54 Well, this suddenly makes sense, everywhere else,
  • 00:57 I'm selling one to two burgers to individual people.
  • 00:59 This was a big tournament that came through, so
  • 01:01 that kind of makes some sense now.
  • 01:03 So I'm going to go back and say, all right,
  • 01:04 let's take a look at the report again.
  • 01:06 Maybe I'll switch to the Beverage for Non Alcoholic Beverage.
  • 01:10 And we can see that on the same day, week two,
  • 01:12 it looks like we have the same problem.
  • 01:14 So let's just double-click on this one again.
  • 01:16 Boy, there's a lot of information in this one.
  • 01:18 So I might go and say, you know what, why don't I go and
  • 01:23 filter on my Amount column here?
  • 01:25 And I'm going to do a number filter and I'm just going to grab a Top 10.
  • 01:29 And this will show me the biggest values for Top 10 items.
  • 01:32 And that's just a standard table feature, not a pivot table thing specifically.
  • 01:35 But now we can see, once again,
  • 01:37 that we've got 288 tournament waters are rung in for $429.
  • 01:41 So that's obviously going to influence the sales for
  • 01:45 the non alcoholic beverage, so this makes sense.
  • 01:48 So this is a pretty handy feature, for being able to actually go back and
  • 01:52 figure out what's going on inside the data set.
  • 01:54 But there are a couple of challenges with it that you should actually be aware.
  • 01:58 The first one, you can already see is I've got Sheet1 and Sheet2,
  • 02:01 these things are just litter in my workbook now.
  • 02:04 If I accidentally double-click on one of these cells and create another worksheet,
  • 02:08 I've now got all these guys left over and they don't go away when I close the file.
  • 02:12 They stick around because you might have actually wanted to do it.
  • 02:16 So in order to get rid of them, I have to manually, oh shudder, go back and
  • 02:20 actually delete these things to get rid of them.
  • 02:23 Now that it's all back though, it's all good, I can clear out my filters.
  • 02:27 Is there a way I can prevent it from actually happening?
  • 02:29 Because if I accidentally double-click on it,
  • 02:31 I'm going to have the same thing happening.
  • 02:33 And there is, we can right-click and we can say PivotTable Options.
  • 02:38 And if we go to the Data tab,
  • 02:39 you'll notice that you actually have this ability here to Enable show details.
  • 02:44 And if we uncheck that box and come back, if we double-click on the pivot table,
  • 02:48 now it just tells us that we can't change it.
  • 02:50 So we've actually protected it from this particular feature.
  • 02:54 Except for one small problem, and that problem is this.
  • 02:57 A user can easily go back and say, hey, I'm going to go and turn this back on.
  • 03:03 And now, we're right back in the state that we were before.
  • 03:05 There's no way for us to actually lock that down and protect it.
  • 03:08 Why is that such a big deal?
  • 03:09 Well, it's a big deal because you might build a report like this and
  • 03:13 want to send it to somebody either outside your organization, or another department
  • 03:17 where maybe they shouldn't be seeing all the information that you have.
  • 03:20 Let's say, for example, that you prepared a payroll report.
  • 03:23 And you used all of this data here that was summarizing individual payroll
  • 03:27 information for different people that you shouldn't really be leaking around.
  • 03:31 You want to show the summary, which is fine, but not the detail.
  • 03:34 So you think, I know, I'll just right-click on the detail here,
  • 03:37 I'll delete the data sheet, boom, there it goes.
  • 03:41 And now, you can still filter so everything here works nicely.
  • 03:45 So that's safe to send out the door, right?
  • 03:47 And the answer to that is wrong, and here's why.
  • 03:51 If I go back to show the field list, I'm going to pull off everything off of
  • 03:55 the individual context fields, the rows, the columns, the filters.
  • 04:00 And I'm going to remove everything from the timelines and slicers.
  • 04:03 And now, I'm back to just looking at the total sales value.
  • 04:06 Well, this happens to be aggregating every row in the data set.
  • 04:11 Double-click, and boom, there's every row in the data set.
  • 04:15 Now, the very last module of this course where we talk about pre-release
  • 04:18 considerations, and I'm going to teach you how to protect against this problem.
  • 04:21 Because you don't want to be leaking data out this way.
  • 04:24 I have seen the salaries of CEOs when people have come to ask for
  • 04:27 help on stuff and they thought they protected their data.
  • 04:30 And it's really easy to actually expose that,
  • 04:32 which is something that I teach them that they shouldn't be doing.
  • 04:34 This is not a state that I want to see you in.
  • 04:36 So remember, before you send this thing out the door,
  • 04:39 if it's a pivot table, this data is still in the file.
  • 04:42 I'll teach you some ways to protect against that in
  • 04:44 the last module of the course.
  • 04:45 Remember, please watch the pre-release considerations.
  • 04:47 Because that's going to help you actually not get into a situation
  • 04:53 where you send data out the door that shouldn't be going out the door.

Lesson notes are only available for subscribers.

Filtering Multiple PivotTables
5m:22s
GETPIVOTDATA
5m:06s
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

© 2021 GoSkills Ltd. Skills for career advancement