Show Details

Quick reference

• 00:04 In this video, we're gonna look at another really cool feature of PivotTable,
• 00:08 which is the Show Details feature.
• 00:11 Now, you'll notice I already have a PivotTable set up.
• 00:13 And as we're want to do with our PivotTable,
• 00:15 I might just drill into this and say, you know what.
• 00:17 I'd kinda like to look at my lunch items.
• 00:19 So I'll drill into food, and I'll drill into lunch.
• 00:22 Then I'll take a look at the numbers that I've got
• 00:24 over the five week period that my data set holds.
• 00:28 And I'm looking down, as I should, at trying review things and
• 00:31 figure out what's going on, and
• 00:33 I can see that in burgers I've got a really large number for one of the weeks.
• 00:38 I'm thinking, well, I wonder what that's all about.
• 00:41 In order to find out quickly and
• 00:43 easily, all I need to do on this is just double click on it.
• 00:47 And when I do, what you'll see is it actually extracts a list of
• 00:51 all the records from the data set that make up that particular number.
• 00:55 And I can now look at this and say, well jeez,
• 00:57 there's one really large number that's going on in here.
• 01:00 And you can see that I had a tournament special on that day, so
• 01:03 it looks like I had 144 people that came in and got burgers where normally I'm
• 01:07 selling one or two on each of these individual periods.
• 01:10 So that explains what's going on.
• 01:12 All right, let's go back here again and
• 01:16 maybe we'll grab the beverage information as well.
• 01:19 And, yeah,
• 01:20 we can see that the non-alcoholic beverage on that same week also looks high.
• 01:24 Let's go take a look at this one too.
• 01:25 We'll double click on that one.
• 01:27 And, once again, I get a much bigger list, because there's a lot
• 01:31 more nonalcoholic beverage transactions that have gone through here.
• 01:35 But I can also see on this, if I want to.
• 01:38 I'm not sure what's going on in this.
• 01:41 Let's go right, or just click on this guy here, and
• 01:44 put a number filter on this and grab the top ten.
• 01:47 This is just a tables feature.
• 01:49 We'll grab the top ten items.
• 01:51 And filters and says, based on the amount, here's the top ten.
• 01:54 It's not in order, but that's okay.
• 01:56 Once again, I can see that we sold 288 tournament waters, and that kinda sticks
• 02:01 out like a sore thumb next to all the other values that are in there.
• 02:03 So, this is a really cool feature, this whole drill through ability.
• 02:08 But it has some problems.
• 02:10 There's a couple of them.
• 02:11 Number one, it leaves extra sheets littering our workbook.
• 02:15 And it's really easy for somebody to just accidentally double click on a cell and
• 02:18 get a whole bunch of these things.
• 02:19 So all of a sudden you've got a whole bunch of these sheets sheet one,
• 02:22 sheet two, sheet three, littered all the way through your workbook.
• 02:24 And they don't go away when you close the file.
• 02:27 You have to manually go and delete them.
• 02:30 So, that's a little bit unfortunate, okay?
• 02:34 Now, if we wanna turn this off, so that we don't accidentally trip onto it,
• 02:39 we right click on pivot tables, go to pivot table options.
• 02:44 Go to the Data tab and uncheck Enable Show Details.
• 02:49 And when we do that, you'll notice now, that double clicking on it just tells us
• 02:53 that it can't change that part of a report.
• 02:56 So we don't have to worry about anybody accidentally dealing with it.
• 02:59 There's something else I wanna show you about this that's a little bit
• 03:02 dangerous too.
• 03:03 I'm gonna go back, I should also point out it's very easy for a user to come back and
• 03:07 turn on this if they want to see it as well, but they have to know where it is.
• 03:11 Now, I wanna show you something really interesting.
• 03:14 A lot of people will blast their file out the door,
• 03:16 thinking boy I don't want anybody to be playing around with this data too much,
• 03:19 so I'm just gonna go and remove the worksheet that has the source data.
• 03:25 Delete it.
• 03:25 It's gone.
• 03:26 They can still filter all the records, everything's good, no problem.
• 03:30 So they can still see the pivot table.
• 03:31 But they can't get to the actual data itself, right?
• 03:36 Wrong.
• 03:37 Watch this now.
• 03:39 We've got sales, which is the amount field on here.
• 03:41 What I'm gonna do is I'm going to unfilter this.
• 03:44 I'm gonna uncheck all of these fields, so
• 03:47 the only thing showing on my pivot table is sales.
• 03:51 There's $25,765 there, and it's made up of
• 03:56 this entire data set, which was 26 hundred rows.
• 04:02 This is the thing that's really dangerous about this particular thing is that when
• 04:06 you send this report out the door, if somebody wants to rip off all the fields,
• 04:11 they can double click on that and expand the entire record set.
• 04:15 I've actually done this with a file that somebody sent me.
• 04:17 They removed the original data table, because it was payroll data, and
• 04:21 were asking for some help.
• 04:22 I took the fields off and double clicked on it to expand the record set because I
• 04:25 needed to see the original data to be able to answer their question.
• 04:28 But that was pretty scary, it was payroll data.
• 04:31 Now, we're gonna look at dealing with a specific issue in a later module, but
• 04:36 I did want you to be aware of it before you start going and
• 04:38 blasting things out the door.
• 04:40 Turning off Show Details is not enough to protect your record set even if you delete
• 04:44 delete it from your file.
• 04:45 Just make sure that you actually follow up and watch the pre release considerations
• 04:49 module because that's gonna tell you how to actually solve this problem and
• 04:53 prevent data from falling into the wrong hands as well.

Lesson notes are only available for subscribers.