{"id":903,"date":"2015-05-13T16:51:16","date_gmt":"2015-05-13T21:51:16","guid":{"rendered":"http:\/\/www.iqaccountingsolutions.com\/blog\/?p=903"},"modified":"2015-05-13T16:51:16","modified_gmt":"2015-05-13T21:51:16","slug":"remove-duplicates-in-excel","status":"publish","type":"post","link":"https:\/\/www.iqaccountingsolutions.com\/blog\/remove-duplicates-in-excel\/","title":{"rendered":"Remove Duplicates in Excel"},"content":{"rendered":"<p>If you have a list in Excel, you may need to remove duplicate entries from that list. You could sort your list and manually delete the duplicates, but Excel has an easier way. On the <em>Data<\/em> tab of the ribbon, you\u2019ll find a <em>Remove Duplicates<\/em> button in the <em>Data Tools<\/em> section. The word \u201cremove\u201d tells you that you need to use this tool with caution. And if you don\u2019t like the results, remember that Ctrl+Z is the keyboard shortcut for Undo.<\/p>\n<p>If you have a single column list, then it\u2019s pretty simple:<\/p>\n<ul>\n<li>Select the range of cells from which you want to remove duplicates, or just select the whole column if there is nothing else below your list.<\/li>\n<li>Go to the <em>Data<\/em> tab on the ribbon.<\/li>\n<li>Click the <em>Remove Duplicates<\/em> button.<\/li>\n<li>If you have a heading at the top of your list, make sure the <em>My data has headers<\/em> box is checked in the Remove Duplicates window.<\/li>\n<li>Click <em>OK<\/em>.<\/li>\n<\/ul>\n<p>Excel will then tell you how many duplicate values it found\/removed and how many unique values remain. It will also move the remaining values up to fill in the gaps left by removing the duplicates. That\u2019s a nice finishing touch since you won\u2019t have to manually remove the blank rows, but it is also the reason that you have to be very careful with this tool when your list has multiple columns.<br \/>\nIf you have multiple columns of data but only want to check one of them for duplicates, <strong><u>you must select all columns in your list before removing duplicates<\/u><\/strong>. If you don\u2019t, then after the duplicates are removed, the remaining values will no longer be on the same line as their corresponding data in the other columns.<\/p>\n<p>Also, when you select multiple columns of data, you can choose how many of those columns should be checked for duplicates by simply checking or clearing the box next to each column name in the <em>Remove Duplicates<\/em> Windows. Excel will then look for duplicates based on the combined content of the chosen columns, so checking multiple columns will result in fewer lines being removed, not more.<\/p>\n<p>Here is an example of how you will get different results depending on the columns you select:<\/p>\n<p>If you highlight columns A and B, then click the Remove Duplicates button, and select only the Product column in the Remove Duplicates window, lines 3, 5, 7, 9, 10, and 11 will all be removed (including the Color data on those lines). You\u2019ll be left with only one entry each for Widget A, Widget B, Widget C, and Widget D.<\/p>\n<table style=\"height: 718px;\" width=\"209\">\n<tbody>\n<tr>\n<td width=\"35\"><\/td>\n<td width=\"89\">A<\/td>\n<td width=\"89\">B<\/td>\n<\/tr>\n<tr>\n<td width=\"35\">1<\/td>\n<td width=\"89\"><strong><u>PRODUCT<\/u><\/strong><\/td>\n<td width=\"89\"><strong><u>COLOR<\/u><\/strong><\/td>\n<\/tr>\n<tr>\n<td width=\"35\">2<\/td>\n<td width=\"89\">Widget A<\/td>\n<td width=\"89\">Blue<\/td>\n<\/tr>\n<tr>\n<td width=\"35\">3<\/td>\n<td width=\"89\">Widget A<\/td>\n<td width=\"89\">White<\/td>\n<\/tr>\n<tr>\n<td width=\"35\">4<\/td>\n<td width=\"89\">Widget B<\/td>\n<td width=\"89\">Blue<\/td>\n<\/tr>\n<tr>\n<td width=\"35\">5<\/td>\n<td width=\"89\">Widget B<\/td>\n<td width=\"89\">White<\/td>\n<\/tr>\n<tr>\n<td width=\"35\">6<\/td>\n<td width=\"89\">Widget C<\/td>\n<td width=\"89\">Blue<\/td>\n<\/tr>\n<tr>\n<td width=\"35\">7<\/td>\n<td width=\"89\">Widget C<\/td>\n<td width=\"89\">White<\/td>\n<\/tr>\n<tr>\n<td width=\"35\">8<\/td>\n<td width=\"89\">Widget D<\/td>\n<td width=\"89\">Blue<\/td>\n<\/tr>\n<tr>\n<td width=\"35\">9<\/td>\n<td width=\"89\">Widget D<\/td>\n<td width=\"89\">White<\/td>\n<\/tr>\n<tr>\n<td width=\"35\">10<\/td>\n<td width=\"89\">Widget C<\/td>\n<td width=\"89\">Blue<\/td>\n<\/tr>\n<tr>\n<td width=\"35\">11<\/td>\n<td width=\"89\">Widget D<\/td>\n<td width=\"89\">White<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>But, if you selected both the Product and Color columns in the <em>Check for Duplicates<\/em> window, only rows 10 and 11 would be removed. That\u2019s because Excel will only consider it a duplicate if the combined contents of both columns create a duplicate.<\/p>\n<p><iframe loading=\"lazy\" width=\"500\" height=\"281\" src=\"https:\/\/www.youtube.com\/embed\/jQq1iMAEGOk?feature=oembed\" frameborder=\"0\" allowfullscreen><\/iframe><\/p>\n","protected":false},"excerpt":{"rendered":"<p>If you have a list in Excel, you may need to remove duplicate entries from that list. You could sort your list and manually delete the duplicates, but Excel has an easier way. On the Data tab of the ribbon, you\u2019ll find a Remove Duplicates button in the Data Tools section. The word \u201cremove\u201d tells [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[9,5],"tags":[],"_links":{"self":[{"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/posts\/903"}],"collection":[{"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/comments?post=903"}],"version-history":[{"count":1,"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/posts\/903\/revisions"}],"predecessor-version":[{"id":904,"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/posts\/903\/revisions\/904"}],"wp:attachment":[{"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/media?parent=903"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/categories?post=903"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/tags?post=903"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}