Data Import & Export

How to Keep Excel Column Widths After Refreshing Linked Data

Anum MushtaqAnum Mushtaq Nov 4, 2026 872 views

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.

How to Keep Excel Column Widths After Refreshing Linked Data
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.
Before you start

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.

Solution 1Recommended

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.

1
Select the Data Table

Click any cell inside the imported data table, linked range, or Power Query result in your worksheet.

2
Open External Data Properties

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.

3
Disable Auto-Adjustment

In the External Data Range Properties dialog, locate the 'Data formatting and layout' section and uncheck the box labeled 'Adjust column width'.

4
Enable Formatting Preservation

Ensure that the boxes for 'Preserve cell formatting' and 'Preserve column sort/filter/layout' are checked.

5
Save and Refresh

Click 'OK' to save your changes. Manually adjust your columns to the desired width, then click 'Refresh' to verify that the widths remain unchanged.

Modify External Data Properties to Preserve Formatting
Formatting Locked: Future data refreshes will now update only the data values without overriding your custom column widths and worksheet layout.
Manage Data Easily with WPS Office

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. 1. Open your file in WPS Spreadsheet: Launch WPS Office and open the workbook containing your dataset.
  2. 2. Import external data: Navigate to the 'Data' tab and select 'Import Data' to establish your data connection.
  3. 3. Adjust layout: Format your cells and drag the column headers to set your preferred column widths.
  4. 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.
Fully compatible with Microsoft Excel file formats (.xlsx, .csv)Intuitive Data tab for managing queries and external connectionsGranular control over layout and cell formatting preservationFree and lightweight alternative for daily data processing
microsoft office alternative - wps office

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.