'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
Fix Excel External Data Properties Not Saving Selected Option
Question details
The selected options in Excel's External Data Properties dialogue fail to save, and refreshed query table data overlaps with adjacent worksheet content.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A user is trying to configure an Excel query table so that imported data expands by inserting new rows without conflicting with surrounding tables.
- Observed behavior
- The External Data Properties settings revert to defaults after closing the dialog, and refreshed data expands improperly, overwriting content below or beside the query table.
Before modifying your query tables, verify the exact data source connection type you are using and take note of the specific rows or columns that are currently being overwritten by the refresh.
Move and Isolate the Query Table
Relocate your query tables to an area with ample empty rows and columns to prevent data overlap upon refreshing.
Click and drag to select the entire query table that is currently causing layout conflicts.
Cut and paste the table to the bottom of your worksheet, or preferably into a completely new, blank worksheet.
Leave enough blank columns and rows around the newly placed query table so that when the external data is refreshed and expands, it cannot overwrite any other workbook content.
Test the Query Configuration in a New Workbook
Create a fresh Excel file to check if the property-saving issue is specific to a corrupted workbook.
Report the Bug to Microsoft Support
If the settings continuously reset despite workarounds, it is likely a software bug requiring developer intervention.
Try WPS Spreadsheet for Stable Data Management
If Microsoft Excel continues to struggle with saving external data properties or layout conflicts, WPS Office offers a free, lightweight, and highly compatible alternative. Enjoy a familiar interface while easily managing your spreadsheet data without encountering constant settings resets.
- 1. Download WPS Office: Visit the official WPS website and download the free WPS Office suite for your operating system.
- 2. Open Your File: Launch WPS Spreadsheet and open your existing .xlsx workbook to continue working immediately.
- 3. Manage Data Effectively: Utilize the intuitive data tools in WPS to organize your tables without the layout conflicts experienced in other software.
Frequently Asked Questions
Why does my Excel External Data Properties setting keep resetting?
This is often caused by a software glitch in specific Excel versions or underlying corruption in the workbook's formatting. Testing the same query in a brand-new workbook can help determine if the file itself is the cause.
How do I prevent query tables from overwriting my other data?
The safest method is to physically move the query table to an isolated section of your worksheet, or place it on a dedicated sheet. This guarantees that newly expanded data rows will not collide with other content.
Is there a way to make the Excel query insert an entire row when new data is added?
Yes, within the External Data Properties dialog, there is normally an option to 'Insert entire rows for new data, clear unused cells'. However, if you are experiencing the bug where this dialog fails to save settings, you may need to use the workaround of moving the table entirely.
