The Formatting Nightmare
When you build a highly formatted, presentation-ready dashboard in Microsoft Excel, you spend hours carefully adjusting the exact width of every column and the height of every row to make the layout perfect. However, if that dashboard pulls data from an external source or a Pivot Table, the moment you click “Refresh,” Excel will completely destroy your formatting. It will automatically shrink and expand every column to fit the newly imported text, ruining your carefully designed visual layout.
How to Disable “AutoFit” on Data Refresh
If your spreadsheet is tied to an external data connection (like a SQL database, a CSV file, or a Power Query pull), you must disable the hidden AutoFit setting inside the connection properties.
1. Open your Excel workbook.
2. Click on the Data tab located in the top ribbon.
3. Click the Queries & Connections button to open the side panel.
4. Right-click on the specific data connection that is ruining your formatting.
5. Select Properties from the context menu.
6. In the “External Data Range Properties” window, look for the section labelled “Data formatting and layout.”
7. Untick the checkbox labelled Adjust column width.
8. Click OK.
How to Disable “AutoFit” on Pivot Tables
If a standard Pivot Table is the culprit causing your columns to jump around every time you apply a filter, the fix requires changing the specific Pivot Table options.
1. Right-click anywhere inside the offending Pivot Table.
2. Select PivotTable Options… from the context menu.
3. Ensure you are on the Layout & Format tab at the top of the new window.
4. Look at the bottom of the “Format” section.
5. Untick the checkbox labelled Autofit column widths on update.
6. Click OK.
Now, whenever you refresh your data or change your Pivot Table filters, Excel will forcefully respect the column widths you manually set, ensuring your dashboard remains perfectly aligned.