Are you frustrated to find that your work seems inaccurate, and now you’ve got a nasty looking #REF! error message popping up in your Microsoft Excel formulas? Well, don’t panic. We’re here to tell you what a #REF! error means, how to find them, and how to fix them.
For any additional information or questions, see our FAQ section below.
Table of Contents
What does a #REF! error in Excel actually mean?
A #REF! error simply means that some of the formulae you have created in excel (e.g. the formula you use in one cell to add up all of the data from another group, row, or column of cells) is now incomplete because one or more of the cells it ‘refers to’ has been deleted.
Let’s say that you input a formula into one cell, commanding Excel to subtract one set of data from another and present a final number. If you were to delete one or more of the cells in the groups which this formula is using to calculate its final number, you will encounter a #REF! Error. In other words, Excel can no longer REFerence the cells you’re asking it to because one or more of them no longer exists.
How do I find #REF! errors in Excel?
If it’s not clear to you exactly how to find all of the #REF! errors that exist in your document, the process is easy. Here are two methods:
- Open the Excel spreadsheet in which you believe you have #REF! errors, press F5 (this is the ‘Go To’ function), then select ‘Special’. When the pop-up menu appears, select ‘Formula’ and then tick/check only the box next to ‘Errors’. Press ‘OK’ and you will be shown all of the cells which have a #REF! error in them.
- Open the Excel spreadsheet in which you believe you have #REF! errors, and hit CTRL+F on your keyboard (this is the ‘Find’ function). Type ‘#REF!# in the ‘Find’ field and hit ‘OK’. All of the cells with #REF! errors in them will be highlighted.
How do I fix #REF! Errors in Excel?
- Open the Excel spreadsheet in which you believe you have #REF! errors, and hit CTRL+F on your keyboard (this is the ‘Find’ function).
- Click the tab reading ‘Replace’
- Type ‘#REF!’ into the ‘Find’ field, and leave the ‘Replace’ field blank
- Lastly, click ‘OK’. All of the #REF! errors in your entire spreadsheet will be removed, thus fixing the formulaic problem – your formulas should all now work as intended.
- Note: Only use this fix method if you are sure that you do wish to remove all #REF! Errors – we recommend first checking that the error notification isn’t alerting you to some error in data input which you may have made
And that’s all there is to the meaning of the #REF! Error in Microsoft Excel, how to find them, and how to fix them. For all other information and queries, see our FAQ section below.
FAQs
Why am I seeing a #REF! error in my Excel spreadsheet?
As we discussed above, a #REF! error in Microsoft Excel – the world’s most popular data-entry and spreadsheet software – means that formula you have previously entered is unable to function because it references a cell that no longer exists. In other words, you have deleted a cell with a number in it that was being used in a calculation by another cell, or group of cells.
For example, if you keep tabs on your finances (your incomings and outgoings) with Excel, and have a ‘Total’ cell which calculates all of your expenses in a given month, say, and you delete one of the cells which that ‘Total’ cell’s formula was referencing, you will likely experience a #REF! error in that formula. Thankfully, it is easily solvable. To learn how simply follow our step-by-step guide below.
How can I avoid getting a #REF! error in Excel in future?
#REF! Errors happen, it’s just a fact of life when using Microsoft Excel, and one which can be quickly solved. However, we do understand how frustrating it can be, especially if you’re planning to present your work to colleagues or classmates, only to find that your formulas don’t play out thanks to the error.
In order to avoid getting a #REF! error in the future, it’s best to only delete cells that are not currently a part of any formula elsewhere in your spreadsheet. Alternatively, give the cell a blank, or zero value, or edit the formula to exclude the cell you’re about to delete before you delete it. Any of these options will help you to avoid accruing #REF! errors in the future.
If I delete rows or cells causing a #REF! error, can I undo the deletion to fix the problem?
Yes, absolutely. If you accidentally delete a cell, triggering a #REF! error, you can quickly undo that deletion by clicking the backward-facing undo arrow icon, or hitting CTRL+Z on your keyboard (the ‘Undo’ shortcut).
One thing worth noting, however, is that it’s likely you deleted that cell or those cells for a reason. Fixing the #REF! error by undoing your deliberate deletion isn’t going to solve the issue completely since you’ll be left with data you don’t want in your spreadsheet. We recommend adjusting the formula pertaining to the data/cells you wish to delete, before deleting them. Doing so will ensure you avoid seeing a #REF! error pops up.
How serious is a #REF! error in Microsoft Excel?
Thankfully, not very. In fact, it really isn’t a big deal at all. All a #REF! Error means is that one or more of your formulas are unable to run properly because some of the cells they are referring to have been deleted. What a #REF! error does mean is that your data may be inaccurate, thanks to the broken formulae until you correct the error. Thankfully, it’s easy to do so. Simply follow our tutorials above.
