{"id":5869,"date":"2021-05-28T09:50:55","date_gmt":"2021-05-28T09:50:55","guid":{"rendered":"https:\/\/officetuts.net\/excel\/?p=5869"},"modified":"2024-03-29T14:12:04","modified_gmt":"2024-03-29T14:12:04","slug":"find-and-replace-in-formulas","status":"publish","type":"post","link":"https:\/\/officetuts.net\/excel\/formulas\/find-and-replace-in-formulas\/","title":{"rendered":"Use Find and Replace in Excel Formulas"},"content":{"rendered":"\n
Find-and-Replace-in-Formulas<\/a>Download File<\/a><\/div>\n\n\n\n

We have all been in a situation where we realize that we have made the same error in a whole document, or a whole worksheet, which can be easily resolved if we just replace some certain value with another one.<\/p>\n\n\n\n

For these instances and occurrences, we can use the Find and Replace<\/strong> option in Excel.<\/p>\n\n\n\n

Using Find and Replace<\/h2>\n\n\n\n

For our example, we will use the table that we created for explaining the functionalities and use of the Index<\/strong> and Match<\/strong> functions. The table presents the list of all Marvel movies with their revenue rating (in relation to other Marvel movies on the list), release date, gross revenue, all-time ranking, and budget.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

In relation to this table, we have also created the Index <\/a><\/strong>and Match<\/strong><\/a> functions that can help us easily navigate and find the movie based on their revenue rating. For our function to work and values to change, we only have to change the value in cell<\/a> I2<\/strong> i.e. revenue rating.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Let us now assume that we want to find the first part of the Thor<\/strong> movie and replace its name with Thor: Original one<\/strong>.<\/p>\n\n\n\n

For this, we can either click on CTRL + H<\/strong> or go to the Home tab<\/strong>, find the Editing subtab<\/strong>, go to Find & Select<\/strong>, and then choose Replace<\/strong> from the dropdown menu.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

We will then type our desired data into the Find what: <\/a><\/strong>and Replace with:<\/strong><\/a> <\/strong>fields in the pop-up window and then click on Find Next <\/strong>.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

The movie called Thor <\/strong>is the first one inside our table so when we find it we will just click Replace<\/strong> and then close our Find and Replace<\/strong> window.<\/p>\n\n\n\n

We do not want to click on Replace All<\/strong>, since we would replace the values in all three of the Thor movies.<\/p>\n\n\n\n

Find and Replace in Formulas<\/h2>\n\n\n\n

Now, let us assume that one thing occurred. We created another column at the beginning of our worksheet, and we named it Release year<\/strong>. This is now column A<\/strong>. Moreover, we have copied and pasted data<\/a> from previous column<\/strong> A<\/strong> to column<\/strong> B<\/strong>.<\/p>\n\n\n\n

So basically, now column B <\/strong>is the Revenue rating<\/strong> column.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

To find out the release year of the movies, we will use the formula:<\/p>\n\n\n\n

=YEAR(serial_number)<\/code><\/pre>\n\n\n\n
In our case, the serial number is the U.S. release date<\/strong>, so our A2<\/strong> row will have the following value:<\/p>\n\n\n\n
=YEAR([@[U.S. release date]])<\/code><\/pre>\n\n\n\n
One thing that happened since we changed the data in our A column<\/strong>. Now, our Index and Match<\/a> formulas simply do not work, and they show #N\/A (not applicable, not available) data:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Our formula in cell J2<\/strong>, which is the cell right to the Movie<\/strong> text in the picture below is:<\/p>\n\n\n\n
=INDEX(Table1,MATCH($J$1,$A$2:$A$24,0),MATCH($I2,$A$1:$G$1,0))<\/code><\/pre>\n\n\n\n
From the look of this formula, we have to change the value of lookup_array<\/strong> in our first MATCH<\/strong> function that defines our row_num<\/strong> values.<\/p>\n\n\n\n
We will not have to do anything with the second MATCH<\/strong> function, which defines column_num<\/strong> since it was not impacted by the changes.<\/p>\n\n\n\n
Since we want to change only the number in our formulas, the good thing for us is to select the range or the worksheet we are using, press Ctrl + G<\/strong> to enable Go To<\/strong> dialog, and then click Special<\/strong>.<\/p>\n\n\n\n
We can also find Go To<\/strong> dialog in the same location as we did for Replace<\/strong>, and that is Home >><\/strong> Editing<\/strong>– Find & Select<\/strong>.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Once we get to this option, we will click on Formulas<\/strong> so that we select only the cells with formulas in our worksheet.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
We click OK <\/strong>and then click again on CTRL + H<\/strong> to get to Find and Replace<\/strong>. We will find all of the $A$2 <\/strong>values and replace them with $B$2<\/strong>. To do this, we input the values into Find what:<\/strong> and Replace with:<\/strong> fields and then click Replace All<\/strong>.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Excel will inform us about the changes made:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
We will then click OK <\/strong>to see the changes.<\/p>\n\n\n\n
Our table looks normal again:<\/p>\n\n\n\n
\"\"<\/figure>\n","protected":false},"excerpt":{"rendered":"
We have all been in a situation where we realize that we have made the same error in a whole document, or a…<\/p>\n","protected":false},"author":4,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[23],"tags":[170,190],"yoast_head":"\nUse Find and Replace in Excel Formulas<\/title>\n<meta name=\"description\" content=\"Learn how to use the Find and Replace option in Excel to quickly and easily replace values in a document or worksheet. Download our sample file.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/officetuts.net\/excel\/formulas\/find-and-replace-in-formulas\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Use Find and Replace in Excel Formulas\" \/>\n<meta property=\"og:description\" content=\"Learn how to use the Find and Replace option in Excel to quickly and easily replace values in a document or worksheet. Download our sample file.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/officetuts.net\/excel\/formulas\/find-and-replace-in-formulas\/\" \/>\n<meta property=\"article:published_time\" content=\"2021-05-28T09:50:55+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-03-29T14:12:04+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/05\/word-image-184.png\" \/>\n<meta name=\"author\" content=\"Harun Spahic\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Harun Spahic\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/officetuts.net\/excel\/formulas\/find-and-replace-in-formulas\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/officetuts.net\/excel\/formulas\/find-and-replace-in-formulas\/\"},\"author\":{\"name\":\"Harun Spahic\",\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/694491c70f776081acdac4ae41f222a2\"},\"headline\":\"Use Find and Replace in Excel Formulas\",\"datePublished\":\"2021-05-28T09:50:55+00:00\",\"dateModified\":\"2024-03-29T14:12:04+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/officetuts.net\/excel\/formulas\/find-and-replace-in-formulas\/\"},\"wordCount\":673,\"publisher\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42\"},\"image\":{\"@id\":\"https:\/\/officetuts.net\/excel\/formulas\/find-and-replace-in-formulas\/#primaryimage\"},\"thumbnailUrl\":\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/05\/word-image-184.png\",\"keywords\":[\"file\",\"pinterest\"],\"articleSection\":[\"formulas\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/officetuts.net\/excel\/formulas\/find-and-replace-in-formulas\/\",\"url\":\"https:\/\/officetuts.net\/excel\/formulas\/find-and-replace-in-formulas\/\",\"name\":\"Use Find and Replace in Excel Formulas\",\"isPartOf\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/officetuts.net\/excel\/formulas\/find-and-replace-in-formulas\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/officetuts.net\/excel\/formulas\/find-and-replace-in-formulas\/#primaryimage\"},\"thumbnailUrl\":\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/05\/word-image-184.png\",\"datePublished\":\"2021-05-28T09:50:55+00:00\",\"dateModified\":\"2024-03-29T14:12:04+00:00\",\"description\":\"Learn how to use the Find and Replace option in Excel to quickly and easily replace values in a document or worksheet. Download our sample file.\",\"breadcrumb\":{\"@id\":\"https:\/\/officetuts.net\/excel\/formulas\/find-and-replace-in-formulas\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/officetuts.net\/excel\/formulas\/find-and-replace-in-formulas\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/officetuts.net\/excel\/formulas\/find-and-replace-in-formulas\/#primaryimage\",\"url\":\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/05\/word-image-184.png\",\"contentUrl\":\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/05\/word-image-184.png\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/officetuts.net\/excel\/formulas\/find-and-replace-in-formulas\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/officetuts.net\/excel\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Use Find and Replace in Excel Formulas\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/officetuts.net\/excel\/#website\",\"url\":\"https:\/\/officetuts.net\/excel\/\",\"name\":\"\",\"description\":\"\",\"publisher\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/officetuts.net\/excel\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42\",\"name\":\"Tomasz Decker\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/18cbe22837193574870ae40ba56bf712?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/18cbe22837193574870ae40ba56bf712?s=96&d=mm&r=g\",\"caption\":\"Tomasz Decker\"},\"logo\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/\"},\"description\":\"Spreadsheet and Python enthusiast.\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/694491c70f776081acdac4ae41f222a2\",\"name\":\"Harun Spahic\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/b7b887a2249679182be5550a4421a7a9?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/b7b887a2249679182be5550a4421a7a9?s=96&d=mm&r=g\",\"caption\":\"Harun Spahic\"},\"sameAs\":[\"http:\/\/human.bsru.ac.th\/\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Use Find and Replace in Excel Formulas","description":"Learn how to use the Find and Replace option in Excel to quickly and easily replace values in a document or worksheet. Download our sample file.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/officetuts.net\/excel\/formulas\/find-and-replace-in-formulas\/","og_locale":"en_US","og_type":"article","og_title":"Use Find and Replace in Excel Formulas","og_description":"Learn how to use the Find and Replace option in Excel to quickly and easily replace values in a document or worksheet. Download our sample file.","og_url":"https:\/\/officetuts.net\/excel\/formulas\/find-and-replace-in-formulas\/","article_published_time":"2021-05-28T09:50:55+00:00","article_modified_time":"2024-03-29T14:12:04+00:00","og_image":[{"url":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/05\/word-image-184.png"}],"author":"Harun Spahic","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Harun Spahic","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/officetuts.net\/excel\/formulas\/find-and-replace-in-formulas\/#article","isPartOf":{"@id":"https:\/\/officetuts.net\/excel\/formulas\/find-and-replace-in-formulas\/"},"author":{"name":"Harun Spahic","@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/694491c70f776081acdac4ae41f222a2"},"headline":"Use Find and Replace in Excel Formulas","datePublished":"2021-05-28T09:50:55+00:00","dateModified":"2024-03-29T14:12:04+00:00","mainEntityOfPage":{"@id":"https:\/\/officetuts.net\/excel\/formulas\/find-and-replace-in-formulas\/"},"wordCount":673,"publisher":{"@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42"},"image":{"@id":"https:\/\/officetuts.net\/excel\/formulas\/find-and-replace-in-formulas\/#primaryimage"},"thumbnailUrl":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/05\/word-image-184.png","keywords":["file","pinterest"],"articleSection":["formulas"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/officetuts.net\/excel\/formulas\/find-and-replace-in-formulas\/","url":"https:\/\/officetuts.net\/excel\/formulas\/find-and-replace-in-formulas\/","name":"Use Find and Replace in Excel Formulas","isPartOf":{"@id":"https:\/\/officetuts.net\/excel\/#website"},"primaryImageOfPage":{"@id":"https:\/\/officetuts.net\/excel\/formulas\/find-and-replace-in-formulas\/#primaryimage"},"image":{"@id":"https:\/\/officetuts.net\/excel\/formulas\/find-and-replace-in-formulas\/#primaryimage"},"thumbnailUrl":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/05\/word-image-184.png","datePublished":"2021-05-28T09:50:55+00:00","dateModified":"2024-03-29T14:12:04+00:00","description":"Learn how to use the Find and Replace option in Excel to quickly and easily replace values in a document or worksheet. Download our sample file.","breadcrumb":{"@id":"https:\/\/officetuts.net\/excel\/formulas\/find-and-replace-in-formulas\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/officetuts.net\/excel\/formulas\/find-and-replace-in-formulas\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/officetuts.net\/excel\/formulas\/find-and-replace-in-formulas\/#primaryimage","url":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/05\/word-image-184.png","contentUrl":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/05\/word-image-184.png"},{"@type":"BreadcrumbList","@id":"https:\/\/officetuts.net\/excel\/formulas\/find-and-replace-in-formulas\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/officetuts.net\/excel\/"},{"@type":"ListItem","position":2,"name":"Use Find and Replace in Excel Formulas"}]},{"@type":"WebSite","@id":"https:\/\/officetuts.net\/excel\/#website","url":"https:\/\/officetuts.net\/excel\/","name":"","description":"","publisher":{"@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/officetuts.net\/excel\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42","name":"Tomasz Decker","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/18cbe22837193574870ae40ba56bf712?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/18cbe22837193574870ae40ba56bf712?s=96&d=mm&r=g","caption":"Tomasz Decker"},"logo":{"@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/"},"description":"Spreadsheet and Python enthusiast."},{"@type":"Person","@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/694491c70f776081acdac4ae41f222a2","name":"Harun Spahic","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/b7b887a2249679182be5550a4421a7a9?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/b7b887a2249679182be5550a4421a7a9?s=96&d=mm&r=g","caption":"Harun Spahic"},"sameAs":["http:\/\/human.bsru.ac.th\/"]}]}},"_links":{"self":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts\/5869"}],"collection":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/comments?post=5869"}],"version-history":[{"count":10,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts\/5869\/revisions"}],"predecessor-version":[{"id":17394,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts\/5869\/revisions\/17394"}],"wp:attachment":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/media?parent=5869"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/categories?post=5869"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/tags?post=5869"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}