Sorting

Quick reference

• 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.