I read this interesting article Quick Trick: Resizing column widths in pivot tables on the Microsoft Excel blog. It is about Excel automatically making column widths too wide when using URLs in pivot tables. Stacey Armstrong demonstrates how to disable this setting.

Here are the steps to manually disable Excel resizing column widths automatically.

1. Press with right mouse button on on any cell in the Pivot table.
   
2. Press with mouse on "Pivot Table Options".
   
3. Press with mouse on the "Layout and Format" tab, then press with left mouse button on the box next to "Autofit column widths on update" to uncheck it.
4. Press with left mouse button on OK button to close the dialog box.

Lindsay Hughes commented:

I recommend that you create a personal macro workbook and save the following macros to that workbook. This will make it easier for you to change this setting automatically, simply run the macro to apply this setting to all Pivot tables in the active workbook.

If you use it really often then I recommend adding the macro to the Quick Access Toolbar located at the very top of Excel.

The following Event macro disables this setting for a Pivot Table named PivotTable1 in a worksheet named Sheet1. An event in Excel is a thing that happens that triggers something else to happen.

In this case, if any worksheet in the workbook is activated the code is rund.

 
'Event code is exectued when any sheet is activated
Private Sub Workbook_SheetActivate(ByVal Sh As Object) 

'Save False to HasAutoFormat property for worksheet Sheet1 and PivotTable named PivotTable1
Worksheets("Sheet1").PivotTables("PivotTable1").HasAutoFormat = False
End Sub 

I can't change it to the default setting but the code below automatically disables this setting for all pivot tables on the active sheet.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

'Dimension variable and declare datatype
Dim pt As PivotTable

    'Iterate through pivottables in active worksheet
    For Each pt In ActiveSheet.PivotTables

        'Change property HasAutoFormat to False
        pt.HasAutoFormat = False

    'Continue with next pivot table
    Next pt
End Sub

This macro changes the setting for all pivot tables in all worksheets in the active workbook.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

'Dimension variables and declare datatypes
Dim pt As PivotTable
Dim WS as Worksheet

'Go through each worksheet in the worksheets object collection
For Each WS In Worksheets

    'Iterate through pivottables in active worksheet
    For Each pt In WS.PivotTables

        'Change property HasAutoFormat to False
        pt.HasAutoFormat = False

    'Continue with next pivot table
    Next pt

'Continue with next worksheet
Next WS
End Sub

The following macro disables the setting for pivot tables in all open workbooks

 
Private Sub Workbook_SheetActivate(ByVal Sh As Object) 

'Dimension variables and declare datatypes
Dim pt As PivotTable 
Dim WS as Worksheet 
Dim j As Single

'Go through all open workbooks
For j = 1 To Workbooks.Count
   'Go through each worksheet in the worksheets object collection 
   For Each WS In Workbooks(j).Worksheets 

       'Iterate through each pivot table
       For Each pt In WS.PivotTables 
           'Change property HasAutoFormat to False
           pt.HasAutoFormat = False 

       'Continue with next PivotTable
       Next pt 

       'Continue with next WorkSheet
   Next WS 

'Continue with next workbook
Next j
End Sub 

Where to put the code?

1. Copy above VBA code.
2. Press Alt+F11 to open the Visual Basic Editor.
3. Double press with left mouse button on your workbook to expand the object list.
4. Double press with left mouse button on Thisworkbook in the project explorer window to show the workbook module.
5. Paste VBA code to the workbook module.
6. Exit VB Editor and return to Excel.

Pivot table category

Analyze trends using pivot tables
Table of Contents Introduction to pivot tables Create pivot table Group data Analyze data (pivot table) Compare performance, year to […]

Auto populate a worksheet
Rodney Schmidt asks: I am a convenience store owner that is looking to make a spreadsheet formula. I want this […]

Auto refresh a pivot table
In a previous post: How to create a dynamic pivot table and refresh automatically I demonstrated how to refresh a pivot […]

Excel categories

Comments (3)