From the course: Excel: Managing and Analyzing Data

Use dynamic arrays: SORT, SORTBY - Microsoft Excel Tutorial

From the course: Excel: Managing and Analyzing Data

Start my 1-month free trial Buy for my team

Use dynamic arrays: SORT, SORTBY

“

- [Instructor] So let's look at the new functions sort and sort by. There are a lot of reasons that you might want to use these and let's dig into this situation. We have this data going from the county all the way through city hall address, Mayor, and through to the population. And we would like to look at the data two different ways, but we want it to remain dynamic. Meaning if we make a change to the source data that anything else tied to it will automatically update. So here are the two ways that we want to look at the data. We want to look at it by population descending and by the founded date ascending. So I'm going to go down here and I want to sort the entire data set equal sort, double click what array we want to sort this table. Okay. Comma, the sort index, that means which column of data do we want to sort by. So we want to sort by population. So that's one, two, three, four, five, six comma sort order. I want to sort descending, so that's negative one and enter. So now the data is down here and the blue line shows that it is a dynamic array. Now those zeros, they can be a bit annoying, but they represent the blank cells. Now let's look at the second way. I'm going to go here equals sort open parentheses. And I only want these columns, comma sort index. I want to sort by the founded date. That's going to be the third column and we want to sort ascending, so we can just click enter here. There we go. The date is sorted by the founded date. Now one thing that's kind of tricky is that we can't include the column headers. One simple solution would be to copy and paste them into place. There are other more tricky ways, but really I want to show you sort and sort by. Here we go. So now we find out that Farkas was founded in 1903. So put 1903 here and watch the update over in our dynamic array, enter. Now Farkas is in the right place. Let's scroll down. This data set is sorted by the population. And what if we find out that Xanthic Lake has actually a million people enter updated. Alright, but it doesn't actually have a million people. I'm going to undo that. So that is how you can use sort. One thing that's important with sort is the data that you want to sort by, it has to be in the data that you capture. That's what's different about sort by. Let's look at that with sort by. I'm going to go here and let's say that we only want the city, the founded by and the city hall address sorted by the population equals sort by double click it. The array that we want, we want to capture these three columns comma by what array. By the population comma and the sort order we want it descending negative one. Now I'm going to hit comma so that you can see now we get by array two sort order two by array three. So we can sort in levels with sort by. I'm going to get rid of this comma and close the parentheses enter. So let's verify this result. So it's saying that Janela is the most populous city. Let's look. Yes, Janela is the most populous at 814,000 and there's Port Cloyer at the bottom of the result and we verify it has 1,388 people. Now one thing about dynamic arrays is this is a live formula and a live array. It's available for what you need. If say somebody wanted you to include this in a report that you are writing, you can highlight the range, copy, and then paste it as values. And now you have the raw data sorted the way that it needs to be. And you didn't have to sort your source data. It's still as it is. And that's how you use sort and sort by.

Contents