How to Stop Excel Power Query from Changing Column Widths
Question details
The user wants to prevent Excel from automatically adjusting and resizing column widths every time Power Query data is refreshed or loaded.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Working with data imported via Power Query into an Excel worksheet and trying to maintain a custom, fixed layout for the columns.
- Observed behavior
- Excel automatically resizes and auto-fits the worksheet columns whenever the connected Power Query data is loaded or refreshed, ruining the user's custom table layout.
Ensure your Power Query data has already been loaded into a worksheet as an Excel Table, as you will need to access the Table Design properties to fix this issue.
Disable Auto-Fit in External Data Properties
Modify the table properties of your loaded data to prevent Excel from adjusting column widths upon refresh.
By default, Excel enables an auto-fit setting for external data connections to ensure all new data is visible. Disabling this setting stops the layout from changing during future refreshes.
Click on any cell inside the table where your Power Query data has been loaded.
Navigate to the 'Table Design' tab that appears on the Excel ribbon.
Click on 'Properties' located in the External Table Data group on the left side of the ribbon.
In the External Data Properties dialog box, locate and uncheck the box next to 'Adjust column width', then click OK.
Prevent Column Resizing in Pivot Tables
If your Power Query data is loaded directly into a PivotTable instead of a standard table, you must adjust the PivotTable options.
Try WPS Office for Seamless Spreadsheet Management
If you are frustrated by heavy processing and automatic formatting overrides in Excel, consider switching to WPS Office. It provides a highly compatible, fast, and stable spreadsheet environment that respects your custom layouts.
- 1. Download WPS Office: Visit the official WPS website to download and install the free WPS Office suite.
- 2. Open Your Spreadsheet: Launch WPS Spreadsheet and open your existing .xlsx files.
- 3. Edit Without Disruption: Manage your data and layouts smoothly without unexpected background formatting changes.
Frequently Asked Questions
Why does Excel keep resizing my columns when refreshing Power Query data?
By default, Excel's external data connection settings are configured to automatically adjust column widths to fit the new data size. This forces an auto-fit action upon every refresh unless manually disabled in the Table Properties.
Will disabling 'Adjust column width' stop my data from refreshing?
No, disabling this setting only affects the visual presentation of the table. Your Power Query data will continue to refresh and update normally in the background without altering your layout.
Can I apply this setting globally to all Power Query tables?
There is no global setting in Excel to disable auto-fitting for all future queries. You must manually uncheck 'Adjust column width' in the properties of each table or PivotTable after creating it.
