Pivot Table Drilldown - formatting, hidden columns, range instead of table

PivotMeThis

Active Member
Joined
Jun 24, 2002
Messages
346
I find it highly annoying that Excel puts my data in a table with a bunch of filters when I drilldown in a pivot table. I always change it back to a range and make multiple changes to the formatting. If I have hidden columns in the base data they are no longer hidden in the worksheet which is created through the pivot table. It would be VERY nice if the new worksheet would be formatted like the base data...

Are there any advanced features I am missing to customize how the worksheet which is created by drilling down in a pivot table will appear? I have multiple workbooks that will be used by management and the data that is generated by the drilldown should appear formatted and ready for their use.

I thought about recording a macro to format everything and assigning it to a button but since the drilled down report will appear on a new worksheet I don't know where to put it or how to make it work. There could be multiple worksheets created by drilling down.

Thanks for any help out there.
I'm using 2010 - they are using 2007. Gotta love THAT!
 
Hello Jerry,

thank you works like a charm! However I noticed one thing, that for example for me it shows for example "1/4/2016 12:00AM" and for my collegue shows just the date, even though it is the same file, and the same source. What are the reasons of this, maybe you happen to know? Thank you very much!

Also, for example the width of columns are different when Drill Down is summoned. Few columns differs by 20 pixels which is more than enough for the value to be shown as ###########. Is this related also to the differently showed up formats?
We have checked the settings / pc's regional settings and kind of lost here.

another option: Is it possible to inject a rule into the code for formatting purposes after the drill down is opened, for example something like:
.ListColumns(12).Range.NumberFormat = "mm/dd/yyyy hh:mm am/pm"
I have tried to put this line in: Public Function Format_PT_Detail(tblNew As ListObject) after/in the "if" but this does not help.
 
Upvote 0

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
I don't recommend hard coding in ListColumn(12) to have a specific format. Anytime you add or remove a column from your datasource then you would need to modify the VBA code.
Try this modified version of Format_PT_Detailcode which will find the field name you specify (modify "MyDateTimeField" to your actual field name).

Code:
Public Function Format_PT_Detail(tblNew As ListObject)
'---Called by Workbook_NewSheet; Passes ShowDetai table object
'---Uses Pivot Table's SourceData Property stored in Public sSourceDataR1C1
'--- to read apply NumberFormats in first row of SourceData to tblNew
    Dim cSourceTopLeft As Range
    Dim lCol As Long
    Dim sSourceDataA1 As String
    
    If sSourceDataR1C1 = vbNullString Then Exit Function
    sSourceDataA1 = Application.ConvertFormula(sSourceDataR1C1, _
            xlR1C1, xlA1)
    Set cSourceTopLeft = Range(sSourceDataA1).Cells(1)
    With tblNew
        For lCol = 1 To .Range.Columns.Count
            If .ListColumns(lCol).Name = "MyDateTimeField" Then
               .ListColumns(lCol).Range.NumberFormat = _
                  "mm/dd/yyyy hh:mm am/pm"
            Else
               .ListColumns(lCol).Range.NumberFormat = _
                   cSourceTopLeft(2, lCol).NumberFormat
            End If
        Next lCol
        .Range.EntireColumn.AutoFit
    End With
    sSourceDataR1C1 = vbNullString
    cSourceTopLeft = Nothing
End Function
 
Upvote 0
Hello, Jerry,
problems seems to remain the same - even though the formatting is forced through VBA, on some computers in the network everything seems ok, and on others date is shown as mm/dd yyyy.
Would you happen to know what is the root cause of this issue? Thank you!
 
Upvote 0
Try comparing the custom number format for that range for users' that display time, and one's that don't.

If that field displays in format "mm/dd/yyyy hh:mm am/pm" on one computer and that workbook is saved and given to a user with a problem computer, does that same workbook display as "mm/dd/yyyy" when they open the file (without doing their own drilldown)?
 
Upvote 0
Rhonda, Starting with the questions above... It will work for multiple pivot tables in a workbook or worksheet provided that you paste the function "Worksheet_BeforeDoubleClick" into the Sheet Code Module of each Worksheet that has one or more Pivot Tables.

The Pivot Tables and Drill Down Tables don't need to formatted the same way, but it will be much simpler if the Drill Down Tables can all have the same rules applied to the formatting.

For example, if you want to hide all columns in the Drill Down Table that aren't currently shown in the Pivot Table, that would be fairly simple.
On the other hand, if you want to hide "Sum of Sales" and "Customers" for PivotTable1" and hide "Count of Sales" and "Vendors" for PivotTable2, that would be more complex and tailored.

Understanding that, perhaps you could describe whatever rules you'd like to apply to all Drill Down Tables. I'll take a first pass at modifying the code to do that.


I need to get this working on two pivot tables on the same sheet (or could be different sheets if necessary). I need each to use a different table to decide which columns to delete and the formats required. I can get one pivot table working using the method at the beginning of this post but can't get two working independently. Please help!!!!
 
Upvote 0
I need to get this working on two pivot tables on the same sheet (or could be different sheets if necessary). I need each to use a different table to decide which columns to delete and the formats required. I can get one pivot table working using the method at the beginning of this post but can't get two working independently. Please help!!!!

Bump! Please help!
 
Upvote 0
Hi Jerry,
This code works perfect for English excel. But if same excel file opened in German version does'nt display as required. Can you please help with this problem? I am using Excel 2016.

Thanks & Regards,
Sur
 
Upvote 0

Forum statistics

Threads
1,224,876
Messages
6,181,524
Members
453,052
Latest member
patriciagarcia3w

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top