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 function
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.
How is the SORT function different from other functions?
The SORT 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 ‘spill’ into all available cells below. In contrast, all other functions require you to copy the result down manually.
How to apply the SORT function
Let’s take some financial data where all records relating to ‘London’ need to be sorted (see below)
- Copy the source data headings into another sheet and select the cell below the first heading – see below:
- The 1st part is the ‘ARRAY’ or range of source data for selection
- The 2nd 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 ‘BudgetDepartment’)
- The 3rd part is the SORT ORDER which is 1 for ascending or -1 for descending
The following data is now sorted by BudgetDepartment:
Sometimes the date field will return dates as an unformatted number e.g. 43160. If this happens, simply reformat the date column back to ‘date’ (use Ctrl 1 to quickly reset the date formatting)
Another problem that could arise is if there are not enough free rows to populate the results. If any data is ‘blocking’ this space, then you will get a #SPILL error:
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 ‘spill’ feature.
Conclusion
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.