GoSkills
Help Sign up Share
Back to course

Show Details

Compact player layout Large player layout

Locked lesson.

Upgrade

  • 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
195.3 KB
Show Details - Completed.xlsx
142.8 KB

Quick reference

Topic

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 Pivot Table.

Instructions

Using Show Details

  • Locate a value from the values area of the pivot
  • 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 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.

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

© 2023 GoSkills Ltd. Skills for career advancement