{"id":360,"date":"2018-06-27T17:45:50","date_gmt":"2018-06-27T17:45:50","guid":{"rendered":"http:\/\/officetuts.net\/excel\/?p=360"},"modified":"2024-03-14T13:24:40","modified_gmt":"2024-03-14T13:24:40","slug":"finding-and-replacing-data","status":"publish","type":"post","link":"https:\/\/officetuts.net\/excel\/training\/finding-and-replacing-data\/","title":{"rendered":"Finding and Replacing Data"},"content":{"rendered":"\n<p>When you work with Excel, sometimes you have to deal with very large amounts of data. As a result, finding a particular value can be very time-consuming. Fortunately, Excel comes with a powerful search tool, thanks to which you can quickly find the data you want.<\/p>\n\n\n\n<h2 class=\"wp-block-heading format\">Finding Data<\/h2>\n\n\n\n<p>To open the&nbsp;<strong>Find and Replace<\/strong>&nbsp;window, go to the&nbsp;<strong>HOME &gt;&gt; Editing &gt;&gt; Find &amp; Select &gt;&gt; Find<\/strong>&nbsp;or use one of the keyboard shortcuts:&nbsp;<strong><span class=\"shortcut\">Ctrl + F<\/span><\/strong>&nbsp;or&nbsp;<strong>Shift + F5<\/strong>.<\/p>\n\n\n\n<p>When a new window appears, you will notice that the tool in its basic form doesn\u2019t contain many options.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"457\" height=\"200\" src=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/find-and-replace.png\" alt=\"\" class=\"wp-image-361\" srcset=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/find-and-replace.png 457w, https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/find-and-replace-300x131.png 300w\" sizes=\"(max-width: 457px) 100vw, 457px\" \/><\/figure>\n\n\n\n<p>If you want to have access to more settings, click&nbsp;<strong>Options &gt;&gt;<\/strong>&nbsp;button.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"466\" height=\"249\" src=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/find-and-replace-options.png\" alt=\"\" class=\"wp-image-362\" srcset=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/find-and-replace-options.png 466w, https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/find-and-replace-options-300x160.png 300w\" sizes=\"(max-width: 466px) 100vw, 466px\" \/><\/figure>\n\n\n\n<p>Here, you will find here three drop-down menus.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Options in the Basic Find Window<\/h3>\n\n\n\n<p class=\"format\"><strong>Within:<\/strong><\/p>\n\n\n\n<p>You can choose whether you want to scan for data in this sheet only or in the entire workbook.<\/p>\n\n\n\n<p class=\"format\"><strong>Search:<\/strong><\/p>\n\n\n\n<p>Here, you can decide whether the values should be searched by rows or columns. This means that when a&nbsp;<strong>Search&nbsp;<\/strong>option is set to&nbsp;<strong>By Columns<\/strong>&nbsp;and you&nbsp;click the&nbsp;<strong>Find Next<\/strong>&nbsp;button, Excel will start searching in the first column and then in the next.<\/p>\n\n\n\n<p class=\"format\"><strong>Look in:<\/strong><\/p>\n\n\n\n<p>You can choose whether Excel searches for data in formulas, values or <a href=\"https:\/\/officetuts.net\/excel\/training\/comments\/\">comments<\/a>.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Advanced Settings<\/h3>\n\n\n\n<p>Click the&nbsp;<strong>Options<\/strong>&nbsp;button to access more settings:<\/p>\n\n\n\n<ul>\n<li><strong>Match case<\/strong>: If unchecked, variations of capitalization (e.g., \u201cJohn Smith\u201d vs. \u201cjohn smith\u201d) will be considered a match.<\/li>\n\n\n\n<li><strong>Match entire cell contents<\/strong>: When checked, only cells containing the exact phrase you type will be considered.<\/li>\n\n\n\n<li><strong>Format<\/strong>: Set additional parameters (e.g., font, background color). For instance, you can search for \u201cJohn Smith\u201d with a blue background.<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading format\">Replacing Data<\/h2>\n\n\n\n<p>You will find a Replace button in&nbsp;<strong>HOME &gt;&gt; Editing &gt;&gt; Find &amp; Select &gt;&gt; Replace<\/strong>. You can also use the&nbsp;<strong><span class=\"shortcut\">Ctrl + H<\/span><\/strong>&nbsp;keyboard shortcut.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"466\" height=\"249\" src=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/replace.png\" alt=\"\" class=\"wp-image-363\" srcset=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/replace.png 466w, https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/replace-300x160.png 300w\" sizes=\"(max-width: 466px) 100vw, 466px\" \/><\/figure>\n\n\n\n<p>This tab is very similar to the&nbsp;<strong>Find<\/strong>&nbsp;tab. The difference is that in the&nbsp;<strong>Replace<\/strong>&nbsp;tab there is an additional textbox called&nbsp;<strong>Replace with<\/strong>. There, you can enter the text to which you want to replace the text inside the&nbsp;<strong>Find what&nbsp;<\/strong>text box. You can also select the formatting style.<\/p>\n\n\n\n<p>If you don&#8217;t want to change the formatting style, but the format style is already set, you can remove it by clicking on the triangle on the&nbsp;<strong>Format<\/strong>&nbsp;button and then selecting&nbsp;<strong>Clear Replace Format<\/strong>.<\/p>\n\n\n\n<p>If you want to change the formatting to normal, click on the triangle, then select&nbsp;<strong>Choose Format From Cell&nbsp;<\/strong>and click on the empty cell.<\/p>\n\n\n\n<blockquote class=\"wp-block-quote\">\n<p>If you\u2019re unsure about the cell format, use the small triangle next to the&nbsp;<strong>Format<\/strong>&nbsp;button to choose a cell as a reference.<\/p>\n<cite>TIP<\/cite><\/blockquote>\n\n\n\n<p>Remember, mastering the\u00a0<strong>Find and Replace<\/strong>\u00a0functionality empowers you to efficiently manage your data, whether you\u2019re working on financial spreadsheets, databases, or any other Excel project.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Excel 365 Update<\/h2>\n\n\n\n<ul>\n<li><strong>Enhanced Search Options:<\/strong>&nbsp;Excel 365 offers more options for refining your search criteria. You can now use wildcards like asterisks (*) and question marks (?) to represent multiple characters or single characters, respectively. This provides greater flexibility when searching for specific patterns within your data.&nbsp;<a target=\"_blank\" rel=\"noreferrer noopener\" href=\"https:\/\/support.microsoft.com\/en-au\/office\/find-or-replace-text-and-numbers-on-a-worksheet-0e304ca5-ecef-4808-b90f-fdb42f892e90\">https:\/\/support.microsoft.com\/en-au\/office\/find-or-replace-text-and-numbers-on-a-worksheet-0e304ca5-ecef-4808-b90f-fdb42f892e90<\/a><\/li>\n\n\n\n<li><strong>Look Inside:<\/strong>\u00a0This feature allows you to specify whether you want to search only within values, formulas, or comments within cells. This level of control can be helpful for targeted replacements based on the data type.<\/li>\n\n\n\n<li><strong>Regular Expressions:<\/strong>&nbsp;While not directly new in Excel 365, mentioning the ability to use regular expressions for advanced pattern matching during search and replace can be valuable for experienced users.<\/li>\n\n\n\n<li><strong>Formulas for Replacements:<\/strong>\u00a0Briefly mentioning that formulas can be used within the &#8220;Replace with&#8221; field allows for more complex replacements based on calculations or manipulations of the found data.<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>When you work with Excel, sometimes you have to deal with very large amounts of data. As a result, finding a particular value&#8230;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[14],"tags":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v22.3 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Finding and Replacing Data<\/title>\n<meta name=\"description\" content=\"With Excel&#039;s search tool, finding data is a breeze! Learn how to use the Find and Replace feature to quickly locate values in your workbook.\" \/>\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\/training\/finding-and-replacing-data\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Finding and Replacing Data\" \/>\n<meta property=\"og:description\" content=\"With Excel&#039;s search tool, finding data is a breeze! Learn how to use the Find and Replace feature to quickly locate values in your workbook.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/officetuts.net\/excel\/training\/finding-and-replacing-data\/\" \/>\n<meta property=\"article:published_time\" content=\"2018-06-27T17:45:50+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-03-14T13:24:40+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/find-and-replace.png\" \/>\n<meta name=\"author\" content=\"Tomasz Decker\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Tomasz Decker\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/officetuts.net\/excel\/training\/finding-and-replacing-data\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/officetuts.net\/excel\/training\/finding-and-replacing-data\/\"},\"author\":{\"name\":\"Tomasz Decker\",\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42\"},\"headline\":\"Finding and Replacing Data\",\"datePublished\":\"2018-06-27T17:45:50+00:00\",\"dateModified\":\"2024-03-14T13:24:40+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/officetuts.net\/excel\/training\/finding-and-replacing-data\/\"},\"wordCount\":668,\"publisher\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42\"},\"image\":{\"@id\":\"https:\/\/officetuts.net\/excel\/training\/finding-and-replacing-data\/#primaryimage\"},\"thumbnailUrl\":\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/find-and-replace.png\",\"articleSection\":[\"training\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/officetuts.net\/excel\/training\/finding-and-replacing-data\/\",\"url\":\"https:\/\/officetuts.net\/excel\/training\/finding-and-replacing-data\/\",\"name\":\"Finding and Replacing Data\",\"isPartOf\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/officetuts.net\/excel\/training\/finding-and-replacing-data\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/officetuts.net\/excel\/training\/finding-and-replacing-data\/#primaryimage\"},\"thumbnailUrl\":\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/find-and-replace.png\",\"datePublished\":\"2018-06-27T17:45:50+00:00\",\"dateModified\":\"2024-03-14T13:24:40+00:00\",\"description\":\"With Excel's search tool, finding data is a breeze! Learn how to use the Find and Replace feature to quickly locate values in your workbook.\",\"breadcrumb\":{\"@id\":\"https:\/\/officetuts.net\/excel\/training\/finding-and-replacing-data\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/officetuts.net\/excel\/training\/finding-and-replacing-data\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/officetuts.net\/excel\/training\/finding-and-replacing-data\/#primaryimage\",\"url\":\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/find-and-replace.png\",\"contentUrl\":\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/find-and-replace.png\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/officetuts.net\/excel\/training\/finding-and-replacing-data\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/officetuts.net\/excel\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Finding and Replacing Data\"}]},{\"@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.\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Finding and Replacing Data","description":"With Excel's search tool, finding data is a breeze! Learn how to use the Find and Replace feature to quickly locate values in your workbook.","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\/training\/finding-and-replacing-data\/","og_locale":"en_US","og_type":"article","og_title":"Finding and Replacing Data","og_description":"With Excel's search tool, finding data is a breeze! Learn how to use the Find and Replace feature to quickly locate values in your workbook.","og_url":"https:\/\/officetuts.net\/excel\/training\/finding-and-replacing-data\/","article_published_time":"2018-06-27T17:45:50+00:00","article_modified_time":"2024-03-14T13:24:40+00:00","og_image":[{"url":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/find-and-replace.png"}],"author":"Tomasz Decker","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Tomasz Decker","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/officetuts.net\/excel\/training\/finding-and-replacing-data\/#article","isPartOf":{"@id":"https:\/\/officetuts.net\/excel\/training\/finding-and-replacing-data\/"},"author":{"name":"Tomasz Decker","@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42"},"headline":"Finding and Replacing Data","datePublished":"2018-06-27T17:45:50+00:00","dateModified":"2024-03-14T13:24:40+00:00","mainEntityOfPage":{"@id":"https:\/\/officetuts.net\/excel\/training\/finding-and-replacing-data\/"},"wordCount":668,"publisher":{"@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42"},"image":{"@id":"https:\/\/officetuts.net\/excel\/training\/finding-and-replacing-data\/#primaryimage"},"thumbnailUrl":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/find-and-replace.png","articleSection":["training"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/officetuts.net\/excel\/training\/finding-and-replacing-data\/","url":"https:\/\/officetuts.net\/excel\/training\/finding-and-replacing-data\/","name":"Finding and Replacing Data","isPartOf":{"@id":"https:\/\/officetuts.net\/excel\/#website"},"primaryImageOfPage":{"@id":"https:\/\/officetuts.net\/excel\/training\/finding-and-replacing-data\/#primaryimage"},"image":{"@id":"https:\/\/officetuts.net\/excel\/training\/finding-and-replacing-data\/#primaryimage"},"thumbnailUrl":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/find-and-replace.png","datePublished":"2018-06-27T17:45:50+00:00","dateModified":"2024-03-14T13:24:40+00:00","description":"With Excel's search tool, finding data is a breeze! Learn how to use the Find and Replace feature to quickly locate values in your workbook.","breadcrumb":{"@id":"https:\/\/officetuts.net\/excel\/training\/finding-and-replacing-data\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/officetuts.net\/excel\/training\/finding-and-replacing-data\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/officetuts.net\/excel\/training\/finding-and-replacing-data\/#primaryimage","url":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/find-and-replace.png","contentUrl":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/find-and-replace.png"},{"@type":"BreadcrumbList","@id":"https:\/\/officetuts.net\/excel\/training\/finding-and-replacing-data\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/officetuts.net\/excel\/"},{"@type":"ListItem","position":2,"name":"Finding and Replacing Data"}]},{"@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."}]}},"_links":{"self":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts\/360"}],"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\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/comments?post=360"}],"version-history":[{"count":7,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts\/360\/revisions"}],"predecessor-version":[{"id":17235,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts\/360\/revisions\/17235"}],"wp:attachment":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/media?parent=360"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/categories?post=360"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/tags?post=360"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}