How to Sort Data in Excel Spreadsheets (The Right Way)

So, you get handed an Excel spreadsheet with thousands of rows inside of it, and you realize the data is all out of order. You might need to sort it based on the names inside of a column, or by sorting data from large to small.

On the surface, sorting data is a simple task in Excel, and the app certainly makes simple sorting easy. However, there's much more power in how you can sort and reorder the data in your worksheets. Here are three Excel data sorting techniques you'll learn in this tutorial:

1. Sort data simply with just a couple of clicks.


2. Set multiple, cascading sorting rules, such as sorting alphabetically by state, and then by county.


3. Create totally custom sorts to order your data by any rule you set.

Sorting data can also feel a little dangerous at times; what if you sort only a single column and the data becomes misaligned? I'll show you how to avoid that. Let's get started.

How to Sort Data in Your Excel Spreadsheet (Quick Video)

This screencast covers several techniques for sorting your data. Watch this three minute video to learn these professional Excel workflows quickly. We'll start with a simple sort, and progress to more advanced techniques to ensure that you can always sort data the way you need.

Read on for a step-by-step guide on how to sort data in Excel spreadsheets, using simple to advanced techniques.

Sample Data (Free Excel Workbook Download)

As part of this tutorial, I've created a workbook that you can work with while learning data sorting. Download the workbook for free, and use it while learning sorting in Excel. 

1. A Simple Sort in Excel

Sorting can be a very simple, two-click process to reorganize the data in your spreadsheet. Let's learn how.

In an Excel workbook, start off by clicking in a cell of the column you want to sort. Now, make sure that you're on the Home tab of Excel's ribbon, and find the Sort and Filter button on the far right side of it.

Notice that in the sort options, you can sort text "A to Z", or "Z to A." These simple options will alphabetically sort the Excel data either direction, depending on the option you choose.

When you are sorting data in Excel, the entire row is being sorted. Essentially, the column you select will be the "key" that Excel uses that to decide how to sort the data, but each row is a record that should stay grouped together.

Depending on the data you've selected, you can sort alphabetically or numerically. If your column has numerical values, you can sort from smallest to largest amounts, while text data will sort based on alphabetical order.

It really is this easy to perform a basic sort. Simply click inside of a column of data, choose a sort option, and Excel will reorder the data inside a spreadsheet.

Quick Tip: Also try out sorting by right clicking inside a column and choosing Sort and selecting how you want to sort the original data.

2. How Not to Sort your Excel Data

It's also important to learn about one of the most dangerous ways of sorting data in Excel, a method that can ruin your original data.

The problem occurs when you have a large spreadsheet of data, but you accidentally sort only one column of data. Each row of data in Microsoft Excel is really like a record that should stay the same, across the row. 

In the screenshot below, I've color coded the rows so that you can see the danger of highlighting and sorting only the data in one column.

The big mistake Excel users make is selecting only a single column when sorting, and choosing the incorrect option on the next window.

Excel even tries to warn us, with a window labeled Sort Warning. The pop-up window has choices for Expand the selection (choose this one!) or Continue with the current selection.

I've always thought that the choices this window gives you aren't totally clear. Just know that you'll want to use Expand the selection to make sure that Excel includes all columns when sorting the data.

For testing's sake, let's see what happens when we've selected a single column, and chosen the Continue with the current selection sort type.

In the screenshot below, you can see how problematic this sort type is. Although the Amount Billed column was sorted smallest to largest, all of the other columns stayed in place. This means that our data is no longer correct.

In summary, there are two key "dont's" when it comes to sorting data:

1. Don't start off by highlighting a single column in your spreadsheet.


2. Don't use the Continue with current selection option if you are working with a single column—make sure to expand your data selection.

3. Advanced Excel Data Sorting

So far, a simple sort has allowed us to sort for a single type of data. What if we want to consider two types of data in our sorting?

What if we wanted to...

• Sort alphabetically by the state, and then by the county in a spreadsheet.


• Sort alphabetically by the name of a client, and then by each type of project we did for them.


• Sort our clients in a list alphabetically, and then by amount for each individual project, from largest to smallest.

The answer to all of these is an advanced sort, where you can set multiple levels of sorting data. Let's walk through that last example using the sample data.

To get started, click somewhere inside of your data and find the Sort & Filter option, and then choose Custom Sort.

On this window, we can add multiple levels of sorting. Start off by clicking on the dropdown option next to Sort by and choose a column that you want to sort by.

In my case, I'll choose Client from the dropdown menu, and leave the the Sort On set to Values, and Order set to A to Z. In plain English, this will sort the Excel spreadsheet based on alphabetical order.

Now, let's click on Add Level. This will create a new row in the sort options and allows us to add a second level of organization.

I can now choose Amount billed from the second dropdown. The combination of these two rules will start by sorting based on the client name, and then by the amount billed on each project.

You could continue to add as many levels as you want to this advanced sorting window. The sequence of the rows matters; you can move a row up to sort first by the amount billed, for example, and then by client.

Once we press OK, Excel will sort the spreadsheet based upon the rules we've built in this window.

Tip: For even more advanced sorting, try out changing the Sort On dropdown menu to change your sort to advanced features, like sorting based on the cell's color.

Advanced sorting allows you to build two levels of data organization into your spreadsheet. If sorting on a single factor isn't enough, use an advanced sort to add more power.

Recap and Keep Learning

Sorting is yet another skill that spreadsheet wranglers will keep at the ready in their Excel workbook. When you need to resequence the data in a spreadsheet, it's too time consuming to cut and paste lines in a specific order, so sorting is a must.

As always, you can build on the skills you learned today by diving into more great Excel tutorials. Check out the links below:

• Sorting data is a powerful skill, but sometimes you'll want to summarize and understand datasets instead, using PivotTables.


• Here's a roundup of more sorting tutorials in case you'd like to learn this skill from another source.


• Learn how to work with Excel formulas in How to Manage Your Excel Formulas.

How do you sort your data? Do you use simple methods or the advanced sorting tool to ensure perfect sorts each time?