Two Ways to Disable Excel’s Pivot Table Drill-Down Feature

Spreadsheets and graphs on a desk

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.

Please Login or Register to read the full article

To access all of the content on our site, register (it's free!) or login to your existing account.

BONUS: If you register now you can opt to receive a digital copy of "Transform!" , Richard Francis' new book for growing firms [US/Canada ONLY].

About David Ringstrom, CPA

David Ringstrom

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.


Please login or register to join the discussion.

There are currently no replies, be the first to post a reply.