Locked lesson.
About this lesson
Learn to sort data in Excel by a single column or by multiple columns.
Exercise files
Download this lesson’s related exercise files.
Sorting.xlsx18.7 KB Sorting - Completed.xlsx
18.8 KB
Quick reference
Topic
Sorting.
Description
Sorting data in Excel by a single column, or by multiple columns.
Where/when to use the technique
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:00 In this video, we're gonna look at how to sort data in Excel.
- 00:08 Now, the big keys that you really wanna understand before you try and
- 00:11 sort data is it's really important to make sure that your data is already in
- 00:16 a tabular format.
- 00:17 So, something that looks a bit like a table like you see here.
- 00:20 And we need to make sure that there are no blank rows,
- 00:24 or columns interrupting the data that you want sorted.
- 00:27 So, a lot of users are tempted to put a blank
- 00:30 row between the header row of their table, and their actual data.
- 00:33 That's like absolute killer to most of Excel's advanced features.
- 00:37 So, you don't want to do that.
- 00:39 Now, what we're gonna do here,
- 00:41 is you can see we have what we call contiguous list of data.
- 00:43 So, there's no blank rows, no blank columns, and that's great.
- 00:46 While it's not essential, it is helpful to have a nice header row that's a little bit
- 00:50 different, and I'll show you why, in a second here.
- 00:53 Sorting is relatively straight forward.
- 00:55 Basically, we go to the Data tab, and you select the column that you want to sort
- 01:00 by, and you click the A to Z, or the Z to A icon.
- 01:04 So, if we wanted to sort by descending by Max Balance,
- 01:07 we could select any cell in that column, any cell at all.
- 01:11 We could sort by interest max, interest period.
- 01:14 This is how we do what we call single level sorts.
- 01:17 So basically, it sorts by one column, and
- 01:19 ignores all the rest of them and, and just sorts based on the column if selected.
- 01:23 But what if we wanna get a little bit more close ,and personal with our data, and
- 01:27 we actually want to sort on multiple levels?
- 01:31 To do that, we select any cell of data in the table, doesn't matter where.
- 01:36 And we're going to click the A-Z sort button, big one here.
- 01:40 And this allows us to actually go, and
- 01:42 get a little bit more technical with the way that we want our data sorted.
- 01:47 So, in this case right now, it's sorting by the Max Interest Per Period.
- 01:51 Well, we really don't want that.
- 01:52 Let's go, and sort by our Maximum balance.
- 01:57 How about that? We'll start there, and
- 01:59 we'll sort it from smallest to largest.
- 02:02 And if we want to add another level here, we could say, let's go,
- 02:06 and add this, and then, we'll sort it by the credit card.
- 02:10 And these are the headers that are showing up in my table here.
- 02:12 So, I'll sort my credit card, in, let's go from Z to A.
- 02:17 So, we'll go with the highest word,
- 02:19 the last alphabetical instance of this in order with our credit card, and say, okay.
- 02:25 So, you'll notice we sorted in ascending here but where we have ties,
- 02:30 it sorted the Visa above MasterCard.
- 02:34 And if I wanted to play around with this now, I could go back into my sort, and
- 02:37 I could change things out as well.
- 02:39 And say you know,
- 02:40 well actually this is actually drilled in a little bit deeper than I want here.
- 02:43 I'm gonna come back out, and that's because I selected a specific area.
- 02:46 I wanna select one cell.
- 02:48 So that it grabs the entire data range here.
- 02:51 And now I could change this, of course,
- 02:53 by going to something different if I wanted, as well.
- 02:55 You'll notice that we can sort based on values.
- 02:57 We can sort based on cell colors, font colors, cell icons as well.
- 03:01 So, there's a lot of robustness available in here, if you,
- 03:04 if you've got a huge table this can be very, very valuable.
- 03:07 The other thing that you wanna be aware of is,
- 03:09 if you wanna get rid of the sorting levels, all you need to do is just,
- 03:12 click the minus button here, and that will remove these levels again, and just say,
- 03:16 hey I'm not gonna bother setting up the sorts at this particular time.
- 03:19 You don't have to do that to clean it up there's no requirement there whatsoever,
- 03:23 but if you'd like to reset the way you're sorting because you want to do something
- 03:27 different you may want to go back in there and actually remove those levels there.
- 03:31 So, just remember when you're trying to add levels, if we go into sort we click
- 03:34 the plus button to add a new level, and we can click the minus button to make anyone
- 03:38 go away, so that is sorting in a nutshell inside excel
Lesson notes are only available for subscribers.