{"id":6496,"date":"2023-06-22T09:17:16","date_gmt":"2023-06-22T09:17:16","guid":{"rendered":"https:\/\/www.stl-training.co.uk\/b\/?p=6496"},"modified":"2023-12-30T22:30:05","modified_gmt":"2023-12-30T22:30:05","slug":"be-more-productive-with-excels-sort-function-2-of-2","status":"publish","type":"post","link":"https:\/\/www.stl-training.co.uk\/b\/be-more-productive-with-excels-sort-function-2-of-2\/","title":{"rendered":"Be More Productive With Excel\u2019s SORT function (2 of 4)"},"content":{"rendered":"

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

\"sort\"<\/p>\n

What is the SORT function<\/strong><\/h2>\n

The SORT function is based on the standard way of sorting in Excel. It works by extracting the sorted rows from the source data and populating these rows to another sheet. As the sorting process does not affect the source data, the sorted data can be re-sorted without having to return to the original dataset. Thereby increasing your efficiency.<\/p>\n

How is the SORT function different from other functions?<\/strong><\/h2>\n

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

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

Let\u2019s take some financial data where all records relating to \u2018London\u2019 need to be sorted (see below)<\/p>\n

\"filter\"<\/p>\n

    \n
  1. Copy the source data headings into another sheet and select the cell below the first heading \u2013 see below:<\/li>\n<\/ol>\n

    \"sort\"<\/p>\n

    \"sort\"<\/p>\n

      \n
    • The 1st<\/sup> part is the \u2018ARRAY\u2019 or range of source data for selection<\/li>\n
    • The 2nd<\/sup> part is the SORT INDEX which is a number assigned to the position of the field to be sorted from the left (e.g. 3 for \u2018BudgetDepartment\u2019)<\/li>\n
    • The 3rd<\/sup> part is the SORT ORDER which is 1 for ascending or -1 for descending<\/li>\n<\/ul>\n

      The following data is now sorted by BudgetDepartment:<\/p>\n

      \"filter\"<\/p>\n

      Sometimes the date field will return dates as an unformatted number e.g. 43160. If this happens, simply reformat the date column back to \u2018date\u2019 (use Ctrl 1 to quickly reset the date formatting)<\/p>\n

      Another problem that could arise is if there are not enough free rows to populate the results. If any data is \u2018blocking\u2019 this space, then you will get a #SPILL error:<\/p>\n

      \"\"<\/p>\n

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

      Conclusion<\/strong><\/h2>\n

      The Excel SORT function is an amazing tool that allows you to sort data whilst leaving the original source data untouched. Consequently, the SORT function can help improve your efficiency and productivity when you need to refer back to your unsorted lists.<\/p>\n","protected":false},"excerpt":{"rendered":"

      This blog is part 2 of a series that looks at the amazing functionality of some of the most popular Office 365 Excel functions. In part 1 the focus was on the FILTER function. Building on this, we will explore how useful the SORT function is in arranging data more efficiently. What is the SORT […]<\/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,9],"tags":[67],"class_list":["post-6496","post","type-post","status-publish","format-standard","hentry","category-application","category-excel-training","category-microsoft-office","tag-autofilter-and-sort-in-excel"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts\/6496","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=6496"}],"version-history":[{"count":2,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts\/6496\/revisions"}],"predecessor-version":[{"id":6528,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts\/6496\/revisions\/6528"}],"wp:attachment":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/media?parent=6496"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/categories?post=6496"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/tags?post=6496"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}