This blog is brought to you by the Excel Community where you can find additional blogs, extended articles and webinar recordings on a variety of Excel related topics. In addition to live training events, Excel Community members have access to a full suite of online training modules from Filtered. There is also an online forum where you can ask questions and share ideas with other community members.
Hello all and welcome back to the Excel Tip of the Week! This week, we have a General User level post in which we're continuing our occasional series looking at some of the (for now) Google Sheets-specific functions. This began a couple of weeks ago with TOTW #299, about UNIQUE, and this week continues on to talk SORT.
What this function does
Like UNIQUE, SORT is an array function. It can take a range of data, and return that same data sorted in a different order. It's a function equivalent of the sort button found in both Excel and Sheets.
Here, a single function is entered in E1 that creates an entire sorted table in columns E-G. This function is set to sort by Amount in descending order.
How to write a SORT function
The syntax is as follows:
=SORT(range, 1st column to sort by, ascending order marker, optional 2nd column to sort by, optional 2nd ascending order marker...)
Range is the entire range of the data you wish to sort. Note that, if you include the header row, this too will be sorted - so you should omit it.This is then be followed by at least one pair of a column identifier and an ascending order marker. The column identifier is usually just a number 1, 2, 3... indicating which column the range should be sorted by (although you can also highlight the range itself if preferred). The order marker should be TRUE if the sort is in ascending order, or FALSE for descending order. After the first pair, you can add more pairs if desired to create a multi-level sort. For example, here is a sort which is ascending order by Customer and then descending order by Amount:
Don't forget that you can combine SORT with other array functions - for example, the aforementioned UNIQUE function:
Also note that, in cases like this where there is only one column, you can skip the other two inputs of SORT - it will default to ascending order by column 1.
You can check out all these examples and experiment with making your own right here.
Previous post - Copying worksheet tabsTOTW index Next post - Linear regression functions
Pity that Google choose to use TRUE and FALSE for the sort order when ASC and DESC, as used in SQL, may have been more easily understood. Particularly as spreadsheet applications move closer to being more like databases.