18

Only Display Used Fields on Pivot Table Show Details Sheet

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

Create Show Details Sheet Only Displays Used Fields Columns of Pivot Table

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.

Double Click Cell in Pivot Table to Create Show Details Sheet

This can be useful when you are tying out numbers and want to see all the rows that make up a specific number.

New sheet is added with all rows from cell in pivot table for show details

The show details sheet also contains ALL of the columns from the source data range.

Show Details Sheet Includes All Fields Columns from the Pivot Table

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.

Show Details Macro Groups and Hides Fields Columns that are NOT used in the Pivot Table

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.

Expand Collapse Unused Column Group Outlines for Show Details Sheet Macro

How Does The Macro Work?

Here's what happens when you run the macro:

  1. The macro creates the ShowDetails sheet for the active cell in the pivot table.
  2. It then loops through each column in the Table (List Object) of the new sheet.
  3. It checks to see if the column (field) is used in any of the areas in the pivot table.
  4. If the column is NOT used then it groups the column (columns can also be hidden or deleted).
  5. Steps 3 & 4 are repeated for each column.
  6. The column outline is collapsed so only the used columns remain visible.
  7. 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.

Show Details On Active Pivot Table Columns.xlsm (117.0 KB)

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.

Show Details On Active Pivot Table Columns.xlsm (117.0 KB)

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.

Macros and VBA Webinar Banner - Join Me - 550x200

Click here to register for the webinar, it's FREE!

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 18 comments
Matthia - May 3, 2018

Wonderful idea! I very often go into the details and then it is great to have the less interesting columns hidden by grouping. I applied this grouping idea without the pivot column selection part and added also sorting and sheet naming – I love it! The biggest comfort gain is definitely not to have to scroll left and right through dozens and dozens of columns.
The only bad thing about it is that I can not apply it to all power pivots. Unfortunately many of them just don’t give the data after double click (I think the message is referring to multiple filters).

Reply
Teresa LaBrie - April 9, 2018

Hi Jon, I’ve been looking for a solution to my “problem”. My “drill down” results include “$range” in front of every field name in my results table. So my field names look like this: [$Range].[Transaction Type] when it should only say Transaction Type. This has been the case since I loaded Excel on my laptop years ago. I run MS Office 2013 on Windows 7. Any thoughts on how to fix this would be very much appreciated.

Reply
    Jon Acampora - April 12, 2018

    Hey Teresa,
    Great question! This happens when the data is added to the Data Model by checking that box when inserting the pivot table, or if the source data is from an external source. The first word in square brackets is the Table name.

    You can do a find and replace on the drill down sheet for that string.

    Find:[$Range].
    Replace:

    Replace it with a blank to delete all occurrences of the string on the sheet.

    We could also write a macro to do this for us, depending on how often you create those drill down sheets.

    I hope that helps.

    Reply
      Teresa LaBrie - April 13, 2018

      Wow!! I can’t believe it was as simple as unchecking the data model box on the insert pivot table screen. Thank you so much! My drill down results are beautiful now.

      Reply
Paul - March 2, 2018

Jon,

Thanks for the great article. Is there a way that when you click on the pivot table cell to open up the detail sheet that I can hide a certain column in that detail sheet? We are creating an absolute value in the pivot table, called “Sum of ABS Amount” and that column comes along for the ride in the detail sheet called ABS Amount and the customer doesn’t want to see that.

Thanks for any help you are able to give.

Paul

Reply
Gavin - February 13, 2018

Hi Jon, great application.
I am unable to run it though.
Click on cell in pivot eg $923 and then run macro and get the message
Cannot run the macro – please help, I have changed macro settings in trusted sources, am I missing something?
I am using excel 2010, could that be the problem?

Reply
    Jon Acampora - April 12, 2018

    Hi Gavin,
    Thanks for letting me know. The button was not assigned to the macro properly. I fixed it and the file in the Downloads section above should work now.

    Reply
David Morimoto - January 25, 2018

Hi Jon, thanks for putting this together! I’m really excited to employ the use of this macro, but I keep receiving the error message “Subject is out of Range”. The Debugger shows this line of code as the culprit:

‘Set ListObject (Table) on show details sheet
Set lo = ActiveSheet.ListObjects(1)

Any idea what’s going on?

Reply
S.Yogarajah - January 21, 2018

Thanks for your understanding of what your stands want and feed in the necessary knowledge to satisfy student’s requirements.

Reply
Jeff Weir - January 19, 2018

Cool idea, Jon. Will require some amendment however if users select the “Add to Data Model” option when setting up Pivots.

Reply
    Jon Acampora - January 19, 2018

    Ah yes, thanks for letting me know Jeff! I forgot about the column naming convention for data model pivots. The macro would probably be even more useful for that, as those drill down sheets are usually pretty wide. 🙂

    Reply
Jon Acampora - January 19, 2018

I just updated the file in the download section to include the latest macro that is on the page. It previously had an older version of the macro that did not account for any changes to display name of the fields. The newer version of the macro does account for this, meaning you can change the display name of the fields and the macro will still work.

Reply
Jerry Nord - January 19, 2018

Will be playing with this one. Validating pivot tables just became a whole lot easier with this. Big Thanks to you for putting it together and Aris for asking.

Reply
    Jon Acampora - January 19, 2018

    Thank you Jerry! I’m happy to hear you will put this to use. And yes, big thanks to Aris for inspiring the idea! 🙂

    Reply
Brian - January 19, 2018

Jon, I love your articles. They are so helpful. I’m interested in the code to add a button to the right-click menu. Thanks!

Reply
    Jon Acampora - January 19, 2018

    Awesome! Thanks for the nice feedback, Brian! I will update the article with the code for the right-click menu and let you know when it’s available. Have a great weekend! 🙂

    Reply
Bryon Smedley - January 19, 2018

Great Macro! I never would have thought of extracting ALL of the columns for an associated record would be an issue, but I can see how it would be with large data sets.

I would be very interested in the process of adding this to a right-click menu or double-click event.

Great job! Keep up the stellar work.

Reply
    Jon Acampora - January 19, 2018

    Thank you Bryon! I will update the article with the code for the right-click menu. I have to think about the double-click event and the best way to implement. It would probably be best to have a toggle button on the right-click menu to turn the feature on/off. As you might not always want to have the columns grouped. This would be similar functionality to the automatic default number formatting behavior that I have in another macro.

    I’ll let you know when the article is updated. Have a great weekend! 🙂

    Reply

Leave a Reply: