How to Fix #REF Errors in Formula Columns After Power Query Refresh
Question details
The user needs to fix #REF errors that appear in formula columns adjacent to an Excel table after running a Power Query refresh.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Refreshing a Power Query connected to an Excel table that contains additional custom formula columns.
- Observed behavior
- Formula columns using structured references (e.g., [@[Column 1]]) break and display #REF errors when the query is refreshed.
Ensure you have a backup of your workbook before modifying formula structures or changing the Power Query data load properties.
Replace Structured References with Standard Cell References
Converting table-based structured references to regular cell references prevents them from breaking when Power Query overwrites the table data.
Power Query often drops and recreates table rows during a data refresh. This default behavior can break structured references (like `[@[Column Name]]`) in adjacent formula columns, resulting in `#REF!` errors because the originally referenced table object was temporarily removed.
Select the first cell in your custom formula column that currently displays the #REF! error.
Edit the formula in the formula bar. Instead of clicking the table cell to create a structured reference (e.g., `=[@[Sales]]*0.1`), manually type the standard cell reference corresponding to the grid (e.g., `=C2*0.1`).
Press Enter to save the formula, then drag the fill handle down to apply this standard reference formula to the rest of the column.
Go to the Data tab and click 'Refresh All' to run the Power Query again. Verify that the standard cell references remain intact without returning #REF errors.
Modify External Data Range Properties
Adjusting how Excel handles data refreshes can sometimes prevent the deletion of rows that causes structured references to break.
Experience Hassle-Free Spreadsheets with WPS Office
Dealing with Microsoft Excel's complex query refresh errors can be frustrating. WPS Office provides a free, lightweight, and highly compatible alternative. Enjoy a familiar interface, seamless migration, and robust support for all your standard Excel formulas and formats without the heavy resource usage.
- 1. Download and Install: Get WPS Office for free from the official website and install it on your PC or Mac.
- 2. Open Your Workbook: Launch WPS Spreadsheet and open your existing .xlsx file directly. No file conversion is needed.
- 3. Manage Data Seamlessly: Continue analyzing your data and writing formulas with full compatibility and reliable performance.
Frequently Asked Questions
Why does Power Query cause #REF errors in adjacent formula columns?
When Power Query refreshes, it typically deletes the existing table rows and recreates them with new data. This rapid deletion breaks structured references (like `[@[Column]]`) in adjacent formula columns because the original object they referenced is temporarily removed.
What is the difference between a structured reference and a standard cell reference?
A structured reference uses specific table and column names (e.g., `Table1[@Sales]`) and automatically adjusts dynamically with the table. A standard cell reference uses rigid grid coordinates (e.g., `C2`) and remains fixed to the worksheet grid regardless of table changes.
Can I stop Power Query from deleting rows during a refresh?
Yes, you can modify the External Data Properties of the query table. Right-click the table, select Table > External Data Properties, and choose 'Overwrite existing cells with new data, clear unused cells' instead of allowing it to insert or delete rows.
