Power Query Problems

Prevent Excel Web Query References from Shifting in Refreshed Data

Jason MillerJason Miller Nov 12, 2026 870 views

Question details

The user needs formulas to remain aligned with refreshed web query results when Excel inserts new rows into the query output area.

Product
Microsoft Excel Power Query and web query results
Device & OS
not provided
Scenario
A workbook contains formulas that reference cells in a web query result range, and the query may add rows during refresh.
Observed behavior
Direct references such as =D19 can shift when Excel inserts rows, causing formulas to point to a different result than intended.
Before you start

Identify the query output column and the row or field your formula must return. Check whether the query is loaded into an Excel Table before choosing between an INDEX reference and a structured reference.

Solution 1Recommended

Use INDEX to keep a fixed row reference

Use this method when the formula must return a specific row from a query result column even if Excel inserts rows around the reference.

A direct reference such as =D19 can be adjusted by Excel when rows are inserted. INDEX evaluates the requested position in the column, so =INDEX(D:D,19) returns the value currently located in row 19 of column D.

1
Select the formula cell

In Excel, click the cell that currently contains a direct reference such as =D19.

2
Replace the direct reference

In the formula bar, enter =INDEX(D:D,19), replacing D with the query result column and 19 with the required row number.

3
Confirm the result

Press Enter, then refresh the query from Data > Refresh All and verify that the formula still returns the intended row.

Check the row definition: INDEX preserves the requested position in the specified range; if the desired record is identified by a key rather than a fixed row, use a lookup based on that key instead.
Free Microsoft Office alternative

Use WPS Office for compatible spreadsheet formula workflows

This issue is specific to Microsoft Excel query refresh and row-insertion behavior, so WPS Office does not directly change Excel Power Query references. WPS Office is a free, lightweight alternative for opening, editing, and sharing compatible spreadsheet files with a familiar interface and an easy migration path.

  1. 1. Download WPS Office: Select Download WPS Office Free and install WPS Office on your computer.
  2. 2. Open the spreadsheet: Launch WPS Spreadsheet, choose Open, and select the compatible .xlsx or .xls workbook.
  3. 3. Review formulas and ranges: Click the formula cells and inspect the formula bar to replace fragile direct references with INDEX-based formulas where appropriate.
Open and edit common Excel .xlsx workbooks with a familiar spreadsheet interface.Use formulas such as INDEX to reduce dependence on shifting direct cell references.Work with lightweight spreadsheet tools without the full Microsoft Office subscription.Maintain compatibility with Microsoft Excel .xlsx and .xls file formats.Share updated spreadsheets with Excel users through familiar file formats.
microsoft office alternative - wps office

Frequently Asked Questions

Why can a formula such as =D19 move after an Excel web query refresh?

Excel may insert rows into the query output range during refresh and automatically adjust direct cell references. As a result, =D19 can point to a different row than it did before the refresh.

How does =INDEX(D:D,19) help prevent a shifting web query reference?

INDEX returns the value at the requested position in column D. Entering =INDEX(D:D,19) tells Excel to return the current value in row 19 rather than preserving a direct cell link that may be adjusted during row insertion.

Should I use an Excel Table instead of INDEX for query results?

Use an Excel Table when the query output changes regularly and formulas should follow named columns as rows are added or refreshed. Structured references such as =QueryTable[Amount] are generally easier to maintain than fixed cell addresses.

Can WPS Office refresh Microsoft Excel Power Query results?

This behavior belongs to Microsoft Excel's Power Query and web query features. WPS Office can open and edit compatible spreadsheet files, but it is not a direct fix for Excel-specific query refresh behavior.