Excel keeps converting to #REF!

Occasional Visitor

How can I prevent my excel spreadsheet from converting some of the fields to #REF! once it is saved and reopened the following day? 

1 Reply

Hi @AshleyT93,

An #REF error  is the message Excel displays when a formula references a cell that no longer exists, usually caused by deleting cells that a formula is referring to. Or perhaps the cell is in another workbook that is no longer accessible (shared drive, deleted workbook...?)

So if your formulas all render the proper info at a given time, you can stop automatic calculations (but that will affect every workbook moving forward). Select File > Options> Formulas and under Calculation options, select "Manual" radio button. Then when you need your workbook to calculate everything, hit "F9".

The best method though is probably to figure out what are your cells trying to reference and ensure your workbook can access it at all time.