How to Prevent Excel Refresh from Moving or Deleting Adjacent Data
Question details
Users need a way to prevent manually entered data from being shifted, resized, or overwritten when an adjacent Excel query table is refreshed.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Updating or refreshing data in an Excel workbook containing query tables (Power Query or Microsoft Query) that have manually added columns next to them.
- Observed behavior
- Manually entered values placed next to a refreshed data table are overwritten or shifted out of alignment when the query table expands or changes its row order.
Confirm whether you are using Power Query or Microsoft Query, and ensure you have a backup copy of your current workbook before making structural changes to your tables.
Store Manual Entries in a Separate Table and Merge Data
The most reliable practice to avoid data displacement is to separate manually entered data from the refreshable query table and link them using a unique identifier.
When an Excel query table refreshes, it automatically adjusts rows to accommodate new or deleted data from the source. This breaks the alignment of any manual data typed directly into adjacent columns. To permanently solve this, you must keep manual data in a completely separate table and join the two tables together.
Ensure your main query source data has a unique identifier (like a Product ID, Invoice Number, or Employee ID) for each row.
In a separate worksheet, create a new table containing the unique ID column alongside your manually entered data columns. Press Ctrl+T to format it as a formal Excel Table.
Go to the 'Data' tab and select 'From Table/Range' to load your manual data table into Power Query. Use the 'Merge Queries' feature to join the refreshed source data query with your manual data query based on the unique ID.
If you prefer not to merge queries, keep the manual table separate and use VLOOKUP or XLOOKUP functions in columns next to your query table to safely pull the manual data matching the row's ID.

Adjust External Data Range Properties
Modify how Excel handles new data and surrounding cells in the Connection Properties to prevent overwriting existing cells.
Manage Your Spreadsheet Data Seamlessly with WPS Office
If complex Power Query refreshes in Microsoft Excel are continually misaligning your manual data, WPS Office offers a straightforward, lightweight, and highly compatible alternative for handling your daily spreadsheet tasks, data sorting, and lookup functions.
- 1. Download WPS Office: Visit the official WPS website to download and install the free office suite.
- 2. Open Your Spreadsheet: Launch WPS Spreadsheet and open your existing .xlsx workbook.
- 3. Organize Your Data: Use WPS Spreadsheet's built-in formatting and lookup functions to safely align your data columns.

Frequently Asked Questions
Why does my manually entered data get misaligned after an Excel query refresh?
When a query refreshes, Excel adds, removes, or sorts rows based on the incoming source data. Because manually typed adjacent cells are not logically connected to the query table's underlying data model, they remain stationary while the query rows shift, causing misalignment.
Can I permanently lock the adjacent columns to my Power Query table?
You cannot directly 'lock' manually typed columns to a Power Query table. The recommended method is to create a separate table for your manual entries that includes a Unique ID, and then join it with the query using Power Query's Merge feature or Excel lookup formulas.
Does this data shifting issue happen with Microsoft Query as well?
Yes, this behavior occurs with Microsoft Query, Power Query, and basic Web Data connections. The underlying cause is identical across all refreshable objects: manual cells are unaware of the structural row changes happening within the query table during a data refresh.




