{"id":1256,"date":"2020-06-30T19:51:25","date_gmt":"2020-06-30T17:51:25","guid":{"rendered":"https:\/\/www.myexcelonline.com\/?p=1256"},"modified":"2024-04-14T14:41:17","modified_gmt":"2024-04-14T12:41:17","slug":"clear-a-ref-error-in-excel","status":"publish","type":"post","link":"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/clear-a-ref-error-in-excel\/","title":{"rendered":"Clear a #REF error in Excel"},"content":{"rendered":"<p>Many times, you may have faced the problem that Excel is not returning the desired value, instead is showing an error. The most <strong>common one amongst these is a#REF! error<\/strong> in Excel.<\/p>\n<p>A #REF error in Excel is shown when the <strong>cell reference provided in a <a class=\"wpil_keyword_link\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/top-excel-formulas-function-examples-to-get-better-at-microsoft-excel\/\" target=\"_blank\" rel=\"noopener\" title=\"formula\" data-wpil-keyword-link=\"linked\">formula<\/a> is not valid<\/strong>. It is important to know why this error occurs and how to fix it.<\/p>\n<p>In this article, you will learn the following:<\/p>\n<ul>\n<li><a href=\"#what-is-ref-error-in-excel\"><strong>What is #REF error in Excel?<\/strong><\/a><\/li>\n<li><a href=\"#how-to-fix-ref-error-in-excel\"><strong>How to fix #REF error in Excel?<\/strong><\/a><\/li>\n<li><a href=\"#conclusion\"><strong>Conclusion<\/strong><\/a><\/li>\n<\/ul>\n<h3><a id=\"what-is-ref-error-in-excel\"><\/a>What is #REF! error in Excel?<\/h3>\n<p>What does #REF mean in Excel? #REF! error stands of reference i.e. this error is shown when the cell that is referenced to in a formula is no longer valid or does not exist.<\/p>\n<p>So, #REF error in Excel occurs when the reference used is invalid. It can happen due to one of the following reasons:<\/p>\n<ul>\n<li>Row, column or sheet has been deleted.<\/li>\n<li>Formula contains incorrect or invalid cell reference.<\/li>\n<\/ul>\n<p>This means that Excel returns a #REF error when the formula refers to a cell that isn\u2019t valid.<\/p>\n<p>Let&#8217;s look at a few examples to see when this error occurs and how to fix it!<\/p>\n<h3><a id=\"how-to-fix-ref-error-in-excel\"><\/a>How to fix #REF error in Excel?<\/h3>\n<p><strong><span style=\"color: #ff0000\">Example 1:<\/span>\u00a0<\/strong><\/p>\n\n<p>Watch the <strong>YouTube video<\/strong> to learn how to fix #REF error in Excel and if you like it give it a thumbs-up!<br \/>\n<iframe src=\"https:\/\/www.youtube.com\/embed\/04RXP2WypF0?rel=0 \" width=\"800\" height=\"400\" frameborder=\"0\" allowfullscreen=\"allowfullscreen\"><span data-mce-type=\"bookmark\" style=\"width: 0px;overflow: hidden;line-height: 0\" class=\"mce_SELRES_start\">\ufeff<\/span><\/iframe><\/p>\n<p><a href=\"https:\/\/www.youtube.com\/c\/MyExcelOnlinecomtutorials?sub_confirmation=1\" target=\"_blank\" rel=\"noopener noreferrer nofollow\"><img loading=\"lazy\" decoding=\"async\" class=\"alignmiddle wp-image-111468 size-full\" style=\"vertical-align: middle\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/04\/1.yt_.png\" alt=\"\" title=\"\"><\/a><\/p>\n<p>In the table below, you will spot multiple #REF! error within <a class=\"wpil_keyword_link\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/top-excel-formulas-function-examples-to-get-better-at-microsoft-excel\/\" target=\"_blank\" rel=\"noopener\" title=\"formulas\" data-wpil-keyword-link=\"linked\">formulas<\/a> used in the cell.<\/p>\n<p>This has happened because you have deleted a range that contains an explicit cell reference within the formula used.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/05\/Screenshot-2020-06-27-at-7.17.34-PM.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Clear a #REF error in Excel\" class=\"alignnone size-full wp-image-18311\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/05\/Screenshot-2020-06-27-at-7.17.34-PM.png\" alt=\"Clear a #REF error in Excel\" width=\"867\" height=\"374\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/05\/Screenshot-2020-06-27-at-7.17.34-PM.png 867w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/05\/Screenshot-2020-06-27-at-7.17.34-PM-300x129.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/05\/Screenshot-2020-06-27-at-7.17.34-PM-768x331.png 768w\" sizes=\"(max-width: 867px) 100vw, 867px\" \/><\/a><\/p>\n<p>To get rid of this error message we have to select the cell(s) with this error, use the Find &amp; Replace dialog box and do the following:<\/p>\n<p><strong>Find What:<\/strong> #REF!<\/p>\n<p><strong>Replace With:<\/strong>\u00a0\u00a0 (Leave this blank)<\/p>\n<p>Press OK and it will clear the #REF error in Excel within the formula.<\/p>\n<h3>Let&#8217;s look at the step-by-step tutorial below to understand how to remove #REF in Excel.<\/h3>\n<p>Download the workbook and follow along:<\/p>\n<p><strong><span style=\"text-decoration: underline;color: #0000ff\"><a class=\"IRPP_minimalist icon-download\" style=\"color: #0000ff\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/07\/Replace-the-REF-error-2.xlsx\" target=\"_blank\"><span class=\"ctaText px-3 text-capitalize\">Download workbook<\/span><span class=\"postTitle px-3\">Replace-the-REF-error-2.xlsx<\/span><\/a><\/span><\/strong><\/p>\n<p><span style=\"color: #ff0000\"><strong>STEP 1:<\/strong><\/span> To check the cell containing the cell, simply click on the cell and press F2.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/05\/Screenshot-2020-06-27-at-7.20.09-PM.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Clear a #REF error in Excel\" class=\"alignnone size-full wp-image-18312\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/05\/Screenshot-2020-06-27-at-7.20.09-PM.png\" alt=\"Clear a #REF error in Excel\" width=\"854\" height=\"379\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/05\/Screenshot-2020-06-27-at-7.20.09-PM.png 854w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/05\/Screenshot-2020-06-27-at-7.20.09-PM-300x133.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/05\/Screenshot-2020-06-27-at-7.20.09-PM-768x341.png 768w\" sizes=\"(max-width: 854px) 100vw, 854px\" \/><\/a><\/p>\n<p>Here, since you have used an explicit cell reference and it was deleted, Excel is returning a #REF error.<\/p>\n<p><span style=\"color: #ff0000\"><strong>STEP 2:<\/strong><\/span> Highlight the table containing the errors.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/05\/Screenshot-2020-06-27-at-7.29.25-PM.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Clear a #REF error in Excel\" class=\"alignnone size-full wp-image-18317\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/05\/Screenshot-2020-06-27-at-7.29.25-PM.png\" alt=\"Clear a #REF error in Excel\" width=\"674\" height=\"269\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/05\/Screenshot-2020-06-27-at-7.29.25-PM.png 674w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/05\/Screenshot-2020-06-27-at-7.29.25-PM-300x120.png 300w\" sizes=\"(max-width: 674px) 100vw, 674px\" \/><\/a><\/p>\n<p><span style=\"color: #ff0000\"><strong>STEP 3:<\/strong><\/span> Press <strong>Ctrl + H<\/strong> to open the <strong>Find &amp; Replace<\/strong> dialog box.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/05\/Ctrl-H-shortcut.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Clear a #REF error in Excel\" class=\"alignnone size-full wp-image-18320\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/05\/Ctrl-H-shortcut.png\" alt=\"Clear a #REF error in Excel\" width=\"443\" height=\"195\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/05\/Ctrl-H-shortcut.png 443w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/05\/Ctrl-H-shortcut-300x132.png 300w\" sizes=\"(max-width: 443px) 100vw, 443px\" \/><\/a><\/p>\n<p><span style=\"color: #ff0000\"><strong>STEP 4:<\/strong><\/span> Under Find What, input <strong>#REF!<\/strong> and leave Replace as <strong>blank<\/strong>. This is done to replace all the #REF! error with a blank.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/05\/1beabdc084e11cb7e32486dab5f0170f.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Clear a #REF error in Excel\" class=\"alignnone size-full wp-image-18321\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/05\/1beabdc084e11cb7e32486dab5f0170f.png\" alt=\"Clear a #REF error in Excel\" width=\"442\" height=\"196\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/05\/1beabdc084e11cb7e32486dab5f0170f.png 442w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/05\/1beabdc084e11cb7e32486dab5f0170f-300x133.png 300w\" sizes=\"(max-width: 442px) 100vw, 442px\" \/><\/a><\/p>\n<p><span style=\"color: #ff0000\"><strong>STEP 5:<\/strong><\/span> Click on Replace All.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/05\/1beabdc084e11cb7e32486dab5f0170f-1.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Clear a #REF error in Excel\" class=\"alignnone size-full wp-image-18322\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/05\/1beabdc084e11cb7e32486dab5f0170f-1.png\" alt=\"Clear a #REF error in Excel\" width=\"442\" height=\"196\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/05\/1beabdc084e11cb7e32486dab5f0170f-1.png 442w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/05\/1beabdc084e11cb7e32486dab5f0170f-1-300x133.png 300w\" sizes=\"(max-width: 442px) 100vw, 442px\" \/><\/a><\/p>\n<p>This is how your replaced data will look like:<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/05\/2f09ecfca33cd5d6e6e711e522df5073.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Clear a #REF error in Excel\" class=\"alignnone size-full wp-image-18323\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/05\/2f09ecfca33cd5d6e6e711e522df5073.png\" alt=\"Clear a #REF error in Excel\" width=\"579\" height=\"241\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/05\/2f09ecfca33cd5d6e6e711e522df5073.png 579w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/05\/2f09ecfca33cd5d6e6e711e522df5073-300x125.png 300w\" sizes=\"(max-width: 579px) 100vw, 579px\" \/><\/a><\/p>\n<p>Let&#8217;s look at another example when this error occurs due to copy-pasting the formula from other cells.<\/p>\n<p><strong><span style=\"color: #ff0000\">Example 2:<\/span><\/strong><\/p>\n<p>In the table below, you have sales data for different customers for 4 quarters and a sum formula used to calculate the total sales. The formula used to calculate the total sales value is <strong>=SUM(B4, C4, D4, E4)<\/strong>.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/06\/2a29922bec52145fe988b72e66d75ff1-1.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Clear a #REF error in Excel\" class=\"alignnone size-full wp-image-18360\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/06\/2a29922bec52145fe988b72e66d75ff1-1.png\" alt=\"Clear a #REF error in Excel\" width=\"1025\" height=\"491\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/06\/2a29922bec52145fe988b72e66d75ff1-1.png 1025w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/06\/2a29922bec52145fe988b72e66d75ff1-1-300x144.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/06\/2a29922bec52145fe988b72e66d75ff1-1-768x368.png 768w\" sizes=\"(max-width: 1025px) 100vw, 1025px\" \/><\/a><\/p>\n<p>If you try and <strong>delete Column E<\/strong> (Quarter 4), the sum formula will change to =SUM(B4, C4, D4,#REF!) and return an error &#8211; #REF.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/06\/e52327b899922caae57510123e1055e4.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Clear a #REF error in Excel\" class=\"alignnone size-full wp-image-18361\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/06\/e52327b899922caae57510123e1055e4.png\" alt=\"Clear a #REF error in Excel\" width=\"968\" height=\"492\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/06\/e52327b899922caae57510123e1055e4.png 968w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/06\/e52327b899922caae57510123e1055e4-300x152.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/06\/e52327b899922caae57510123e1055e4-768x390.png 768w\" sizes=\"(max-width: 968px) 100vw, 968px\" \/><\/a><\/p>\n<p>This error is caused because the formula to calculate the total sales uses explicit cell reference. 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.<\/p>\n<p>A simple fix to this problem is to use a range instead of an explicit cell reference. Let&#8217;s look at the step-by-step tutorial to learn how:<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/06\/2b494f7964c43cc480d272176e2ba4b4.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Clear a #REF error in Excel\" class=\"alignnone size-full wp-image-18363\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/06\/2b494f7964c43cc480d272176e2ba4b4.png\" alt=\"Clear a #REF error in Excel\" width=\"1103\" height=\"456\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/06\/2b494f7964c43cc480d272176e2ba4b4.png 1103w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/06\/2b494f7964c43cc480d272176e2ba4b4-300x124.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/06\/2b494f7964c43cc480d272176e2ba4b4-1024x423.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/06\/2b494f7964c43cc480d272176e2ba4b4-768x318.png 768w\" sizes=\"(max-width: 1103px) 100vw, 1103px\" \/><\/a><\/p>\n<p><span style=\"color: #ff0000\"><strong>STEP 1:\u00a0<\/strong><span style=\"color: #000000\">Use formula <strong>=SUM(B4: E4)\u00a0<\/strong>in cell F4 and copy-paste the formula below to cells F5: F11.<\/span><\/span><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/06\/80485770edc7bee2dc8c66808d513997.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Clear a #REF error in Excel\" class=\"alignnone size-full wp-image-18367\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/06\/80485770edc7bee2dc8c66808d513997.png\" alt=\"Clear a #REF error in Excel\" width=\"1106\" height=\"500\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/06\/80485770edc7bee2dc8c66808d513997.png 1106w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/06\/80485770edc7bee2dc8c66808d513997-300x136.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/06\/80485770edc7bee2dc8c66808d513997-1024x463.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/06\/80485770edc7bee2dc8c66808d513997-768x347.png 768w\" sizes=\"(max-width: 1106px) 100vw, 1106px\" \/><\/a><\/p>\n<p><span style=\"color: #ff0000\"><strong>STEP 2: <\/strong><span style=\"color: #000000\">Now delete the <strong>Column E<\/strong> to get the total sales for only 3 quarters.<\/span><\/span><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/06\/f95adde54f101ea77a255ddf5ce47e28.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Clear a #REF error in Excel\" class=\"alignnone size-full wp-image-18365\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/06\/f95adde54f101ea77a255ddf5ce47e28.png\" alt=\"Clear a #REF error in Excel\" width=\"1049\" height=\"496\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/06\/f95adde54f101ea77a255ddf5ce47e28.png 1049w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/06\/f95adde54f101ea77a255ddf5ce47e28-300x142.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/06\/f95adde54f101ea77a255ddf5ce47e28-1024x484.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/06\/f95adde54f101ea77a255ddf5ce47e28-768x363.png 768w\" sizes=\"(max-width: 1049px) 100vw, 1049px\" \/><\/a><\/p>\n<p>If you change the formula from <strong>=SUM(B4, C4, D4, E4)\u00a0<\/strong>to <strong>=SUM(B4: E4)<\/strong>, you will no longer to vulnerable to #REF in Excel. This formula recalculates the total sales value by removing the deleted cell.<\/p>\n<p>Hence, it is advised to use range while writing a formula instead of explicit cell reference.<\/p>\n<p>Let&#8217;s take a look at another example when the error occurred due to VLOOKUP containing invalid cell reference.<\/p>\n<p><strong><span style=\"color: #ff0000\">Example 3:<\/span><\/strong><\/p>\n<p>In the table below you have quarterly and total sales for different customers and using the VLOOKUP formula, you have tried to find out the total sales for the customer name mentioned.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/05\/d144ebb0fa4387515206e6a8a90be752.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Clear a #REF error in Excel\" class=\"alignnone size-full wp-image-18346\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/05\/d144ebb0fa4387515206e6a8a90be752.png\" alt=\"Clear a #REF error in Excel\" width=\"1102\" height=\"417\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/05\/d144ebb0fa4387515206e6a8a90be752.png 1102w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/05\/d144ebb0fa4387515206e6a8a90be752-300x114.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/05\/d144ebb0fa4387515206e6a8a90be752-1024x387.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/05\/d144ebb0fa4387515206e6a8a90be752-768x291.png 768w\" sizes=\"(max-width: 1102px) 100vw, 1102px\" \/><\/a><\/p>\n<p>The formula used to find the total sales for customers mentioned in cell H4 is <strong>=VLOOKUP(H4,$A$4:$F$11,7,0).<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/05\/dbc75533243b1b3ca726289ef250808c-1.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Clear a #REF error in Excel\" class=\"alignnone size-full wp-image-18348\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/05\/dbc75533243b1b3ca726289ef250808c-1.png\" alt=\"Clear a #REF error in Excel\" width=\"1132\" height=\"487\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/05\/dbc75533243b1b3ca726289ef250808c-1.png 1132w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/05\/dbc75533243b1b3ca726289ef250808c-1-300x129.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/05\/dbc75533243b1b3ca726289ef250808c-1-1024x441.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/05\/dbc75533243b1b3ca726289ef250808c-1-768x330.png 768w\" sizes=\"(max-width: 1132px) 100vw, 1132px\" \/><\/a><\/p>\n<p>If you look into the formula used in detail, you will see that the value used to indicate the column index number is incorrect.<\/p>\n<p>The arguments for a VLOOKUP function is:<\/p>\n<ul>\n<li><strong>Lookup_value<\/strong> = The value you want to look up in the first column of the table.<\/li>\n<li><strong>Table_array<\/strong> = The table from which you need to retrieve the data.<\/li>\n<li><strong>Col_index_num<\/strong> = The column number in the table array from which matching value should be returned.<\/li>\n<li><strong>Range_lookup<\/strong> = Value should be 1 if you want an approximate match or 0 if you want an exact match of the return value.<\/li>\n<\/ul>\n<p>Excel is returning an error in this formula because VLOOKUP is looking to return a value from the 7th column but the reference $A$4:$F$11 contains only 6 columns.<\/p>\n<p>To fix this error, use the formula <strong>=VLOOKUP(H4,$A$4:$F$11,6,0).<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/05\/04a349c4389c7936ac4decce8bd55a38.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Clear a #REF error in Excel\" class=\"alignnone size-full wp-image-18350\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/05\/04a349c4389c7936ac4decce8bd55a38.png\" alt=\"Clear a #REF error in Excel\" width=\"1133\" height=\"489\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/05\/04a349c4389c7936ac4decce8bd55a38.png 1133w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/05\/04a349c4389c7936ac4decce8bd55a38-300x129.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/05\/04a349c4389c7936ac4decce8bd55a38-1024x442.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/05\/04a349c4389c7936ac4decce8bd55a38-768x331.png 768w\" sizes=\"(max-width: 1133px) 100vw, 1133px\" \/><\/a><\/p>\n<h3><a id=\"conclusion\"><\/a>Conclusion<\/h3>\n<p>Excel returns #REF error to indicate that the cell reference used in the formula is not valid. It may be because the referred cell, row, or column has been deleted or the range used in the formula is incorrect.<\/p>\n<p>With the help of this tutorial, you will be able to spot a #REF <a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/ifna-function\/\" target=\"_blank\" rel=\"noopener\">error in Excel<\/a> and learn how to fix #REF in Excel.<\/p>\n<p><span style=\"text-decoration: underline;color: #0000ff\"><strong>HELPFUL RESOURCE:<\/strong> <\/span><\/p>\n<p>To learn how to correct the Hash Error, <a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/hash-error\/\" target=\"_blank\">click here<\/a>.<\/p>\n<p><strong>Make sure to download our FREE PDF on the<a href=\"https:\/\/app.monstercampaigns.com\/c\/czseo0viyrmspcx5ndve\/\" target=\"_blank\" rel=\"noopener noreferrer\"> 333 Excel keyboard Shortcuts here<\/a>:<\/strong><\/p>\n<p><a href=\"https:\/\/app.monstercampaigns.com\/c\/czseo0viyrmspcx5ndve\/\" target=\"_blank\" rel=\"nofollow noopener\"><img loading=\"lazy\" decoding=\"async\" title=\"How To Use Excel For Dummies\" class=\"aligncenter wp-image-14329 size-medium\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/03\/333coffeMockups-300x200.jpg\" alt=\"How To Use Excel For Dummies\" width=\"300\" height=\"200\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/03\/333coffeMockups-300x200.jpg 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/03\/333coffeMockups-1024x682.jpg 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/03\/333coffeMockups-768x512.jpg 768w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/03\/333coffeMockups.jpg 1340w\" sizes=\"(max-width: 300px) 100vw, 300px\" \/><\/a><\/p>\n<p><strong>You can learn more about how to use Excel by viewing our FREE Excel webinar training on <a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/100-43.html\" target=\"_blank\" rel=\"noopener noreferrer\">Formulas<\/a>, <a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/100-5.html\" target=\"_blank\" rel=\"noopener noreferrer\">Pivot Tables<\/a>, and <a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/100-39.html\" target=\"_blank\" rel=\"noopener noreferrer\">Macros &amp; VBA<\/a>!<\/strong><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Many times, you may have faced the problem that Excel is not returning the desired value, instead is showing an error. The most common one amongst these is a#REF! error in Excel. A #REF error in Excel is shown when the cell reference provided in a formula is not valid. It is important to know [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":17127,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"cos_headline_score":0,"cos_seo_score":0,"cos_headline_text":"Clear a #REF error in Excel","cos_headline_has_been_analyzed":false,"cos_last_analyzed_headline":[],"om_disable_all_campaigns":false},"categories":[6,37],"tags":[660,88,52],"acf":[],"_links":{"self":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/1256"}],"collection":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/comments?post=1256"}],"version-history":[{"count":0,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/1256\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media\/17127"}],"wp:attachment":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media?parent=1256"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/categories?post=1256"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/tags?post=1256"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}