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 - Begin.xlsx27.9 KB Sorting - Complete.xlsx
27.8 KB
Quick reference
Sorting
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 want 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 The critical skill for any analyst is to be able to sort data inside Excel.
- 00:08 Now, you might think, well, how hard is that?
- 00:11 It's not but there are a couple of nuances that you should be aware of.
- 00:16 The first one and most critically important is blank rows.
- 00:20 You never want a blank row within your data because this is
- 00:23 how Excel separates different data groups.
- 00:26 So if you want to sort all of this, well, this is going to get in the way here,
- 00:30 unless you select absolutely everything.
- 00:32 So the first thing that I like to do is make sure that we don't have blank rows
- 00:36 inside our data and I'm going to get rid of the ones here both within the data and
- 00:40 between the header and the data itself.
- 00:43 Now it's not critical to actually have a header
- 00:45 although it definitely makes sorting a lot more logical.
- 00:49 Now, what I ultimately want to do is I want to sort first by my maximum
- 00:54 balance in descending order.
- 00:56 So what I'm going to do is I'm going to come up and I'm going to find my Sort and
- 01:00 Filter button right up here, and I'm going to go and sort from Z to A,
- 01:04 and that's going to put my biggest balances at the top.
- 01:07 Now it happens that I've got multiple large balances in this area here and
- 01:11 there's ties and now what I want to do is I want to break those ties by
- 01:15 sorting by the credit card name, but I want to do that in alphabetical order.
- 01:19 So what I'm going to do is I'm going to come over here and sort from A to Z.
- 01:23 But the problem is, you'll notice, it's now resorted my max balance column.
- 01:28 And this is one of the real challenges that you're going to run into right away
- 01:31 when you look at that Sort and Filter and you think, hey I'm just going to go and
- 01:34 hit it with that.
- 01:35 Well, the problem is you can only sort by one column
- 01:38 at a time through this user interface setup.
- 01:41 We need to know how to be able to sort by multiple columns.
- 01:44 So here's what we're going to do.
- 01:46 I'm going to click somewhere inside my header.
- 01:48 It's not totally necessary, actually, I'm going to click inside my data,
- 01:51 because why not?
- 01:52 I'm going to go to the Data tab.
- 01:54 And in the Data tab, you'll notice we have a quick A to Z, Z to A.
- 01:58 We also have a Sort button.
- 02:01 And when we click on the Sort button,
- 02:03 this takes us into a more advanced user interface.
- 02:06 So what I'm going to do here is I'm going to choose how do I want
- 02:08 things sorted.
- 02:09 I'm going to start here my sorting by the maximum balance.
- 02:13 Notice that I can sort by cell values, but
- 02:16 I also have the ability to sort by cell colors, font colors,
- 02:19 conditional formatting, icons, all kinds of different things.
- 02:22 I don't need any of those right now, cell values is just fine.
- 02:25 And I'm going to sort this from largest to smallest.
- 02:28 And then going to add a sorting level and next I’m going to sort by credit card.
- 02:34 So this is going to be for ties based on max balance.
- 02:37 Again, we’ll sort by cell values and this time we are going to go from A to Z.
- 02:42 Notice at the top here there is a checkbox to say my data has headers.
- 02:45 If you don't have headers you can uncheck that, but most data that I find that looks
- 02:49 kind of like this that we're sorting, usually has some kind of a header row.
- 02:52 And honestly, if it doesn't,
- 02:54 you should probably add one because it adds to the descriptiveness of your data.
- 02:57 It makes it clear to understand.
- 03:00 When we go and say okay, what you'll notice at this point, 25 is here,
- 03:04 we got MV, we've got two 20s, we've gone MV,
- 03:07 that's perfect, we've got 18, we got 15 and 15 and again where M and V.
- 03:12 So these are all sorting into the correct orders.
- 03:15 So that's absolutely brilliant.
- 03:17 If I decided, hey maybe I want to go and actually look at this again and
- 03:20 that's not the sort order that I want.
- 03:22 Well, I can go back into the Sort and say, hm you know what, let's go and
- 03:27 add a new level in here.
- 03:29 How that happens, right in between here.
- 03:31 Let's go and sort by the annual interest rate from, let's go smallest to largest.
- 03:37 That'll work.
- 03:38 So now if there are any ties with interest rate, it'll sort the credit card name, but
- 03:43 first it's going to go max balance then by the interest rate.
- 03:47 And if we do that we can see that these two items flip.
- 03:50 So Visa comes before MasterCard because the interest rate on our
- 03:54 $20,000 is 16.9 in this case and 19.9 in this place.
- 03:59 So that actually works fairly well.
- 04:00 That's how you set up a multi-level sort inside Excel
Lesson notes are only available for subscribers.