How to Fix #REF! Error in Excel? 6 Easy Ways

Written by

This helps identify where the broken cell reference is. Using Trace Precedents – First, select the cell with the #REF! INDIRECT function returns a reference specified by a text string. Error because the reference to row 10 is broken. Fixing #REF errors in Excel is straightforward once you understand what causes them. Yes, you can use Excel’s Error Checking feature under the Formula tab to identify and fix #REF errors.

Copied Formula to a New Location

In the above example, I referenced column A in Sheet8 of the same workbook. After a few days, you rename the referenced worksheet. If any of the following issues occur with the source data, you’ll get #REF! In this case, you don’t see such an error note in the formula.

Download 100+ Important Excel Functions

In the image above, we have added up a few cells, and everything seems good. The guide below will teach you everything about diagnosing and fixing #REF errors in Excel. We hope this has been a helpful CFI guide on Excel errors. This will highlight every cell with the error in it.

Get the Excel File

This is why the error is called #REF! It occurs when a formula refers to a cell that is no longer valid. An error in Excel called “#REF! Excel errors are always undesirable.

” error in Excel and ensure that your formulas reference valid cells or ranges. This usually occurs when cells referenced by formulas are deleted or pasted over. “Control + Z” will restore deleted cells/rows/columns, but a sheet once gone is gone forever. This will cause the reference errors to be deleted from your formulas.

Why do #REF! errors often appear after deleting columns or rows?

The latter will save you a lot of time, although it may not show you how each reference error came about.
Fixing the col_index_num argument should fix the #REF error in VLOOKUP
One such common error is the #REF!
In the place of the LOOKUP value, there is a table range.
If you know what the reference should be, you can simply fix it manually.
Issues with OLE (Object Linking and Embedding), DDE (Dynamic Data Exchange), or macros can also cause the #REF!

This usually happens when a referenced cell in a formula has been deleted or moved. Whether you’ve deleted a row or column by mistake, or you’ve copied a formula incorrectly, this guide will help you resolve the issue quickly. Here, since you have used an explicit cell reference and it was deleted, Excel is returning a #REF error.

However, when writing this formula, if I mistakenly put 5 in place of a row or column number argument, Excel can’t calculate the cell due to a reference error. The INDEX function returns a value or reference of a cell located at the intersection of a specific row and column within a designated range. An #REF error (the “ref” stands for reference) 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. An invalid cell reference error occurs when a formula contains incorrect cell references. The error value itself plus the error options button, which is displayed in cells with error formulas, help identify the problem. This pesky error can pop up after removing a sheet, rows or columns, or copying a formula.

When copying cells from one range and pasting them into another range with a different size or shape, Excel might generate the #REF! However, like any formula in Excel, calculated references can also result in #REF! This function takes a text string as an argument and interprets it as a reference to a cell or range.

You need to consider that the #REF! Error in the Formula bar when you highlight the target cell. However, if you enter 4 instead of 2 (to find age) or 3 (to find city), you shall get the #REF! For example, in the above dataset, the value of C2 is calculated by multiplying A2 and B2. Find below various scenarios where you might expect the #REF! Errors, you typically need to identify the source of the invalid reference and correct it.

The reason is that Excel updates relative references based on the cell where the formula is pasted. After retrieving your deleted sheet, apply the necessary updates on the formulas. Deleting any of columns A, B or C causes the formula to return a #REF! In the column index number, we have mentioned 3, which is out of range of the table range, so the VLOOKUP returns the #REF! Therefore, we selected the table range from A3 to B8, which means we have chosen only two columns.

Common Scenarios Leading to #REF! Errors

This is the most common way for users to receive the #REF! There are many ways you can end up with a #REF! Errors you may encounter when dealing with a spreadsheet, and how you can rectify these to avoid the risk of losing important data. Let’s explore the most common types of #REF! With the step-by-step guide above, you should now be able to fix the #REF!

Why Cause #REF Error in Excel Or Which Situation Will Result In #REF Error?

This is because the formula doesn’t automatically adjust when a referenced cell is removed. However, this only removes the error—it doesn’t restore the original references. Error in Excel means that a formula is referring to a cell, range, or sheet that no longer exists. Let’s take a look at another example when the error occurred due to VLOOKUP containing invalid cell reference. A simple fix to this problem is to use a range instead of an explicit cell reference.

In the case of a deleted cell causing the error, you can fix it with Find and replace. If there is an error in the formula the text from the function is displayed instead of #REF! B) The formula refers to a cell that was deleted. Luckily, Excel has a handy tool called “Go To Special” that allows us to quickly search for cells with Excel errors.

How to solve the #REF! error on spreadsheets

The TRIM function removes the extra unwanted space. If we delete this extra space, we will get the result. So one extra character is in cell E4 compared to cell A4. But in cell E4, we have 12 characters. In the A4 cell, we have 11 characters.

Here, the INDEX function tries to return a %KEYWORD_VAR% value from the intersection of the 10th row and 20th column. This VLOOKUP function tries to return a value from the 5th column of a 3-column range. Excel can handle removing cells from named ranges automatically.

Understanding this error is key to fixing it.
In most cases, it doesn’t make sense to trap the #REF!
In other formulas, you can see the #REF!
If you delete a worksheet tab, and see #REF errors, your best option is probably to close the file and re-open the last saved version.
If you delete a named range referred to in your formula, you will encounter the #REF!

If you want to get rid of numerous #REF! An importrange alternative, more efficient and easier to manage 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.

Here, the summation formula used C5 and D5 as reference. Error after deleting a row or a column. Error in Excel is a message that is displayed when a formula refers to an invalid cell. The latter will save you a lot of time, although it may not show you how each reference error came about. 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. Although this function does not correct a #REF!

Stands for value or cell Reference Error. Read on to learn everything you need to know about #REF! However, the most common one is #REF!. Then you start to wonder, what does #REF!

Download Practice Workbook

Error being inserted into formula instead. Therefore, if you look at your Vlookup formula, you will clearly be able to see this. Errors in the formula.