One of the benefits of pivot tables is that you can quickly summarize a large list of data into a concise summary with just a few mouse clicks. However, in certain cases you may not want users drilling down into the details.
For instance, you may need to present salary information in the aggregate by summarizing the data by department or division in a pivot table. In this article, I’ll share two different ways that you can prevent users from drilling down within pivot tables.
Let’s begin by creating a pivot table from some sample data. I did the following in Figure 1 to create my sample data:
Column A has this formula: ="Division"&IF(ISODD(ROW()),"A","B")
In Column B I typed Employee1 in cell B2 and then dragged the fill handle down to create a series.
In column C I used this formula: =RANDBETWEEN(20000,125000)
Once you’ve created the series, press Ctrl-A to select the entire list, press Ctrl-C to copy, and then right-click on cell A1 and choose either the Values icon (or choose Paste Special and then double-click Values).
You’re now ready to create a pivot table:
1. Click any cell within a list of data, such as shown in Figure 1. 2. Activate Excel’s Insert menu. 3. Click the PivotTable command. 4. Click OK to close the Create PivotTable dialog box. 5. Check the Division checkbox within the PivotTable Field list. 6. Check the Salary checkbox within the PivotTable Field list. 7. Double-click the total for Division A. 8. A new worksheet appears within the workbook, displaying the salary information by employee.
Figure 1: Pivot tables enable you to quickly summarize information into a concise summary.
The first technique for preventing users from drilling down within a pivot table involves changing a pivot table option, as illustrated in Figure 2:
1. Right-click on the pivot table. 2. Choose PivotTable Options. 3. Select the Data tab within the PivotTable Options dialog box. 4. Uncheck the Enable Show Details checkbox. 5. Click OK. 6. Activate Excel’s Review menu. 7. Click Protect Worksheet. 8. Optional: provide a password. 9. Check the Use PivotTable & PivotChart checkbox. 10. Click OK. If you provided a password in step 8 you’ll be prompted to confirm the password again.
Figure 2: Clear the Enable Show Details option to prevent users from drilling down within a pivot table.
Depending upon the version of Excel you’re using, at this point if you double-click on the pivot table you’ll likely get a warning that you cannot change a protected area of the spreadsheet. If you unprotect the spreadsheet, two different scenarios may then arise if you double-click on the pivot table:
A vague prompt may appear about being unable to change that part of the pivot table.
Nothing at all occurs when you double-click on a cell within the pivot table.
To restore the drill-down capability, simply click the Enable Show Details checkbox again in the PivotTable Options dialog box. Keep in mind that in most versions of Excel this is a per-pivot table setting. Office 365 subscribers using Excel 2016 do have the ability to set a global preference for all future pivot tables by way of the new Data section within the Excel Options dialog box that you access by way of Excel’s File menu.
A second approach simply involves protecting the structure of the workbook that contains the pivot table:
1. Activate Excel’s Review menu. 2. Choose Protect Workbook. 3. Optional: Provide a password. 4. Click OK. 5. Double-click the pivot table to trigger the first of two prompts. 6. The first prompt signals that Excel was unable to insert a new worksheet due to the workbook being protected. 7. The second prompt indicates that Excel could not copy the records in question from the source data because a new worksheet could not be inserted.
Figure 3: Protecting the workbook prevents Excel from inserting new worksheets to display the drill-down results.
Depending upon how high you wish to raise the bar on preventing users from drilling down on Excel pivot tables, you can use one or both of these techniques.
David H. Ringstrom, CPA, is an author and nationally recognized instructor who teaches scores of webinars each year. His Excel courses are based on over 25 years of consulting and teaching experience. His mantra is “Either you work Excel, or it works you.” David offers spreadsheet and database consulting services nationwide.