Bottom line: Learn how to create a Show Details Drill Down Sheet from a pivot table that only contains the fields (columns) used in the pivot table.
Skill level: Intermediate
The Show Details Sheet Usually Shows All Fields
When we double-click a cell in the values area of a pivot table (or right-click > Show Details), a new sheet is added to the workbook. The new sheet contains all of the source data rows for cell we double-clicked.
This can be useful when you are tying out numbers and want to see all the rows that make up a specific number.
The show details sheet also contains ALL of the columns from the source data range.
Aris, a member of the Excel Campus community, asked a great question and wants to know if we can create a details sheet that ONLY includes the fields used in the pivot table?
This isn't directly possible in Excel, but we can use a macro to solve this problem. Let's see how we can use VBA to save the day (and save a ton of time)! 🙂
The Show Details of Used Fields Macro
The following macro will group and hide or delete columns in the Show Details sheet that are NOT used in the pivot table. This means we will be left with a show details sheet that only contains the fields (columns) that are used in pivot table. This gives us a much skinnier table that can be easier to read.
The advantage of grouping and hiding the columns is that they can easily be made visible by pressing the 2 in the top left corner to expand the groups.
How Does The Macro Work?
Here's what happens when you run the macro:
- The macro creates the ShowDetails sheet for the active cell in the pivot table.
- It then loops through each column in the Table (List Object) of the new sheet.
- It checks to see if the column (field) is used in any of the areas in the pivot table.
- If the column is NOT used then it groups the column (columns can also be hidden or deleted).
- Steps 3 & 4 are repeated for each column.
- The column outline is collapsed so only the used columns remain visible.
- The columns widths of the Table are auto fit, which saves another step with the show details sheets.
Download the Excel File
Download the file that contains the macro.
Here is the macro that you can copy & paste to your Personal Macro Workbook or any VBA Project.
Sub Show_Details_Used_Fields_Only() 'Creates a Details Sheet for a Pivot Table 'based on the activecell and Deletes or Hides 'columns that are not used in the pivot table. 'The macro can be added to your Personal Macro 'Workbook and be run on any open file. 'Source: https://www.excelcampus.com/vba/pivot-table-show-details-used-fields-only Dim pt As PivotTable Dim pf As PivotField Dim pfData As PivotField Dim lo As ListObject Dim loCol As ListColumn Dim bVisible As Boolean 'Check the activecell is in a pivot table On Error Resume Next Set pt = ActiveCell.PivotTable On Error GoTo 0 If pt Is Nothing Then MsgBox "Please select a cell inside a pivot table" Exit Sub End If 'Check the activecell is in the values area of a pivot table If Not Intersect(ActiveCell, pt.DataBodyRange) Is Nothing Then 'Create the show details sheet ActiveCell.ShowDetail = True 'Set ListObject (Table) on show details sheet Set lo = ActiveSheet.ListObjects(1) 'Delete unused columns from the details sheet For Each loCol In lo.ListColumns bVisible = False 'Check the field is not used in the Filters, Rows, or 'Columns areas For Each pf In pt.PivotFields If pf.Name <> "Values" Then If pf.SourceName = loCol.Name Then If pf.Orientation = xlHidden Then 'Check the field isn't used in the Values area 'DataFields in the values area have a Hidden Orientation For Each pfData In pt.DataFields If pfData.SourceName = loCol.Name Then bVisible = True End If Next pfData Else 'Field is used in rows, cols, or filters areas bVisible = True End If 'Group and collapse the columns in the details sheet If bVisible = False Then 'Uncomment any of the lines below to delete or hide 'the columns instead of grouping loCol.Range.EntireColumn.Group 'loCol.Delete 'loCol.Range.EntireColumn.Hidden = True End If End If End If Next pf Next loCol 'Collapse groups ActiveSheet.Outline.ShowLevels ColumnLevels:=1 'Autofit columns lo.Range.SpecialCells(xlCellTypeVisible).EntireColumn.AutoFit Else MsgBox "Please select a cell in the values area of the pivot table." Exit Sub End If End Sub
How to Run the Macro
Download the file that contains the macro.
The macro can be added to your Personal Macro Workbook and run on any Excel file you have open. You can add a button to the ribbon that will run the macro any time. Checkout my free video series on the Personal Macro Workbook to learn how to get started and create the ribbon buttons.
We could also add a button to the pivot table's right-click menu to run the macro. I shared similar code in my article on Automatic Default Number Formatting for Pivot Tables, but please leave a comment below if you are interested in the code to add a button to the right-click menu.
Another option is to add the code to an App Event for the BeforeDoubleClick event. Then the macro could automatically run when a cell in double-clicked in the pivot table.
There are a lot of ways to implement this macro, depending on how often you want to use it. Please leave a comment below with any questions or suggestions on how to improve it. Thank you! 🙂
How to Learn Excel Macros
If you are interested in learning more about macros, I’m currently running my free webinar called “The 7 Steps to Getting Started with Macros & VBA”.
During the webinar I explain why you might want to learn VBA, and a lot of the basic coding concepts that will help you get started. I jump into Excel and the VB Editor and walk through how to write and run our first macro. Even if you have been using VBA for awhile, I’m sure you will learn some new tips.