Data Import & Export

Fix Excel Error: Selection Overlaps External Data Range

Maira Mehtab Sep 24, 2026 869 views

Question details

The user needs to keep an external data connection active while converting old Microsoft Query results into an Excel table, but encounters an overlapping range error.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Converting an older Microsoft Query result to an Excel table without breaking the external data connection.
Observed behavior: Excel blocks the conversion and displays an error message stating that the selection overlaps an external data range.

Before you start

Before modifying the range, visually inspect the worksheet to identify where the imported Microsoft Query data begins and ends so you know the exact dimensions of the external connection.

Solution 1Recommended

Include Hidden Columns in the Table Selection

Expand your highlighted selection to include any hidden columns that are part of the external data range before attempting to convert it to a table.

This error typically occurs when the original Microsoft Query result includes columns that have been hidden in the worksheet. Because Excel requires the exact boundaries of an external connection to convert it into a table, missing those hidden columns triggers the overlapping error.

Reveal Hidden Columns

Highlight the visible columns surrounding your data range, right-click the column headers at the top of the worksheet, and select 'Unhide' to display any missing data columns.

Select the Complete Range

Click and drag to select the entire expanded data range, making sure all newly unhidden columns and rows belonging to the Microsoft Query result are included.

Convert Range to Table

Navigate to the Insert tab on the ribbon and click 'Table' (or press Ctrl+T). Check the box for 'My table has headers' if applicable, and click OK to finalize the conversion while preserving the external connection.

Preserved Connection: By selecting the complete boundaries of the original query, Excel correctly formats the data as a table without severing the link to your external database.

Solution 2

Consult the Microsoft 365 Admin Center for Legacy Issues

If the overlapping data range error occurs persistently on older workbook files despite selecting all hidden columns, contact support.

Free Microsoft Office alternative

Try WPS Office for Seamless Data Management

Tired of dealing with legacy external connection errors and complex query glitches in Microsoft Excel? Switch to WPS Office. It provides a highly intuitive spreadsheet application that handles standard Excel files flawlessly without the frustration of overlapping range errors.

1. Download and Install: Visit the official WPS website to download the free WPS Office suite for your operating system.
2. Open Your Workbooks: Launch WPS Spreadsheets and directly open your existing Excel files. All standard formats and tables are natively supported.
3. Manage Tables Easily: Use the intuitive Insert Table feature to organize your datasets without encountering legacy overlapping connection errors.

Seamlessly manage imported data and tables without legacy query conflicts.Fully compatible with Microsoft Excel formats including .xlsx, .xls, and .csv.Lightweight design ensures fast loading even with large external datasets.Completely free alternative featuring a familiar spreadsheet interface for zero learning curve.

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel say my selection overlaps an external data range?

This happens when your highlighted selection misses hidden columns or rows that belong to the original Microsoft Query external connection. Excel requires the complete, exact range to safely convert the queried data into a table format.

How do I reveal hidden columns containing external data?

Select the entire worksheet by clicking the triangle in the top-left corner (between row 1 and column A), navigate to the Home tab, click Format in the Cells group, select Hide & Unhide, and choose Unhide Columns.

Can I remove the external connection to bypass the overlapping error?

Yes. If live updates from the external database are no longer needed, go to the Data tab, select Queries & Connections, right-click the specific connection, and choose Delete. The data will convert to static text, allowing you to easily format it as a standard table.

Excel & Data Problems