Data Import & Export

Fix Excel External Data Properties Not Saving Insert Entire Row Setting

Maira Mehtab Sep 22, 2026 868 views

Question details

The user needs to configure an Excel query table to insert complete rows when new records are added, but the specific setting fails to save.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Retrieving external data (such as Brazilian inflation data) into an Excel query table where the dataset frequently expands and requires new rows to be inserted automatically.
Observed behavior: The 'Insert entire row for new data, clear unused cells' option in the External Data Properties dialog resets and fails to apply after the dialog box is closed.

Before you start

Ensure your Excel workbook is fully saved and check if there are any hidden rows, merged cells, or data blocks directly beneath your current query table.

Solution 1Recommended

Clear Space and Prevent Table Overlap

Move the query table to a completely empty worksheet or clear the area beneath it to ensure Excel has enough unobstructed space to insert entire rows.

Excel will often refuse to save the 'Insert entire row' setting if it detects that expanding the table might overwrite adjacent data, blank formatted columns, or overlapping tables. Moving the query output to an unrestricted area usually resolves this.

Select the query table

Click and drag to highlight the entire query table, or click inside the table and press Ctrl+A.

Move the table

Cut the table (Ctrl+X) and paste it (Ctrl+V) into a new, blank worksheet within the same workbook.

Modify data properties

Right-click anywhere inside the relocated query table and select 'Data Range Properties' or 'External Data Properties' from the context menu.

Apply the row setting

Check the box for 'Insert entire row for new data, clear unused cells' and click OK to close the dialog.

Refresh and test

Go to the 'Data' tab and click 'Refresh All' to import new records and verify if the setting now persists.

Checking for hidden obstructions: Even a seemingly empty cell with a background color or data validation rule below the table can act as a blockage. Using a brand new worksheet is the most reliable way to test this.

Solution 2

Submit a Microsoft Support Ticket

If the setting continues to reset after providing ample space, the issue may be a known software bug requiring official support.

Free Microsoft Office alternative

Try WPS Office for Seamless Data Management

If Microsoft Excel continues to experience persistent bugs with query table settings and data properties, WPS Office provides a highly compatible, free, and lightweight alternative. It ensures seamless spreadsheet management with a familiar interface, allowing you to handle large datasets without the frustration of glitchy property dialogs.

1. Download the software: Visit the official WPS website to download and install WPS Office for free.
2. Open your workbook: Launch WPS Spreadsheet and open your existing Excel workbook (.xlsx) directly.
3. Manage data flawlessly: Continue managing your tables and importing data seamlessly with WPS Office's reliable tools.

Fully compatible with Microsoft Excel formats including .xlsx, .xls, and .csv.Robust data import and table management features for complex datasets.Lightweight design ensures fast loading times even with large amounts of external data.Familiar user interface requires zero learning curve for Excel users.

microsoft office alternative - wps office

Frequently Asked Questions

Why does the External Data Properties dialog reset in Excel?

This typically occurs when Excel detects obstructions like adjacent data, formatted cells, or other tables below the query table, preventing it from safely expanding. It can also be caused by temporary software bugs in specific Microsoft 365 builds.

How do I access External Data Properties?

Click anywhere inside your imported data table, navigate to the 'Data' tab on the ribbon, and select 'Properties' in the Connections group. Alternatively, right-click the table and choose 'Data Range Properties' from the menu.

Will moving my query table break the external data connection?

No. Cutting and pasting a query table to a new location or a different worksheet within the same workbook preserves the existing external data connection and query settings.

Can I force Excel to overwrite existing data when a table expands?

Yes. In the External Data Properties dialog, you can select 'Overwrite existing cells with new data, clear unused cells' instead of inserting new rows, provided you are okay with losing the data situated immediately below the table.

Excel & Data Problems