Show Details

Quick reference

• 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.