Bottom line: Learn how to prevent or disable the columns in a pivot table from resizing when the pivot table is updated, refreshed, changed, or filtered.

Skill level: Beginner

How to Prevent Columns from Resizing in Pivot Table

Typically when we make any change or update to a pivot table, the column widths resize automatically to autofit the contents of each cell in the pivot table.

The “update” includes just about every action we take on a pivot table including: adding/removing fields, refreshing, filtering with a drop-down menu or slicer, layout changes, etc.  The autofit feature will resize the column to the width of the widest cell (the cell with the most contents) in each column.

This can be annoying!  Especially when the worksheet contains data in other cells outside the pivot table or any shapes (charts, slicers, shapes, etc.).

Turn Off Autofit Column Widths on Update

Fortunately, there is a quick fix for this.  The pivot table has a setting that allows us to turn this feature on/off.

Here are the steps to turn off the Autofit on Column Width on Update setting:

  1. Right-click a cell inside the pivot table.
  2. Select “Pivot Table Options…” from the menu.
  3. On the Layout & Format tab, uncheck the “Autofit on column widths on update” checkbox.
  4. Press OK.

Excel Pivot Table Options Right-Click Menu

Turn off Autofit column widths on update on 2nd pivot table

The columns will NOT automatically resize when changes are made to the pivot table.

I also shared this tip in my post on how to create a search box for a slicer.

Shortcut to Autofit the Column Widths Manually

After turning this feature off, there may be times when you want to resize the columns after modifying the pivot table.  We can do this pretty quickly with a few keyboard shortcuts.

Make sure a cell is selected inside the pivot table, then press the following.

  1. Ctrl+A to select the pivot table body range.
  2. Alt,h,o,i to Autofit Column Widths.

AutoFit Column Widths Pivot Table Keyboard Shortcut

That keyboard shortcut combination will resize the columns for the cell contents of the pivot table only.

If you want to include cell contents outside of the pivot table, then press Ctrl+Space after Ctrl+A.  Ctrl+Space is the keyboard shortcut to select the entire column.

Change the Default Pivot Table Settings

In the latest version of Excel 2016 we can now change the default settings for most pivot table options.  This means we can disable the Autofit column width on update setting on all new pivot tables we create.  This will save us time from having to manually change this setting each time we create a pivot table in the future.

Here are the steps to change the default pivot table settings.  This applies to Excel 2016 (Office 365) only.

  1. Go to File > Options.
  2. Select the Data menu on the left sidebar.
  3. Click the Edit Default Layout button.
  4. Click the PivotTable Options… button.
  5. Uncheck the Autofit column width on update setting.
  6. Press OK 3 times to save & close the Excel Options menu.

Excel Options Pivot Table Layout Excel 2016

Pivot Table Options - Edit Default Layout in Excel

The default settings will apply to all NEW pivot tables you create.  I will do a follow-up post that explains this new default settings feature in more detail.  Again, it's only available on the latest version of Excel 2016 (Office 365 Current Channel).

If you are on an Office 365 ProPlus subscription, then you might be on the Deferred Channel, which might not have this update yet.  Here is an article on how to switch the Current Channel.

Macro to Turn Off Autofit Columns on All Pivot Tables

If your workbook already has a lot of pivot tables, and you want to turn Autofit off on all pivot tables, then we can use a macro for this.

Here is a VBA macro that turns off the Autofit column width setting on all pivot tables in the workbook. The macro loops through all the worksheets in the workbook, and all the pivot tables on each worksheet to turn off the setting. You can also use this to turn the setting back on, by changing the HasAutoFormat property to True.

Sub Autofit_Column_Width_All_Pivots()
'Turn off Autofit column widths on update setting
'on all pivot tables in the active workbook.

Dim ws As Worksheet
Dim pt As PivotTable

  'Loop through each sheet in the activeworkbook
  For Each ws In ActiveWorkbook.Worksheets
  
    'Loop through each pivot table in the worksheet
    For Each pt In ws.PivotTables
    
      'Autofit on column widths on update setting
      'change to True to turn on
      pt.HasAutoFormat = False
    
    Next pt
  Next ws

End Sub

The macro can be copied and pasted to a code module in your Personal Macro Workbook and used on any open workbook. Checkout my free video series on the Personal Macro Workbook to learn more.

Also, checkout my article on the For Next Loop for a detailed explanation on how these types of loops work in VBA.

Note, the macros will work on all versions of Excel.

Macro to List Autofit Column Setting for All Pivot Table

Here is a macro that will list the current value of the Autofit column width setting for all pivot tables in the workbook. The Debug.Print line outputs the results to the Immediate Window in the VB Editor.

Sub List_Pivot_Autofit_Setting()
'Create a list of the current Autofit column width
'setting for each pivot table in the active workbook.
'List is printed to the Immediate Window (Ctrl+G)

