Power Query Problems

How to Prevent Excel Refresh from Moving or Deleting Adjacent Data

Muhammad TalhaMuhammad Talha Nov 2, 2026 869 views

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.

How to Prevent Excel Refresh from Moving or Deleting Adjacent Data
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.
Before you start

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.

Solution 1Recommended

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.

1
Create a unique ID column

Ensure your main query source data has a unique identifier (like a Product ID, Invoice Number, or Employee ID) for each row.

2
Create a new table for manual data

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.

3
Link the data using Power Query

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.

4
Alternative: Use lookup formulas

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.

Store Manual Entries in a Separate Table and Merge Data
Data Alignment Maintained: By joining data via a unique ID, your manual entries will correctly align with the refreshed source data, even if rows are sorted or new rows are added.
Free Microsoft Office alternative

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. 1. Download WPS Office: Visit the official WPS website to download and install the free office suite.
  2. 2. Open Your Spreadsheet: Launch WPS Spreadsheet and open your existing .xlsx workbook.
  3. 3. Organize Your Data: Use WPS Spreadsheet's built-in formatting and lookup functions to safely align your data columns.
Fully compatible with Microsoft Excel file formats (.xlsx, .xls, .csv).Reliable VLOOKUP, XLOOKUP, and data matching features without complex background query setups.Free to download and use with a highly familiar user interface.Lightweight application that opens large data files rapidly and securely.
microsoft office alternative - wps office

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.