How to Avoid Changing the Column Width When Refreshing PivotTable

In a previous Excel Tip we have learned how to create a simple Excel Pivot Table. Now we will go on from there and learn how to eliminate one of the major pains of Pivot Tables: It changes the size of the columns after each update of the values.

Steps for fixing the column width when updating Pivot Tables

When you change your data, you have to refresh the Pivot Table. Therefore, right-click on any value and click on “Refresh”. Now, the column width adapts to the new data – even if you carefully changed the columns before. To avoid that, do the following steps (the numbers are corresponding to the picture above):

pivot, table, column width, column, width, update, refresh
  1. Right click on any value within your Pivot Table.
  2. Click on “PivotTable Options”.
  3. Remove the tick at “Autofit column widths on update”.

That’s it. Now the column width stays the same on each update.

Do you want to know one more very helpful tip about PivotTables? Read here how to disable the “GETPIVOTDATA” when you refer to a PivotTable.

Henrik Schiffner is a freelance business consultant and software developer. He lives and works in Hamburg, Germany. Besides being an Excel enthusiast he loves photography and sports.

2 comments

  1. I have somehow managed to add a link in my spreadsheet which is showing on all worksheets. My aim was to show data in a cell on one worksheet from another worksheet on the document, to do this I used the = in the cell, went to the page that I thought it was on and realised it was the wrong worksheet so then tried to move to another, but what I have now in the same cell in each workshop is what looks like a text box which I can’t remove and when I type something it shows in the cell in each worksheet. I can’t clear it down, even using the back arrow to try and go back to before the error. Any suggestions On how to clear this please?

    1. Hi AMC, not sure what you mean…? Typing =Sheet1!A1 (for example) into a cell should work for linking to that cell?

Comments are closed.