Prevent Excel Cell References from Returning #REF! After Row Deletions
Question details
The user wants to prevent spreadsheet formulas from breaking and returning #REF! errors when the specifically referenced rows in the source data are deleted or modified.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Managing dynamic spreadsheets where source data rows are frequently deleted or refreshed, which causes direct cell references linked to those rows to break.
- Observed behavior
- Formulas utilizing direct references (such as =DATA!G1) return a #REF! error immediately after the targeted row is deleted.
Before updating your formulas, confirm the structure of your source data and ensure that columns are not frequently deleted, as the alternative functions rely on stable column references.
Use the INDEX Function to Preserve Cell References
Replace direct cell references with the INDEX function referencing the entire column. This ensures the reference remains intact even if specific rows are deleted.
A direct reference specifically points to the exact cell object. When that object is deleted, Excel loses the target and throws a #REF! error. The INDEX function bypasses this by looking at an entire column array and pulling the nth position, keeping your formula safe from individual row deletions.
Click on the cell where you want to output the referenced data.
Instead of typing a direct reference like =DATA!G1, type =INDEX(DATA!G:G, 1). This tells the spreadsheet to look at the entirety of Column G on the 'DATA' sheet and return the value in the first row.
Press Enter to apply the formula. Test its stability by deleting a row in the source data; the reference will update automatically to pull the new first row without causing a #REF! error.

Use Structured Table References
Convert your source data range into a structured table to make formula references more dynamic and resistant to layout changes.
Seamlessly Handle Advanced Formulas with WPS Spreadsheet
WPS Spreadsheet offers full compatibility with advanced functions like INDEX and structured tables. Build robust, error-free financial models and data reports without worrying about breaking references when data changes.
- 1. Open Your Data: Launch WPS Spreadsheet and open the workbook containing your dynamic data.
- 2. Apply Dynamic References: Select your target cell, type your formula using =INDEX(SheetName!Column:Column, RowNumber), and press Enter.
- 3. Audit Errors: Use the 'Error Checking' tool located under the 'Formulas' tab if you need to troubleshoot previously broken links and quickly correct them.

Frequently Asked Questions
What exactly causes the #REF! error in spreadsheets?
The #REF! error occurs when a formula refers to a cell that is no longer valid. This most commonly happens when cells, rows, or columns that are explicitly referenced in a formula have been permanently deleted or pasted over.
Can I use the INDIRECT function instead of INDEX to prevent #REF! errors?
Yes, a formula like =INDIRECT("DATA!G1") will prevent #REF! errors when rows are deleted because it evaluates a text string rather than a direct cell link. However, INDIRECT is a volatile function that recalculates every time any change is made, which can slow down large workbooks. INDEX is non-volatile and generally preferred for better performance.
How do I quickly find all #REF! errors in my workbook?
You can press Ctrl+F to open the Find dialog, type #REF!, and click 'Find All' to see a comprehensive list of every broken formula in your workbook. Alternatively, you can use the 'Go To Special' feature (F5 > Special) and select 'Formulas' with only 'Errors' checked.
Will undoing a deleted row fix a #REF! error?
Yes, immediately pressing Ctrl+Z to undo the deletion of a row will restore the referenced data and resolve the #REF! error. However, if you have already saved, closed, and reopened the file, the undo history will be cleared, and the error will remain.




