Sorting

Quick reference

• 00:04 In this video, we're going to look at sorting data in Excel.
• 00:08 You can see that I have a table of data here that is perfectly ready to be sorted.
• 00:13 Now, when I say ready, why is that?
• 00:15 Well, it's because this data has some key characteristics that are very important.
• 00:19 Number one, it's got a really nice descriptive header row.
• 00:23 To be fair, this is optional.
• 00:25 But as you're gonna see as we work through the example, having a good header row,
• 00:28 actually makes it even easier to sort than if you don't.
• 00:32 The next thing that is critically important,
• 00:34 there's no blank row between the headers and the actual data itself.
• 00:38 And this is really, really important to recognize, because when you try and
• 00:42 run a sorting algorithm on the data, what's gonna happen is Excel will
• 00:46 gonna pick up the range up to where the first blank row or column is.
• 00:49 And after that, it won't pick up any more.
• 00:52 Now, to be fair, you can override that.
• 00:54 But if your data's in good shape, you shouldn't have to.
• 00:58 Underneath this, we have nice consistent types of data down the columns.
• 01:01 Again, useful, not critical but nice to have.
• 01:05 So let's go and take a look at this.
• 01:06 We're gonna grab the credit card names and
• 01:08 we're gonna sort them in alphabetical order.
• 01:11 I'm gonna go to the data tab, and I'm gonna choose to sort A to Zed.
• 01:15 And that will actually put all the MasterCards up front and
• 01:18 then all the Visas.
• 01:19 Naturally, if I wanted to see this the other way, I could sort from Zed to A.
• 01:25 I can sort by values, if I wanna have the highest values at the top for
• 01:28 maximum interest.
• 01:30 And I can also go an sort by things like the maximum balance.
• 01:34 Now, this is where things get challenging though.
• 01:36 What if I wanna sort by the maximum balance to show the card that actually has
• 01:40 the highest available balance, but I want to show the lowest interest rate on that?
• 01:45 Well, if I come over now to the lower interest rate, and
• 01:48 I sort this A to Zed, you'll notice that it resorts the balances down below here.
• 01:54 The first two are fine, but these guys are not.
• 01:57 So that's challenging.
• 01:58 I need to set up a multi-level sort, and
• 02:01 to do that, we go to the sorting button here.
• 02:05 Now, you'll notice that this is where the headers come into play,
• 02:07 because it's telling me right now that it's sorting by the annual interest rate.
• 02:11 And I'm gonna say, well, that's not really what I wanna sort by.
• 02:14 I'd like to sort by the maximum balance based on the cell values.
• 02:18 And you'll notice that we have other options here as far as cell colors,
• 02:21 font colors, even conditional formatting icons if we use those.
• 02:25 But we'll go with cell values for right now.
• 02:27 And I wanna sort from largest to smallest.
• 02:31 Now, why did these show up?
• 02:33 Well, it's because it knows that my data has headers, and it's been able to pick
• 02:37 that up because the headers here are text, everything else underneath is values.
• 02:41 That's where your consistent data types really come in useful.
• 02:45 Now, that's great, but that only allows me to sort on the maximum balance.
• 02:48 What I want to do is now sort the ties for the annual interest rate.
• 02:53 So, what I'm gonna do, is I'm gonna choose to add level.
• 02:56 And this will allow me to set up a successive sort.
• 02:59 I can now say, let's go with the annual interest rate.
• 03:03 Again, we'll sort on the cell values.
• 03:05 And this time, we'll go from smallest to largest.
• 03:07 And then, just in case there's any ties within that area as well,
• 03:11 maybe I'll sort by the credit card name, from A to Zed.
• 03:15 And at this point, when I say okay, it sorts everything quite nicely so
• 03:20 that I have my maximum balance cards at the top in the descending order,
• 03:23 and the interest rates are in ascending order.
• 03:26 And if there were any ties in this particular area here,
• 03:29 it would then sort the credit cards in alphabetical order.
• 03:33 So that's actually pretty useful.
• 03:35 Now, what if I want to modify this sort for any reason or clear it?
• 03:38 Well, I would go back to the sort.
• 03:40 You'll notice that as I'm doing this, I've only selected one cell, but
• 03:44 it's picked up the entire range of data.
• 03:46 And again, that's why those blank rows and blank columns are so
• 03:49 important to not have in there.
• 03:52 Notice at this point, I have the option to go and select a rule here.
• 03:56 I could move it up if I wanted to change the order or move it down.
• 04:00 I could delete the entire level if I decide this credit card values or
• 04:03 credit cards with the names aren't necessary at all, I can click delete.
• 04:07 And I could keep deleting all the way through if I wanted to do that.
• 04:11 At this point the order is not going to change, but
• 04:14 I'm actually removing the sorting layers from this.

Lesson notes are only available for subscribers.