Excel formula: How to fix the #REF! error

WRITTEN BY: supportmymoto.com STAFF

Concerning the #REF! error

The #REF! error happens when a reference is invalid. In lots of instances, it’s because sheets, rows, or columns have been eliminated, or as a result of a method with relative references has been copied to a brand new location the place references are invalid. Within the instance proven, the method in C10 returns a #REF! error when copied to cell E5:

=SUM(C5:C9) // unique C10
=SUM(#REF) // when copied to E5

Stopping #REF errors

The easiest way to stop #REF! errors is to stop then from occurring within the first place.  Earlier than you delete columns, rows, or sheets make sure they are not referenced by formulation within the workbook. In case you are copying and pasting a method to a brand new location, it’s possible you’ll need to convert some cell references to an absolute reference to stop adjustments in the course of the copy operation.

In case you trigger a #REF! error, it is best to repair instantly. For instance, when you delete a column, and #REF! errors seem, undo that motion (you need to use the shortcut Management + Z). Once you undo, the #REF! errors will disappear. Then edit the method(s) to exclude the column you need to delete, transferring knowledge if wanted. Lastly, delete the column and ensure there are not any #REF! errors.

Clearing a number of #REF! errors

To shortly take away many #REF errors from a worksheet, you need to use Discover and Change. Use the shortcut Management + H to open the Discover and Change dialog.  Enter #REF! within the discover enter space, and go away the exchange enter space clean:

Clear REF errors with find and replace

You possibly can then make case-by-case adjustments with Discover subsequent + Change, or use Change All to switch all #REF errors in a single step.

Fixing #REF errors

#REF errors are considerably tough to repair as a result of the unique cell reference is gone perpetually. If you realize what the reference needs to be, you’ll be able to merely repair it manually. Simply edit the method and exchange #REF! with a sound reference. If you do not know what the cell reference needs to be, you will have to check the worksheet in additional element earlier than you’ll be able to restore the method.

Be aware: you’ll be able to’t undo the deletion of a sheet in Excel. In case you delete a worksheet tab, and see #REF errors, the best choice might be to shut the file and re-open the final saved model. For that reason, all the time save a workbook (or save a replica) earlier than deleting a number of sheets.

#REF! errors with VLOOKUP

You may even see a #REF! error with the VLOOKUP perform, when a column is specified incorrectly. Within the display under, VLOOKUP returns #REF! as a result of there is no such thing as a column 3 within the desk vary, which is B3:C7:

Example of #REF! error with VLOOKUP

When the column index is about to the right worth of two the #REF! error is resolved and VLOOKUP works correctly:

Example of #REF! error resolved with VLOOKUP

Be aware: you may additionally see a #REF error with the INDEX perform when a row or column reference just isn’t legitimate.

Trapping the #REF! error with IFERROR

Normally, it would not make sense to lure the #REF! error in a method, as a result of #REF! signifies a low-level downside. Nonetheless, one state of affairs the place IFERROR might make sense to catch a #REF error is when you find yourself constructing references dynamically with the INDIRECT perform.

NOTE : Please do not copy - https://supportmymoto.com
See also  How to use the Excel ROUNDUP function

Leave a Reply

Situs Judi Slot Online Terpercaya

Link Slot Gacor

Slot Anti Rungkat

Slot Online Terpercaya