Hello, Excellers. Welcome back to another #Excel Tip blog post. This Pivot Table blog will take you through the Drill Down feature in Excel Pivot Tables.
When you create a Pivot Table in Excel, each of the values represents one line of data or record in that data set. The Pivot Table creates a summary of your data set. Let’s use an example in this blog post. You can download the sample data set and Pivot Table, by clicking the download button below.
I have a very simple data set and gone ahead and created a Pivot Table.
If you want to know the basics of creating your first Pivot Table then please read my blog post below or watch my YouTube video.
Now, back yo our current situation with our data set. I have already created the Pivot Table which summarises my monthly regional sales. So, for example, cell G4 represents North Sales in January of 2554.
Viewing The Drill Down Data.
To see the underlying data in the data set I can drill down or extract those individual records.
- Right Click in the cell that you want to see the records for
- Select Show Details
The underlying data record or records will be extracted to a new Excel worksheet in the current workbook. This is the most simple of example. I have only one underlying record for this figure, which has been extracted to a new worksheet.
If I use the Pivot Table to change the view of my data to a Total Sales per Region the summary looks like this.
If I now repeat the process and right-click on the South Sales in cell F5, all of the records that make up that figure are extracted. Good stuff right?.
Another Method Of Drilling Down.
So far we have been using the right-click method to extract or drill down into the records that make up our Pivot Table. There is an alternative, however. Just double click on the cell you want to drill into. Simple as that. You get the same result at the right-click method. It really is just a question of preference.
Preventing Drill Down Into A Pivot Table.
Now we have looked at drilling down into a Pivot Table, you may not want users to have access to the underlying data records. In that case, you can actually prevent users from drilling down into it.
There is a small setting we can change to prevent this.
- Select your Pivot Table
- Go to the Analyze tab in the ribbon
- Press the Options button in the Pivot Table section to open the options menu
- Go to the Data tab and un-check the Enable show details box to disable this feature
Now, this is NOT an ultra-secure or really effective way of preventing data drill down if the user knows what they are doing, but it can help to prevent generating extra worksheets in error. If you want to read the full blog post on this or watch my YouTube Video then click on the relevant links below.