How to Stop Pivot Table Columns from Resizing on Change or Refresh - Excel Campus
33

How to Stop Pivot Table Columns from Resizing on Change or Refresh

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! 🙂

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 33 comments
José - October 10, 2017

Hi jon, I would like to know how to remove the zeros of the generated graphs from a PivotTable

Reply
Scotty - September 29, 2017

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

Reply
Mickael - September 18, 2017

Thank you so much Jon!
I learn so much from you. Microsoft and every Excel user are lucky to have you.

Reply
James - September 12, 2017

Another good tip to end the week!

Reply
Patricia - September 10, 2017

A very handy post Jon 🙂 thank you

Reply
Rob Africk - September 10, 2017

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!

Reply
    Jon Acampora - September 11, 2017

    Thanks Rob! There are so many settings and features of Excel that we will never run out of things to learn. That’s one thing I love about Excel. Endless possibilities… 🙂

    Reply
Belinda - September 9, 2017

Hi jon,

Thank you for this post! It’s so simple and actually never thought of it. You’re amazing really!!
Thank you

Belinda

Reply
Ann - September 9, 2017

Thank you. This was really helpful and reduced my annoyance 🙂

Reply
Kevin Tarry - September 9, 2017

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

Reply
Ahmed - September 9, 2017

Hi Jon

Very useful tips on PivotTables.
It’s highly appreciated

Thank you

Reply
Ben from HK - September 8, 2017

Very useful tips !! Thanks very much !!

Reply
PeterA - September 8, 2017

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

Reply
Hang tran - September 8, 2017

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.

Reply
    Jon Acampora - September 11, 2017

    Thank you Hang! I added a section at the bottom of the article with links to other posts on pivot tables.

    Reply
Bob Terrell - September 8, 2017

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.

Reply
    Jon Acampora - September 8, 2017

    Hi Bob,

    You are probably on the Deferred Channel, which might not have the updated feature yet. The ProPlus subscriptions are on the Deferred Channel by default, but you can switch to the Current Channel. Here is an article that explains how to switch to the Current Channel.

    I will note this in the article above. There are a lot of channels for updates with the Office 365 subscription, and it’s tough to keep track of which channel gets what…

    Thanks! 🙂

    Reply
Ally Cao - September 8, 2017

Yes! Can you point me to how to prevent a column from disappearing if there’s no data for a specific slicer?
Thank you!

Reply
    Jon Acampora - September 8, 2017

    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! 🙂

    Reply
      Ally - September 19, 2017

      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!

      Reply
Pat McKeown - September 8, 2017

Hi Jon

Very useful tips on PivotTables.

Thank you
Pat

Reply
koyel rana - September 8, 2017

explanation is very nice..its very helpful..
thank you

Reply
Ibou sarr - September 8, 2017

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.

Reply

Leave a Reply: