How to correct a #REF! error
Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel for the web Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 for Mac Excel 2016 Excel 2016 for Mac Excel 2013 Excel for iPad Excel for iPhone Excel for Android tablets Excel 2010 Excel 2007 Excel for Mac 2011 Excel for Android phones Excel for Windows Phone 10 Excel Mobile Excel Starter 2010 More…Less
The #REF! error shows when a formula refers to a cell that’s not valid. This happens most often when cells that were referenced by formulas get deleted, or pasted over.
The following example uses the formula =SUM(B2,C2,D2) in column E.
If you were to delete column B, C or D it would cause a #REF! error. In this case we’ll delete column C (2007 Sales), and the formula now reads =SUM(B2,#REF!,C2). When you use explicit cell references like this (where you reference each cell individually, separated by a comma) and delete a referenced row or column, Excel can’t resolve it, so it returns the #REF! error. This is the primary reason why using explicit cell references in functions is not recommended.
Solution
-
If you accidentally deleted rows or columns, you can immediately click the Undo button on the Quick Access Toolbar (or press CTRL+Z) to restore them.
-
Adjust the formula so that it uses a range reference instead of individual cells, like =SUM(B2:D2). Now you could delete any column within the sum range and Excel will automatically adjust the formula. You could also use =SUM(B2:B5) for a sum of rows.
In the following example, =VLOOKUP(A8,A2:D5,5,FALSE) will return a #REF! error because it’s looking for a value to return from column 5, but the reference range is A:D, which is only 4 columns.
Solution
Adjust the range to be larger, or reduce the column lookup value to match the reference range. =VLOOKUP(A8,A2:E5,5,FALSE) would be a valid reference range, as would =VLOOKUP(A8,A2:D5,4,FALSE).
In this example, the formula =INDEX(B2:E5,5,5) returns a #REF! error because the INDEX range is 4 rows by 4 columns, but the formula is asking to return what’s in the 5th row and 5th column.
Solution
Adjust the row or column references so they’re inside the INDEX lookup range. =INDEX(B2:E5,4,4) would return a valid result.
In the following example, an INDIRECT function is attempting to reference a workbook that’s closed, causing a #REF! error.
Solution
Open the referenced workbook. You’ll encounter the same error if you reference a closed workbook with a dynamic array function.
Structured references to table and column names in linked workbooks aren’t supported.
Calculated references to linked workbooks aren’t supported.
Moving or deleting cells caused an invalid cell reference, or function is returning reference error.
If you have used an Object Linking and Embedding (OLE) link that is returning a #REF! error, then start the program that the link is calling.
Note: OLE is a technology that you can use to share information between programs.
Macro Issues
If a macro enters a function on the worksheet that refers to a cell above the function, and the cell that contains the function is in row 1, the function will return #REF! because there are no cells above row 1. Check the function to see if an argument refers to a cell or range of cells that is not valid. This may require editing the macro in the Visual Basic Editor (VBE) to take that situation into account.
Need more help?
You can always ask an expert in the Excel Tech Community or get support in the Answers community.
See Also
Overview of formulas in Excel
How to avoid broken formulas
Detect errors in formulas
Excel functions (alphabetical)
Excel functions (by category)
Need more help?
Содержание:
- Что такое #REF! Ошибка в Excel?
- Пример #REF! Ошибка
- Найдите #REF! Ошибка при использовании функции «Найти и заменить»
- Способы исправить ошибку #REF! ошибка
- Найдите ошибки, используя специальный параметр «Перейти»
Если вы какое-то время работали с формулами в Excel, я уверен, что вы уже встречались с #ССЫЛКА! Ошибка (справочная ошибка).
Поскольку это довольно распространенное явление, я подумал о написании учебного пособия только для решения проблемы #REF! ошибка.
В этом руководстве по Excel я расскажу, что такое #REF! ошибка, что ее вызывает и как ее исправить.
Что такое #REF! Ошибка в Excel?
Ошибка ссылки — это то, что вы получаете, когда ваша формула не знает, на какую ячейку / диапазон ссылаться, или когда ссылка недействительна.
Наиболее частая причина, по которой вы можете получить справочную ошибку, — это когда у вас есть формула, которая ссылается на ячейку / строку / столбец, и вы удаляете эту ячейку / строку / столбец.
Теперь, поскольку формула (которая относилась к диапазону до его удаления) не знает, куда указывать, эта предыдущая ссылка в формуле меняется на # ССЫЛКА !.
Это в свою очередь. заставляет формулу возвращать # ССЫЛКА! ошибка результата формулы.
Пример #REF! Ошибка
Позвольте мне показать вам простой пример, на котором мы получаем справочную ошибку, а позже я расскажу, как ее найти и исправить.
Ниже представлен набор данных, в который я хочу добавить четыре ячейки и использую простую арифметику с оператором сложения.
Все идет нормально!
Теперь, если я удалю один из столбцов, в формуле не будет ссылки на удаленную ячейку, поэтому мне будет выдан #REF! ошибка (как показано ниже)
В приведенном выше примере, поскольку я удалил четвертую строку (в которой была ячейка со значением 3), формула не знает, на что ссылаться, и возвращает ошибку ссылки.
Справочные ошибки довольно распространены, и вы можете выполнить быструю проверку, прежде чем использовать набор данных в расчетах (при этом может возникнуть ошибка # ССЫЛКА!).
Итак, позвольте мне показать вам два способа быстрого поиска ячеек с ошибкой ссылки и некоторые возможные исправления.
Найдите #REF! Ошибка при использовании функции «Найти и заменить»
Предположим, у вас есть набор данных, показанный ниже, в котором есть пара опорных ошибок.
Ниже приведены шаги по поиску и выделению всех ячеек с «ссылочными ошибками»:
- Выберите весь набор данных, который вы хотите проверить
- Удерживая клавишу Ctrl, нажмите клавишу F (или Command + F, если вы используете Mac). Откроется диалоговое окно «Найти и заменить«.
- В поле «Найти» введите # ССЫЛКА!
- Нажмите кнопку «Найти все». Это найдет все ячейки с #REF! ошибка
- Удерживая клавишу Ctrl, нажмите клавишу A (или Command + A, если вы используете Mac). При этом будут выбраны все ячейки с ошибкой ссылки.
- Закройте диалоговое окно «Найти и заменить».
Вышеупомянутые шаги найдут и затем выберут все ячейки с #REF! ошибка и выберите их.
Выбрав все ячейки, в которых есть ошибка, вы можете выбрать, что с ней делать.
Давайте посмотрим, как можно справиться с ошибкой ссылки.
Способы исправить ошибку #REF! ошибка
После того, как вы выбрали все ячейки с эталонной ошибкой, вы можете выполнить одно из следующих действий:
- Удалить ошибку: Просто нажмите клавишу удаления, и эта ошибка будет устранена, и вместо этого у вас будут пустые ячейки.
- Заменить значением или текстом: Вы можете заменить ошибку на 0, тире или любой другой значимый текст. Для этого просто введите то, чем вы хотите заменить ошибку, удерживайте клавишу Ctrl, а затем нажмите клавишу Enter. Это введет текст, который вы ввели во все выбранные ячейки.
- Выделите эти ячейки с помощью параметра цвета ячейки на вкладке Главная на ленте
Примечание. Когда вы используете метод «Найти и заменить», он найдет только те ячейки, у которых есть #REF! ошибка, так как это то, что мы искали в поле «Найти». Если есть другие ошибки (например, ошибка #NA или #DIV!), Они не будут обнаружены (если вы не повторите те же шаги и для этих ошибок.
Если вы хотите найти все ошибки (включая ошибку #REF!), Используйте метод, описанный ниже.
Найдите ошибки, используя специальный параметр «Перейти»
Еще один способ быстро найти #REF! ошибки которые являются результатом формулы с помощью специального метода вставки.
Преимущество этого метода заключается в том, что он найдет и выберет все ячейки с ошибками любого типа (включая ошибки ссылок). Но недостатком этого метода является то, что он будет находить и выбирать только те ячейки, в которых ошибка возникает из-за формулы. Если ошибка присутствует в виде текста, этот метод не сможет ее найти.
Предположим, у вас есть набор данных, показанный ниже, и вы хотите найти и выбрать все ячейки, в которых есть ошибки.
Ниже приведены шаги по использованию опции «Перейти к специальному», чтобы найти и выбрать все ошибки:
- Выберите весь набор данных, в котором вы хотите проверить наличие ошибок
- Перейдите на вкладку «Главная«
- В группе редактирования нажмите «Найти и выбрать».
- В появившихся опциях нажмите на опцию «Перейти к специальному».
- В диалоговом окне «Перейти к специальному» выберите параметр «Формулы«.
- В параметрах формулы снимите все флажки, кроме флажка Ошибка.
- Нажмите ОК.
Вышеупомянутые шаги мгновенно выберут все ячейки с формулами, возвращающими ошибку.
После того, как вы выбрали эти ячейки, вы можете удалить их, выделить или заменить на 0, тире или другой значимый текст.
Примечание. Одним из ограничений этого метода является то, что он будет находить и выбирать только те ячейки, в которых ошибка является результатом формулы. Если у вас есть дамп данных, в котором ошибки находятся внутри ячеек как само значение, а не как результат формулы, этот метод не будет выбирать эти ячейки.
Итак, это два быстрых метода, которые вы можете использовать для поиска и исправления ошибки #REF (справочная ошибка) в Excel.
Надеюсь, вы нашли этот урок полезным.
How to find and fix #REF Excel errors
What are #REF Excel Errors?
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. Every good financial analyst knows how to find and fix #REF Excel errors, which we will explain in detail below.
#REF Excel Error Example
Below is an example of how you may inadvertently create a #REF Excel error. To learn more, watch CFI’s free Excel course and follow the video instruction.
The first image shows three numbers being added together (5, 54, and 16). In column D we show the formula adding cells D3, D4, and D5 together, to get 75.
The next image shows what happens if row 4 is deleted. When the entire row is deleted, the formula that referenced cell D4 is no longer able to locate that cell, and it produces a #REF error in the spreadsheet.
Learn more about #REF Excel errors in our free Excel training course.
How to Find #REF Excel Errors
Method #1
A fast way to find all #REF Excel errors is to press F5 (Go To) and then click on Special, which for short is referred to as Go To Special.
When the Go To Special menu appears, select Formulas, and then check only the box that says Errors. Click OK and that will automatically take you to every cell that has a #REF! error in it.
Method #2
Another method is to press Ctrl + F (known as the Excel find function) and then type “#REF!” in the Find field and click Find All. This will highlight every cell with the error in it.
How to Fix #REF Excel Errors
The best method is to press Ctrl + F (known as the find function) and then select the tab that says Replace. Type “#REF!” in the Find field and leave the Replace field empty, then press Replace All. This will remove any #REF Excel errors from formulas and thus fix the problem.
As the above screenshot shows, after typing the error message you are experiencing in the “Find what” filed, leave “Replace with” blank and press “Replace All” in the bottom left corner. This will cause the reference errors to be deleted from your formulas.
More Excel Resources from CFI
We hope this has been a helpful CFI guide on Excel errors. 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.
Additional CFI guides and resources you may find useful include:
- Excel for Dummies
- Excel modeling best practices
- Excel formulas cheat sheet
- Financial modeling guide
- See all Excel resources
It can be frustrating when your Excel spreadsheet doesn’t act the way you expect. This can often happen when you are working with formulas, where you might get a series of errors. One of the most common errors in Excel related to formulas is the #REF! error. Here is why this might occur and how you can fix it.
Learn how to recognize and resolve #REF! errors in Excel with this guide.
About the #REF Error in Excel
The #REF error in Excel stands for “reference,” meaning that a cell that is referenced in a formula no longer exists or is valid for the operation. These errors often occur because data is moved or rows, columns, or sheets have been moved. It can also happen when you copy a formula that has relative references to a new location, and those references are no longer valid.
A simple example of a #REF error is below. The first image shows a formula with four numbers added together (15,31,3, and 55). In Cell C8, we show the formula adding these figures together.
The next figure shows what happens if row 5 is deleted. Now that one of the references in the formulas is no longer valid, it returns a #REF error.
Preventing #REF Errors
The best way to deal with #REF errors is never to get them or to prevent them from occurring. How can you do this? First, check the formulas in your worksheet before you delete rows, columns, or sheets. If a formula will be affected, think twice.
Next, if you are copying and pasting formulas between locations, check for relative references. If these exist, you might want to convert them to an absolute reference to prevent any changes during the copy/paste operation.
How to Find #REF Excel Errors
A #REF error might be obvious when you see that one of your formulas isn’t working. On the other hand, you might have a large spreadsheet and be unsure where your various issues lie. There are two different ways to located #REF errors in an Excel sheet.
1. The first way is to press either F5 or Control-G (Go To) and then select Special. After you do this, the Go To Special box will appear. Then, select Formulas, and only check the box that says “Errors.” Click OK. Excel will then take you to every cell that contains an error.
2. The other way to find #REF errors in Excel is by using the “Find” function. Press Ctrl-F or located “Find” under “Find & Select.” Select the tab labeled “Find.” Enter #REF in the Find what section, and hit either “Find All” or “Find Next.” You can then go through all of the errors and make some decisions.
Fixing a #REF Error in Excel
Fixing a #REF error can be tricky because the original cell that is supposed to be referenced is often deleted. If you know what that reference is supposed to be, you can just fix the error manually. If you are sure what the reference refers to, you might have to do some more research or backtracking so that you can fix your formula.
You can also use the Find and Replace tool in Excel to clear out all of the #REF errors in your Excel sheet. Open up the tool (same as above) and select the Replace tab. In Find what: put #REF and leave Replace with: blank. Select “Replace All.” While this will clear out all of the errors and make your formulas work again, this does not mean that they will work as intended since you’ve deleted some data. It’s best to double-check these after running this operation.
Because #REF! errors can be compounding, the best course of action is to fix them as quickly as possible. In other worse, assuming you delete a column or row and then see that you have several #REF errors, undo that last action. The errors should disappear, and you fix your formulas first to either exclude the cells you want to delete or not return an error.
One particular thing to note is that you can’t undo the deletion of a sheet in Excel. If you delete a tab in your worksheet and then have #REF errors, you can’t undo this. The quickest way to backtrack and fix this error is to close the Excel file without saving it and open the last saved version, which was hopefully before you deleted that sheet.
What About the VLOOKUP #REF Error?
You can also get a #REF error with the VLOOKUP function if a column isn’t correctly specified. In the image below, the VLOOKUP function returns a #REF! error because the table range does not have a column 3.
When the column figure is correctly set to 2, the #REF error disappears, and the correct data is displayed.
Other Possible #REF Error Situations
There are just a few more common instances in which #REF errors in Excel occur:
- Macro Issues. If a macro refers to a cell above a function, but the cell above that function is in row 1, you’ll get a #REF error. This might require editing of the macro.
- DDE Issues. If you’re using Dynamic Data Exchange (DDE) and getting a #REF error on a topic, first check that you are referencing the right topic and then review your external content settings in the Trust Center Settings.
- OLE Issues. If an Object Linking and Embedding (OLE) link returns a #REF error, check the program that the link references.
Still Need Help With Excel Troubleshooting?
This is most of what you need to know about the #REF error, but we might not have covered everything. Maybe you’re having trouble finding or fixing an error, or just don’t want to jump through all these hoops. That’s ok. Excelchat can provide problem-specific help 24/7.
Get the one-on-one help you need with Excel now. Your first session is always free.
Ошибка #REF («ref» означает ссылку) — это сообщение, которое Excel отображает, когда формула ссылается на ячейку, которая больше не существует, обычно вызывается удалением ячеек, на которые ссылается формула. Каждый хороший финансовый аналитик The Analyst Trifecta® Guide Полное руководство о том, как стать финансовым аналитиком мирового уровня. Вы хотите быть финансовым аналитиком мирового уровня? Вы хотите следовать передовым отраслевым практикам и выделяться из толпы? Наш процесс, называемый «Аналитик Trifecta®», состоит из аналитики, презентации и мягких навыков, он знает, как найти и исправить ошибки #REF Excel, которые мы подробно объясним ниже.
#REF Пример ошибки Excel
Ниже приведен пример того, как вы можете случайно создать ошибку #REF Excel. Чтобы узнать больше, посмотрите бесплатный курс Excel по финансам и следуйте видео-инструкции.
На первом изображении показано сложение трех чисел (5, 54 и 16). В столбце D мы показываем формулу, складывающую ячейки D3, D4 и D5 вместе, чтобы получить 75.
На следующем изображении показано, что произойдет, если удалить строку 4. Когда вся строка удаляется, формула, ссылающаяся на ячейку D4, больше не может найти эту ячейку, и в электронной таблице появляется ошибка #REF.
Узнайте больше об ошибках #REF Excel в нашем бесплатном учебном курсе по Excel.
Как найти ошибки #REF Excel
Способ # 1
Быстрый способ найти все ошибки #REF Excel — нажать F5 (Перейти), а затем нажать кнопку « Специальный» , которая для краткости называется «Перейти к специальному» Перейти к специальному «Перейти к специальному» в Excel — важная функция для электронных таблиц финансового моделирования. . Клавиша F5 открывает «Перейти к», выберите «Специальные сочетания клавиш для формул Excel», что позволяет быстро выбрать все ячейки, соответствующие определенным критериям. .
Когда появится меню «Перейти к специальному», выберите « Формулы» и установите только флажок «Ошибки». Нажмите OK, и вы автоматически перейдете к каждой ячейке с #REF! ошибка в этом.
Способ # 2
Другой способ — нажать Ctrl + F (известная как функция поиска Excel «Найти и заменить в Excel». Узнайте, как выполнять поиск в Excel — это пошаговое руководство научит, как выполнять поиск и замену в таблицах Excel с помощью сочетания клавиш Ctrl + F. Поиск в Excel и заменить позволяет вам быстро искать во всех ячейках и формулах в электронной таблице все экземпляры, которые соответствуют вашим критериям поиска. В этом руководстве рассказывается, как это сделать), а затем введите «# ССЫЛКА!» в поле «Найти» и нажмите «Найти все». Это выделит каждую ячейку с ошибкой в ней.
Как исправить ошибки #REF Excel
Лучший способ — нажать Ctrl + F (известная как функция поиска), а затем выбрать вкладку с надписью « Заменить» . Введите «#REF!» в поле «Найти» и оставьте поле «Заменить» пустым, затем нажмите « Заменить все» . Это удалит все ошибки Excel #REF из формул и тем самым решит проблему.
Как показано на скриншоте выше, после ввода сообщения об ошибке в поле «Найти что» оставьте поле «Заменить на» пустым и нажмите «Заменить все» в нижнем левом углу. Это приведет к удалению справочных ошибок из ваших формул.
Дополнительные ресурсы Excel от Финансов
Мы надеемся, что это было полезным финансовым руководством по ошибкам Excel. Если вы хотите стать мастером Excel, ознакомьтесь со всеми нашими ресурсами Excel Ресурсы Excel Изучите Excel онлайн с сотнями бесплатных руководств, ресурсов, руководств и шпаргалок! Финансовые ресурсы — лучший способ изучить Excel на своих условиях. чтобы изучить наиболее важные формулы, функции и ярлыки.
Дополнительные финансовые руководства и ресурсы, которые могут оказаться полезными, включают:
- Excel для чайников Excel для начинающих Это руководство по Excel для начинающих научит вас всему, что вам нужно знать о таблицах и формулах Excel для выполнения финансового анализа. Посмотрите видео и узнайте все, что нужно знать новичку, от того, что такое Excel, почему мы его используем, и какие сочетания клавиш, функции и формулы являются наиболее важными.
- Рекомендации по моделированию в Excel Рекомендации по моделированию в Excel Следующие передовые практики моделирования в Excel позволяют пользователю обеспечить наиболее чистый и удобный опыт моделирования. Microsoft Excel — чрезвычайно надежный инструмент. Стать опытным пользователем Excel практически обязательно для тех, кто работает в сфере инвестиционного банкинга, корпоративных финансов и прямых инвестиций.
- Шпаргалка по формулам Excel Шаблон диаграммы водопада Excel Загрузите наш бесплатный файл шаблона диаграммы водопада Excel в формате .xls и следуйте нашим инструкциям, чтобы создать собственную диаграмму водопада денежных потоков в Excel. Если вы работаете в Excel 2013 или более ранних версиях, следуйте приведенным ниже инструкциям в качестве обходного пути для создания собственной диаграммы с помощью функции столбчатой диаграммы с накоплением.
- Руководство по финансовому моделированию Бесплатное руководство по финансовому моделированию Это руководство по финансовому моделированию содержит советы и передовые методы работы с Excel в отношении предположений, драйверов, прогнозирования, связывания трех отчетов, анализа DCF и т.
- Все статьи Excel Руководства по самообучению Knowledge Finance — отличный способ улучшить технические знания в области финансов, бухгалтерского учета, финансового моделирования, оценки, торговли, экономики и т. Д.
If you have worked with formulas in Excel for some time, I am sure you have already met the #REF! Error (reference error).
Since this is quite common, I thought of writing a tutorial just to tackle the #REF! error.
In this Excel tutorial, I will cover what is the #REF! error, what causes it, and how to fix it.
What is #REF! Error in Excel?
A reference error is something you get when your formula doesn’t know what cell/range to refer to or when the reference is not valid.
The most common reason you may end up with the reference error is when you have a formula that refers to a cell/row/column and you delete that cell/row/column.
Now, since the formula (that was referring to the range before it got deleted) has no clue where to point to, that earlier reference in the formula changes to #REF!.
This, in turn. makes the formula return the #REF! error for the formula result.
Example of the #REF! Error
Let me show you a simple example where we end up getting the reference error and later I will cover how to find and fix it.
Below is a dataset where I want to add the four cells, and I use simple arithmetic using the addition operator.
So far so good!
Now, if I delete one of the columns, the formula will not have the reference for the deleted cell, so it’s going to give me a #REF! error (as shown below)
In the above example, since I deleted the fourth row (that had the cell with the value 3), the formula doesn’t know what to refer to and returns a reference error.
Reference errors are quite common and you may want to do a quick check before you use a dataset in the calculation (that has the possibility of having the #REF! error).
So, let me show you two ways to quickly find cells that have the reference error and some possible fixes.
Related tutorial: Delete Rows Based on a Cell Value (or Condition) in Excel
Find #REF! Error using Find and Replace
Suppose you have a dataset as shown below where you have a couple of reference errors.
Below are the steps to find and select all the cells that have the ‘reference errors’:
- Select the entire dataset where you want to check
- Hold the Control key and press the F key (or Command + F if you’re using a Mac). This will open the Find and Replace dialog box
- In the ‘Find what’ field, enter #REF!
- Click on the ‘Find All’ button. This will find all the cells that have the #REF! error
- Hold the Control key and press the A key (or Command + A if you’re using Mac). This would select all the cells that have the reference error.
- Close the Find and Replace dialog box.
The above steps would find and then select all the cells that have the #REF! error and select these.
Once you have all these cells selected that has the error, you can choose what to do with it.
Let’s have a look at some of the possible ways you can handle the reference error.
Ways to Fix the #REF! error
Once you have all the cells with the reference error selected, you can choose to do any of the following:
- Delete the error: Simply hit the delete key and it will remove these error and you’ll have blank cells instead
- Replace with a value or text: You can choose to replace the error with 0 or dash or any other meaningful text. To do this, simply type what you want to replace the error with, hold the Control key and then press the Enter key. This will enter the text you entered in all the selected cells.
- Highlight these cells using the cell color option in the Home tab in the ribbon
Note: When you use the Find and Replace method, it will only find the cells that have the #REF! error, as that’s what we searched for in the ‘Find what’ field. In case there are other errors (such as #NA or #DIV! error), these will not be found (unless you repeat the same steps for these errors as well.
In case you want to find all the errors (including the #REF! error), use the method covered next.
Find Errors using Go To Special Option
Another method to quickly find #REF! errors which are a result of a formula is by using the Paste Special method.
The good part about this method is that it will find and select all the cells that have any type of error (including the reference errors). But the downside with this method is that it will only find and select cells where the error is because of formula. In case the error is there as text, this method will not be able to find it.
Suppose you have a dataset as shown below and you want to find and select all the cells that have errors in it.
Below are the steps to use Go To Special option to find and select all the errors:
- Select the entire dataset where you want to check for errors
- Click the Home tab
- In the Editing group, click on the ‘Find & Select’ option.
- In the options that show up, click on the ‘Go-To Special’ option
- In the Go To Special dialog box, click on the Formulas option
- In the options within Formulas, uncheck everything except the Error checkbox.
- Click OK
The above steps would instantly select all the cells that have formulas that return an error.
Once you have these cells selected, you can choose to delete these, highlight it, or replace it with 0 or dash or some other meaningful text.
Note: One limitation of this method is that it will only find and select those cells where the error is a result of a formula. In case you have got a data dump where the errors are within the cells as the value itself and not as aa result of a formula, this method will not select those cells.
So these are two quick methods you can use to find and fix the #REF error (reference error) in Excel.
Hope you found this tutorial useful.
You may also like the following Excel tutorials:
- Use IFERROR with VLOOKUP to Get Rid of #N/A Errors
- How to Quickly Find Hyperlinks in Excel
- How to Find Hyperlinks in Excel
- Identify Errors Using Excel Formula Debugging
- Excel IFERROR Function
- #NAME Error in Excel – What Causes it and How to Fix it!
- How to Get Rid of #DIV/0! Error in Excel?
- How to Fix #VALUE Error in Excel?
About the #REF! error
The #REF! error occurs when a reference is invalid. In many cases, this is because sheets, rows, or columns have been removed, or because a formula with relative references has been copied to a new location where references are invalid. In the example shown, the formula in C10 returns a #REF! error when copied to cell E5:
=SUM(C5:C9) // original C10
=SUM(#REF) // when copied to E5
Preventing #REF errors
The best way to prevent #REF! errors is to prevent then from occurring in the first place. Before you delete columns, rows, or sheets be sure they aren’t referenced by formulas in the workbook. If you are copying and pasting a formula to a new location, you may want to convert some cell references to an absolute reference to prevent changes during the copy operation.
If you cause a #REF! error, it’s best to fix immediately. For example, if you delete a column, and #REF! errors appear, undo that action (you can use the shortcut Control + Z). When you undo, the #REF! errors will disappear. Then edit the formula(s) to exclude the column you want to delete, moving data if needed. Finally, delete the column and confirm there are no #REF! errors.
Clearing multiple #REF! errors
To quickly remove many #REF errors from a worksheet, you can use Find and Replace. Use the shortcut Control + H to open the Find and Replace dialog. Enter #REF! in the find input area, and leave the replace input area blank:
You can then make case-by-case changes with Find next + Replace, or use Replace All to replace all #REF errors in one step.
Fixing #REF errors
#REF errors are somewhat tricky to fix because the original cell reference is gone forever. If you know what the reference should be, you can simply fix it manually. Just edit the formula and replace #REF! with a valid reference. If you don’t know what the cell reference should be, you may have to study the worksheet in more detail before you can repair the formula.
Note: you can’t undo the deletion of a sheet in Excel. 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. For this reason, always save a workbook (or save a copy) before deleting one or more sheets.
#REF! errors with VLOOKUP
You may see a #REF! error with the VLOOKUP function, when a column is specified incorrectly. In the screen below, VLOOKUP returns #REF! because there is no column 3 in the table range, which is B3:C7:
When the column index is set to the correct value of 2 the #REF! error is resolved and VLOOKUP works properly:
Note: you may also see a #REF error with the INDEX function when a row or column reference is not valid.
Trapping the #REF! error with IFERROR
In most cases, it doesn’t make sense to trap the #REF! error in a formula, because #REF! indicates a low-level problem. However, one situation where IFERROR may make sense to catch a #REF error is when you are building references dynamically with the INDIRECT function.
(Note: This guide on how to fix the #REF Error in Excel is suitable for all Excel versions including Office 365)
Facing error messages is the last thing you want to see in Excel. They are quite frustrating to deal with and often difficult to resolve. The Excel #REF! Error is one of the most dreaded error messages in Excel. If you don’t know what you are doing, it may take forever to resolve this error.
But, don’t worry. In this guide, I will give you some practical tips to avoid this error and resolve it easily.
You’ll learn:
- What Causes the REF Error in Excel?
- Method 1: How to Prevent the #REF Error in Excel?
- Method 2: How to Find Multiple #REF! Errors in Excel?
- Method 3: How to Fix the #REF Error in Excel?
Related:
How to Fix the Excel Circular Reference Error?
How to Fix the Excel NAME Error? 3 Simple Tips
How to Fix the Excel Spill Error? — 5 Easy Fixes
What Causes the REF Error in Excel?
Using invalid references inside Excel formulas is the main cause behind the Excel REF error. But, keep in mind that almost all of these invalid references are accidentally created when copying or moving formulas from one cell to another.
Let me explain this with an example.
Let us say you want to calculate the total sales achieved in the last quarter. You calculate this in cell B5 using the formula SUM(B2:B4), but later decide to move it to cell E2. Excel will throw up a #REF error, leaving you confused.
What is even more perplexing is that, if you try to move the formula to cell E9, Excel will return a 0 instead of the #REF error.
So, what is causing all this?
The real culprit behind the Excel REF error is the inappropriate usage of relative references.
You see, when you use the relative reference B2:B4 in the SUM function in cell B5, you are asking Excel to find the sum of the previous three cell values i.e B2, B3 and B4.
When you try to move the formula to cell E2, there are no three cells preceding it. That’s why this is considered an invalid reference and leads to the #REF error. On the other hand, if you try to move the formula to cell E9, there are in fact three cells preceding it albeit empty (E6, E7 and E8). This is the reason, Excel returns a 0 instead of the #REF error.
Method 1: How to Prevent the #REF Error in Excel?
The best way to prevent #REF errors from cropping up is to use absolute references as much as possible. Also, keep in mind that, accidentally deleting rows or columns which are already used in calculations may also lead to reference errors.
Pro Tip: It is always better to resolve #REF errors as soon as they appear (By using the Undo button and using the correct reference). Doing this will save you from a lot of pain and confusion. Otherwise, you will end up spending a lot of time figuring out what caused the REF error.
Method 2: How to Find Multiple #REF! Errors in Excel?
In case your worksheet already has a lot of reference errors, fixing them is going to be quite a challenge, to be honest. But, on the other hand, finding them is very easy.
To find REF errors scattered over an Excel worksheet, just follow these steps:
- Press Ctrl+H on your keyboard and open the Find and Replace dialog box.
- Type #REF! and click on the Find All option. This will highlight all instances of reference errors.
- Click on Find Next to cycle through the list of REF errors in the sheet and fix them one by one.
Also Read:
How to Use Goal Seek in Excel? (3 Simple Examples)
How to Insert Multiple Rows in Excel? The 4 Best Methods
How to Autofit Excel Cells? 3 Best Methods
Method 3: How to Fix the #REF Error in Excel?
The only way to fix a #REF error is to figure out what was wrong with the formula reference before and replace it with the correct reference. The tricky thing is, that this formula is no longer visible. That is why it is highly recommended to resolve #REF errors as and when they arise.
If not, the only way out is to carefully examine the sheet and figure out what should have been the correct formula. In case, a REF error is caused by a deleted sheet or row or column, it is recommended that you restore the previous version of the sheet.
Note: If your #REF error is caused by a VLOOKUP formula, it is most probably due to an incorrect column reference. For example, let us say use the formula =VLOOKUP(B20, C15:D17, 3) but, there is no column 3 in the range C15:D17. This will cause a #REF error. To fix it, just change the index to a proper value.
Suggested Reads:
How to Group Worksheets in Excel? (In 3 Simple Steps)
How to Shade Every Other Row in Excel? (5 Best Methods)
How to Use the Excel Fill Handle Easily? (Top 3 Uses with Examples)
Closing Thoughts
In this guide, we saw how to smartly avoid and fix #REF errors in Excel. I hope you find this guide helpful. If you have any questions, please feel free to ask us in the comments. We are always happy to help.
If you need more high-quality Excel guides, please check out our free Excel resources center.
Simon Sez IT has been teaching Excel for over ten years. For a low, monthly fee you can get access to 130+ IT training courses. Click here for advanced Excel courses with in-depth training modules.
Simon Calder
Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA.
He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course — the rest, as they say, is history!
Say you’ve got this worksheet from a couple of months ago ready to work with. You pull it up on your computer and start working. You’re updating the worksheet and suddenly, you notice that a few of the old formulas are showing a #REF! error. The fix? Just reference the correct element.
#REF! error is pretty simple to solve. In this tutorial, you’ll find out what causes the #REF! error and how to fix it. If this sounds a little vague, let’s talk about the #REF! error right from what it is, to how to fix it.
What is #REF! Error in Excel?
A #REF! error is an Excel error that tells you that one or more of the references used in your formula is invalid. For instance, if you’ve summed up cells C1 to C5 [=C1+C2+C3+C4+C5] in cell C6, and then any one of the cells from C1 to C5 gets deleted, the formula in C6 will return a #REF! error. To understand this more clearly, refer to example 1.
Why Does it Appear?
#REF! error occurs when you’ve referenced an element on your worksheet or workbook that no longer exists. Most commonly, you’ll see a #REF! error because you had referenced a cell, column, row, or a sheet, and subsequently removed it from your workbook. You may also see a #REF! error because you’ve copied a formula with relative references to another worksheet where those references are rendered invalid.
Examples
Following are some examples of when you’d see a #REF! error. While there are a lot of other instances where you’d see a #REF! error, the solutions we discuss here should help you get rid of all #REF! errors that you see on your worksheet.
1. The reference has been removed
When you remove a cell, column, row, or worksheet that you’ve referenced in a formula somewhere in the workbook, you’ll see a #REF! error. The reason is simple. The element that you’re referring to in the formula no longer exists, and Excel has no way to use that data in the formula any longer.
For instance, say you’ve got some sales figures in column B. You want to sum those up, so you use the following formula:
=B2+B3+B4+B5+B6
You get your output and everything is working just fine. Now, you select and right-click on row 6, and delete all the numbers. You’ll notice the output immediately changes to #REF! error.
The solution? There are two. You can either change the formula and add valid references to a data set that does exist on the worksheet. Or you could add data into the cells that have been referenced in the formula.
2. Copying a formula with relative cell references
When you copy a formula into some other cell, and you see a #REF! error, it’s almost always a case of relative references. Not sure what this means? Try this out on your sheet. Enter some numbers in a column, and sum them in a cell using the SUM function.
Then, copy the SUM function and paste it into another cell in a column far away.
Getting a #REF! error? That’s because Excel assumes that when you paste a formula in a different column, you’ll also refer to corresponding data in that column. The fix is easier than the kindergarten volcano experiment though.
All you need to do is make your references absolute. First, undo the paste action by pressing Ctrl + Z. This should remove the #REF! error from your sheet. Now, put dollar signs in your cell references. This is what your formula should look like:
=SUM($B$2:$B$6)
Neat, huh?
3. #REF! Error with the VLOOKUP Function
Now we all know that #REF! errors are referencing errors. However, there’s just a slight variation in how a #REF! error occurs when you’re using a VLOOKUP function. Let’s use the data from the previous example and instead of summing it up, we’ll apply the VLOOKUP function to the data.
Here’s the formula we’ll use:
=VLOOKUP(D4,A2:B6,3,0)
Getting a #REF! error?
Perfect. Think about the reason for a moment.
Okay, so the reason is that you’ve referenced the third column in the col_index_num argument, which isn’t included in the array that you’ve entered in the second argument. As far as the VLOOKUP function is concerned, the third column doesn’t exist. Therefore, it gives you a #REF! error.
The fix, again, is pretty straightforward.
Just change the third argument to 2, and you should be set.
Could it be any easier?
Other solutions for #REF! Error
In most cases, you should be able to resolve the #REF! error using the methods that we’ve already discussed. However, some situations are trickier.
For instance, what if you deleted a worksheet from your workbook and have several #REF! errors across the worksheet where those cells were referenced? Well, there’s no getting around it. You’ll have to resolve those errors by referencing them properly.
1. Clear the #REF! Error from the Worksheet
If you’d like to just clear them out of your worksheet instead of fixing them, you can just use the Find and Replace option to clear out the #REF! errors from your worksheet.
To do this, press Ctrl + H and enter #REF! in the Find what field. Leave the Replace with field blank and click Replace All.
2. Trap the #REF! Error
#REF! error typically doesn’t warrant the use of the IFERROR function to trap the error. However, in some cases, you may want to use it. For instance, if you’re getting a #REF! error as a result of adding dynamic references using the INDIRECT function, IFERROR function can help you. To learn how you can trap an error, read through our comprehensive IFERROR function tutorial.
That’s all the ways that you can solve a #REF! error on your worksheet. Again, solving the #REF! error is a child’s play in most cases. On the off chance that you have a more complex issue like with the INDIRECT function, you’ll need to deploy some more Excel chops.
As long as you’ve got us, there’s nothing you can’t do on your worksheet. While you finish getting rid of all the #REF! errors on your worksheet, we’ll pull out another tutorial for you.