Excel #REF Error
The #REF! excel error appears if a formula refers to an invalid cell. Most often, this occurs when formulas reference the cells, and the cells get pasted over or deleted. The error occurs in a formula that has a cell reference that is invalid or because there was removing of columns, rows, or sheets. To avoid this, one should not delete a range that has explicit cell references in formulas (Ayer, 2017).
Resolving The Error
To prevent #REF error it is important you do not allow the error to occur in the first place. In cases where it occurs, ensure before pasting a set of cells there are formulas that make reference (hence REF) to the cells you are deleting. Besides, when you delete cells, double checking the formulas that have been referred to those cells is basically important. If you delete some cells accidentally, you could restore them by clicking the Undo button or press ctrl +Z.
Clearing multiple #REF! errors
A #REF error should be fixed immediately. To quickly fix the error from excel worksheet, one can use Control+ H to open the Find and Replace dialog then in the Find input area enter #REF and the Replace input area should remain black (Akbar & Putra, 2017).
References
Akbar, A., & Putra, Z. P. (2017). Excel Functions and Formulas Pocketbook.
Ayer, G. (2017). Excel in Excel®!. Energy Engineering, 114(1), 19-38.