Dim ws As Worksheet
Dim pt As PivotTable

    For Each ws In ActiveWorkbook.Worksheets
        For Each pt In ws.PivotTables
            Debug.Print pt.HasAutoFormat & " | " & ws.Name & " | " & pt.Name  
        Next pt
    Next ws

End Sub

The output structure is:

HasAutoFormat value | Worksheet Name | Pivot Table Name

The HasAutoFormat value will be True if the setting is on, and False if the setting is off.

Output of the macro to list pivot table autofit setting

Additional Resources on Pivot Tables & Macros

I hope that helps save some time and frustration with the pivot table columns resizing.  Please leave a comment below with any questions or other tips you have for this issue.  Thank you! 🙂

40 comments

Your email address will not be published. Required fields are marked *

  • For years I’ve wasted insane amounts of time almost daily resizing dashboard slicers. I have “Move and size with cells” checked so that slicers can be hidden inside collapsed grouped rows — and then made to reappear when the grouped rows are expanded (thus saving critical real estate when not needed). But this critical functionality then turns around and destroys everything by frustratingly allowing slicers to expand all over the place as columns are added/changed on my pivot during normal analysis.

    In short, I would gain back untold hours each year if there was a “Size but don’t move with cells” option. Does anyone have any idea how to create the desired slicer functionality?

  • Hi,

    I do appreciate what you shared in your website. But unfortunately I could not solve my problem to fix the width of ONLY one of my pivotal columns. I guess there is no way to tackle the issue and it is the software or my file bug. ]s there any way that you can help in this regard?

    Regards,

    Ata

  • Hello Jon,

    I have an issue with my pivot table filter. For example, I set my filter to “A” so the table shows all data sets containing “A” in their respective place. However, when there are no “A’s” present, my filter resets itself to “(All)” and shows all data sets. I want it to show 0 results. Do you know how to fix this?

    Thank you,
    Jacob

  • Hello Jon,

    I have an issue with my pivot table filter. For example, I set my filter to “A” so the table shows all data sets containing “A” in their respective place. However, when there are no “A’s” present, my filter resets itself to “(All)” and shows all data sets. I want it to show 0 results. Do you know how to fix this?

    Thank you,
    Jacob

  • Hello Jon,

    Your articles, videos, and webinars are priceless. Thank you so much for your efforts getting everyone up to speed.

    I have a question about disabling auto-column width. I have Office 2016 365. So I tried to disable from the File/Options menu to disable auto-width every time. Didn’t work. You mentioned that may not work for everyone yet so I used your macro. Didn’t work. Then I tried simply right-clicking and going to Pivot Table Options, deselecting auto-width. Didn’t work.

    I must be doing something wrong but can’t figure this one out. Any advice?

    Thanks so much

    Scotty

  • Jon…I’ve been an Excel power user since, well, the birth of Excel…I never knew this easy, yet very valuable trick…thanks a million and keep them coming!

  • Hi Jon, just subscribed to your blogs and newsletters, thoroughly enjoying your tips.
    Looking forward to learning lots from you.
    Thank you so much.
    Happy Surfing
    Kevin

  • Hi Jon
    The pivottable autofit to columns macro was exactly what I was looking for, for a long time.
    Many thanks to you for taking the trouble to publish all these tips.
    Peter

  • Thank you so much. I love it. You have a related article about Pivot table. Can you introduce it for me please? Have a nice weekend.

  • I have Excel 2016 as part of Microsoft Office 365 ProPlus. I don’t see “Data” as a choice on the left side of the Excel Options window. I use PowerQuery, PowerPivot, and PowerBI.

    • Hey Ally,

      Great question! This is Field Setting that is specific to each field.

      In the following image the Q4 column disappears when we filter for the Northeast region because the cell does not contain any data.

      Pivot Item is Hidden when it does not contain data

      We can change this behavior in the Field Settings for the Quarter field by right-clicking on any cell in the field’s area, then selecting Field Settings…

      Pivot Table Field Settings Right Click Menu

      On the Layout & Print tab, check the box that says “Show items with no data”.

      Pivot Table Show Item with No Data Field Setting

      The Q4 column will now remain visible even when there is not data in the values area of the pivot table.

      Pivot Column remains visible when show items with no data is turned on

      I will do a follow-up post on this. Thanks for the question! 🙂

      • Hi Jon, thank you for your fast reply and the detailed explanation! That checkbox is grayed out for me. What might be causing that?
        Thank you!

  • Thank you Jon. I am senegalese and french speaker. My english is not very good but I understand easyly your post. This is very helpfull for me.
    May God take care of you. Thanks.

Search
Generic filters
Exact matches only
Filter by Custom Post Type

JOIN US & LEARN EXCEL

Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
Plus weekly updates to help you learn Excel.

Download the eBook

About Me

Jon Acampora Profile

Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...

MVP_Horizontal_BlueOnly