Each step will guide you through identifying and correcting the error in your worksheet. Alternatively, consider using INDEX/MATCH for more flexibility and fewer errors. Always double-check the size of your lookup range and ensure your column index stays within that boundary. So while it’s useful for bulk cleanup, it’s not a true fix unless you’re okay with leaving those cells empty or handling them manually afterward.

And as we delete cell A3, we have no more valid references. #REF error is Excel’s way of saying that the cell reference for this formula is no more valid. This will remove any #REF Excel errors from formulas and thus fix the problem. In column D we show the formula adding cells D3, D4, and D5 together, to get 75.

There’s a chance that you will get the #REF! In the above example, we have specified three as col_index_num in the formula. While using VLOOKUP, specify the column from which you want to get the value. Understanding this error is key to fixing it.

#REF Error in Excel from deleting a row, a column, or a sheet

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.

As a result, things are clearer for newcomers to the spreadsheet, rather than just being presented with a #REF!. This could be to let them know there is a problem with the formula. 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. I have used a simple SUM formula to calculate the total number of orders for each store.

• Error disappears, and cell E2 shows the correct value.
• In the second schema, the description and default properties are ignored, so this schema ends up exactly the same as the referenced Date schema.
• Suppose, you’ve created an external reference to a workbook within a formula in your current worksheet.
• The best method is to press Ctrl + F (known as the find function) and then select the tab that says Replace.
• The reason is that the incorrect column index number – 5 was used.

Other Possible Reasons for Excel Reference (#REF!) Errors

However, if one of the arguments in the INDIRECT function is from another workbook on your PC, then you might see the #REF! For instance, in the above dataset, I’m trying to populate the total revenue for the North region using the INDEX function. VLOOKUP is one of the most popular Excel functions where you often get the #REF!

• See the Excel Cell References page for more information on cell references.
• An importrange alternative, more efficient and easier to manage
• Then edit the formula(s) to exclude the column you want to delete, moving data if needed.
• Here, since you have used an explicit cell reference and it was deleted, Excel is returning a #REF error.
• To recover the missing link, you need to recreate the worksheet or restore it from a backup if you have one.

In the table below, you have sales data for different customers for 4 quarters and a sum formula used to calculate the total sales. Error within formulas used in the cell. Sometimes, using cut and paste instead of copy and paste can also break references and trigger the dreaded #REF! As a placeholder to let me know that it can’t find the referenced data, and the formula can’t execute properly until it’s fixed. Error in Excel signifies that a formula is referencing a cell or range that no longer exists.

How can I prevent #REF errors?

It’s a widely used application with various helpful functions. Many people rely on Excel for organizing, analyzing, and presenting data. This guide sheds light on what causes the #REF! Each video comes with its own practice worksheet. In most cases, it doesn’t make sense to trap the #REF!

How to Fix #REF! Error in Excel (Step-by-Step)

Suppose you deleted Row 3, causing the #REF! Usually, the cell is not valid because it was deleted or pasted over. That way, even if the original sources are deleted, you have your own copies that will allow the formula to work. 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. Armed with this understanding, you can use Excel with confidence and prevent errors that might affect your data analysis.

Example No. 1: VLOOKUP function

Now, if any of these linked cells go missing, Excel won’t be able to calculate the target cell. That’s because all the Excel functions require referencing to a cell for input values. Some of these errors are #SPILL!

The total sales was calculated using a formula. The dataset showcases sales data. As a result, the error is fixed. To fix #REF! You can also use the IFERROR Function to get rid of #REF! Error disappears, and cell E2 shows the correct value.

Once you’ve identified the cells, the next step is to figure out why the error is there. Identify the cell or cells that show the #REF error. This error typically pops up when a formula refers to a cell that isn’t valid anymore. Errors in VLOOKUP typically occur when the column index number is greater than the number of columns in your table array.

Try our AI Formula Generator

The lookup table has 3 columns only. As a result, all the cells with the #REF! Don’t delete the whole row, and you won’t produce the #REF! Also, if you are specifically referencing cells and want to delete some of them, just delete the cell values.

Understanding the #REF! Error

Follow the below steps to rectify the error. But still, we got an error a #N/A. In Table 2, we have applied the VLOOKUP formula and tried to extract the values from Table 1. We have a sales report table in Table 1. Before fixing this problem, we need to know why it is giving an error as #N/A.

IFERROR only allows you to remove the #REF! If you anticipate the #REF! Errors cannot be resolved, or you anticipate multiple #REF! In situations where #REF! To recover the missing link, you need to recreate the worksheet or restore it from a backup if you have one. Regrettably, it is not possible to undo the deletion of a worksheet.

When one of the cell references used in the formula is deleted (here cell E4), Excel is unable to calculate the value and returns an error. The most common is deleting cells, rows, or columns that my formulas depend on. These formulas can reference other columns in the same row or even perform calculations across multiple rows within the table. If you delete any %KEYWORD_VAR% column or rows referred to in any of these structured references in a table, you’ll get the #REF! This could happen due to several reasons, such as deleting a referenced cell or range, moving cells, renaming sheets, or using incorrect cell references in formulas.

If you want to be a master of Excel, please check out all our Excel Resources to learn the most important formulas, functions, and shortcuts. The next image shows what happens if row 4 is deleted. It is caused by the length of data entered into a formatted cell. The green triangle indicates that the cell contents violate one of Excel’s error checking rules. If you supply a string that can not be a reference, you will get a #REF error in Excel.

In Excel formulas, the space character is used as the intersect operator, which means it is used when listing two or more intersecting or overlapping ranges of data. Error values occur when the two or more cell references are separated incorrectly or unintentionally by a space in a formula. We see an OFFSET function that references a cell located in negative references. Errors caused by copying and pasting cells, convert relative references into absolute ones.