{"id":6529,"date":"2023-08-01T12:26:06","date_gmt":"2023-08-01T12:26:06","guid":{"rendered":"https:\/\/www.stl-training.co.uk\/b\/?p=6529"},"modified":"2023-12-30T23:00:54","modified_gmt":"2023-12-30T23:00:54","slug":"be-more-productive-with-excels-unique-function-3-of-4","status":"publish","type":"post","link":"https:\/\/www.stl-training.co.uk\/b\/be-more-productive-with-excels-unique-function-3-of-4\/","title":{"rendered":"Be more productive with Excel\u2019s UNIQUE function (3 of 4)"},"content":{"rendered":"

This blog is part 3 of a series that looks at the amazing functionality of some of the most popular Office 365 Excel<\/a> functions. In parts 1 and 2 the focus was on the FILTER and SORT functions. Building on this, we will explore how useful the UNIQUE function is in creating\u00a0 unique lists more efficiently.<\/p>\n

\"\"<\/p>\n

What is the UNIQUE function and why is it useful<\/h2>\n

The UNIQUE function lets you extract all the multiple entries in a list and populates them as single entries in a unique list. For example, clients that do business with a company may place many different orders so their name will get repeated many times. Having a unique list of clients can help to show summaries of total orders or sales.<\/p>\n

How is it different from other functions?<\/h2>\n

The UNIQUE function is one of a number of Office 365 functions that behaves differently to all other Excel functions. With the UNIQUE function, the results automatically \u2018spill\u2019 into all available cells below. In contrast, all other functions require the result to be copied down manually.<\/p>\n

How to apply the function<\/h2>\n

Let\u2019s take a list of names where some of them appear more than once \u2013 see below:<\/p>\n

\"Unique\"<\/p>\n

    \n
  1. Type =UNIQUE(A1:A14) in an empty cell to produce the following unique list:\n

    \"Unique\"
    \n2. To return a range from a horizontal list do the following:<\/p>\n

    \"\"<\/p>\n

    A) Select the range of cells in the list e.g. E1:R1Type a comma and then TRUE \u2013 B) this returns all unique items across all the columns.<\/p>\n

    Another really useful feature of UNIQUE is being able to produce a list of all items that appear only once. From the original list, we can see all the items that have no duplicates i.e.. those that display no colour (see below):<\/p>\n

    \"Unique\"
    \n3. To return a distinct list type =UNIQUE(A1:A14,,TRUE) where:
    \nA) the 2 commas mean \u201cbypass the 2<\/span>nd<\/sup> part\u201d because the list is vertical not horizontal<\/span>
    \nB) TRUE returns only items that appear once in the original list <\/p>\n

    The result is the following list:
    \n\"\"<\/p>\n

    One thing to look out for is if any data is \u2018blocking\u2019 this space then you will get a #SPILL error. Consequently, the resulting data will not populate the cells. (See below)<\/p>\n

    To remove the spill error, simply delete the data that is blocking the spill. Other Office 365 functions such as SORT and FILTER also have this \u2018spill\u2019 feature.<\/p>\n

    Conclusion<\/h1>\n

    The Excel UNIQUE function is an amazing tool that allows you to extract specific data quickly and efficiently from a list of multiple entries.<\/li>\n<\/ol>\n","protected":false},"excerpt":{"rendered":"

    This blog is part 3 of a series that looks at the amazing functionality of some of the most popular Office 365 Excel functions. In parts 1 and 2 the focus was on the FILTER and SORT functions. Building on this, we will explore how useful the UNIQUE function is in creating\u00a0 unique lists more […]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[637,4,8],"tags":[219,685],"class_list":["post-6529","post","type-post","status-publish","format-standard","hentry","category-application","category-excel-training","category-microsoft","tag-excel-tips","tag-unique-function"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts\/6529","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/comments?post=6529"}],"version-history":[{"count":2,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts\/6529\/revisions"}],"predecessor-version":[{"id":6537,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts\/6529\/revisions\/6537"}],"wp:attachment":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/media?parent=6529"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/categories?post=6529"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/tags?post=6529"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}