Prevent Excel Web Query References from Shifting in Refreshed Data
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.
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.
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.
In Excel, click the cell that currently contains a direct reference such as =D19.
In the formula bar, enter =INDEX(D:D,19), replacing D with the query result column and 19 with the required row number.
Press Enter, then refresh the query from Data > Refresh All and verify that the formula still returns the intended row.
Load the query into an Excel Table and use structured references
Use this approach for workbooks that need formulas to expand with refreshed query columns and rows more reliably.
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. Download WPS Office: Select Download WPS Office Free and install WPS Office on your computer.
- 2. Open the spreadsheet: Launch WPS Spreadsheet, choose Open, and select the compatible .xlsx or .xls workbook.
- 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.

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.




