{"id":400249,"date":"2022-01-27T22:00:21","date_gmt":"2022-01-27T19:00:21","guid":{"rendered":"https:\/\/en.buradabiliyorum.com\/how-to-highlight-duplicates-in-google-sheets\/"},"modified":"2022-01-27T22:00:21","modified_gmt":"2022-01-27T19:00:21","slug":"how-to-highlight-duplicates-in-google-sheets","status":"publish","type":"post","link":"https:\/\/buradabiliyorum.com\/en\/how-to-highlight-duplicates-in-google-sheets\/","title":{"rendered":"#How to Highlight Duplicates in Google Sheets"},"content":{"rendered":"<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_84 counter-hierarchy ez-toc-counter ez-toc-custom ez-toc-container-direction\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">Table of Contents<\/p>\n<label for=\"ez-toc-cssicon-toggle-item-6a29de0cc9df6\" class=\"ez-toc-cssicon-toggle-label\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #dd3333;color:#dd3333\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #dd3333;color:#dd3333\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/label><input type=\"checkbox\"  id=\"ez-toc-cssicon-toggle-item-6a29de0cc9df6\" checked aria-label=\"Toggle\" \/><nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/buradabiliyorum.com\/en\/how-to-highlight-duplicates-in-google-sheets\/#Find_Duplicates_in_Google_Sheets_by_Highlighting_Them\" >Find Duplicates in Google Sheets by Highlighting Them<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/buradabiliyorum.com\/en\/how-to-highlight-duplicates-in-google-sheets\/#Edit_Add_or_Delete_a_Conditional_Formatting_Rule\" >Edit, Add, or Delete a Conditional Formatting Rule<\/a><\/li><\/ul><\/nav><\/div>\n<p><strong>&#8220;#How to Highlight Duplicates in Google Sheets&#8221;<\/strong><\/p>\n<div>\n<img loading=\"lazy\" decoding=\"async\" class=\"type:primaryImage alignnone size-full wp-image-731741\" data-pagespeed-lazy-srcset=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/05\/google_sheets_her_1200x675.jpg?width=398&amp;trim=1,1&amp;bg-color=000&amp;pad=1,1 400w, https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/05\/google_sheets_her_1200x675.jpg?width=1198&amp;trim=1,1&amp;bg-color=000&amp;pad=1,1 1200w\" sizes=\"auto, 400w, 1200w\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/05\/google_sheets_her_1200x675.jpg?width=1198&amp;trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"\" width=\"1200\" height=\"675\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>No matter how careful you are entering or importing data, duplicates can h<a href=\"https:\/\/buradabiliyorum.com\/en\/category\/download-scripts-themes-apps\/\" data-internallinksmanager029f6b8e52c=\"9\" title=\"Download Scripts &amp; Themes &amp; Apps\" target=\"_blank\" rel=\"noopener\">app<\/a>en. While you could\u00a0find and remove duplicates, you may want to review them, not necessarily remove them. We\u2019ll show you how to highlight duplicates in Google Sheets.<\/p>\n<p>You may have a list of customer email addresses and phone numbers, product identifiers and order numbers, or similar data where duplicates shouldn\u2019t exist. By locating and highlighting duplicates in your spreadsheet, you can then review and fix the incorrect data.<\/p>\n<p>While Microsoft Excel offers an easy way to find duplicates with conditional formatting, Google Sheets doesn\u2019t currently provide such a convenient option. But with a custom formula in addition to the conditional formatting, highlighting duplicates in your sheet can be done in a few clicks.<\/p>\n<h2 role=\"heading\" aria-level=\"2\"><span class=\"ez-toc-section\" id=\"Find_Duplicates_in_Google_Sheets_by_Highlighting_Them\"><\/span>Find Duplicates in Google Sheets by Highlighting Them<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Sign in to Google Sheets and open the spreadsheet you want to work with. Select the cells where you want to find duplicates. This can be a column, row, or cell range.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-776628\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/12\/SelectedCellRange-GoogleSheetsDuplicates.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Selected cell range\" width=\"500\" height=\"248\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>Click Format &gt; Conditional Formatting from the menu. This opens the Conditional Formatting sidebar where you\u2019ll set up a rule to highlight the duplicate data.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-776630\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/12\/FormatConditionalFormatting-GoogleSheetsDuplicates.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Select Format, Conditional Formatting\" width=\"300\" height=\"426\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>At the top of the sidebar, select the Single Color tab and confirm the cells beneath Apply to Range.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-776631\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/12\/ApplyToRange-GoogleSheetsDuplicates.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Confirm the cells in Apply To Range\" width=\"650\" height=\"213\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>Below Format Rules, open the drop-down box for Format Cells If and select \u201cCustom Formula Is\u201d at the bottom of the list.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-776633\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/12\/CustomFormulaIs-GoogleSheetsDuplicates.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Select Custom Formula Is\" width=\"224\" height=\"500\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>Enter the following formula into the Value or Formula box that displays beneath the drop-down box. Replace the letters and cell reference in the formula with those for your selected cell range.<\/p>\n<pre>=COUNTIF(B:B,B1)&gt;1<\/pre>\n<p>Here, <code>COUNTIF<\/code> is the function, <code>B:B<\/code> is the range (column,) <code>B1<\/code> is the criteria, and <code>&gt;1<\/code> is more than one.<\/p>\n<p>Alternatively, you can use the following formula for exact cell references as the range.<\/p>\n<pre>=COUNTIF($B$1:$B$10,B1)&gt;1<\/pre>\n<p>Here, <code>COUNTIF<\/code> is the function, <code>$B$1:$B$10<\/code> is the range, <code>B1<\/code> is the criteria, and <code>&gt;1<\/code> is more than one.<\/p>\n<p>Under Formatting Style, choose the type of highlight you want to use. You can use the Fill Color icon to choose a color from the palette. Alternatively, you can format the font in the cells with bold, italic, or a color if you prefer.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-776635\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/12\/FillColor-GoogleSheetsDuplicates.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Fill Color palette\" width=\"300\" height=\"410\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>Click \u201cDone\u201d when you finish to apply the conditional formatting rule. You should see the cells containing duplicate data formatted with the style you selected.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-776636\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/12\/DuplicatesHighlighted-GoogleSheetsDuplicates.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Duplicates Highlighted in Google Sheets\" width=\"650\" height=\"346\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>As you make corrections to the duplicate data, you\u2019ll see the conditional formatting disappear leaving you with the remaining duplicates.<\/p>\n<p><strong>RELATED:<\/strong> <strong><em>How to Highlight a Row in Google Sheets Using Conditional Formatting<\/em><\/strong><\/p>\n<h2 role=\"heading\" aria-level=\"2\"><span class=\"ez-toc-section\" id=\"Edit_Add_or_Delete_a_Conditional_Formatting_Rule\"><\/span>Edit, Add, or Delete a Conditional Formatting Rule<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>You can make changes to a rule, add a new one, or delete a rule easily in Google Sheets. Open the sidebar with Format &gt; Conditional Formatting. You\u2019ll see the rules you\u2019ve set up.<\/p>\n<ul>\n<li>To edit a rule, select it, make your changes, and click \u201cDone.\u201d<\/li>\n<li>To set up an additional rule, select \u201cAdd Another Rule.\u201d<\/li>\n<li>To remove a rule, hover your cursor over it and click the trash can icon.<\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-776637\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/12\/EditAddDelete-GoogleSheetsDuplicates.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Edit, add, or delete a rule\" width=\"300\" height=\"179\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>By finding and highlighting duplicates in Google Sheets, you can work on correcting the wrong data. And if you\u2019re interested in other ways to use conditional formatting in Google Sheets, look at how to apply a color scale based on value or how to highlight blanks or cells with errors.<\/p>\n<\/div>\n<p><script>\n setTimeout(function(){\n  !function(f,b,e,v,n,t,s)\n  {if(f.fbq)return;n=f.fbq=function(){n.callMethod?\n  n.callMethod.apply(n,arguments):n.queue.push(arguments)};\n  if(!f._fbq)f._fbq=n;n.push=n;n.loaded=!0;n.version='2.0';\n  n.queue=[];t=b.createElement(e);t.async=!0;\n  t.src=v;s=b.getElementsByTagName(e)[0];\n  s.parentNode.insertBefore(t,s) } (window, document,'script',\n  'https:\/\/connect.facebook.net\/en_US\/fbevents.js');\n   fbq('init', '335401813750447');\n   fbq('track', 'PageView');\n  },3000);\n<\/script><\/p>\n<blockquote><p><strong><span style=\"color: #ff6600;\">If you liked the article, do not forget to share it with your friends. Follow us on\u00a0<span style=\"color: #ff0000;\"><a style=\"color: #ff0000;\" href=\"https:\/\/news.google.com\/publications\/CAAqBwgKMLG0nwswvr63Aw\" target=\"_blank\" rel=\"nofollow noopener noreferrer\">Google News<\/a><\/span>\u00a0too, click on the star and choose us from your favorites.<\/span><\/strong><\/p><\/blockquote>\n<blockquote>\n<p style=\"text-align: center;\">For forums sites go to <span style=\"color: #ff9900;\"><a style=\"color: #ff9900;\" href=\"https:\/\/forum.buradabiliyorum.com\/\" target=\"_blank\" rel=\"noopener\">Forum.BuradaBiliyorum.Com<\/a><\/span><\/strong><\/p>\n<\/blockquote>\n<blockquote>\n<p style=\"text-align: center;\"><strong>If you want to read more like this article, you can visit our <span style=\"color: #ff9900;\"><a style=\"color: #ff9900;\" href=\"https:\/\/en.buradabiliyorum.com\/technology\/\" target=\"_blank\" rel=\"noopener\">Technology category.<\/a><\/span><\/strong><\/p>\n<\/blockquote>\n<p><span style=\"color: black;\"><a style=\"color: #ff9900;\" href=\"https:\/\/www.howtogeek.com\/776621\/how-to-highlight-duplicates-in-google-sheets\/\" target=\"_blank\" rel=\"noopener\">Source<\/a><\/span><\/p>\n","protected":false},"excerpt":{"rendered":"<p>&#8220;#How to Highlight Duplicates in Google Sheets&#8221; No matter how careful you are entering or importing data, duplicates can happen. While you could\u00a0find and remove duplicates, you may want to review them, not necessarily remove them. We\u2019ll show you how to highlight duplicates in Google Sheets. You may have a list of customer email addresses&#8230;<\/p>\n","protected":false},"author":1,"featured_media":400250,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"fifu_image_url":"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/05\/google_sheets_her_1200x675.jpg?height=200p&trim=2,2,2,2","fifu_image_alt":"","footnotes":""},"categories":[18],"tags":[],"class_list":["post-400249","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-technology"],"_links":{"self":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/posts\/400249","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/comments?post=400249"}],"version-history":[{"count":0,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/posts\/400249\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/media\/400250"}],"wp:attachment":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/media?parent=400249"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/categories?post=400249"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/tags?post=400249"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}