Power Query Problems

Fix Excel External Data Properties Not Preserving Column Layout

Rana GarciaRana Garcia Oct 15, 2026 869 views

Question details

Users need to restore the functionality of the 'Preserve column sort/filter/layout' option for external data connections.

How to Fix Excel External Data Properties Not Preserving Column Layout
Product
Microsoft Excel
Device & OS
not provided
Scenario
Refreshing Power Query external data connections after upgrading from Office 2016 to Office 2021 LTSC.
Observed behavior
Column layouts, sorting, and filters are reset and not preserved upon data refresh, despite the properties being configured to keep them.
Before you start

Ensure you have saved a backup copy of your workbook and check your exact Excel version and build number via the Account settings.

Solution 1Recommended

Install the Latest Microsoft Office Updates

Since this is a known bug in specific Office 2021 LTSC builds, updating to a newer supported build often resolves the issue.

Microsoft frequently releases patches for Power Query integration bugs. Installing the latest update ensures you have all recent bug fixes applied to your Excel LTSC environment.

1
Open Account Settings

Launch Microsoft Excel, click on 'File' in the top-left corner, and select 'Account' from the left-hand menu.

2
Check for Updates

Under 'Product Information', click on the 'Update Options' dropdown button and select 'Update Now'.

3
Restart and Test

Wait for the updates to download and install. Restart Excel, open your workbook, and test the Power Query refresh to confirm if the column layout is preserved.

Install the Latest Microsoft Office Updates
Keep Office Updated: Regularly updating Office is the most effective way to prevent version-specific bugs related to Power Query and external data connections.
Free Microsoft Office alternative

Try WPS Office for Reliable Spreadsheet Management

If you are tired of version-upgrade bugs in Microsoft Office, consider switching to WPS Office. It provides a lightweight, highly compatible, and free alternative for managing spreadsheets, data analysis, and document creation without the heavy system requirements or expensive licensing.

  1. 1. Visit the Website: Go to the official WPS Office website on your browser.
  2. 2. Download the Installer: Click the 'Free Download' button to get the latest installation file.
  3. 3. Install WPS Office: Run the downloaded installer and follow the on-screen instructions to set up WPS Office on your computer.
Highly compatible with Microsoft Excel (.xlsx, .xls, .csv) formats to ensure smooth data migration.Lightweight design ensures fast loading and reliable data processing.Familiar user interface requires no learning curve to switch.Free to use with comprehensive spreadsheet and formatting functionalities.
microsoft office alternative - wps office

Frequently Asked Questions

What does the 'Preserve column sort/filter/layout' option do in Excel?

This external data property ensures that any custom sorting, filtering, or column width adjustments you apply to a query table are kept intact even after the data is refreshed from its external source.

Why do external data properties fail after upgrading to Office 2021?

This issue is often tied to specific build bugs introduced during version upgrades, particularly in Excel LTSC versions. Microsoft typically addresses these Power Query integration bugs in subsequent patch releases.

Where can I find my exact Excel version and build number?

Open Excel, click on 'File', select 'Account', and look under the 'About Excel' section. This specific build information is crucial when reporting persistent bugs to Microsoft Support.