How to Keep Excel Column Widths After Refreshing Linked Data
Question details
The user needs to stop the spreadsheet application from automatically reverting custom column widths and cell formatting back to default whenever external data connections are refreshed.

- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Refreshing external data connections, linked tables, or Power Query results within a worksheet.
- Observed behavior
- Upon clicking 'Refresh', all manually adjusted column widths and applied cell formats in the destination sheet are overwritten or reset to default.
Ensure your spreadsheet has an active external data connection or Power Query table that can be successfully refreshed, as the properties dialog will only be available for active data ranges.
Modify External Data Properties to Preserve Formatting
Change the connection properties of your imported data table to explicitly lock your manual column widths and cell formats during a data refresh.
By default, spreadsheet applications automatically adjust column widths to fit imported data when refreshing connections. You can disable this automatic adjustment through the External Data Properties menu.
Click any cell inside the imported data table, linked range, or Power Query result in your worksheet.
Right-click the selected cell and choose 'External Data Properties' from the context menu. Alternatively, navigate to the Data tab and click 'Properties' under the Connections group.
In the External Data Range Properties dialog, locate the 'Data formatting and layout' section and uncheck the box labeled 'Adjust column width'.
Ensure that the boxes for 'Preserve cell formatting' and 'Preserve column sort/filter/layout' are checked.
Click 'OK' to save your changes. Manually adjust your columns to the desired width, then click 'Refresh' to verify that the widths remain unchanged.

Use WPS Spreadsheet to Manage and Format External Data
WPS Spreadsheet offers powerful, intuitive tools for importing external data. You can easily connect to data sources, lock down your custom cell formatting, and maintain a perfectly structured layout during every data refresh.
- 1. Open your file in WPS Spreadsheet: Launch WPS Office and open the workbook containing your dataset.
- 2. Import external data: Navigate to the 'Data' tab and select 'Import Data' to establish your data connection.
- 3. Adjust layout: Format your cells and drag the column headers to set your preferred column widths.
- 4. Lock data range properties: Right-click the data range, select 'Data Range Properties', and check the layout preservation options to prevent resetting upon refresh.

Frequently Asked Questions
Why do column widths change automatically during a refresh?
By default, the software attempts to ensure all new incoming data is fully visible without clipping text. Enabling 'Adjust column width' allows it to expand or contract columns based on the longest string of text in the refreshed dataset.
What if the 'External Data Properties' option is grayed out?
This option is only active when an external data range is selected. Make sure your active cell cursor is positioned directly inside the connected data table or query result before right-clicking or opening the Data tab properties.
Will unchecking 'Adjust column width' hide new data?
If new incoming data is wider than your manually set column width, the text may be truncated visually or spill over if the adjacent cell is empty. The data is still there, but you will need to manually resize the column to read long entries.




