🥳 GOSKILLS TURNS 10: Get 10 days of free access with code 10YEARS

GoSkills
Help Sign up Share
Back to course

Sorting

Compact player layout Large player layout

Locked lesson.

Upgrade

  • Lesson resourcesResources
  • Quick referenceReference
  • Transcript
  • Notes

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.xlsx
27.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
Login to download
  • 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.

Introduction to XLOOKUP
07m:04s
Filtering
04m:20s
Share this lesson and earn rewards

Facebook Twitter LinkedIn WhatsApp Email

Gift this course
Give feedback

How is your GoSkills experience?

I need help

Your feedback has been sent

Thank you

Back to the top

© 2023 GoSkills Ltd. Skills for career advancement