How to fix the #ref error
We’ve now seen the reasons why the #REF error might occur in Excel. Let’s now jump into the solutions to these errors.
Use In-Built Excel Functions
Here’s the simple addition example that we saw only in the previous section:
Instead of writing the formula = A2 + A3 + A4 had you used the SUM function, the results would’ve been different 🌟
Check out here:
Instead of using the “+” operator, we have applied the SUM function to add up the numbers.
Now, if you delete Row A3 altogether, this is what happens 👀
No more #REF errors. The SUM function automatically adapts the cell references and changes the formula to = SUM (A2:A3)
And so, most of the time when operators would land you into errors, Excel functions might save you 🦺
Do not delete linked rows/columns/sheets
Do not delete rows, columns, or sheets that might bear references to any of your formulas.
This might delete the abundant data from your spreadsheet. But your formulas would go crazy.
If you delete a row, a column, or a worksheet and you get the #REF error, don’t lose your calm already 😩
There are two ways how you may fix this:
- Hit ‘Control + Z’ to undo your last action. This will restore the deleted cells, and all your formulas will come back to life.
Search for the data that’s still relevant to your formulas, and do not delete it.
Be very careful while you delete sheets from your workbook. “Control + Z” will restore deleted cells/rows/columns, but a sheet once gone is gone forever.
- Remove the cell or range reference from your formulas and use simple values before you delete any abundant data.
You can do this by copying formulas and pasting them as simple values📃
Do not copy formulas with relative references
Remember what happened when we copied the formula from one cell to another?
The cell references in the formula (A2, A3, and A4) were relative references. As the formula changed place, Excel updated the cell references which resulted in a #REF error.
You can help with this as follows:
- Select the cell containing the formula.
- Click on a cell reference (say A2) and press the F4 key.
This will convert the formula into an absolute reference.
- Do this for every cell reference.
Your formula must look like below:
- Press “Enter” and you’re good to go.
Try copying/pasting the formula to a different location now 🤩
When converted into absolute references, cell references do not change when the formula is moved.
So the results remain consistent, and you get no more #REF errors.