{"id":664,"date":"2018-06-28T13:49:59","date_gmt":"2018-06-28T13:49:59","guid":{"rendered":"http:\/\/officetuts.net\/excel\/?p=664"},"modified":"2024-03-26T15:23:49","modified_gmt":"2024-03-26T15:23:49","slug":"duplicates","status":"publish","type":"post","link":"https:\/\/officetuts.net\/excel\/examples\/duplicates\/","title":{"rendered":"Finding and Removing Duplicates in Excel"},"content":{"rendered":"\n<p>While working in Excel, you can quickly remove duplicated values when you deal with a few cells. But when you deal with thousands of cells, this is not the best way. It\u2019s not only time-consuming but also error-prone.<\/p>\n\n\n\n<p>That\u2019s why Microsoft gives you a few ways to eliminate duplicates. In this lesson, I\u2019ll show you how you can achieve this using different methods, both the basic ones and the more complicated ones, with the VBA code included.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Highlighting duplicate values using conditional formatting<\/h2>\n\n\n\n<p><a href=\"https:\/\/officetuts.net\/excel\/vba\/conditional-formatting-in-vlookup-in-excel\/\">Conditional formatting<\/a> is probably the most popular method to distinguish data visually. It can be used to highlight low and high values with different colors. Besides that, you can find here a tool that will help you find duplicated and unique values.<\/p>\n\n\n\n<p><strong>Example:<\/strong><\/p>\n\n\n\n<p>First, create the following table with names.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"282\" height=\"181\" src=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/table-highlight-duplicates.png\" alt=\"\" class=\"wp-image-666\"\/><\/figure>\n\n\n\n<p>Select all the cells, and choose conditional formatting in&nbsp;<strong>HOME &gt;&gt; Styles &gt;&gt; Conditional Formatting &gt;&gt; Highlight Cells Rules &gt;&gt; Duplicate Values<\/strong>.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"568\" height=\"443\" src=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/highligh-duplicate-values.png\" alt=\"\" class=\"wp-image-667\" srcset=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/highligh-duplicate-values.png 568w, https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/highligh-duplicate-values-300x234.png 300w\" sizes=\"(max-width: 568px) 100vw, 568px\" \/><\/figure>\n\n\n\n<p>When you choose this option, a new window will appear.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"373\" height=\"129\" src=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/duplicate-values-window.png\" alt=\"\" class=\"wp-image-668\" srcset=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/duplicate-values-window.png 373w, https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/duplicate-values-window-300x104.png 300w\" sizes=\"(max-width: 373px) 100vw, 373px\" \/><\/figure>\n\n\n\n<p>Here, you can choose whether you want to search for duplicate or unique values, and mark them with one of the available formats or even create your own custom format.<\/p>\n\n\n\n<p>We will get the following result.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"282\" height=\"182\" src=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/highlighted-duplicates.png\" alt=\"\" class=\"wp-image-669\"\/><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\">Finding duplicate values using DGET<\/h2>\n\n\n\n<p>Another way to find duplicates is by using the&nbsp;<strong>DGET<\/strong>&nbsp;function. This function is used to retrieve a single record from a database that matches the criteria given in the argument.<\/p>\n\n\n\n<p>This function returns one of three values:<\/p>\n\n\n\n<ul>\n<li>If no records meet the condition, the function returns the #VALUE! error.<\/li>\n\n\n\n<li>If one record meets the condition, DGET returns the result.<\/li>\n\n\n\n<li>If more than one record meets the condition, DGET returns the #NUM! error.<\/li>\n<\/ul>\n\n\n\n<p>Take a look at the example:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"427\" height=\"241\" src=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/dget-function.png\" alt=\"\" class=\"wp-image-670\" srcset=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/dget-function.png 427w, https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/dget-function-300x169.png 300w\" sizes=\"(max-width: 427px) 100vw, 427px\" \/><\/figure>\n\n\n\n<p>The formula will return&nbsp;<strong>StudentID<\/strong>&nbsp;if there is only one&nbsp;<strong>StudentID<\/strong>&nbsp;value inside the table. Because there is more than one number 3813 in the C column, the formula will return the #NUM! value.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">=DGET(C1:C5, 1, A1:A2)<\/pre>\n\n\n\n<p>Let\u2019s improve the formula so we will get better results.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">=IF(ISERROR(DGET(C1:C5,1,A1:A2)),IF(ERROR.TYPE(DGET(C1:C5,1,A1:A2))=3,\"No student\", \"Duplicated student\"), \" Student entered\")<\/pre>\n\n\n\n<p>The&nbsp;<strong>IF<\/strong>&nbsp;function checks whether the condition is met. The condition is met when the&nbsp;<strong>ISERROR<\/strong>&nbsp;function returns TRUE. As I wrote earlier the&nbsp;<strong>DGET<\/strong>&nbsp;function returns errors when there is none or multiple values, otherwise, it returns a value.<\/p>\n\n\n\n<p>If it doesn\u2019t return an error, the message \u201cStudent entered\u201d appears. Now we have two possibilities, the #VALUE! error and the #NUM! error.<\/p>\n\n\n\n<p>Here, we will use the&nbsp;<strong>ERROR.TYPE<\/strong>&nbsp;function which returns the number of the error.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"138\" height=\"138\" src=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/error-type.png\" alt=\"\" class=\"wp-image-671\"\/><\/figure>\n\n\n\n<p>Inside the formula, we have the following code.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">IF(ERROR.TYPE(DGET(C1:C5,1,A1:A2))=3<\/pre>\n\n\n\n<p>It means when the returned value is #VALUE!, then it shows \u201cNo student\u201d. If the condition is not met then the error is #NUM! and the returned value must be \u201cDuplicated student\u201d.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Removing duplicates from a range<\/h2>\n\n\n\n<p>To delete duplicates, first select cells with data.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"154\" height=\"203\" src=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/duplicates-table.png\" alt=\"\" class=\"wp-image-672\"\/><\/figure>\n\n\n\n<p>Then go to&nbsp;<strong>DATA &gt;&gt; Data Tools<\/strong>&nbsp;and click the&nbsp;<strong><a href=\"https:\/\/officetuts.net\/excel\/formulas\/remove-duplicates-using-vlookup\/\">Remove Duplicates<\/a><\/strong><a href=\"https:\/\/officetuts.net\/excel\/formulas\/remove-duplicates-using-vlookup\/\">&nbsp;button<\/a>. A new window will appear.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"450\" height=\"283\" src=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/remove-duplicates-window.png\" alt=\"\" class=\"wp-image-673\" srcset=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/remove-duplicates-window.png 450w, https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/remove-duplicates-window-300x189.png 300w\" sizes=\"(max-width: 450px) 100vw, 450px\" \/><\/figure>\n\n\n\n<p>Because our data has a header, check&nbsp;<strong>My data has headers<\/strong>.<\/p>\n\n\n\n<p>The new window appears. It shows the information on how many cells were removed and how many unique items remained.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"420\" height=\"147\" src=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/microsoft-excel.png\" alt=\"\" class=\"wp-image-674\" srcset=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/microsoft-excel.png 420w, https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/microsoft-excel-300x105.png 300w\" sizes=\"(max-width: 420px) 100vw, 420px\" \/><\/figure>\n\n\n\n<p>The result is as follows.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"154\" height=\"201\" src=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/remove-duplicates-result.png\" alt=\"\" class=\"wp-image-675\"\/><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\">Removing duplicates from a table using Advanced Filter<\/h2>\n\n\n\n<p>Another trick to delete duplicates is by using the&nbsp;<strong>Advanced filter<\/strong>.<\/p>\n\n\n\n<p><strong>Example:<\/strong><\/p>\n\n\n\n<p>First, select the data where you want to <a href=\"https:\/\/officetuts.net\/excel\/examples\/remove-duplicates-from-the-pivot-table\/\">remove duplicates<\/a>.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"154\" height=\"263\" src=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/duplicates-names.png\" alt=\"\" class=\"wp-image-676\"\/><\/figure>\n\n\n\n<p>Go to&nbsp;<strong>DATA &gt;&gt; Sort &amp; Filter<\/strong>&nbsp;and click the&nbsp;<strong>Advanced<\/strong>&nbsp;button.<\/p>\n\n\n\n<p>A new window will appear.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"242\" height=\"242\" src=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/advanced-filter-window.png\" alt=\"\" class=\"wp-image-677\" srcset=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/advanced-filter-window.png 242w, https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/advanced-filter-window-150x150.png 150w\" sizes=\"(max-width: 242px) 100vw, 242px\" \/><\/figure>\n\n\n\n<p>Now you need to <a href=\"https:\/\/officetuts.net\/excel\/training\/copying-cutting-and-pasting-data\/\">copy data<\/a> to another location. Let\u2019s copy it to the next column. Choose&nbsp;<strong>Copy to another location<\/strong>&nbsp;and change $A$1:$A$12 to $B$1:$B$12. This will copy the cells to column B. What you also need to do is to check&nbsp;<strong>Unique records only<\/strong>.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"242\" height=\"242\" src=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/advanced-filter-window2.png\" alt=\"\" class=\"wp-image-678\" srcset=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/advanced-filter-window2.png 242w, https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/advanced-filter-window2-150x150.png 150w\" sizes=\"(max-width: 242px) 100vw, 242px\" \/><\/figure>\n\n\n\n<p>And the result.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"218\" height=\"262\" src=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/duplicates-table-advanced-filter.png\" alt=\"\" class=\"wp-image-679\"\/><\/figure>\n\n\n\n<p>Only <a href=\"https:\/\/officetuts.net\/excel\/formulas\/excel-list-unique-values\/\">unique values<\/a> were copied.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Removing duplicates based on several columns<\/h2>\n\n\n\n<p>Normally when you delete duplicates you analyze only a single column, but sometimes you need to take into consideration two or more.<\/p>\n\n\n\n<p>The following example will illustrate you how you can remove duplicates based on several columns.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"218\" height=\"263\" src=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/list-of-names1.png\" alt=\"\" class=\"wp-image-680\"\/><\/figure>\n\n\n\n<p>Select the data, then choose&nbsp;<strong>DATA &gt;&gt; Data Tools &gt;&gt; Remove Duplicates<\/strong>. If you check only Name you will get the following result.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"218\" height=\"184\" src=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/result-name-only.png\" alt=\"\" class=\"wp-image-681\"\/><\/figure>\n\n\n\n<p>As you can see all names in the A table are unique. Now, do the same, but this time with the surnames and your result will be as follows.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"218\" height=\"162\" src=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/result-surname-only.png\" alt=\"\" class=\"wp-image-682\"\/><\/figure>\n\n\n\n<p>Just as with names, all values are unique. Now keep both tables checked. This time, Excel removed only two rows, the first with Noah Smith and the second with Mason Williams.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"218\" height=\"222\" src=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/result-name-surname.png\" alt=\"\" class=\"wp-image-683\"\/><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\">Removing duplicates automatically<\/h2>\n\n\n\n<p>In order to remove entered duplicates automatically, you need to do two things<\/p>\n\n\n\n<ol>\n<li>Record a macro to know how the code for <a href=\"https:\/\/officetuts.net\/excel\/training\/removing-duplicates\/\">removing duplicates<\/a> looks like.<\/li>\n\n\n\n<li>Create an&nbsp;<a href=\"https:\/\/officetuts.net\/excel\/en\/training\/objects-in-vba\">event<\/a>.<\/li>\n<\/ol>\n\n\n\n<p>Enter any value in cell&nbsp;<strong>A2.<\/strong>&nbsp;If you do it only for cell&nbsp;<strong>A1<\/strong>&nbsp;Excel will display the following error.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"669\" height=\"121\" src=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/excel-error.png\" alt=\"\" class=\"wp-image-684\" srcset=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/excel-error.png 669w, https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/excel-error-300x54.png 300w\" sizes=\"(max-width: 669px) 100vw, 669px\" \/><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\">NOTICE<\/h3>\n\n\n\n<p>If you enter a value to cell&nbsp;<strong>A2,<\/strong>&nbsp;then delete it and enter it to cell&nbsp;<strong>A1,<\/strong>&nbsp;or if you just move the value of cell&nbsp;<strong>A2<\/strong>&nbsp;to&nbsp;<strong>A1,<\/strong> Excel won\u2019t display the error.<\/p>\n\n\n\n<p>Select cells&nbsp;<strong>A1:A10<\/strong>&nbsp;and start&nbsp;<a href=\"https:\/\/officetuts.net\/excel\/en\/training\/the-macro-recorder\">recording a macro<\/a>. After creating a name, click OK. Now, go to&nbsp;<strong>DATA &gt;&gt; Data Tools &gt;&gt; Remove Duplicates<\/strong>.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"450\" height=\"283\" src=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/remove-duplicates-column-a.png\" alt=\"\" class=\"wp-image-685\" srcset=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/remove-duplicates-column-a.png 450w, https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/remove-duplicates-column-a-300x189.png 300w\" sizes=\"(max-width: 450px) 100vw, 450px\" \/><\/figure>\n\n\n\n<p><strong>Column A<\/strong>&nbsp;should be selected. Click&nbsp;<strong>OK.<\/strong><\/p>\n\n\n\n<p>Stop the macro.<\/p>\n\n\n\n<p>Go to&nbsp;<strong>VIEW &gt;&gt; Macros &gt;&gt; Macros<\/strong>.<\/p>\n\n\n\n<p>Copy the following part of the macro and edit the range, changing it to&nbsp;<strong>A1:A10.<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">ActiveSheet.Range(\"$A$1:$A$10\").RemoveDuplicates Columns:=1, Header:=xlNo<\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">CAUTION<\/h3>\n\n\n\n<p>Don\u2019t use ActiveSheet.Range(&#8220;A&#8221;) because Excel will return an error.<\/p>\n\n\n\n<p>In the second part, we need to create an event.<\/p>\n\n\n\n<p>Open the VBA window (<strong><span class=\"shortcut\">Alt + F11<\/span><\/strong>), double-click the current sheet, then choose worksheet and&nbsp;<strong>SelectionChange.<\/strong>&nbsp;This will execute the code each time you change something in the worksheet. Now, enter the previous code inside the Subroutine.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"758\" height=\"435\" src=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/vba-event-creation.png\" alt=\"\" class=\"wp-image-686\" srcset=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/vba-event-creation.png 758w, https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/vba-event-creation-300x172.png 300w\" sizes=\"(max-width: 758px) 100vw, 758px\" \/><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\">CAUTION<\/h3>\n\n\n\n<p>Choose&nbsp;<strong>Worksheet,<\/strong>&nbsp;instead of&nbsp;<strong>Workbook,<\/strong>&nbsp;otherwise, Excel will return an error.<\/p>\n\n\n\n<p>Each time you enter the <a href=\"https:\/\/officetuts.net\/excel\/formulas\/count-duplicate-values\/\">value that is duplicated<\/a>, Excel will remove it automatically.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Finding duplicates using the Pivot table<\/h2>\n\n\n\n<p>This time we will count the number of duplicates using a pivot table.<\/p>\n\n\n\n<p><strong>Example:<\/strong><\/p>\n\n\n\n<p>First, create the following table.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"218\" height=\"321\" src=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/pivot-tables-duplicates.png\" alt=\"\" class=\"wp-image-687\" srcset=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/pivot-tables-duplicates.png 218w, https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/pivot-tables-duplicates-204x300.png 204w\" sizes=\"(max-width: 218px) 100vw, 218px\" \/><\/figure>\n\n\n\n<p>Click any cell inside the table and choose&nbsp;<strong>INSERT &gt;&gt; Tables &gt;&gt; PivotTable<\/strong>.<\/p>\n\n\n\n<p>When a new window appears, click OK. Now check both&nbsp;<strong>Name<\/strong>&nbsp;and&nbsp;<strong>Number.<\/strong><\/p>\n\n\n\n<p>Sort your table and you will get the following result.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"287\" height=\"303\" src=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/pivot-table-results.png\" alt=\"\" class=\"wp-image-688\" srcset=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/pivot-table-results.png 287w, https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/pivot-table-results-284x300.png 284w\" sizes=\"(max-width: 287px) 100vw, 287px\" \/><\/figure>\n","protected":false},"excerpt":{"rendered":"<p>While working in Excel, you can quickly remove duplicated values when you deal with a few cells. But when you deal with thousands&#8230;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[21],"tags":[190],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v22.3 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Finding and Removing Duplicates in Excel<\/title>\n<meta name=\"description\" content=\"Learn how to quickly remove duplicates in Excel using different methods: conditional formatting and DGET function, with VBA code included.\" \/>\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\/examples\/duplicates\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Finding and Removing Duplicates in Excel\" \/>\n<meta property=\"og:description\" content=\"Learn how to quickly remove duplicates in Excel using different methods: conditional formatting and DGET function, with VBA code included.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/officetuts.net\/excel\/examples\/duplicates\/\" \/>\n<meta property=\"article:published_time\" content=\"2018-06-28T13:49:59+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-03-26T15:23:49+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/table-highlight-duplicates.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=\"10 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/duplicates\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/duplicates\/\"},\"author\":{\"name\":\"Tomasz Decker\",\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42\"},\"headline\":\"Finding and Removing Duplicates in Excel\",\"datePublished\":\"2018-06-28T13:49:59+00:00\",\"dateModified\":\"2024-03-26T15:23:49+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/duplicates\/\"},\"wordCount\":1119,\"publisher\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42\"},\"image\":{\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/duplicates\/#primaryimage\"},\"thumbnailUrl\":\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/table-highlight-duplicates.png\",\"keywords\":[\"pinterest\"],\"articleSection\":[\"examples\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/duplicates\/\",\"url\":\"https:\/\/officetuts.net\/excel\/examples\/duplicates\/\",\"name\":\"Finding and Removing Duplicates in Excel\",\"isPartOf\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/duplicates\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/duplicates\/#primaryimage\"},\"thumbnailUrl\":\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/table-highlight-duplicates.png\",\"datePublished\":\"2018-06-28T13:49:59+00:00\",\"dateModified\":\"2024-03-26T15:23:49+00:00\",\"description\":\"Learn how to quickly remove duplicates in Excel using different methods: conditional formatting and DGET function, with VBA code included.\",\"breadcrumb\":{\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/duplicates\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/officetuts.net\/excel\/examples\/duplicates\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/duplicates\/#primaryimage\",\"url\":\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/table-highlight-duplicates.png\",\"contentUrl\":\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/table-highlight-duplicates.png\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/duplicates\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/officetuts.net\/excel\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Finding and Removing Duplicates in Excel\"}]},{\"@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 Removing Duplicates in Excel","description":"Learn how to quickly remove duplicates in Excel using different methods: conditional formatting and DGET function, with VBA code included.","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\/examples\/duplicates\/","og_locale":"en_US","og_type":"article","og_title":"Finding and Removing Duplicates in Excel","og_description":"Learn how to quickly remove duplicates in Excel using different methods: conditional formatting and DGET function, with VBA code included.","og_url":"https:\/\/officetuts.net\/excel\/examples\/duplicates\/","article_published_time":"2018-06-28T13:49:59+00:00","article_modified_time":"2024-03-26T15:23:49+00:00","og_image":[{"url":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/table-highlight-duplicates.png"}],"author":"Tomasz Decker","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Tomasz Decker","Est. reading time":"10 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/officetuts.net\/excel\/examples\/duplicates\/#article","isPartOf":{"@id":"https:\/\/officetuts.net\/excel\/examples\/duplicates\/"},"author":{"name":"Tomasz Decker","@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42"},"headline":"Finding and Removing Duplicates in Excel","datePublished":"2018-06-28T13:49:59+00:00","dateModified":"2024-03-26T15:23:49+00:00","mainEntityOfPage":{"@id":"https:\/\/officetuts.net\/excel\/examples\/duplicates\/"},"wordCount":1119,"publisher":{"@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42"},"image":{"@id":"https:\/\/officetuts.net\/excel\/examples\/duplicates\/#primaryimage"},"thumbnailUrl":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/table-highlight-duplicates.png","keywords":["pinterest"],"articleSection":["examples"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/officetuts.net\/excel\/examples\/duplicates\/","url":"https:\/\/officetuts.net\/excel\/examples\/duplicates\/","name":"Finding and Removing Duplicates in Excel","isPartOf":{"@id":"https:\/\/officetuts.net\/excel\/#website"},"primaryImageOfPage":{"@id":"https:\/\/officetuts.net\/excel\/examples\/duplicates\/#primaryimage"},"image":{"@id":"https:\/\/officetuts.net\/excel\/examples\/duplicates\/#primaryimage"},"thumbnailUrl":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/table-highlight-duplicates.png","datePublished":"2018-06-28T13:49:59+00:00","dateModified":"2024-03-26T15:23:49+00:00","description":"Learn how to quickly remove duplicates in Excel using different methods: conditional formatting and DGET function, with VBA code included.","breadcrumb":{"@id":"https:\/\/officetuts.net\/excel\/examples\/duplicates\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/officetuts.net\/excel\/examples\/duplicates\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/officetuts.net\/excel\/examples\/duplicates\/#primaryimage","url":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/table-highlight-duplicates.png","contentUrl":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/table-highlight-duplicates.png"},{"@type":"BreadcrumbList","@id":"https:\/\/officetuts.net\/excel\/examples\/duplicates\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/officetuts.net\/excel\/"},{"@type":"ListItem","position":2,"name":"Finding and Removing Duplicates in Excel"}]},{"@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\/664"}],"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=664"}],"version-history":[{"count":13,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts\/664\/revisions"}],"predecessor-version":[{"id":17296,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts\/664\/revisions\/17296"}],"wp:attachment":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/media?parent=664"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/categories?post=664"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/tags?post=664"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}