What it means:

“#REF!” is Excel’s way of telling you that a formula used to point at something that has been deleted and no longer exists. This can occur if a single cell, or range of cells in the model has been deleted. Clearing the contents inside a cell will NOT cause this.

Typical causes:

To avoid this potential problem, you should never delete rows or columns unless you are confident they aren’t being used for anything. There are only a few types of assumptions that expect you to insert rows (deleting rows would be ok there), and these are clearly labelled. Deleting anywhere else is likely to break something.

A second common cause is a slip of the mouse. If, instead of highlighting by clicking inside a cell, the edge is clicked and dragged, Excel assumes that you want to move it. This is equivalent to CUTTING and pasting the cell. Any formula that referred to the cell that is overwritten no longer knows what to point at and a #REF! error is generated to tell you something is wrong.

If this happens by accident, and the cell you are overwriting is not blank, then a warning dialog box will appear “There is already data there. Do you want to replace it?”. Do NOT press enter, or click Ok. Instead press Escape, or click Cancel.

Unfortunately, if the cell was empty, then even though a formula somewhere else was using it, Excel will not warn you, and you will create a #REF! error (probably in the Formulas sheet).

A good safe rule to follow is never to CUT and paste, or DRAG cells within the model, always COPY and paste if you want to reuse values.

If this has already happened and you need to fix it, there are ways to do that yourself too… You probably first spotted the problem in the Dashboard sheet or P&L, as any formula that uses the broken cell will also show a #REF!, so it will cascade throughout the model.

This means that the total rows are unlikely to be the problem, and the problem is more likely to be caused by the cells being added up, or that they point to. So although you will have the impression that everything is suddenly broken, in fact it may be a single cell that got dragged over its neighbour. So don’t panic 🙂

How to fix it:

If you spotted the problem fairly quickly, then the good old “UNDO” is probably your best bet. Each time you use Undo, check back on the Dashboard sheet, and see if normality has returned.

If you run out of Undos and the problem remains, there are still a few options. Going back to an earlier saved version, or a bit of detective work will be needed.

For an earlier version, this does not have to be one you manually created days ago. If you have Autosave enabled (strongly recommend that the toggle switch at the very top left of the Excel application should be set to “On”) then Excel will be automatically saving versions every few minutes (Google Sheets will automatically have saved changes for you).

Next to the toggle is the Excel file name, and to the right of that is a down arrow (like a “v”). Click that and select “Version History” next to the clock symbol.

Now work your way back through the list of versions by clicking “Open Version” and checking when the #REF! errors disappear. “Save As” with a new file name, and carry on.

If it is important to keep recent changes, then detective work is another option… Although the cause of the problem was most likely created in assumptions, the broken formula is probably in the “Formulas” sheet.

Without “undo” being available, no change in Assumptions can fix the broken formula, so we will need to find and fix it in Formulas:

• Switch to that sheet and press CTRL + F (or CMD + F) to open the Find tool.

• Enter “#REF!” (without the quotes) in the “Find what:” box, in “Look in:” select the option to search in “Formulas”, untick “Match entire cell contents” and untick “Match case”.

• Now click “Find Next” button. Hopefully, this will have jumped to the first cell where the formula inside the cell has #REF! replacing a cell reference. e.g. is might say something like “=Assumptions!#REF!…”.

Great. You have found at least one of the problems, and you don’t need to directly edit that formula to fix it.

The model is designed so that every formula in a block is identical. This means that if you COPY a neighbouring cell that doesn’t have #REF! in its formula over the the ones IN THE SAME BLOCK that are broken, they will be repaired. Take care to pick a cell to copy that is far away from the broken area.

If the #REF! only appears in one row, pick a cell to copy from a different row in the same block. If it looks like you dragged cells to the right to create the error, pick a cell even further to the right to use to replace formulae, etc. Exceptions to “every formula is the same” are the total rows at the bottom of a block, and sometimes the leftmost column may be shaded or in a box to show it is different. Blank rows, are used to show when different blocks of formulae start.

Double check that the formula you are copying has the same structure as the broken one you are replacing. If “Find” didn’t find any issues in the Calcs sheet, first check that the search options were configured correctly, but then repeat the process on the Assumptions tab, then the Financials Tab.