Each time you Refresh a Pivot Table you will most likely get annoyed at the fact that the column widths that you worked so hard to align – will return back to normal. Do not fear, Pivot Table Options is here! All you need to do is Right Click in the Pivot Table and choose PivotTable Options and then under the Layout & Format tab you need to “uncheck” the box that says: Autofit column widths on update. Next time you update your data and Refresh your Pivot Table, the column width will never change.
Key Takeaways
-
Prevent Auto-Resizing on Refresh – By default, Pivot Tables adjust column widths when refreshed. To stop this, go to PivotTable Options > Layout & Format and uncheck Autofit column widths on update.
-
Manually Set Column Widths – Adjust the column widths manually by dragging the edges of the column headers. This ensures consistency in data presentation.
-
Use a Macro for Fixed Widths – If working with multiple Pivot Tables, a VBA macro can automate column width adjustments after each refresh.
-
Apply Cell Formatting – Use Wrap Text or Shrink to Fit options under Format Cells to optimize column widths without cutting off data.
-
Use Table Layouts to Control Widths – Switching to Tabular Form under Report Layout provides better control over column spacing and readability.
Table of Contents
How to Fix Column Width in a Pivot Table
STEP 1: Right click in the Pivot Table and select Pivot Table Options
STEP 2: Uncheck Autofit Column Widths on Update
STEP 3: Update your data
STEP 4: Refresh your pivot table
Our Pivot Table column widths do not change anymore!
Frequently Asked Questions
Why does my Pivot Table column width keep changing after refreshing?
Pivot Tables automatically adjust column widths when refreshed. To prevent this, go to PivotTable Options > Layout & Format and uncheck Autofit column widths on update.
How can I manually fix column widths in a Pivot Table?
Simply drag the edges of the column headers to set your desired width. If you’ve disabled autofit, the column widths will stay fixed even after refreshing.
How do I maintain column width while keeping my Pivot Table readable?
Switch to Tabular Form under Report Layout in the PivotTable Design tab. It provides better spacing control, making columns easier to manage.
Why is some text still getting cut off even after setting the column width?
Check if Wrap Text or Shrink to Fit options are enabled under Format Cells. These formatting options adjust text display within a column while keeping the width fixed.
Can I apply a specific column width to multiple Pivot Tables at once?
Yes, you can select multiple Pivot Tables on different sheets and apply the same column width manually. Alternatively, you can set a uniform column width by selecting the columns and using Format > Column Width from the Home tab.
John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship MyExcelOnline Academy Online Course.