Hello, Excellers welcome back to another blog post with an Excel Pivot Table Tip. In today’s blog post let’stake a look at how to turn OFF the option of a user Double Clicking in your Excel Pivot Table to drill down into the data.
Drill Down Into A Pivot Table.
By double-clicking on the data in the Values Area of your Pivot Table you can extract the underlying records These are the records that make up that data. See the screencast below for an example. (You can also Rick- Click and hit Show Details – whatever method seems more comfortable to you).
The result of the Double – Click or Show Details will result in a new Excel Worksheet being inserted into your workbook. The workbook will contain the details of the data you drilled into. In this example, it is the sales values of the North Region in January 2019.
However, you may want a user NOT to be able to accidentally or otherwise drill down into the data. 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 now try to Double Click or Rick Click to Show Details then you will simply get the warning below.
Recommended Pivot Table Course.
If you want to find out more about Pivot Tables and looking for a course. This is my personal recommendation from my training resources.
Want To Watch The YouTube Video?.
Download The Example File.
[wpdm_package id=’22673′]