{"id":432470,"date":"2022-04-15T18:00:36","date_gmt":"2022-04-15T15:00:36","guid":{"rendered":"https:\/\/en.buradabiliyorum.com\/how-to-list-and-sort-unique-values-and-text-in-microsoft-excel\/"},"modified":"2022-04-15T18:00:36","modified_gmt":"2022-04-15T15:00:36","slug":"how-to-list-and-sort-unique-values-and-text-in-microsoft-excel","status":"publish","type":"post","link":"https:\/\/buradabiliyorum.com\/en\/how-to-list-and-sort-unique-values-and-text-in-microsoft-excel\/","title":{"rendered":"#How to List and Sort Unique Values and Text in Microsoft 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-6a2d1e2f8aa2f\" 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-6a2d1e2f8aa2f\" 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-1'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/buradabiliyorum.com\/en\/how-to-list-and-sort-unique-values-and-text-in-microsoft-excel\/#%E2%80%9CHow_to_List_and_Sort_Unique_Values_and_Text_in_Microsoft_Excel%E2%80%9D\" >&#8220;How to List and Sort Unique Values and Text in Microsoft Excel&#8221;<\/a><ul class='ez-toc-list-level-2' ><li class='ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/buradabiliyorum.com\/en\/how-to-list-and-sort-unique-values-and-text-in-microsoft-excel\/#Use_the_UNIQUE_Function_in_Excel\" >Use the UNIQUE Function in Excel<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/buradabiliyorum.com\/en\/how-to-list-and-sort-unique-values-and-text-in-microsoft-excel\/#Sort_the_List_Automatically\" >Sort the List Automatically<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/buradabiliyorum.com\/en\/how-to-list-and-sort-unique-values-and-text-in-microsoft-excel\/#Combine_Unique_Values\" >Combine Unique Values<\/a><\/li><\/ul><\/li><\/ul><\/nav><\/div>\n<h1><span class=\"ez-toc-section\" id=\"%E2%80%9CHow_to_List_and_Sort_Unique_Values_and_Text_in_Microsoft_Excel%E2%80%9D\"><\/span>&#8220;How to List and Sort Unique Values and Text in Microsoft Excel&#8221;<span class=\"ez-toc-section-end\"><\/span><\/h1>\n<div>\n<img loading=\"lazy\" decoding=\"async\" class=\"type:primaryImage alignnone size-full wp-image-757715\" data-pagespeed-lazy-srcset=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/09\/microsoft_excel_hero_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\/09\/microsoft_excel_hero_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\/09\/microsoft_excel_hero_1200x675.jpg?width=1198&amp;trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Microsoft Excel logo on a green background\" 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>When you want a list of customers, email addresses, product IDs, or something similar where each one is distinct, Excel has a function to help. We\u2019ll show you how to use this function to list unique values and text.<\/p>\n<p>You can already use functions in Excel to total the number of distinct values. But here we\u2019ll show you how to list those values instead using the UNIQUE function. Plus, we\u2019ll throw in easy ways to sort the list and combine values.<\/p>\n<blockquote class=\"admonishment_note\"><p><strong>Note:<\/strong> As of March 2022, the UNIQUE function is available in Excel for Microsoft 365, Excel for the web, Excel 2021 or later, or Excel for iPhone, iPad, or Android phones or tablets.<\/p><\/blockquote>\n<p><strong>RELATED:<\/strong> <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/www.howtogeek.com\/792407\/count-unique-values-in-excel\/\"><strong><em>How to Count Unique Values in Microsoft Excel<\/em><\/strong><\/p>\n<h2 role=\"heading\" aria-level=\"2\"><span class=\"ez-toc-section\" id=\"Use_the_UNIQUE_Function_in_Excel\"><\/span>Use the UNIQUE Function in Excel<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>You can use the UNIQUE <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/www.howtogeek.com\/791554\/excel-functions-for-text\/\">function for text or numbers, decide how to compare the cell range, and opt to show results that only appear once.<\/p>\n<p>The syntax for the function is <code>UNIQUE(array, column, only_once)<\/code> where only the first argument is required. Include the <code>column<\/code> argument to compare columns instead of rows and the <code>only_once<\/code> argument to return values that occur only one time in the array.<\/p>\n<p>Should you choose to include the optional arguments, you\u2019ll use the TRUE indicator in the formula for each. If no indicator is included, the function assumes FALSE.<\/p>\n<p>As an example, we\u2019ll create a list of customers for an email blast. Rather than use the existing list in cells A2 through A10 because some customers ordered more than once, we\u2019ll make a new list where each customer <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>ears one time.<\/p>\n<pre>=UNIQUE(A2:A10)<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-793364\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/03\/UNIQUE-ExcelUNIQUEFunction.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"UNIQUE function in Excel\" width=\"650\" height=\"254\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>For another example, we\u2019ll add the third argument, <code>only_once<\/code>, to find those customers who\u2019ve only ordered once.<\/p>\n<pre>=UNIQUE(A2:A10,,TRUE)<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-793363\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/03\/OnlyOnce-ExcelUNIQUEFunction.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"UNIQUE function for data appearing once\" width=\"650\" height=\"254\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>Because the second argument assumes FALSE if nothing is included, we simply add a comma after the first argument and then another comma before the last argument. Alternatively, you can use this formula to obtain the same result:<\/p>\n<pre>=UNIQUE(A2:A10,FALSE,TRUE)<\/pre>\n<p>You can use the UNIQUE function to list distinct values as well as text. In this formula, we can list unique dates:<\/p>\n<pre>=UNIQUE(F2:F10)<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-793362\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/03\/UNIQUEDates-ExcelUNIQUEFunction.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"UNIQUE function for dates\" width=\"650\" height=\"235\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p><strong>RELATED:<\/strong> <strong><em>How to Sort by Date in Microsoft Excel<\/em><\/strong><\/p>\n<h2 role=\"heading\" aria-level=\"2\"><span class=\"ez-toc-section\" id=\"Sort_the_List_Automatically\"><\/span>Sort the List Automatically<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>As mentioned, you can <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/www.howtogeek.com\/702718\/how-to-sort-and-filter-data-in-excel\/\">sort the list automatically at the same time you use the UNIQUE function to create it. To do this, you simply add the SORT function to the beginning of the formula.<\/p>\n<blockquote class=\"admonishment_note\"><p><strong>Note:<\/strong> The SORT function is currently only available in the Excel versions listed <a rel=\"nofollow noopener\" target=\"_blank\" href=\"#moka_anchor_Versions\">earlier<\/a>.<\/p><\/blockquote>\n<p>The syntax for this function is <code>SORT(array, index, order, column)<\/code> where only the first argument is required.<\/p>\n<p>Using the first list of unique customers we created above and sort it im<a href=\"https:\/\/buradabiliyorum.com\/en\/category\/social-mediaa\/\" data-internallinksmanager029f6b8e52c=\"1\" title=\"Social Media\" target=\"_blank\" rel=\"noopener\">media<\/a>tely, you would use this formula:<\/p>\n<pre>=SORT(UNIQUE(A2:A10))<\/pre>\n<p>As you can see, the UNIQUE formula is the required <code>array<\/code> argument for the SORT function.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-793366\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/03\/SortAscending-ExcelUNIQUEFunction.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Sort values in ascending order\" width=\"650\" height=\"257\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>By default, the SORT function lists items in ascending order. To sort the same list in descending order, you would use the following formula which adds the <code>order<\/code> argument.<\/p>\n<pre>=SORT(UNIQUE(A2:A10),,-1)<\/pre>\n<p>Notice here we have a double comma again. This is because we don\u2019t want the <code>index<\/code> argument, only the <code>order<\/code> argument.\u00a0Use 1 for ascending order and -1 for descending order. If no value is used, the function assumes 1 by default.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-793365\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/03\/SortDescending-ExcelUNIQUEFunction.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Sort values in descending order\" width=\"650\" height=\"254\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<h2 role=\"heading\" aria-level=\"2\"><span class=\"ez-toc-section\" id=\"Combine_Unique_Values\"><\/span>Combine Unique Values<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>One more handy addition to the UNIQUE function allows you to combine values. For instance, maybe your list has values in two columns instead of just one as in the screenshot below.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-793367\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/03\/NamesToConcatenate-ExcelUNIQUEFunction.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"First and last names to combine\" width=\"240\" height=\"270\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>By adding the ampersand (&amp;) operator and a space, we can create a list of first and last names of unique customers with this formula:<\/p>\n<pre>=UNIQUE(A2:A10&amp;\" \"&amp;B2:B10)<\/pre>\n<p>To break down the formula, the first array, A2 through A10, contains the first names, the ampersands <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/www.howtogeek.com\/782211\/how-to-merge-two-columns-in-microsoft-excel\/\">concatenate the first names to the last names in B2 through B10 with a space between them in quotes.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-793369\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/03\/Concatenate-ExcelUNIQUEFunction.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Combine unique first and last names\" width=\"650\" height=\"231\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>You can also include the SORT function here to put your list in ascending order with this formula:<\/p>\n<pre>=SORT(UNIQUE(A2:A10&amp;\" \"&amp;B2:B10))<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-793368\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/03\/ConcatenateSort-ExcelUNIQUEFunction.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Combine and sort unique first and last names\" width=\"650\" height=\"231\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>Just like you might want to highlight duplicate values in Excel, you may want to find unique ones. Keep the UNIQUE function and these additional ways to use it in the mind the next time you need to create a list of distinct values or text in Excel.<\/p>\n<p><strong>RELATED:<\/strong> <strong><em>How to Use Conditional Formatting to Find Duplicate Data in Excel<\/em><\/strong><\/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\/793358\/list-and-sort-unique-values-and-text-in-microsoft-excel\/\" target=\"_blank\" rel=\"noopener\">Source<\/a><\/span><\/p>\n","protected":false},"excerpt":{"rendered":"<p>&#8220;How to List and Sort Unique Values and Text in Microsoft Excel&#8221; When you want a list of customers, email addresses, product IDs, or something similar where each one is distinct, Excel has a function to help. We\u2019ll show you how to use this function to list unique values and text. You can already use&#8230;<\/p>\n","protected":false},"author":1,"featured_media":432471,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"fifu_image_url":"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/09\/microsoft_excel_hero_1200x675.jpg?height=200p&trim=2,2,2,2","fifu_image_alt":"","footnotes":""},"categories":[18],"tags":[],"class_list":["post-432470","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\/432470","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=432470"}],"version-history":[{"count":0,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/posts\/432470\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/media\/432471"}],"wp:attachment":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/media?parent=432470"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/categories?post=432470"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/tags?post=432470"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}