{"id":753,"date":"2024-04-24T11:00:00","date_gmt":"2024-04-24T11:00:00","guid":{"rendered":"https:\/\/coworkingspacesforruralamerica.org\/?p=753"},"modified":"2024-04-27T12:05:48","modified_gmt":"2024-04-27T12:05:48","slug":"how-to-find-highlight-remove-duplicates-in-google-sheets-step-by-step","status":"publish","type":"post","link":"https:\/\/coworkingspacesforruralamerica.org\/index.php\/2024\/04\/24\/how-to-find-highlight-remove-duplicates-in-google-sheets-step-by-step\/","title":{"rendered":"How to Find, Highlight &amp; Remove Duplicates in Google Sheets [Step-by-Step]"},"content":{"rendered":"<p>Duplicate data is the bane of spreadsheet solutions, especially at scale. Given the volume and variety of data now entered by teams, it\u2019s possible that duplicate data in tools like <a href=\"https:\/\/blog.hubspot.com\/marketing\/google-sheets\">Google Sheets<\/a> may be relevant and necessary, or it could be a frustrating distraction from the primary purpose of spreadsheet efforts.<\/p>\n<\/p>\n<p>The potential problem raises a good question: How do you highlight duplicates in Google Sheets?<\/p>\n<p><a class=\"cta_button\" href=\"https:\/\/www.hubspot.com\/cs\/ci\/?pg=e7cd3f82-cab9-4017-b019-ee3fc550e0b5&amp;pid=53&amp;ecid=&amp;hseid=&amp;hsic=\"><img loading=\"lazy\" class=\"hs-cta-img \" style=\"height: auto !important;width: auto !important;max-width: 100% !important;border-width: 0px;margin: 0 auto;margin-top: 20px;margin-bottom: 20px\" alt=\"\u2192 Access Now: Google Sheets Templates [Free Kit]\" height=\"59\" width=\"459\" src=\"https:\/\/no-cache.hubspot.com\/cta\/default\/53\/e7cd3f82-cab9-4017-b019-ee3fc550e0b5.png\" align=\"middle\" \/><\/a><\/p>\n<p>I\u2019ve got you covered with a step-by-step look at how to highlight duplicates in Google Sheets and find duplicates in Google Sheets, complete with images to ensure you\u2019re on the right track when it comes to de-duplicating your data.<\/p>\n<p style=\"font-weight: bold\">Table of Contents<\/p>\n<ul>\n<li style=\"font-weight: bold\"><a href=\"#find-dupliactes-sheets\">How to Find Duplicates in Google Sheets<\/a><\/li>\n<li style=\"font-weight: bold\"><a href=\"#highlight-duplicates-sheets\">Highlighting Duplicate Data in Google Sheets<\/a><\/li>\n<li style=\"font-weight: bold\"><a href=\"#step-by-step\">Step-by-Step: How to Highlight Duplicates in Google Sheets (With Pictures)<\/a><\/li>\n<li style=\"font-weight: bold\"><a href=\"#highlight-duplicates-in-rows-and-columns\">How to Highlight Duplicates in Multiple Rows and Columns<\/a><\/li>\n<li style=\"font-weight: bold\"><a href=\"#remove-duplicates-in-google-sheets\"><strong>How to Remove Duplicates in Google Sheets<\/strong><\/a><\/li>\n<\/ul>\n<p><a><\/a> <\/p>\n<h2>How to Find Duplicates in Google Sheets<\/h2>\n<p>Google Sheets is a free, cloud-based alternative to proprietary spreadsheet programs and \u2014 no surprise, since it\u2019s Google we\u2019re dealing with \u2014 offers a host of great features to help streamline data entry, formatting, and calculations.<\/p>\n<p>There are two ways to remove duplicates in Google Sheets: conditional formatting and the UNIQUE function. I\u2019ll go over both below, but, before you start following along, I have two things to note:&nbsp;<\/p>\n<ol>\n<li>You can run multiple conditional formatting rules at a time, so you don\u2019t need to delete any to run your conditional formatting rule to delete duplicates.&nbsp;<\/li>\n<li>You won\u2019t get an accurate duplicate count if you have any extra characters or spaces in your data, so you need to make sure your set is clean. Even an accidental extra space will count it as a separate data point.<\/li>\n<\/ol>\n<p>Let\u2019s dive into how you can highlight and remove duplicates in Google Sheets.&nbsp;<\/p>\n<p><a><\/a> <\/p>\n<h2>Highlighting Duplicate Data in Google Sheets<\/h2>\n<p>Google Sheets has all the familiar functions: File, Edit, View, Format, Data, Tools, etc., and makes it easy to quickly enter your data, add <a href=\"https:\/\/blog.hubspot.com\/marketing\/google-sheets-formulas?hubs_content=blog.hubspot.com\/marketing\/highlight-duplicates-google-sheets&amp;hubs_content-cta=formulas\">formulas<\/a> for calculations, and discover key relationships.&nbsp;<\/p>\n<p>While other spreadsheet tools, <a href=\"https:\/\/blog.hubspot.com\/marketing\/remove-duplicates-excel?hubs_content=blog.hubspot.com\/marketing\/highlight-duplicates-google-sheets&amp;hubs_content-cta=such%20as%20Excel\">such as Excel<\/a>, have built-in conditional formatting tools to pinpoint duplicate data in your sheet, Google\u2019s solution requires a little more manual effort.&nbsp;<\/p>\n<p><img loading=\"lazy\" src=\"data:image\/gif;base64,R0lGODlhAQABAAAAACH5BAEKAAEALAAAAAABAAEAAAICTAEAOw==\" alt=\"blank google sheets for highlighting duplicate data\" width=\"650\" height=\"348\" style=\"width: 650px;margin-left: auto;margin-right: auto;height: auto;max-width: 100%\" class=\"lazyload\" data-src=\"https:\/\/coworkingspacesforruralamerica.org\/wp-content\/uploads\/2024\/04\/highlight-duplicates-google-sheets_3.jpg\"><\/p>\n<p>So how do you automatically highlight duplicates in Google Sheets? While there\u2019s no built-in tool for this purpose, you can leverage some built-in functions to highlight duplicate data.<\/p>\n<p><a><\/a><br \/>\n<a><\/a> <\/p>\n<h2>Step-by-Step: How to Highlight Duplicates in Google Sheets (With Pictures)<\/h2>\n<p>Here\u2019s a step-by-step guide to highlighting duplicates in Google Sheets:<\/p>\n<p>Step 1: Open your spreadsheet.<\/p>\n<p>Step 2: Highlight the data you want to check.<\/p>\n<p>Step 3: Under \u201cFormat\u201d, select \u201cConditional Formatting.\u201d<\/p>\n<p>Step 4: Select \u201cCustom formula is.\u201d<\/p>\n<p>Step 5: Enter the custom duplicate checking formula.<\/p>\n<p>Step 6: Click \u201cDone\u201d to see the results.<\/p>\n<h3>Step 1: Open your spreadsheet.<\/h3>\n<p>First, head to Google Sheets and open the spreadsheet you want to check for duplicate data.<\/p>\n<h3><img loading=\"lazy\" src=\"data:image\/gif;base64,R0lGODlhAQABAAAAACH5BAEKAAEALAAAAAABAAEAAAICTAEAOw==\" alt=\"how to highlight duplicate data in google sheets: open spreadsheet\" width=\"650\" height=\"572\" style=\"width: 650px;margin-left: auto;margin-right: auto;height: auto;max-width: 100%\" class=\"lazyload\" data-src=\"https:\/\/coworkingspacesforruralamerica.org\/wp-content\/uploads\/2024\/04\/highlight-duplicates-google-sheets_2.jpg\"><\/h3>\n<h3>Step 2: Highlight the data you want to check.<\/h3>\n<p>Next, drag your cursor over the data you want to check to highlight it.<\/p>\n<p><img loading=\"lazy\" src=\"data:image\/gif;base64,R0lGODlhAQABAAAAACH5BAEKAAEALAAAAAABAAEAAAICTAEAOw==\" alt=\"how to highlight duplicate data in google sheets:  select data\" width=\"650\" height=\"420\" style=\"width: 650px;margin-left: auto;margin-right: auto;height: auto;max-width: 100%\" class=\"lazyload\" data-src=\"https:\/\/coworkingspacesforruralamerica.org\/wp-content\/uploads\/2024\/04\/highlight-duplicates-google-sheets_9.jpg\"><\/p>\n<h3>Step 3: Under \u201cFormat\u201d, select \u201cConditional Formatting.\u201d<\/h3>\n<p>Now, head to \u201cFormat\u201d in the top menu row and select \u201cConditional Formatting.\u201d You should then see a popup window titled \u201cConditional format rules.\u201d&nbsp;<\/p>\n<p><img loading=\"lazy\" src=\"data:image\/gif;base64,R0lGODlhAQABAAAAACH5BAEKAAEALAAAAAABAAEAAAICTAEAOw==\" alt=\"how to highlight duplicate data in google sheets: open conditional formatting options\" width=\"350\" height=\"581\" style=\"width: 350px;margin-left: auto;margin-right: auto;height: auto;max-width: 100%\" class=\"lazyload\" data-src=\"https:\/\/coworkingspacesforruralamerica.org\/wp-content\/uploads\/2024\/04\/highlight-duplicates-google-sheets_1.jpg\"><\/p>\n<h3>Step 4: Select \u201cCustom formula is.\u201d<\/h3>\n<p>Next, you need to create a custom formula. Click the down arrow underneath \u201cFormat cells if,\u201d and select \u201cCustom formula is\u201d from the dropdown menu. It\u2019s the last option to choose from, so you can scroll right to the end.<\/p>\n<p><img loading=\"lazy\" src=\"data:image\/gif;base64,R0lGODlhAQABAAAAACH5BAEKAAEALAAAAAABAAEAAAICTAEAOw==\" alt=\"how to highlight duplicate data in google sheets: select custom formula is\" width=\"350\" height=\"768\" style=\"width: 350px;margin-left: auto;margin-right: auto;height: auto;max-width: 100%\" class=\"lazyload\" data-src=\"https:\/\/coworkingspacesforruralamerica.org\/wp-content\/uploads\/2024\/04\/highlight-duplicates-google-sheets_8.jpg\"><\/p>\n<h3>Step 5: Enter the custom duplicate checking formula.<\/h3>\n<p>To search for duplicate data, we need to enter the custom duplicate checking formula, which for our column of data (A) looks like this:<\/p>\n<p style=\"text-align: center\"><em>=COUNTIF(A:A,A1)&gt;1<\/em><\/p>\n<p>The formula searches for any text string that appears more than once in a data set. The default highlight color is green, but you can change it by clicking on the paint can icon in the \u201cFormatting style\u201d menu.&nbsp;<\/p>\n<p><img loading=\"lazy\" src=\"data:image\/gif;base64,R0lGODlhAQABAAAAACH5BAEKAAEALAAAAAABAAEAAAICTAEAOw==\" alt=\"how to highlight duplicate data in google sheets: insert formula\" width=\"350\" height=\"537\" style=\"width: 350px;margin-left: auto;margin-right: auto;height: auto;max-width: 100%\" class=\"lazyload\" data-src=\"https:\/\/coworkingspacesforruralamerica.org\/wp-content\/uploads\/2024\/04\/highlight-duplicates-google-sheets_10.jpg\"><\/p>\n<h3>Step 6: Click \u201cDone\u201d to see the results.<\/h3>\n<p>And voil\u00e0 \u2014 we\u2019ve highlighted the duplicate data in Google Sheets.<\/p>\n<p><img loading=\"lazy\" src=\"data:image\/gif;base64,R0lGODlhAQABAAAAACH5BAEKAAEALAAAAAABAAEAAAICTAEAOw==\" alt=\"how to highlight duplicate data in google sheets: result\" width=\"450\" height=\"554\" style=\"width: 450px;margin-left: auto;margin-right: auto;height: auto;max-width: 100%\" class=\"lazyload\" data-src=\"https:\/\/coworkingspacesforruralamerica.org\/wp-content\/uploads\/2024\/04\/highlight-duplicates-google-sheets_4.jpg\"><\/p>\n<p><a><\/a> <\/p>\n<h2>How to Highlight Duplicates in Multiple Rows and Columns<\/h2>\n<p>You can also highlight duplicates in multiple rows and columns if you have a larger data set. The process starts the same as above, but you enter an expanded data range in the Conditional format rules menu to account for all the cells you want to compare.&nbsp;<\/p>\n<p>I\u2019ll use the same example above as a starting point, but I\u2019ll add a few more names so we use a formula to search across three columns: A, B, and C, and also across rows 1-10.<\/p>\n<p><img loading=\"lazy\" src=\"data:image\/gif;base64,R0lGODlhAQABAAAAACH5BAEKAAEALAAAAAABAAEAAAICTAEAOw==\" alt=\"how to highlight duplicate data in google sheets: data with multiple rows\" width=\"650\" height=\"585\" style=\"width: 650px;margin-left: auto;margin-right: auto;height: auto;max-width: 100%\" class=\"lazyload\" data-src=\"https:\/\/coworkingspacesforruralamerica.org\/wp-content\/uploads\/2024\/04\/highlight-duplicates-google-sheets_0.jpg\"><\/p>\n<p>To start, repeat steps two &#8211; four from above, but enter the following equation during step 5:&nbsp;<\/p>\n<p style=\"text-align: center\"><em>=COUNTIF($A$2:G,Indirect(Address(Row(),Column(),)))&gt;1<\/em><\/p>\n<p>This will highlight all duplicates across all three columns and all ten rows, making it easy to spot data doppelgangers:<\/p>\n<p><img loading=\"lazy\" src=\"data:image\/gif;base64,R0lGODlhAQABAAAAACH5BAEKAAEALAAAAAABAAEAAAICTAEAOw==\" alt=\"how to highlight duplicate data in google sheets in multiple rows and columns: result\" width=\"650\" height=\"408\" style=\"width: 650px;margin-left: auto;margin-right: auto;height: auto;max-width: 100%\" class=\"lazyload\" data-src=\"https:\/\/coworkingspacesforruralamerica.org\/wp-content\/uploads\/2024\/04\/highlight-duplicates-google-sheets_5.jpg\"><\/p>\n<p><a><\/a> <\/p>\n<h3>Find and Highlight Duplicates in Google Sheets With the Unique Function<\/h3>\n<p>Another way to find duplicates in Sheets is to use the UNIQUE function, which looks for the unique values in your designated range and produces a duplicate-free list. Here\u2019s the formula:&nbsp;<\/p>\n<p>=UNIQUE(RANGE)<\/p>\n<p><strong>Note:<\/strong> This formula can only identify duplicates in a single column.&nbsp;<\/p>\n<p>There\u2019s only one step to this method, which is entering your formula into an empty cell. Continuing with the same data set from above, I entered <em>=UNIQUE(A1:A10)<\/em>. The image below is my duplicate-free list (on the left).<\/p>\n<p><img loading=\"lazy\" src=\"data:image\/gif;base64,R0lGODlhAQABAAAAACH5BAEKAAEALAAAAAABAAEAAAICTAEAOw==\" width=\"708\" height=\"483\" alt=\"unique list 1\" style=\"height: auto;max-width: 100%;width: 708px;margin-left: auto;margin-right: auto\" class=\"lazyload\" data-src=\"https:\/\/coworkingspacesforruralamerica.org\/wp-content\/uploads\/2024\/04\/unique20list201.jpg\"><\/p>\n<p>To use the UNIQUE function to find duplicates in multiple columns and rows, use this formula:&nbsp;<\/p>\n<p>&nbsp;=UNIQUE(TOCOL(RANGE))<\/p>\n<p><img loading=\"lazy\" src=\"data:image\/gif;base64,R0lGODlhAQABAAAAACH5BAEKAAEALAAAAAABAAEAAAICTAEAOw==\" width=\"653\" height=\"650\" alt=\"unique 2\" style=\"height: auto;max-width: 100%;width: 653px;margin-left: auto;margin-right: auto\" class=\"lazyload\" data-src=\"https:\/\/coworkingspacesforruralamerica.org\/wp-content\/uploads\/2024\/04\/unique202.jpg\"><\/p>\n<p>A drawback to using the UNIQUE function to find duplicates in Google Sheets is that it spits out a separate duplicate-free list instead of highlighting and deleting them. It creates an added step since you\u2019ll have to manually remove duplicates with your new list as a reference, so I recommend this method for those with a smaller data set who don\u2019t mind a few manual updates.&nbsp;<\/p>\n<p>Alternatively, this method is an excellent option for producing a cleaned list to start fresh.<\/p>\n<p><a><\/a> <\/p>\n<h2>How to Remove Duplicates in Google Sheets<\/h2>\n<p>In addition to highlighting duplicates, you can also use Google Sheets to delete duplicates with the Data Cleanup feature. Below, I\u2019ll show you how.<\/p>\n<p><strong>Step 1:<\/strong> Select any cell.<\/p>\n<p><strong>Step 2:<\/strong> Navigate to the header toolback, select \u201cData,\u201d then \u201cData cleanup,\u201d then \u201cRemove duplicates.\u201d<\/p>\n<p><img loading=\"lazy\" src=\"data:image\/gif;base64,R0lGODlhAQABAAAAACH5BAEKAAEALAAAAAABAAEAAAICTAEAOw==\" width=\"603\" height=\"434\" alt=\"remove duplicates step 1\" style=\"height: auto;max-width: 100%;width: 603px;margin-left: auto;margin-right: auto\" class=\"lazyload\" data-src=\"https:\/\/coworkingspacesforruralamerica.org\/wp-content\/uploads\/2024\/04\/remove20duplicates20step201.jpg\"><\/p>\n<p><strong>Step 3:<\/strong> In the popup window, select the columns you want to delete duplicate data from, then select \u201cRemove duplicates.<\/p>\n<p><em><strong>Note:<\/strong><\/em> If you have a sheet header, make sure to select \u201cData has a header row\u201d so it\u2019s not included in the duplicate search.&nbsp;<\/p>\n<p><img loading=\"lazy\" src=\"data:image\/gif;base64,R0lGODlhAQABAAAAACH5BAEKAAEALAAAAAABAAEAAAICTAEAOw==\" width=\"546\" height=\"394\" alt=\"remove duplicates window\" style=\"height: auto;max-width: 100%;width: 546px;margin-left: auto;margin-right: auto\" class=\"lazyload\" data-src=\"https:\/\/coworkingspacesforruralamerica.org\/wp-content\/uploads\/2024\/04\/remove20duplicates20window.jpg\"><\/p>\n<p>All duplicates are now gone!<\/p>\n<h2>Dealing With Duplicates in <u style=\"background-color: #ffff04\">Duplicates in<\/u> Google Sheets<\/h2>\n<p>Can you highlight duplicates in Google Sheets? Absolutely. While the process takes more effort than some other spreadsheet solutions, it\u2019s easy to replicate once you\u2019ve done it once or twice, and once you\u2019re comfortable with the process you can scale up to find duplicates across rows, columns, and even much larger data sets.<\/p>\n<p><img loading=\"lazy\" src=\"data:image\/gif;base64,R0lGODlhAQABAAAAACH5BAEKAAEALAAAAAABAAEAAAICTAEAOw==\" alt=\"\" width=\"1\" height=\"1\" style=\"min-height:1px!important;width:1px!important;border-width:0!important;margin-top:0!important;margin-bottom:0!important;margin-right:0!important;margin-left:0!important;padding-top:0!important;padding-bottom:0!important;padding-right:0!important;padding-left:0!important\" class=\"lazyload\" data-src=\"https:\/\/track.hubspot.com\/__ptq.gif?a=53&amp;k=14&amp;r=https%3A%2F%2Fblog.hubspot.com%2Fmarketing%2Fhighlight-duplicates-google-sheets&amp;bu=https%253A%252F%252Fblog.hubspot.com%252Fmarketing&amp;bvt=rss\"><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Duplicate data is the bane of spreadsheet solutions, especially at scale. Given the volume and variety of data now entered by teams, it\u2019s possible that duplicate data in tools like Google Sheets may be relevant and necessary, or it could be a frustrating distraction from the primary purpose of spreadsheet efforts. The potential problem raises&#8230;<\/p>\n","protected":false},"author":1,"featured_media":755,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[14],"tags":[],"_links":{"self":[{"href":"https:\/\/coworkingspacesforruralamerica.org\/index.php\/wp-json\/wp\/v2\/posts\/753"}],"collection":[{"href":"https:\/\/coworkingspacesforruralamerica.org\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/coworkingspacesforruralamerica.org\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/coworkingspacesforruralamerica.org\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/coworkingspacesforruralamerica.org\/index.php\/wp-json\/wp\/v2\/comments?post=753"}],"version-history":[{"count":2,"href":"https:\/\/coworkingspacesforruralamerica.org\/index.php\/wp-json\/wp\/v2\/posts\/753\/revisions"}],"predecessor-version":[{"id":769,"href":"https:\/\/coworkingspacesforruralamerica.org\/index.php\/wp-json\/wp\/v2\/posts\/753\/revisions\/769"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/coworkingspacesforruralamerica.org\/index.php\/wp-json\/wp\/v2\/media\/755"}],"wp:attachment":[{"href":"https:\/\/coworkingspacesforruralamerica.org\/index.php\/wp-json\/wp\/v2\/media?parent=753"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/coworkingspacesforruralamerica.org\/index.php\/wp-json\/wp\/v2\/categories?post=753"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/coworkingspacesforruralamerica.org\/index.php\/wp-json\/wp\/v2\/tags?post=753"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}