Locked lesson.
About this lesson
Learn to sort data in Excel by a single column or by multiple columns.
Lesson versions
Multiple versions of this lesson are available, choose the appropriate version for you:
Exercise files
Download the ‘before’ and ‘after’ Excel workbooks from the video tutorial and try the lesson yourself.
Sorting.xlsx16.1 KB Sorting - Completed.xlsx
16.9 KB Sorting - Extra Practice.xlsx
17.9 KB
Quick reference
Topic
Sorting data in Excel by a single column, or by multiple columns.
When to use
Knowledge of how to use Excel’s sorting feature can be essential for presenting your data in an easy to read format.
Instructions
Preparing your data for sorting
- Make certain that your data is in tabular format
- It is essential that there be no blank rows or columns interrupting the data that you wanted sorted
- While not essential, it is certainly preferable to have a clear header row for your data
Single level sorts
- Select any cell inside your data table (or the entire range you want to sort)
- Go to Data > Sort
- Configure the way you’d like it sorted, then click OK
Multiple level sorts
- Multi-level sorts allow you to sort by one column, then use another column to sort ties
- Select any cell inside your data table (or the entire range you want to sort)
- Go to Data > Sort
- Configure the primary sort the way you’d like to see it
- Click Add Level
- Configure the secondary sort the way you’d like to sort the ties
- You can continue to add as many sorting levels as you need
- 00:04 Right now we're going to look at Sorting in Excel and the key things you really need to know in order to make Excel sort properly.
- 00:11 So we have a big list of data here. Which right now is running from row
- 00:14 five and if I press End and my down arrow key it takes me to the end
- 00:18 of the list. So we're in row 104. Go End and back up again you can see that
- 00:22 there's no gaps in this list anywhere, by the virtue of it actually running all the way through.
- 00:27 Except for directly below the header row from my list, from my table. Now this is an
- 00:32 actual big no-no. We never want to have this blank row in here and I'll show you why.
- 00:37 Excel always works with what's called a contiguous range, so a consistent one
- 00:41 with no blank rows or columns in there. That actually breaks things up and treats it as
- 00:45 two different data areas. When we go to Sort on the Data tab, I can click anywhere in my list of data here at all. And I click Sort
- 00:53 and it comes back and it gives me the Sort box and if I check this
- 00:57 you'll notice that it's pulling from the top row of data. It's assuming that I
- 01:01 have headers here and it's pulling from the top row of data. Well these don't really mean a lot to me.
- 01:06 Now watch what happens when I go and delete this blank row.
- 01:12 And I now go back to my Sort
- 01:15 and check the Sort by column.
- 01:17 Now I get my headers. Ok. This is the number one reason why we never want to have a blank row between your headers and your actual data.
- 01:24 So this is cool, let's see what we can do with it. We'll go with DRG Definition. We can sort on values, we can sort on colours or
- 01:31 icons. Now this data doesn't have any of those so we're not going to bother doing that. We'll stick with values.
- 01:35 And we'll sort it from A to Z and let's see what happens.
- 01:40 Great, we've got a nice big list, it's sorted A to Z. We could go and look through the whole thing
- 01:45 if we wanted to but can definitely see from here that it looks like it's working correctly.
- 01:49 But what if we wanted multiple levels of sorts? We can see here that the first three items are actually in a tie and I'm not quite sure
- 01:57 how it's actually sorting here. So let's go back to Sort and see what we can do about that. How can we set this up, ah.
- 02:04 We get this message because I selected three cells. So we'll expand the selection and Excel's going to grab the entire contiguous range for me.
- 02:11 And it inherits the old Sort that I had. So let's go into Add Level.
- 02:16 Now let's say if we have these ties let's sort by the Average Total Payments.
- 02:21 And we'll set that up on values again. We're going to go largest to smallest because we always want to
- 02:25 know where the bigger amounts of money are coming from. And then
- 02:29 I can see that there is a couple of these that are tied as well so why don't we go in and add another level.
- 02:34 And we'll sort this one say by the total amount of discharges and we'll sort those ones smallest to largest. So we can sort by different
- 02:41 columns they don't have to be in order and we can sort each one in a different way as well. Some up,
- 02:46 some down, that'll work for us. Let's see what happens. We click ok.
- 02:51 You can see that we're still sorted in alphabetical order in the first column. That's all fine. But where we have some ties,
- 02:59 I'm going to pull up that Sort order just so you can see and expand the selection again.
- 03:03 Where we have some ties we said we wanted to sort by the Average Total Payments from
- 03:08 largest to smallest and we can see here that D4 has 12,600 in it,
- 03:12 D5 and D6 they are tied with 8118.28.
- 03:18 So then we reach over and say what is a tiebreaker now? We're going to go and
- 03:22 look by total discharges and sort those smallest to largest. And sure
- 03:26 enough 21,000 is smaller than 40,000 so it's actually done that. Which is great.
- 03:31 So this is how we use sorting inside Excel for both single and
- 03:36 multiple level sorts. The big key there make sure you have a header row and make sure you have
- 03:41 a contiguous dataset with no blank rows or columns in there and Excel will sort really nicely for you.
Lesson notes are only available for subscribers.