{"id":327062,"date":"2021-08-18T18:00:04","date_gmt":"2021-08-18T15:00:04","guid":{"rendered":"https:\/\/en.buradabiliyorum.com\/how-to-use-conditional-formatting-to-find-duplicate-data-in-excel\/"},"modified":"2021-08-18T18:00:04","modified_gmt":"2021-08-18T15:00:04","slug":"how-to-use-conditional-formatting-to-find-duplicate-data-in-excel","status":"publish","type":"post","link":"https:\/\/buradabiliyorum.com\/en\/how-to-use-conditional-formatting-to-find-duplicate-data-in-excel\/","title":{"rendered":"#How to Use Conditional Formatting to Find Duplicate Data in Excel"},"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-6a2e380bab1d0\" 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-6a2e380bab1d0\" 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-use-conditional-formatting-to-find-duplicate-data-in-excel\/#Highlight_Duplicate_Data_in_Excel\" >Highlight Duplicate Data in Excel<\/a><\/li><\/ul><\/nav><\/div>\n<p><strong>&#8220;#How to Use Conditional Formatting to Find Duplicate Data in Excel&#8221;<\/strong><\/p>\n<div>\n<!-- UNCACHED CONTENT --><br \/>\n<img loading=\"lazy\" decoding=\"async\" class=\"type:primaryImage alignnone size-full wp-image-731027\" srcset=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/05\/MS-excel-logo-675.png?width=398&amp;trim=1,1&amp;bg-color=000&amp;pad=1,1 400w, https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/05\/MS-excel-logo-675.png?width=1198&amp;trim=1,1&amp;bg-color=000&amp;pad=1,1 1200w\" sizes=\"auto, 400w, 1200w\" src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/05\/MS-excel-logo-675.png?width=1198&amp;trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Microsoft Excel Logo\" width=\"1200\" height=\"675\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>Whether you obtain data from an external source or have someone performing data entry, mistakes 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. If you\u2019re concerned that you may have duplicate data in Microsoft Excel, you can find out quickly with conditional formatting.<\/p>\n<p>You might have details for customers, locations, vendors, or something similar where duplicate data can be inconvenient. But maybe you have data like product, order, or payment identifiers where duplicates can be detrimental. Here\u2019s how to find that duplicate data fast in your Excel sheet.<\/p>\n<h2 role=\"heading\" aria-level=\"2\"><span class=\"ez-toc-section\" id=\"Highlight_Duplicate_Data_in_Excel\"><\/span>Highlight Duplicate Data in Excel<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Setting up\u00a0conditional formatting rules can sometimes get complicated. Fortunately, locating duplicates in your Excel sheet using the feature isn\u2019t one of those situations. You can actually accomplish this task in just a few clicks.<\/p>\n<p>Start by selecting the cells you want to review for duplicates. If your entire spreadsheet is in question, you can select it instead by clicking the triangle in the upper left corner of the sheet.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-740186 size-full\" src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/07\/SelectSheetConditionalFormatting-Excel.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Click the triangle to select the entire sheet in Excel\" width=\"320\" height=\"340\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>Go to the Home tab and the Styles section of the ribbon. Click \u201cConditional Formatting,\u201d move to \u201cHighlight Cell Rules,\u201d and choose \u201cDuplicate Values\u201d in the pop-out menu.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-740185\" src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/07\/ConditionalFormattingHighlightCellRulesDuplicateValues-Excel.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Click Conditional Formatting, Highlight Cell Rules, Duplicate Values\" width=\"495\" height=\"500\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>When the Duplicate Values window displays, you should im<a href=\"https:\/\/buradabiliyorum.com\/en\/category\/social-mediaa\/\" data-internallinksmanager029f6b8e52c=\"1\" title=\"Social Media\" target=\"_blank\" rel=\"noopener\">media<\/a>tely see your duplicates highlighted with the default formatting applied. However, you can change this if you wish.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-740187\" src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/07\/ConditionalFormattingDuplicateValuesDefaultFormat-Excel.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Conditional formatting defaults for duplicates\" width=\"650\" height=\"285\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>Confirm that \u201cDuplicate\u201d displays in the first drop-down box. Then, click the second drop-down box to select a different format. Click \u201cOK\u201d to apply the format to your duplicate data.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-740188\" src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/07\/ConditionalFormattingDuplicateValuesPickFormat-Excel.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Pick a format style from the list\" width=\"430\" height=\"230\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>If you prefer a format that isn\u2019t listed, click \u201cCustom Format\u201d in the drop-down box to pick a font, border, or fill style in the subsequent pop-up window. Click \u201cOK.\u201d<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-740189\" src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/07\/ConditionalFormattingDuplicateValuesCreateFormat-Excel.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Create a Custom Format\" width=\"650\" height=\"556\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>You\u2019ll see the Custom Format applied to the cell immediately. If you like it, click \u201cOK\u201d to apply it.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-740190\" src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/07\/ConditionalFormattingDuplicateValuesCustomFormat-Excel.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Custom Conditional Formatting in Excel\" width=\"650\" height=\"285\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>Once you have your duplicate data highlighted using conditional formatting, you can make the corrections or adjustments you need to. And once you do, the formatting will disappear as long as it\u2019s not duplicated elsewhere in your cell selection.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-740191\" src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/07\/BeforeAfterDuplicateValuesCustomFormat-Excel.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Duplicates formatting and corrected in Excel\" width=\"650\" height=\"325\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>Wondering what other ways conditional formatting can help you find data entry mistakes? Check out how to highlight blank cells or errors with it in Excel.<\/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\/740183\/how-to-use-conditional-formatting-to-find-duplicate-data-in-excel\/\" target=\"_blank\" rel=\"noopener\">Source<\/a><\/span><\/p>\n","protected":false},"excerpt":{"rendered":"<p>&#8220;#How to Use Conditional Formatting to Find Duplicate Data in Excel&#8221; Whether you obtain data from an external source or have someone performing data entry, mistakes can happen. If you\u2019re concerned that you may have duplicate data in Microsoft Excel, you can find out quickly with conditional formatting. You might have details for customers, locations,&#8230;<\/p>\n","protected":false},"author":1,"featured_media":327063,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"fifu_image_url":"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/05\/MS-excel-logo-675.png?height=200p&trim=2,2,2,2","fifu_image_alt":"","footnotes":""},"categories":[18],"tags":[],"class_list":["post-327062","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\/327062","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=327062"}],"version-history":[{"count":0,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/posts\/327062\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/media\/327063"}],"wp:attachment":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/media?parent=327062"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/categories?post=327062"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/tags?post=327062"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}