Error messages are simply a way for Excel to alert you about a problem, so don’t worry if you come across the #REF! error. In this article, we will discuss how you get the error, its fixes, and how you may prevent it the next time!
#REF! Error in Excel
To simply put it, you get the #REF! error when Excel cannot locate the specified cell in the formula. Excel cannot simultaneously update the location of the cell content in a formula when you move it through your spreadsheet. Excel will use the data in the cell referred to it; if it is empty, you will get the #REF! error.
How to Correct a #REF! Error in Excel?
After you’ve diagnosed the problem you might be dealing with, you can move on to the relevant solutions. This article covers everything from you deleting cell contents accidentally to correcting incorrect references.
Data Deleted From The CellData Moved to Another CellEmbedded Program is ClosedWrong DDE ReferenceError with Macro
Undo Action
When you delete or move the data from the referred cells in a formula, Excel will have no data to retrieve for calculation. Excel will display the #REF! error to alert you about the missing values. If you accidentally deleted or moved the data, you can revert it immediately after it is done. Hit the shortcut Ctrl + Z to undo the immediate action. Remember, you may only use this method if you’ve just removed the cell contents. The Undo Action reverts immediate changes. If you still want to use this method, you must revert all actions you made after removing the cell contents.
Change Formula
As we’ve already mentioned, Excel does not update formulas when you change the cell location for your data. This makes the formula you’ve entered invalid; return the #REF! error. If you do not wish to return the cell data to the previous cell location, you must change the Formula. Select the cell with the error, head to the formula bar, and update the argument to the cell that currently holds the data you want to calculate.
Re-Open the Embedded Program
You can link the Office program from the special OLE (Object Linking and Embedding) feature. If the program you’ve linked through is closed, Excel cannot locate the source of the value you wished to embed. You need to reopen the embedded program to solve the #REF! error. When you reopen the program, the exchange of information between the programs is re-initiated as the connection is established.
Inspect the DDE Topic
Microsoft allows users to connect Office programs to other Windows-based programs through DDE (Dynamic Data Exchange). You will need to reference a topic you want Excel to connect to. If you incorrectly enter this topic, you will receive the #REF! error. Select the cell with the #REF! error from the worksheet. Inspect the formula you entered from the formula bar. If incorrect, Excel cannot extract the data you wish from the topic to view in the workbook.
Unblock External Connections
Suppose you’ve entered the formula correctly to establish data exchange but are still getting the #REF! error. This is because you have disabled all data connections on your Excel settings. Although this is a good security measure, this blocks all information coming from external sources, including Windows-based programs. This essentially fails the DDE connection. You must set the permissions accordingly to allow Excel to establish data exchange with other Windows programs. You can either Enable all Data Connections or Prompt users about Data Connections. Follow these steps to unblock external connections:
Update Macro
Excel allows you to save time in performing frequent commands through Macros. However, in certain cases, Macros cause the #REF! Error. For instance, if a macro is entered on the first row but enters a function that cites the cell above it, it will have no cell above it to refer to. Hence, Excel displays the #REF! error. You can edit the macro according to your preference to dodge the #REF! error. Follow these steps to update your macro on MS Excel: Excel will launch Microsoft Visual Basic Application. Make necessary changes on the Module and click on File > Save (name).
Prevent the #REF! Error
As cliché as it sounds, prevention really is better than cure. You can take certain measures to avoid the #REF! error in total. For starters, before you copy a cell to paste it to a different location, check if you’ve used it in a formula. You can always use Ctrl + Z to undo the action if you paste it accidentally. You can also convert some cells to an absolute reference. Converting a cell into absolute fixes the location for the cell content so they will not change when they’re pasted elsewhere. You can change the reference to absolute by adding the dollar sign before you enter the row and column in the formula bar. Here is the format to convert a relative cell to an absolute cell: =$B$12
title: “How To Correct A Ref Error In Excel " ShowToc: true date: “2022-11-26” author: “Esther Hathaway”
Error messages are simply a way for Excel to alert you about a problem, so don’t worry if you come across the #REF! error. In this article, we will discuss how you get the error, its fixes, and how you may prevent it the next time!
#REF! Error in Excel
To simply put it, you get the #REF! error when Excel cannot locate the specified cell in the formula. Excel cannot simultaneously update the location of the cell content in a formula when you move it through your spreadsheet. Excel will use the data in the cell referred to it; if it is empty, you will get the #REF! error.
How to Correct a #REF! Error in Excel?
After you’ve diagnosed the problem you might be dealing with, you can move on to the relevant solutions. This article covers everything from you deleting cell contents accidentally to correcting incorrect references.
Data Deleted From The CellData Moved to Another CellEmbedded Program is ClosedWrong DDE ReferenceError with Macro
Undo Action
When you delete or move the data from the referred cells in a formula, Excel will have no data to retrieve for calculation. Excel will display the #REF! error to alert you about the missing values. If you accidentally deleted or moved the data, you can revert it immediately after it is done. Hit the shortcut Ctrl + Z to undo the immediate action. Remember, you may only use this method if you’ve just removed the cell contents. The Undo Action reverts immediate changes. If you still want to use this method, you must revert all actions you made after removing the cell contents.
Change Formula
As we’ve already mentioned, Excel does not update formulas when you change the cell location for your data. This makes the formula you’ve entered invalid; return the #REF! error. If you do not wish to return the cell data to the previous cell location, you must change the Formula. Select the cell with the error, head to the formula bar, and update the argument to the cell that currently holds the data you want to calculate.
Re-Open the Embedded Program
You can link the Office program from the special OLE (Object Linking and Embedding) feature. If the program you’ve linked through is closed, Excel cannot locate the source of the value you wished to embed. You need to reopen the embedded program to solve the #REF! error. When you reopen the program, the exchange of information between the programs is re-initiated as the connection is established.
Inspect the DDE Topic
Microsoft allows users to connect Office programs to other Windows-based programs through DDE (Dynamic Data Exchange). You will need to reference a topic you want Excel to connect to. If you incorrectly enter this topic, you will receive the #REF! error. Select the cell with the #REF! error from the worksheet. Inspect the formula you entered from the formula bar. If incorrect, Excel cannot extract the data you wish from the topic to view in the workbook.
Unblock External Connections
Suppose you’ve entered the formula correctly to establish data exchange but are still getting the #REF! error. This is because you have disabled all data connections on your Excel settings. Although this is a good security measure, this blocks all information coming from external sources, including Windows-based programs. This essentially fails the DDE connection. You must set the permissions accordingly to allow Excel to establish data exchange with other Windows programs. You can either Enable all Data Connections or Prompt users about Data Connections. Follow these steps to unblock external connections:
Update Macro
Excel allows you to save time in performing frequent commands through Macros. However, in certain cases, Macros cause the #REF! Error. For instance, if a macro is entered on the first row but enters a function that cites the cell above it, it will have no cell above it to refer to. Hence, Excel displays the #REF! error. You can edit the macro according to your preference to dodge the #REF! error. Follow these steps to update your macro on MS Excel: Excel will launch Microsoft Visual Basic Application. Make necessary changes on the Module and click on File > Save (name).
Prevent the #REF! Error
As cliché as it sounds, prevention really is better than cure. You can take certain measures to avoid the #REF! error in total. For starters, before you copy a cell to paste it to a different location, check if you’ve used it in a formula. You can always use Ctrl + Z to undo the action if you paste it accidentally. You can also convert some cells to an absolute reference. Converting a cell into absolute fixes the location for the cell content so they will not change when they’re pasted elsewhere. You can change the reference to absolute by adding the dollar sign before you enter the row and column in the formula bar. Here is the format to convert a relative cell to an absolute cell: =$B$12
