{"id":11553,"date":"2022-07-12T08:11:09","date_gmt":"2022-07-12T11:11:09","guid":{"rendered":"https:\/\/contabilidadecaxias.com.br\/index.php\/2022\/07\/12\/how-to-highlight-duplicates-in-google-sheets-step-by-step\/"},"modified":"2022-07-12T08:11:09","modified_gmt":"2022-07-12T11:11:09","slug":"how-to-highlight-duplicates-in-google-sheets-step-by-step","status":"publish","type":"post","link":"https:\/\/contabilidadecaxias.com.br\/index.php\/2022\/07\/12\/how-to-highlight-duplicates-in-google-sheets-step-by-step\/","title":{"rendered":"How to Highlight Duplicates in Google Sheets [Step-by-Step]"},"content":{"rendered":"

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<\/a> may be relevant and necessary, or it could be a frustrating distraction from the primary purpose of spreadsheet efforts.<\/p>\n<\/p>\n

The potential problem raises a good question: How do you highlight duplicates in Google Sheets?<\/p>\n

We\u2019ve got you covered with a step-by-step look at how to highlight duplicates in Google Sheets, complete with images to make sure you\u2019re on the right track when it comes to de-duplicating your data.<\/p>\n

<\/a><\/p>\n

Highlighting Duplicate Data in Google Sheets<\/h2>\n

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>\n

Google Sheets has all the familiar functions: File, Edit, View, Format, Data, Tools, etc. and makes it easy to quickly enter your data, add formulas<\/a> for calculations, and discover key relationships. What Sheets doesn\u2019t have, however, is an easy way to find and highlight duplicates.<\/p>\n

While other spreadsheet tools, such as Excel<\/a>, have built-in conditional formatting tools that can pinpoint duplicate data in your sheet, Google\u2019s solution requires a little more manual effort.<\/p>\n

Step-by-Step: How to Highlight Duplicates in Google Sheets (With Pictures)<\/h2>\n

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

Here\u2019s a step-by-step guide:<\/p>\n

Step 1: Open your spreadsheet.<\/p>\n

Step 2: Highlight the data you want to check.<\/p>\n

Step 3: Under \u201cFormat\u201d, select \u201cConditional Formatting.\u201d<\/p>\n

Step 4: Select \u201cCustom formula is.\u201d<\/p>\n

Step 5: Enter the custom duplicate checking formula.<\/p>\n

Step 6: Click \u201cDone\u201d to see the results.<\/p>\n

Step 1: Open your spreadsheet.<\/h3>\n

First, head to Google Sheets and open the spreadsheet you want to check for duplicate data.<\/p>\n

<\/h3>\n

Step 2: Highlight the data you want to check.<\/h3>\n

Next, left-click and drag your cursor over the data you want to check to highlight it.<\/p>\n<\/p>\n

Step 3: Under \u201cFormat\u201d, select \u201cConditional Formatting.\u201d<\/h3>\n

Now, head to \u201cFormat\u201d in the top menu row and select \u201cConditional Formatting\u201d. You may get a notification that says \u201ccell is not empty\u201d \u2014 if so, click on it, and you should see this:<\/p>\n<\/p>\n

Step 4: Select \u201cCustom formula is.\u201d<\/h3>\n

Next, we need to create a custom formula. Under \u201cFormat cells if\u201d, select the drop-down menu and scroll down to \u201cCustom formula is\u201d.<\/p>\n<\/p>\n

Step 5: Enter the custom duplicate checking formula.<\/h3>\n

To search for duplicate data, we need to enter the custom duplicate checking formula, which for our column of data looks like this:<\/p>\n

=COUNTIF(A:A,A1)>1<\/em><\/p>\n

This formula searches for any text string that appears more than once in our selected data set, and by default will highlight it in green. If you prefer a different color, click on the small paint pot icon in the formatting style bar and select the color you prefer.<\/p>\n<\/p>\n

Step 6: Click \u201cDone\u201d to see the results.<\/h3>\n

And voil\u00e0 \u2014 we\u2019ve highlighted the duplicate data in Google Sheets.<\/p>\n<\/p>\n

How to Highlight Duplicates in Multiple Rows and Columns<\/h2>\n

If you\u2019ve got a larger data set to check, it\u2019s also possible to highlight data duplicates in multiple columns or rows.<\/p>\n

This starts the same way as the duplicate checking process above \u2014 the only difference is that you change the data range to include all the cells you want to compare.<\/p>\n

In practice, this means entering an expanded data range in the Conditional format rules menu and the custom format box. Let\u2019s use the example above as a starting point, but instead of just searching column A for duplicates, we\u2019re going to search across three columns: A, B, and C, and also across rows 1-10.<\/p>\n<\/p>\n

When we enter our conditional format rules, Apply to Range becomes A1:C10 and our custom formula becomes:<\/p>\n

=COUNTIF($A$2:G,Indirect(Address(Row(),Column(),)))>1<\/em><\/p>\n

This will highlight all duplicates across all three columns and all 10 rows, making it easy to spot data doppelgangers:<\/p>\n<\/p>\n

Dealing With Duplicates in Duplicates in Google Sheets<\/h2>\n

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>\n

<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"

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 […]<\/p>\n","protected":false},"author":0,"featured_media":11554,"comment_status":"open","ping_status":"","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[8],"tags":[],"class_list":["post-11553","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-noticias-marketing"],"blocksy_meta":{"styles_descriptor":{"styles":{"desktop":"","tablet":"","mobile":""},"google_fonts":[],"version":6}},"yoast_head":"\nHow to Highlight Duplicates in Google Sheets [Step-by-Step] - Contabilidade Caxias<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/contabilidadecaxias.com.br\/index.php\/2022\/07\/12\/how-to-highlight-duplicates-in-google-sheets-step-by-step\/\" \/>\n<meta property=\"og:locale\" content=\"pt_BR\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How to Highlight Duplicates in Google Sheets [Step-by-Step] - Contabilidade Caxias\" \/>\n<meta property=\"og:description\" content=\"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 […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/contabilidadecaxias.com.br\/index.php\/2022\/07\/12\/how-to-highlight-duplicates-in-google-sheets-step-by-step\/\" \/>\n<meta property=\"og:site_name\" content=\"Contabilidade Caxias\" \/>\n<meta property=\"article:published_time\" content=\"2022-07-12T11:11:09+00:00\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Est. tempo de leitura\" \/>\n\t<meta name=\"twitter:data1\" content=\"4 minutos\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/contabilidadecaxias.com.br\\\/index.php\\\/2022\\\/07\\\/12\\\/how-to-highlight-duplicates-in-google-sheets-step-by-step\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/contabilidadecaxias.com.br\\\/index.php\\\/2022\\\/07\\\/12\\\/how-to-highlight-duplicates-in-google-sheets-step-by-step\\\/\"},\"author\":{\"name\":\"\",\"@id\":\"\"},\"headline\":\"How to Highlight Duplicates in Google Sheets [Step-by-Step]\",\"datePublished\":\"2022-07-12T11:11:09+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/contabilidadecaxias.com.br\\\/index.php\\\/2022\\\/07\\\/12\\\/how-to-highlight-duplicates-in-google-sheets-step-by-step\\\/\"},\"wordCount\":750,\"commentCount\":0,\"image\":{\"@id\":\"https:\\\/\\\/contabilidadecaxias.com.br\\\/index.php\\\/2022\\\/07\\\/12\\\/how-to-highlight-duplicates-in-google-sheets-step-by-step\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/contabilidadecaxias.com.br\\\/wp-content\\\/uploads\\\/2022\\\/07\\\/e7cd3f82-cab9-4017-b019-ee3fc550e0b5-pQLcva.png\",\"articleSection\":[\"Marketing\"],\"inLanguage\":\"pt-BR\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/contabilidadecaxias.com.br\\\/index.php\\\/2022\\\/07\\\/12\\\/how-to-highlight-duplicates-in-google-sheets-step-by-step\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/contabilidadecaxias.com.br\\\/index.php\\\/2022\\\/07\\\/12\\\/how-to-highlight-duplicates-in-google-sheets-step-by-step\\\/\",\"url\":\"https:\\\/\\\/contabilidadecaxias.com.br\\\/index.php\\\/2022\\\/07\\\/12\\\/how-to-highlight-duplicates-in-google-sheets-step-by-step\\\/\",\"name\":\"How to Highlight Duplicates in Google Sheets [Step-by-Step] - Contabilidade Caxias\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/contabilidadecaxias.com.br\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/contabilidadecaxias.com.br\\\/index.php\\\/2022\\\/07\\\/12\\\/how-to-highlight-duplicates-in-google-sheets-step-by-step\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/contabilidadecaxias.com.br\\\/index.php\\\/2022\\\/07\\\/12\\\/how-to-highlight-duplicates-in-google-sheets-step-by-step\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/contabilidadecaxias.com.br\\\/wp-content\\\/uploads\\\/2022\\\/07\\\/e7cd3f82-cab9-4017-b019-ee3fc550e0b5-pQLcva.png\",\"datePublished\":\"2022-07-12T11:11:09+00:00\",\"author\":{\"@id\":\"\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/contabilidadecaxias.com.br\\\/index.php\\\/2022\\\/07\\\/12\\\/how-to-highlight-duplicates-in-google-sheets-step-by-step\\\/#breadcrumb\"},\"inLanguage\":\"pt-BR\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/contabilidadecaxias.com.br\\\/index.php\\\/2022\\\/07\\\/12\\\/how-to-highlight-duplicates-in-google-sheets-step-by-step\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"pt-BR\",\"@id\":\"https:\\\/\\\/contabilidadecaxias.com.br\\\/index.php\\\/2022\\\/07\\\/12\\\/how-to-highlight-duplicates-in-google-sheets-step-by-step\\\/#primaryimage\",\"url\":\"https:\\\/\\\/contabilidadecaxias.com.br\\\/wp-content\\\/uploads\\\/2022\\\/07\\\/e7cd3f82-cab9-4017-b019-ee3fc550e0b5-pQLcva.png\",\"contentUrl\":\"https:\\\/\\\/contabilidadecaxias.com.br\\\/wp-content\\\/uploads\\\/2022\\\/07\\\/e7cd3f82-cab9-4017-b019-ee3fc550e0b5-pQLcva.png\",\"width\":443,\"height\":59},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/contabilidadecaxias.com.br\\\/index.php\\\/2022\\\/07\\\/12\\\/how-to-highlight-duplicates-in-google-sheets-step-by-step\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"In\u00edcio\",\"item\":\"https:\\\/\\\/contabilidadecaxias.com.br\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How to Highlight Duplicates in Google Sheets [Step-by-Step]\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/contabilidadecaxias.com.br\\\/#website\",\"url\":\"https:\\\/\\\/contabilidadecaxias.com.br\\\/\",\"name\":\"Contabilidade Caxias\",\"description\":\"Site especializado em Contabilidade para Caxias.\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/contabilidadecaxias.com.br\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"pt-BR\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"How to Highlight Duplicates in Google Sheets [Step-by-Step] - Contabilidade Caxias","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:\/\/contabilidadecaxias.com.br\/index.php\/2022\/07\/12\/how-to-highlight-duplicates-in-google-sheets-step-by-step\/","og_locale":"pt_BR","og_type":"article","og_title":"How to Highlight Duplicates in Google Sheets [Step-by-Step] - Contabilidade Caxias","og_description":"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 […]","og_url":"https:\/\/contabilidadecaxias.com.br\/index.php\/2022\/07\/12\/how-to-highlight-duplicates-in-google-sheets-step-by-step\/","og_site_name":"Contabilidade Caxias","article_published_time":"2022-07-12T11:11:09+00:00","twitter_card":"summary_large_image","twitter_misc":{"Est. tempo de leitura":"4 minutos"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/contabilidadecaxias.com.br\/index.php\/2022\/07\/12\/how-to-highlight-duplicates-in-google-sheets-step-by-step\/#article","isPartOf":{"@id":"https:\/\/contabilidadecaxias.com.br\/index.php\/2022\/07\/12\/how-to-highlight-duplicates-in-google-sheets-step-by-step\/"},"author":{"name":"","@id":""},"headline":"How to Highlight Duplicates in Google Sheets [Step-by-Step]","datePublished":"2022-07-12T11:11:09+00:00","mainEntityOfPage":{"@id":"https:\/\/contabilidadecaxias.com.br\/index.php\/2022\/07\/12\/how-to-highlight-duplicates-in-google-sheets-step-by-step\/"},"wordCount":750,"commentCount":0,"image":{"@id":"https:\/\/contabilidadecaxias.com.br\/index.php\/2022\/07\/12\/how-to-highlight-duplicates-in-google-sheets-step-by-step\/#primaryimage"},"thumbnailUrl":"https:\/\/contabilidadecaxias.com.br\/wp-content\/uploads\/2022\/07\/e7cd3f82-cab9-4017-b019-ee3fc550e0b5-pQLcva.png","articleSection":["Marketing"],"inLanguage":"pt-BR","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/contabilidadecaxias.com.br\/index.php\/2022\/07\/12\/how-to-highlight-duplicates-in-google-sheets-step-by-step\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/contabilidadecaxias.com.br\/index.php\/2022\/07\/12\/how-to-highlight-duplicates-in-google-sheets-step-by-step\/","url":"https:\/\/contabilidadecaxias.com.br\/index.php\/2022\/07\/12\/how-to-highlight-duplicates-in-google-sheets-step-by-step\/","name":"How to Highlight Duplicates in Google Sheets [Step-by-Step] - Contabilidade Caxias","isPartOf":{"@id":"https:\/\/contabilidadecaxias.com.br\/#website"},"primaryImageOfPage":{"@id":"https:\/\/contabilidadecaxias.com.br\/index.php\/2022\/07\/12\/how-to-highlight-duplicates-in-google-sheets-step-by-step\/#primaryimage"},"image":{"@id":"https:\/\/contabilidadecaxias.com.br\/index.php\/2022\/07\/12\/how-to-highlight-duplicates-in-google-sheets-step-by-step\/#primaryimage"},"thumbnailUrl":"https:\/\/contabilidadecaxias.com.br\/wp-content\/uploads\/2022\/07\/e7cd3f82-cab9-4017-b019-ee3fc550e0b5-pQLcva.png","datePublished":"2022-07-12T11:11:09+00:00","author":{"@id":""},"breadcrumb":{"@id":"https:\/\/contabilidadecaxias.com.br\/index.php\/2022\/07\/12\/how-to-highlight-duplicates-in-google-sheets-step-by-step\/#breadcrumb"},"inLanguage":"pt-BR","potentialAction":[{"@type":"ReadAction","target":["https:\/\/contabilidadecaxias.com.br\/index.php\/2022\/07\/12\/how-to-highlight-duplicates-in-google-sheets-step-by-step\/"]}]},{"@type":"ImageObject","inLanguage":"pt-BR","@id":"https:\/\/contabilidadecaxias.com.br\/index.php\/2022\/07\/12\/how-to-highlight-duplicates-in-google-sheets-step-by-step\/#primaryimage","url":"https:\/\/contabilidadecaxias.com.br\/wp-content\/uploads\/2022\/07\/e7cd3f82-cab9-4017-b019-ee3fc550e0b5-pQLcva.png","contentUrl":"https:\/\/contabilidadecaxias.com.br\/wp-content\/uploads\/2022\/07\/e7cd3f82-cab9-4017-b019-ee3fc550e0b5-pQLcva.png","width":443,"height":59},{"@type":"BreadcrumbList","@id":"https:\/\/contabilidadecaxias.com.br\/index.php\/2022\/07\/12\/how-to-highlight-duplicates-in-google-sheets-step-by-step\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"In\u00edcio","item":"https:\/\/contabilidadecaxias.com.br\/"},{"@type":"ListItem","position":2,"name":"How to Highlight Duplicates in Google Sheets [Step-by-Step]"}]},{"@type":"WebSite","@id":"https:\/\/contabilidadecaxias.com.br\/#website","url":"https:\/\/contabilidadecaxias.com.br\/","name":"Contabilidade Caxias","description":"Site especializado em Contabilidade para Caxias.","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/contabilidadecaxias.com.br\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"pt-BR"}]}},"_links":{"self":[{"href":"https:\/\/contabilidadecaxias.com.br\/index.php\/wp-json\/wp\/v2\/posts\/11553","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/contabilidadecaxias.com.br\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/contabilidadecaxias.com.br\/index.php\/wp-json\/wp\/v2\/types\/post"}],"replies":[{"embeddable":true,"href":"https:\/\/contabilidadecaxias.com.br\/index.php\/wp-json\/wp\/v2\/comments?post=11553"}],"version-history":[{"count":0,"href":"https:\/\/contabilidadecaxias.com.br\/index.php\/wp-json\/wp\/v2\/posts\/11553\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/contabilidadecaxias.com.br\/index.php\/wp-json\/wp\/v2\/media\/11554"}],"wp:attachment":[{"href":"https:\/\/contabilidadecaxias.com.br\/index.php\/wp-json\/wp\/v2\/media?parent=11553"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/contabilidadecaxias.com.br\/index.php\/wp-json\/wp\/v2\/categories?post=11553"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/contabilidadecaxias.com.br\/index.php\/wp-json\/wp\/v2\/tags?post=11553"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}