'%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 Table Design Options Not Staying Selected in Power Query
Question details
Users are unable to keep the 'Overwrite existing cells with new data and clear unused cells' option selected in Excel Table Design properties.
- Product
- Microsoft Excel
- Device & OS
- Windows
- Scenario
- Importing or refreshing a data query in Excel and configuring Table Design properties to preserve adjacent formulas.
- Observed behavior
- The Table Design setting automatically reverts or unchecks itself after a query refresh, causing existing formulas to be removed and reference errors to appear.
Verify your current version of Microsoft Office and check if the issue occurs across all Excel files or just one specific workbook to isolate whether it is a widespread update bug.
Contact Microsoft 365 Support for Update Issues
Since this behavior is frequently caused by a bug introduced in recent Office updates, contacting Microsoft Support through the Admin Center is the most effective resolution path.
This specific Table Design property issue is known to occur across multiple Windows and Office installations simultaneously, indicating it is likely an application-level bug rather than user error.
Take clear screenshots or a brief screen recording demonstrating the Table Design property unchecking itself immediately after you refresh the Power Query.
Open your web browser and log in to the Microsoft 365 Admin Center using your administrator credentials.
Navigate to the Support section in the left sidebar, click on 'New service request', and provide your screenshots along with a detailed description of the table property bug.
Recreate the Query Connection
If the issue is isolated to a single corrupted file rather than an Office update, completely rebuilding the query may bypass the bug and allow the setting to save.
Try WPS Office for Stable Spreadsheet Management
If you are frustrated by recurring bugs from Office updates disrupting your workflow, consider switching to WPS Office. It provides a lightweight, highly compatible, and user-friendly alternative for handling complex spreadsheets and data without unexpected setting resets.
- 1. Download the software: Visit the official WPS Office website and download the free installation package for your Windows system.
- 2. Install WPS Office: Run the installer and follow the on-screen prompts to quickly set up the application.
- 3. Open your spreadsheet: Launch WPS Spreadsheet and open your .xlsx file to continue managing your data smoothly.
Frequently Asked Questions
Why do my Excel Table Design properties keep resetting?
This behavior is frequently tied to bugs introduced in recent Microsoft Office updates that affect how Power Query handles external data properties. It can also occasionally stem from localized file corruption within a specific workbook.
Where do I find the 'Overwrite existing cells' option in Excel?
After loading a query into a table, click anywhere inside the table. Navigate to the Table Design tab on the top ribbon, click Properties, and check the External Data Properties dialog for the data overwrite and cell clearing options.
How do I stop reference errors when refreshing a Power Query?
Ensuring the table property is set to 'Overwrite existing cells with new data, clear unused cells' typically prevents the deletion of adjacent formula rows, thereby avoiding #REF! errors. If the setting refuses to stick due to a software bug, you may need to escalate the issue to Microsoft Support or rebuild the query.
