Skip main navigation

SORT or SORTBY

Excel has two new sort functions. SORT and SORTBY. Watch Paula Guilfoyle demonstrate both.
6
Now, let us take a look at the SORT function. Well, the SORT function will take a list and it will sort the list. So the syntax for SORT is array, then we have sort index, sort order, and by column as well. So if we were just to take an array, because the other options are in square brackets, they are optional and hit enter. We will get a sorted list of items. Now we can combine SORT with UNIQUE, so if we were to go back in here and put in UNIQUE, then we can get a sorted list of unique items. So that is the basics of SORT.
50.6
Now, SORT can work on multiple columns, so let us say SORT and we want to take in all of these columns here, and now we want to select a sort index. So for example, what column is it we want to sort by… Well, let’s say we want to sort by the third column. We then also have an option of sorting by ascending, which is optional, or descending. So we will skip that which will be sorted by ascending. Then we need to select whether we want to sort by column or by row.
89.2
Again, by row is the default so we will put in by row, but if we have the data going across the columns, we would sort by columns and this will return an entire way sorted by the third column which is the sales rep column. Now, we can also sort by multiple columns. So let’s go back in here and I will delete back to there and then we have our sort index. Now, let’s say we wanted to sort by two different columns. Let’s say first we wanted to sort by the sales rep and then we wanted to sort by the value.
122
So we want to sort by column three and then by column four Now note, they are put inside of curly brackets because you’re putting in an array in an array formula. So you put these inside the sort bracket. Now if I hit enter and close, it’s going to sort the values by, first, by the sales rep column and second. by the value column. But what if you wanted the columns sorted differently? For example, the First Column started Ascending and the second column started Descending. Well, let’s go back in and we can amend our formula again. So this time, inside curly brackets you put in the sort order for each column that you have specified to sort by.
169.5
So, for example, we want one for our sales rep and we want minus one this time for our values and that should be in a curly bracket and then My bracket and now you see we have our sort order has changed around at the highest value is first, but we have our sales rep sorting in Ascending and we have our values sorting in Descending order. So what you need to remember is you can sort by multiple columns when you’re using the sort function. However, what you cannot do is sort by a column that’s not included in the actual array.
209.6
For example, if we now wanted to sort this column by date we would actually need to include the date column in this array here or we can use the sort by function. If you don’t want to actually show that particular column in your formula. So let’s go through the sortby function. I’m going to delete this. We will relabel this to “sortby.” So we are start by looking at our “Sortby Syntax.” So, “Sortby” first looks for an array. Now this is the array that you want to sort something by. Then it looks for what array you want to sort out by and the sort order. Now by array and sort order you can have multiple by arrays and multiple sort orders.
258.7
For example, we want to sort our table, our product customer and sales reps. We want to sort this by the value but we don’t want to actually show the value and we want to sort it in descending order. So you can see there now that we have sorted our table of products, customers and sales reps, but we’ve actually sorted it by our sales value in descending order but we haven’t shown our actual sales by value. So that’s the difference between sort and our sortby function. Sort will allow you to sort the columns that you selected in the array by any of the columns that you selected within the array.
311.2
Sortby will allow you sort by a column that you’ve not selected in the sorting array. So, in the array that you want sorted, you can select a different column that’s not in that array in the sortby function. [End]

The SORT function and SORTBY function will allow you to sort data, but both have very different use cases. The SORT function will sort a list but the SORTBY function will sort a list based on a column not included in the sort array.

Comment below

How can you see SORT or SORTBY being applied in your work? Comment below

This article is from the free online

Excel Skills to Make an Impression

Created by
FutureLearn - Learning For Life

Our purpose is to transform access to education.

We offer a diverse selection of courses from leading universities and cultural institutions from around the world. These are delivered one step at a time, and are accessible on mobile, tablet and desktop, so you can fit learning around your life.

We believe learning should be an enjoyable, social experience, so our courses offer the opportunity to discuss what you’re learning with others as you go, helping you make fresh discoveries and form new ideas.
You can unlock new opportunities with unlimited access to hundreds of online short courses for a year by subscribing to our Unlimited package. Build your knowledge with top universities and organisations.

Learn more about how FutureLearn is transforming access to education