Let’s explore the most common types of #REF! errors you may encounter when dealing with a spreadsheet, and how you can rectify these to avoid the risk of losing important data.
Why am I getting #REF! In spreadsheets?
There are many ways you can end up with a #REF! in one of your cells. It can result from accidentally deleting a row/column, putting incorrect references into a formula, or moving formulas/sheets to a new location. Although #REF! errors can appear in any number of situations, they will always originate from one of two places:
- Losing the reference of a particular cell
- Losing the reference of a particular spreadsheet
How to solve #REF! When you have lost the reference of a particular cell
This is the most common way for users to receive the #REF! error. Losing the reference of a particular cell can happen in many different ways when using formulas in your spreadsheets:
- Deleting an essential cell
- Deleting an essential row/column
- Deleting an essential sheet
- Copying and pasting your formula to another cell/sheet
Imagine that I am managing the orders of my sportswear chain. The screenshot below shows my spreadsheet containing the number of orders placed for each product, for each store location. I have used a simple SUM formula to calculate the total number of orders for each store.
Undo actions and reinsert inputs
The simplest way to rectify this error is to undo your actions to revert back to your original spreadsheet and find another way to fulfill your objective.
- Undo your actions by clicking the Undo button in the toolbar, or using the shortcuts Ctrl + Z, or Cmd + Z for Mac.
- Your original data should appear. In this example, my basketball column has reappeared.
- You can choose to find an alternative solution to your problem. In this case, I could replace the cells in the basketball column with a zero value ‘0’, so that the formula remains valid and I get the result I’m looking for.
Use the IFERROR function
Although this function does not correct a #REF! error, it is extremely useful to employ into your spreadsheets when choosing to share them with other users.
Not only does it allow you to clean your spreadsheet up from multiple errors, but you can adjust the formula in order to leave a message for future users. This could be to let them know there is a problem with the formula. As a result, things are clearer for newcomers to the spreadsheet, rather than just being presented with a #REF!.
- Double click the cell containing #REF!
- Rewrite your formula using the IFERROR. You can choose to replace your errors with a blank cell or a zero value, or you can opt to add a message to alert other users of the issue. For example, in my formula, I will use the following:
How to solve #REF! When you have lost the reference of a particular spreadsheet
There are two main ways you may lose the reference of a particular spreadsheet:
- Deleting a spreadsheet file that has been used within a formula in another spreadsheet file
- Sharing a spreadsheet with other users who do not have access to the other files referenced in a formula
What’s more, you can avoid #REF! errors completely by making a copy of the files referenced in the formula and adding them to the folder where your current spreadsheet is located. That way, even if the original sources are deleted, you have your own copies that will allow the formula to work. This is also a great solution when sharing your spreadsheet with other users – they will then have access to all the data within that folder.
How to remove multiple #REF! errors at once
If you want to get rid of numerous #REF! errors, you have two options: you can correct each of them manually using the techniques shown above, or you can target all of them at once. The latter will save you a lot of time, although it may not show you how each reference error came about.
If you want to target all of your #REF! errors at the same time, follow these steps.
#REF error Google spreadsheet
And there you have it! When it comes to #REF! errors, prevention is the best cure. When dealing with these errors within a single Google Sheets file, it’s often best to undo your actions to revert the changes and find an alternative solution. On the other hand, if you’re dealing with #REF! errors that span across multiple spreadsheets in different locations, make a note of the different files involved in your formulas, and make copies to avoid risking worse repercussions. Ultimately, the best solution is to get rid of your #REF! errors by using the simple Find and replace feature.
Interested in learning more about reverting changes and protecting your sheets from any accidental edits? Check out our article on How to view the edit history in Google Sheets.
Alternatively, discover related blog posts below!
Editor’s note: This is a revised version of a previous post that has been updated for accuracy and comprehensiveness.