The best method is to press Ctrl + F (known as the find function) and then select the tab that says Replace. Type “#REF!” in the Find field and leave the Replace field empty, then press Replace All. This will remove any #REF Excel errors from formulas and thus fix the problem.
How do I return 0 if error?
Evaluate the denominator for 0 or no value error value, otherwise calculate the formula. For example, if the formula that returns the error is =A2/A3, use =IF(A3,A2/A3,0) to return 0 or =IF(A3,A2/A3,””) to return an empty string.
How do I convert ref in Excel?
- Select the cell that contains the formula.
- In the formula bar. , select the reference that you want to change.
- Press F4 to switch between the reference types.