This is a bit of a strange one to get your head round, but if you cut and paste cells into cells that your formula refers to you, you will get the #REF! error. Reason 2: You Have Cut & Pasted Cells Over Cells That Your Formula Refers To Please note you also delete cells when you delete an entire column or row. This is different to deleting the data in cells with the Delete key on your keyboard which will not cause the #REF! error. So the error will occur if you actually delete cells. You are then asked what to do with surrounding cells. Deleting cells requires you to right-click on a cell or range of cells and select Delete from the shortcut menu, as shown below. If you were to delete the two cells – that is the cells, not just the data, you would get the #REF! error. The formula refers to two cells in column M. The commission calculation is shown below. Take this scenario where we are recording sales data and calculating commission due. If you have deleted the cells your formula refers to you will get the #REF! error. Read our tutorial on the #REF error or if you would prefer watch our video tutorial at the end of this page.ĬLICK HERE TO GET 5 FREE EXCEL TEMPLATES Reason 1: You Have Deleted the Cells a Formula Refers To There are number of reasons why this might be the case. Excel’s #REF! error normally indicates that the cell or cells you are referring to in a formula do not exist.

Invalid reference error in excel