Sheetgo

How to solve the #REF! error on spreadsheets

ref error google spreadsheet featured image
Written by
Holly Meikle
Whether you’re an advanced spreadsheet user, or you’re more comfortable with the more basic functions of spreadsheets, we’ve all experienced a #REF! error at some point. However, these errors pose a more difficult problem in a business environment, especially when you are sharing your files with other users, or collaborating on them simultaneously. If a #REF! error is left uncorrected, there is a high risk of losing entire columns, rows, or even sheets of data.

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:

  1. Losing the reference of a particular cell
  2. Losing the reference of a particular spreadsheet
Let’s explore these two scenarios in more detail and how to correct the #REF! errors in each.

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
You can rectify all of the above using the following methods. Let’s look at a use case as an example to show how to correct #REF! errors in these circumstances.

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.

ref error google spreadsheet 1
I have canceled all of the basketball orders across my stores. To keep my spreadsheet up-to-date, I delete the basketball column entirely. When I do this, #REF! errors appear.
ref error google spreadsheet 2
So, how can I rectify this? Let’s look at the two main ways I can solve my #REF! errors.

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.

  1. Undo your actions by clicking the Undo button in the toolbar, or using the shortcuts Ctrl + Z, or Cmd + Z for Mac.
  2. Your original data should appear. In this example, my basketball column has reappeared.
  3. 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.
ref error google spreadsheet 3

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!.

  1. Double click the cell containing #REF!
  2. 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:
=IFERROR(#REF!, “basketball orders canceled”)
ref error google spreadsheet 4
For more information on how to use the IFERROR function for #REF! Errors and other spreadsheet errors, take a look at our article on How to use the IFERROR function in Google Sheets.

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:

  1. Deleting a spreadsheet file that has been used within a formula in another spreadsheet file
  2. Sharing a spreadsheet with other users who do not have access to the other files referenced in a formula
Firstly, always make note of the sources that play a part in your spreadsheet formulas. You can do this by adding a comment to the cell containing the formula. That way, you know exactly which files you cannot delete without affecting your formulas.

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.

1. Open the Find and replace feature by using the keyboard shortcut Ctrl + H/Cmd + Shift + H, or head to Edit > Find and replace.
ref error google spreadsheet 5
2. When the box appears, type ‘#REF!’ into the Find box. To replace with blank cell, leave the Replace with field blank. Tick the box, Also search within formulas.
ref error google spreadsheet 7
3. Click on the Replace all button to apply. You should see a message notifying you of how many #REF! errors have been found and replaced.
ref error google spreadsheet 6
4. Click Done.

#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.

You may also like…