{"id":1926,"date":"2021-03-10T19:56:16","date_gmt":"2021-03-10T18:56:16","guid":{"rendered":"https:\/\/www.myexcelonline.com\/?p=1926"},"modified":"2024-03-29T21:36:12","modified_gmt":"2024-03-29T20:36:12","slug":"remove-duplicates-in-an-excel-table","status":"publish","type":"post","link":"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/remove-duplicates-in-an-excel-table\/","title":{"rendered":"Remove Duplicates in an Excel Table"},"content":{"rendered":"<p>There is a quick and easy way to Excel Remove Duplicates from Table!<\/p>\n<p>The duplicate values could be all over your Excel Table and sometimes it takes valuable time trying to locate those duplicates and then deleting them.<\/p>\n<p>Not to worry, Excel Table Remove Duplicates to the rescue!<\/p>\n<p>In this tutorial, you will learn how to remove duplicates in Excel table:<\/p>\n<ul>\n<li><a href=\"#remove-duplicates\"><strong>Remove Duplicates<\/strong><\/a><\/li>\n<li><a href=\"#highlight-duplicates-using-conditional-formatting\"><strong>Highlight Duplicates using Conditional Formatting<\/strong><\/a><\/li>\n<li><a href=\"#copy-unique-list-to-new-location\"><strong>Copy Unique List to New Location<\/strong><\/a><\/li>\n<\/ul>\n<p>Let&#8217;s look at each of these methods!<\/p>\n<p>&nbsp;<\/p>\n<h3><a id=\"remove-duplicates\"><\/a><span style=\"color: #339966\">Remove Duplicates<\/span><\/h3>\n<p>Watch it on <a href=\"https:\/\/youtu.be\/76M58IQTaq0\" target=\"_blank\" rel=\"nofollow noopener\"><strong>YouTube<\/strong><\/a> and give it a thumbs-up!<br \/>\n<iframe src=\"https:\/\/www.youtube.com\/embed\/76M58IQTaq0?rel=0 \" width=\"800\" height=\"400\" frameborder=\"0\" allowfullscreen=\"allowfullscreen\"><span data-mce-type=\"bookmark\" style=\"width: 0px;overflow: hidden;line-height: 0\" class=\"mce_SELRES_start\">\ufeff<\/span><\/iframe><\/p>\n<p><a href=\"https:\/\/www.youtube.com\/c\/MyExcelOnlinecomtutorials?sub_confirmation=1\" target=\"_blank\" rel=\"noopener noreferrer nofollow\"><img loading=\"lazy\" decoding=\"async\" class=\"alignmiddle wp-image-111468 size-full\" style=\"vertical-align: middle\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/04\/1.yt_.png\" alt=\"\" title=\"\"><\/a><\/p>\n<p>Follow the<strong>\u00a0step-by-step tutorial<\/strong> on Excel Remove Duplicates from Table and <strong>download this Excel workbook<\/strong>\u00a0to practice along:<\/p>\n<div class=\"after-post-box\">\n<p class=\"line-two\"><a class=\"IRPP_minimalist icon-download button small\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/11\/Remove-Duplicates-1.xlsx\" target=\"_blank\" rel=\"noopener noreferrer\" data-leadbox=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/11\/Remove-Duplicates-1.xlsx\" data-leadbox-id=\"147284833f72a2:12f08263ab46dc\"><span class=\"ctaText px-3 text-capitalize\"><strong>download excel workbook<\/strong><i class=\"fa fa-download\"><\/i><\/span><span class=\"postTitle px-3\">Remove-Duplicates-1.xlsx<\/span><\/a><\/p>\n<\/div>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000\"><strong>STEP 1:<\/strong><\/span> Click inside your <em>Excel Table<\/em> and select<strong><em> Table Tools &gt; Design &gt; Remove Duplicates<\/em><\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" title=\"Remove Duplicates in an Excel Table\" class=\"alignnone size-full wp-image-19362\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/11\/Remove-Duplicates-01.png\" alt=\"Remove Duplicates in an Excel Table\" width=\"1005\" height=\"700\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/11\/Remove-Duplicates-01.png 1005w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/11\/Remove-Duplicates-01-300x209.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/11\/Remove-Duplicates-01-768x535.png 768w\" sizes=\"(max-width: 1005px) 100vw, 1005px\" \/><\/p>\n<p><span style=\"color: #ff0000\"><strong>STEP\u00a02:<\/strong><\/span> This will bring up the Remove Duplicates dialogue box. \u00a0<strong>Select only the Column box that contains the duplicates<\/strong> that you want to remove and press <strong>OK<\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" title=\"Remove Duplicates in an Excel Table\" class=\"alignnone size-full wp-image-19363\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/11\/Remove-Duplicates-02.png\" alt=\"Remove Duplicates in an Excel Table\" width=\"436\" height=\"277\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/11\/Remove-Duplicates-02.png 436w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/11\/Remove-Duplicates-02-300x191.png 300w\" sizes=\"(max-width: 436px) 100vw, 436px\" \/><\/p>\n<p>Your duplicates are now removed!<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" title=\"Remove Duplicates in an Excel Table\" class=\"alignnone wp-image-19364 size-full\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/11\/Remove-Duplicates-03.png\" alt=\"Remove Duplicates in an Excel Table\" width=\"765\" height=\"463\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/11\/Remove-Duplicates-03.png 765w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/11\/Remove-Duplicates-03-300x182.png 300w\" sizes=\"(max-width: 765px) 100vw, 765px\" \/><\/p>\n<p>&nbsp;<\/p>\n<h3><span style=\"color: #339966\"><strong><a id=\"highlight-duplicates-using-conditional-formatting\"><\/a>Highlight Duplicates using Conditional Formatting<\/strong><\/span><\/h3>\n<p>Instead of simply removing duplicates from the list, you may sometimes <strong>require to highlight duplicate values from a range<\/strong>.<\/p>\n<p>Let&#8217;s see how it can be done:<\/p>\n<p><span style=\"color: #ff0000\"><strong>STEP 1:<\/strong><\/span> Select the column containing customer name.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/11\/Screenshot-272.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Remove Duplicates in an Excel Table\" class=\"alignnone size-full wp-image-22937\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/11\/Screenshot-272.png\" alt=\"Remove Duplicates in an Excel Table\" width=\"759\" height=\"463\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/11\/Screenshot-272.png 759w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/11\/Screenshot-272-300x183.png 300w\" sizes=\"(max-width: 759px) 100vw, 759px\" \/><\/a><\/p>\n<p><span style=\"color: #ff0000\"><strong>STEP 2:<\/strong><\/span> Go to Home &gt; Conditional Formatting &gt; Highlight Cell Rules &gt; Duplicate Values.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/11\/Screenshot-273.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Remove Duplicates in an Excel Table\" class=\"alignnone size-full wp-image-22938\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/11\/Screenshot-273.png\" alt=\"Remove Duplicates in an Excel Table\" width=\"1243\" height=\"504\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/11\/Screenshot-273.png 1243w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/11\/Screenshot-273-300x122.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/11\/Screenshot-273-1024x415.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/11\/Screenshot-273-768x311.png 768w\" sizes=\"(max-width: 1243px) 100vw, 1243px\" \/><\/a><\/p>\n<p><span style=\"color: #ff0000\"><strong>STEP 3:<\/strong><\/span> In the dialog box, Click OK.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/11\/Screenshot-274.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Remove Duplicates in an Excel Table\" class=\"alignnone size-full wp-image-22940\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/11\/Screenshot-274.png\" alt=\"Remove Duplicates in an Excel Table\" width=\"369\" height=\"132\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/11\/Screenshot-274.png 369w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/11\/Screenshot-274-300x107.png 300w\" sizes=\"(max-width: 369px) 100vw, 369px\" \/><\/a><\/p>\n<p>All the duplicate values in the customer column will be highlighted in red!<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/11\/Screenshot-275.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Remove Duplicates in an Excel Table\" class=\"alignnone size-full wp-image-22941\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/11\/Screenshot-275.png\" alt=\"Remove Duplicates in an Excel Table\" width=\"745\" height=\"452\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/11\/Screenshot-275.png 745w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/11\/Screenshot-275-300x182.png 300w\" sizes=\"(max-width: 745px) 100vw, 745px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<h3><span style=\"color: #339966\"><strong><a id=\"copy-unique-list-to-new-location\"><\/a>Copy Unique List to New Location<\/strong><\/span><\/h3>\n<p>If you wish you remove duplicates and copy the list of unique values in a new location without making changes to the existing table, follow along.<\/p>\n<p><span style=\"color: #ff0000\"><strong>STEP 1:<\/strong><\/span> Go to <strong>Data<\/strong> &gt; Under <strong>Sort &amp; Filter<\/strong> &gt; Select<strong> Advanced<\/strong>.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/11\/Screenshot-276.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Remove Duplicates in an Excel Table\" class=\"alignnone size-full wp-image-22942\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/11\/Screenshot-276.png\" alt=\"Remove Duplicates in an Excel Table\" width=\"990\" height=\"129\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/11\/Screenshot-276.png 990w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/11\/Screenshot-276-300x39.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/11\/Screenshot-276-768x100.png 768w\" sizes=\"(max-width: 990px) 100vw, 990px\" \/><\/a><\/p>\n<p><span style=\"color: #ff0000\"><strong>STEP 2:<\/strong><\/span> In the Advanced Filter dialog box,<\/p>\n<ul>\n<li>Under Action, select <strong>Copy to another location<\/strong><\/li>\n<li>Under List Range, select the <strong>customer column<\/strong><\/li>\n<li>Under Copy to, select <strong>cell where you want to paste the unique customer<\/strong> list<\/li>\n<li>Check <strong>Unique records only<\/strong><\/li>\n<li>Click <strong>OK<\/strong><\/li>\n<\/ul>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/11\/Screenshot-282.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Remove Duplicates in an Excel Table\" class=\"alignnone size-full wp-image-22943\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/11\/Screenshot-282.png\" alt=\"Remove Duplicates in an Excel Table\" width=\"827\" height=\"458\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/11\/Screenshot-282.png 827w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/11\/Screenshot-282-300x166.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/11\/Screenshot-282-768x425.png 768w\" sizes=\"(max-width: 827px) 100vw, 827px\" \/><\/a><\/p>\n<p>The unique list of customers will be copied to cell I6.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/11\/Screenshot-283.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Remove Duplicates in an Excel Table\" class=\"alignnone size-full wp-image-22944\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/11\/Screenshot-283.png\" alt=\"Remove Duplicates in an Excel Table\" width=\"1057\" height=\"455\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/11\/Screenshot-283.png 1057w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/11\/Screenshot-283-300x129.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/11\/Screenshot-283-1024x441.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/11\/Screenshot-283-768x331.png 768w\" sizes=\"(max-width: 1057px) 100vw, 1057px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<h3><span style=\"color: #339966\">Conclusion<\/span><\/h3>\n<p>You can easily use either of the three options to remove duplicates from table Excel.<\/p>\n<p>You can remove duplicates from the existing table or highlight duplicates or copy a list of <a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/find-unique-values-in-excel\/\" target=\"_blank\" rel=\"noopener\">unique values<\/a> to a new location!<\/p>\n<p>&nbsp;<\/p>\n<p><strong>Make sure to download our FREE PDF on the<a href=\"https:\/\/app.monstercampaigns.com\/c\/czseo0viyrmspcx5ndve\/\" target=\"_blank\" rel=\"noopener noreferrer nofollow\">\u00a0333 Excel keyboard Shortcuts here<\/a>:<\/strong><\/p>\n<p><a href=\"https:\/\/app.monstercampaigns.com\/c\/czseo0viyrmspcx5ndve\/\" target=\"_blank\" rel=\"nofollow noopener\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-14329 size-medium\" title=\"Rank Largest to Smallest With Excel Pivot Tables | MyExcelOnline\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/03\/333coffeMockups-300x200.jpg\" alt=\"Microsoft Excel Training\" width=\"300\" height=\"200\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/03\/333coffeMockups-300x200.jpg 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/03\/333coffeMockups-1024x682.jpg 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/03\/333coffeMockups-768x512.jpg 768w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/03\/333coffeMockups.jpg 1340w\" sizes=\"(max-width: 300px) 100vw, 300px\" \/><\/a><\/p>\n<p>You can learn more about how to use Excel by viewing our FREE Excel webinar training on\u00a0<strong><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/100-43.html\" target=\"_blank\" rel=\"noopener noreferrer\">Formulas<\/a>,\u00a0<a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/100-5.html\" target=\"_blank\" rel=\"noopener noreferrer\">Pivot Tables<\/a>,\u00a0<a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/power-query-data-cleansing-webinar.html\" target=\"_blank\">Power Query<\/a>,<\/strong>\u00a0and\u00a0<strong><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/100-39.html\" target=\"_blank\" rel=\"noopener noreferrer\">Macros &amp; VBA<\/a><\/strong>!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>There is a quick and easy way to Excel Remove Duplicates from Table! The duplicate values could be all over your Excel Table and sometimes it takes valuable time trying to locate those duplicates and then deleting them. Not to worry, Excel Table Remove Duplicates to the rescue! In this tutorial, you will learn how [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":17418,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"cos_headline_score":0,"cos_seo_score":0,"cos_headline_text":"","cos_headline_has_been_analyzed":false,"cos_last_analyzed_headline":[],"om_disable_all_campaigns":false},"categories":[6,36],"tags":[88,97],"acf":[],"_links":{"self":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/1926"}],"collection":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/comments?post=1926"}],"version-history":[{"count":0,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/1926\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media\/17418"}],"wp:attachment":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media?parent=1926"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/categories?post=1926"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/tags?post=1926"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}