Fix Excel External Data Properties Not Preserving Column Layout
Question details
Users need to restore the functionality of the 'Preserve column sort/filter/layout' option for external data connections.

- 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.
Ensure you have saved a backup copy of your workbook and check your exact Excel version and build number via the Account settings.
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.
Launch Microsoft Excel, click on 'File' in the top-left corner, and select 'Account' from the left-hand menu.
Under 'Product Information', click on the 'Update Options' dropdown button and select 'Update Now'.
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.

Submit a Bug Report to Microsoft Support
If updating does not resolve the problem, use the Feedback Hub or contact Microsoft Support directly to report the persistent bug.
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. Visit the Website: Go to the official WPS Office website on your browser.
- 2. Download the Installer: Click the 'Free Download' button to get the latest installation file.
- 3. Install WPS Office: Run the downloaded installer and follow the on-screen instructions to set up WPS Office on your computer.

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.




