Use the new SORT and SORTBY functions to extract sorted lists

- [Instructor] If you have Office 365 and have the latest updates installed, you do have access to some additional functions. Two related functions, new functions, are called sort and sort by, and what we call spill functions. With the single formula, or one of these functions, it will get results across multiple cells. In this worksheet I'd like to have a new list. I don't want to destroy or change the existing list for the moment, but I'd like to have a list over in column I, consisting of just the information that we see here, but sorted by employee name. So in cell I2, I'm going to be typing equals sort, left parentheses, and the data here goes down to row 400. So in dragging across this, I could go highlight it all. I'll simply drag into row four, and then type those additional zeroes out there. And look what happens when I press enter. I've got a list. It's got a unique look to it. The edges have a slight shadowy effect. If I click the cell in the upper left corner and press delete, the data's gone. Now we do want to work with it for a bit, so I'll press control + Z to bring it back. Something else that you wouldn't pick up on at first, the data here is actually linked to the source data to the left. And in this list I see Doug Ayers down here in cell I12. Over in the original list we see that name over in A4. Now Doug says he likes to be preffered, at least officially, to have the name Douglass in, so I'm going to change this to Douglass. I'm typing this in cell A4. Keep your eye on cell I12 as I press enter. And we see what's happened. If I change a compensation amount in column F, that's going to be changing over in column N as well. So there is linkage here that's not exactly obvious. And this is one of those spill functions. A function that does allow us to create new data into additional cells. But what happens if there's not enough room. Let me again delete the table, since the active cell's up in the upper left corner, and get rid of these columns right here at least for the moment, and delete them. The list doesn't have enough room to spill into the right, so If I use this very same entry again you can sort, and then refer to the data off to the left, down to row 400, enter, look what happens, spill. If you click that cell and click the exclamation point to the right, a cell we need to spill data into isn't blank. So if I insert a new column here, still not enough room. Another one, Insert, another one. Eventually we do have enough room, and we get the answer. The companion function is called Sort By. Put that in there, and we have the ability to indicate how we want this to be sorted, and sometimes we can even refer to data that's outside of the range that we want displayed. Suppose for example here I want a list of employee name, status, and department. The data from column A into column C, all the way down to row 400, then a comma. I would actually want to sort this by the data that's in column F. So F2 to F400, and I'll type in those two zeroes there. Comma, but I want this to be in descending order. See the popup here, minus one. Enter, we will now see three columns, and they will be in descending order by a field that we won't even see in the result. We assume based on what we're seeing here, that Roger Copeland has the highest compensation. Now we could quickly verify the results here. I'll click over on column F somewhere. On the Data tap click the ZA button, and there we see it. So the order of the names in column I, matches the list over to the left which has been sorted in descending order by comp. But again, as we take a look at this formula in cell I2, the use of the SORTBY function, we see what's happening. Sort a copy of these cells perhaps is the better way to say it, based on the order that we see here in descending order. That's the minus one. Now when you work with data that's been converted to a table, sometimes these formulas are even easier to work with, with a potential slight downside. Next worksheet over has pretty much the same data, but in table format. I'll zoom in a little bit on this, make it a little bit larger to see it, and in cell I1 equals sort. Now this is a table. It has the name table, I never changed it, that was the original name. Hit the letter T, table names appear in the list of functions. I'll double click Table1, and enter. There it is. The same thing we did with the previous data. If we want to pull data out like we did before with SORTBY we can use that in here too. The approach we take here might strike you as a little different at first, but we do want to sort Table1, comma, and now we want to sort it by what? By one of the fields, we'll use Comp again. Once again I'll press the letter T, there's Table1, but when we use tables and formulas we can put in a bracket behind it, and that brings up the field names, there's Comp. A right bracket there, comma, we want to sort this in descending order, minus one. There we are, so we use the names. Even though these formulas to some people might seem a little bit unwieldy, over time I think you recognize they do provide better documentation. So I've seen a couple different examples of using these new functions, Sort and SORTBY.