Author: Oscar Cronquist Article last updated on August 19, 2019

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. Right-click on any cell in the Pivot table.
  2. Click on "Pivot Table Options".
  3. Click on the "Layout and Format" tab, then click on the box next to "Autofit column widths on update" to uncheck it.
  4. Click OK button to close the dialog box.

Lindsay Hughes commented:

This should be the default setting, or there should be some way to enable this as the default setting. I have to do this every time I make a pivot table (which can be a dozen times a day in some projects)

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 executed.

 
'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 click on your workbook to expand the object list.
  4. Double click 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.
Note, save the workbook with file extension *.xlsm (macro-enabled workbook) to attach the code to the workbook you are working with.