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.
About David Ringstrom, CPA
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.