{"id":30829,"date":"2020-04-27T23:10:23","date_gmt":"2020-04-27T17:40:23","guid":{"rendered":"https:\/\/trumpexcel.com\/?p=30829"},"modified":"2023-09-29T15:19:52","modified_gmt":"2023-09-29T09:49:52","slug":"ref-error-in-excel","status":"publish","type":"post","link":"https:\/\/trumpexcel.com\/ref-error-in-excel\/","title":{"rendered":"#REF! Error in Excel &#8211; How to Fix the Reference Error!"},"content":{"rendered":"<p>If you have worked with formulas in Excel for some time, I am sure you have already met the #<strong>REF! Error (reference error).<\/strong><\/p>\n<p>Since this is quite common, I thought of writing a tutorial just to tackle the #REF! error.<\/p>\n<p>In this Excel tutorial, I will cover what is the #REF! error, what causes it, and how to fix it.<\/p>\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_82_2 counter-hierarchy ez-toc-counter ez-toc-light-blue ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">This Tutorial Covers:<\/p>\n<span class=\"ez-toc-title-toggle\"><a href=\"#\" class=\"ez-toc-pull-right ez-toc-btn ez-toc-btn-xs ez-toc-btn-default ez-toc-toggle\" aria-label=\"Toggle Table of Content\"><span class=\"ez-toc-js-icon-con\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #999;color:#999\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #999;color:#999\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/span><\/a><\/span><\/div>\n<nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/trumpexcel.com\/ref-error-in-excel\/#What-is-REF-Error-Reference-Error-in-Excel\" >What is #REF! Error (Reference Error) in Excel?<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/trumpexcel.com\/ref-error-in-excel\/#Example-of-the-REF-Error\" >Example of the #REF! Error<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/trumpexcel.com\/ref-error-in-excel\/#Find-REF-Error-using-Find-and-Replace\" >Find #REF! Error using Find and Replace<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/trumpexcel.com\/ref-error-in-excel\/#Ways-to-Fix-the-REF-error\" >Ways to Fix the #REF! error<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/trumpexcel.com\/ref-error-in-excel\/#Find-Errors-using-Go-To-Special-Option\" >Find Errors using Go To Special Option<\/a><\/li><\/ul><\/nav><\/div>\n\n<h2><span class=\"ez-toc-section\" id=\"What-is-REF-Error-Reference-Error-in-Excel\"><\/span>What is #REF! Error (Reference Error) in Excel?<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>A reference error is something you get when your formula doesn&#8217;t know what cell\/range to refer to or when the reference is not valid.<\/p>\n<p>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.<\/p>\n<p>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!.<\/p>\n<p>This, in turn. makes the formula return the #REF! error for the formula result.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Example-of-the-REF-Error\"><\/span>Example of the #REF! Error<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>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.<\/p>\n<p>Below is a dataset where I want to add the four cells, and I use simple arithmetic using the addition operator.<\/p>\n<p><img fetchpriority=\"high\" decoding=\"async\" class=\"size-full wp-image-32265 aligncenter\" src=\"https:\/\/trumpexcel.com\/wp-content\/uploads\/2020\/04\/Formula-to-calculate-the-sum-of-cells.png\" alt=\"Formula to calculate the sum of cells\" width=\"458\" height=\"363\" srcset=\"https:\/\/trumpexcel.com\/wp-content\/uploads\/2020\/04\/Formula-to-calculate-the-sum-of-cells.png 458w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2020\/04\/Formula-to-calculate-the-sum-of-cells-379x300.png 379w\" sizes=\"(max-width: 458px) 100vw, 458px\" \/><\/p>\n<p>So far, so good!<\/p>\n<p>Now, if I delete one of the columns, the formula will not have the reference for the deleted cell, so it&#8217;s going to give me a #REF! error (as shown below)<\/p>\n<p><img decoding=\"async\" class=\"size-full wp-image-32266 aligncenter\" src=\"https:\/\/trumpexcel.com\/wp-content\/uploads\/2020\/04\/Formula-that-returns-the-REF-error.png\" alt=\"Formula that returns the REF error\" width=\"470\" height=\"319\" srcset=\"https:\/\/trumpexcel.com\/wp-content\/uploads\/2020\/04\/Formula-that-returns-the-REF-error.png 470w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2020\/04\/Formula-that-returns-the-REF-error-442x300.png 442w\" sizes=\"(max-width: 470px) 100vw, 470px\" \/><\/p>\n<p>In the above example, since I <a href=\"https:\/\/trumpexcel.com\/delete-rows\/\" target=\"_blank\" rel=\"noopener\">deleted the fourth row<\/a> (that had the cell with the value 3), the formula doesn&#8217;t know what to refer to and returns a reference error.<\/p>\n<p>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).<\/p>\n<p>So, let me show you two ways to quickly find cells that have the reference error and some possible fixes.<\/p>\n<pre><em>Related tutorial:<\/em>\u00a0<a href=\"https:\/\/trumpexcel.com\/delete-rows-based-on-cell-value\/\" target=\"_blank\" rel=\"noopener noreferrer\">Delete Rows Based on a Cell Value (or Condition) in Excel<\/a><\/pre>\n<h2><span class=\"ez-toc-section\" id=\"Find-REF-Error-using-Find-and-Replace\"><\/span>Find #REF! Error using Find and Replace<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Suppose you have a dataset as shown below where you have a couple of reference errors.<\/p>\n<p><img decoding=\"async\" class=\"size-full wp-image-32271 aligncenter\" src=\"https:\/\/trumpexcel.com\/wp-content\/uploads\/2020\/04\/KPI-Data-with-errors.png\" alt=\"KPI Data with errors\" width=\"620\" height=\"508\" srcset=\"https:\/\/trumpexcel.com\/wp-content\/uploads\/2020\/04\/KPI-Data-with-errors.png 620w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2020\/04\/KPI-Data-with-errors-366x300.png 366w\" sizes=\"(max-width: 620px) 100vw, 620px\" \/><\/p>\n<p>Below are the steps to find and select all the cells that have the &#8216;reference errors&#8217;:<\/p>\n<ol>\n<li>Select the entire dataset where you want to check<\/li>\n<li>Hold the Control key and press the F key (or Command + F if you&#8217;re using a Mac). This will open the Find and Replace dialog box<\/li>\n<li>In the &#8216;Find what&#8217; field, enter\u00a0<strong>#REF!<img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-32274 aligncenter\" src=\"https:\/\/trumpexcel.com\/wp-content\/uploads\/2020\/04\/Enter-REF-in-Find-All-field.png\" alt=\"Enter REF in Find All field\" width=\"669\" height=\"251\" srcset=\"https:\/\/trumpexcel.com\/wp-content\/uploads\/2020\/04\/Enter-REF-in-Find-All-field.png 669w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2020\/04\/Enter-REF-in-Find-All-field-600x225.png 600w\" sizes=\"(max-width: 669px) 100vw, 669px\" \/><\/strong><\/li>\n<li>Click on the &#8216;Find All&#8217; button. This will find all the cells that have the #REF! error<img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-32273 aligncenter\" src=\"https:\/\/trumpexcel.com\/wp-content\/uploads\/2020\/04\/Click-on-the-Find-All-button.png\" alt=\"Click on the Find All button\" width=\"669\" height=\"251\" srcset=\"https:\/\/trumpexcel.com\/wp-content\/uploads\/2020\/04\/Click-on-the-Find-All-button.png 669w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2020\/04\/Click-on-the-Find-All-button-600x225.png 600w\" sizes=\"(max-width: 669px) 100vw, 669px\" \/><\/li>\n<li>Hold the Control key and press the A key (or Command + A if you&#8217;re using Mac). This would select all the cells that have the reference error.<img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-32272 aligncenter\" src=\"https:\/\/trumpexcel.com\/wp-content\/uploads\/2020\/04\/All-the-selected-cell-in-Find-and-Replace-that-have-the-REF-error.png\" alt=\"All the selected cell in Find and Replace that have the !REF# error\" width=\"669\" height=\"447\" srcset=\"https:\/\/trumpexcel.com\/wp-content\/uploads\/2020\/04\/All-the-selected-cell-in-Find-and-Replace-that-have-the-REF-error.png 669w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2020\/04\/All-the-selected-cell-in-Find-and-Replace-that-have-the-REF-error-449x300.png 449w\" sizes=\"(max-width: 669px) 100vw, 669px\" \/><\/li>\n<li>Close the Find and Replace dialog box.<\/li>\n<\/ol>\n<p>The above steps would find and then select all the cells that have the #REF! error and select these.<\/p>\n<p>Once you have all these cells selected that has the error, you can choose what to do with it.<\/p>\n<p>Let&#8217;s have a look at some of the possible ways you can handle the reference error.<\/p>\n<h3><span class=\"ez-toc-section\" id=\"Ways-to-Fix-the-REF-error\"><\/span>Ways to Fix the #REF! error<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>Once you have all the cells with the reference error selected, you can choose to do any of the following:<\/p>\n<ul>\n<li><strong>Delete the error<\/strong>: Simply hit the delete key and it will remove these error and you&#8217;ll have blank cells instead<\/li>\n<li><strong>Replace with a value or text<\/strong>: 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.<\/li>\n<li><strong>Highlight these cells<\/strong> using the cell color option in the Home tab in the ribbon<\/li>\n<\/ul>\n<p>Note: When you use the <a href=\"https:\/\/trumpexcel.com\/find-and-replace-in-excel\/\" target=\"_blank\" rel=\"noopener noreferrer\">Find and Replace<\/a> method, it will only find the cells that have the #REF! error, as that&#8217;s what we searched for in the &#8216;Find what&#8217; 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.<\/p>\n<p>In case you want to find all the errors (including the #REF! error), use the method covered next.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Find-Errors-using-Go-To-Special-Option\"><\/span>Find Errors using Go To Special Option<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Another method to quickly find #REF! errors <strong>which are a result of a formula<\/strong> is by using the Paste Special method.<\/p>\n<p>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.<\/p>\n<p>Suppose you have a dataset as shown below and you want to find and select all the cells that have errors in it.<\/p>\n<p><img decoding=\"async\" class=\"size-full wp-image-32271 aligncenter\" src=\"https:\/\/trumpexcel.com\/wp-content\/uploads\/2020\/04\/KPI-Data-with-errors.png\" alt=\"KPI Data with errors\" width=\"620\" height=\"508\" srcset=\"https:\/\/trumpexcel.com\/wp-content\/uploads\/2020\/04\/KPI-Data-with-errors.png 620w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2020\/04\/KPI-Data-with-errors-366x300.png 366w\" sizes=\"(max-width: 620px) 100vw, 620px\" \/><\/p>\n<p>Below are the steps to use Go To Special option to find and select all the errors:<\/p>\n<ol>\n<li>Select the entire dataset where you want to check for errors<\/li>\n<li>Click the Home tab<\/li>\n<li>In the Editing group, click on the &#8216;Find &amp; Select&#8217; option.<\/li>\n<li>In the options that show up, click on the &#8216;Go-To Special&#8217; option<img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-32269 aligncenter\" src=\"https:\/\/trumpexcel.com\/wp-content\/uploads\/2020\/04\/Click-on-the-Go-To-Special-dialog-box.png\" alt=\"Click on the Go To Special dialog box\" width=\"295\" height=\"510\" srcset=\"https:\/\/trumpexcel.com\/wp-content\/uploads\/2020\/04\/Click-on-the-Go-To-Special-dialog-box.png 295w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2020\/04\/Click-on-the-Go-To-Special-dialog-box-174x300.png 174w\" sizes=\"(max-width: 295px) 100vw, 295px\" \/><\/li>\n<li>In the Go To Special dialog box, click on the Formulas option<\/li>\n<li>In the options within Formulas, uncheck everything except the Error checkbox.<img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-32270 aligncenter\" src=\"https:\/\/trumpexcel.com\/wp-content\/uploads\/2020\/04\/Select-formula-with-errors-option.png\" alt=\"Select formula with errors option\" width=\"380\" height=\"431\" srcset=\"https:\/\/trumpexcel.com\/wp-content\/uploads\/2020\/04\/Select-formula-with-errors-option.png 380w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2020\/04\/Select-formula-with-errors-option-265x300.png 265w\" sizes=\"(max-width: 380px) 100vw, 380px\" \/><\/li>\n<li>Click OK<\/li>\n<\/ol>\n<p>The above steps would instantly select all the cells that have formulas that return an error.<\/p>\n<p>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.<\/p>\n<div class=\"cbred\">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.<\/div>\n<p>So these are two quick methods you can use to find and fix the #REF error (reference error) in Excel.<\/p>\n<p>Hope you found this tutorial useful.<\/p>\n<p><strong>You may also like the following Excel tutorials:<\/strong><\/p>\n<ul>\n<li><a href=\"https:\/\/trumpexcel.com\/iferror-vlookup\/\" target=\"_blank\" rel=\"noopener noreferrer\">Use IFERROR with VLOOKUP to Get Rid of #N\/A Errors<\/a><\/li>\n<li><a href=\"https:\/\/trumpexcel.com\/find-hyperlinks-in-excel\/\" target=\"_blank\" rel=\"noopener noreferrer\">How to Quickly Find Hyperlinks in Excel<\/a><\/li>\n<li><a href=\"https:\/\/trumpexcel.com\/find-hyperlinks-in-excel\/\" target=\"_blank\" rel=\"noopener noreferrer\">How to Find Hyperlinks in Excel<\/a><\/li>\n<li><a href=\"https:\/\/trumpexcel.com\/excel-formula-debugging\/\" target=\"_blank\" rel=\"noopener noreferrer\">Identify Errors Using Excel Formula Debugging<\/a><\/li>\n<li><a href=\"https:\/\/trumpexcel.com\/excel-iferror-function\/\" target=\"_blank\" rel=\"noopener noreferrer\">Excel IFERROR Function<\/a><\/li>\n<li><a href=\"https:\/\/trumpexcel.com\/name-error-excel\/\" target=\"_blank\" rel=\"noopener\" data-schema-attribute=\"\">#NAME Error in Excel &#8211; What Causes it and How to Fix it!<\/a><\/li>\n<li><a href=\"https:\/\/trumpexcel.com\/div-error-in-excel\/\" target=\"_blank\" rel=\"noreferrer noopener\" data-type=\"post\" data-id=\"36329\">How to Get Rid of #DIV\/0! Error in Excel?<\/a><\/li>\n<li><a href=\"https:\/\/trumpexcel.com\/fix-value-error-in-excel\/\" target=\"_blank\" rel=\"noreferrer noopener\" data-type=\"post\" data-id=\"36240\">How to Fix #VALUE Error in Excel?<\/a><\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>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, &#8230; <\/p>\n<p class=\"read-more-container\"><a title=\"#REF! Error in Excel &#8211; How to Fix the Reference Error!\" class=\"read-more button\" href=\"https:\/\/trumpexcel.com\/ref-error-in-excel\/#more-30829\" aria-label=\"Read more about #REF! Error in Excel &#8211; How to Fix the Reference Error!\">Read more<\/a><\/p>\n","protected":false},"author":80,"featured_media":32271,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[7,153,4],"tags":[],"class_list":["post-30829","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-excel-tips","category-excel-functions","category-formula-hacks"],"_links":{"self":[{"href":"https:\/\/trumpexcel.com\/wp-json\/wp\/v2\/posts\/30829","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/trumpexcel.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/trumpexcel.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/trumpexcel.com\/wp-json\/wp\/v2\/users\/80"}],"replies":[{"embeddable":true,"href":"https:\/\/trumpexcel.com\/wp-json\/wp\/v2\/comments?post=30829"}],"version-history":[{"count":0,"href":"https:\/\/trumpexcel.com\/wp-json\/wp\/v2\/posts\/30829\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/trumpexcel.com\/wp-json\/wp\/v2\/media\/32271"}],"wp:attachment":[{"href":"https:\/\/trumpexcel.com\/wp-json\/wp\/v2\/media?parent=30829"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/trumpexcel.com\/wp-json\/wp\/v2\/categories?post=30829"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/trumpexcel.com\/wp-json\/wp\/v2\/tags?post=30829"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}