How To Sort Data In Excel:
Organize Your Data In A Few Clicks

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

Excel packs a lot of organizational power.

Useful tools like charts, filters, and pivot tables let you slice and dice your data any way you want.

But sometimes you need something a little simpler: you just want to sort.

And if it’s sorting you’re looking for, you’ve come to the right place 🙂

Excel can sort by all sorts of things.

From a simple numerical ordering to sorting by the background color of cells!

No matter what kind of sorting you need, Excel can do it.

Let’s take a look at some of the options…

Get your FREE exercise file

The best way to learn all the different methods of sorting is to practice them yourself!

Download the free exercise file to get a workbook full of data that you can sort as you read along with the tutorial.

Download the FREE Exercise File

Download exercise file
Download free exercise file

Alphabetizing Excel data

On the first sheet of the example workbook, you’ll see a list of companies, together with several managers at each company. Let’s alphabetize the list by company to make it a bit more organized.

Click the Sort button to open the sorting menu, then select Company in the Column dropdown:

sort-by-column

Make sure that Values and A to Z are selected, as well.

Then hit OK. The entire sheet will now be sorted by company name, in alphabetical order!

alphabetized

That’s pretty simple. Let’s try something a bit more useful.

Kasper Langmann, Co-founder of Spreadsheeto

Each company has several managers—why don’t we alphabetize those within each company, too? This is where multi-level sorting comes in.

Open the Sort menu again, and set it to sort by company, from A to Z. Then, click Add Level.

add-sorting-level

Now there’s another set of dropdowns that lets you choose additional sorting options. To add the secondary sorting by manager, we’ll select Manager, Values, and A to Z.

sorting-manager

Hit OK, and the original sorting will still be in place, but now the managers will also be alphabetized within their own company sections.

two-level-sorted

If you want to alphabetize any of these lists in reverse, just select Z to A in the Order column.

Kasper Langmann, Co-founder of Spreadsheeto

Sorting numbers

Of course, there’s a good chance that you’re using your spreadsheet for numerical data. The sorting process is exactly the same for numbers.

On the second sheet of the example workbook, we have a long list of stores and their associated revenue. Let’s sort by revenue to see which stores are doing best.

Note that the Revenue column isn’t the first column in the spreadsheet. That’s fine—you can sort by any column at any time, and Excel won’t have a problem with it.

Just follow the same steps as above: open Sort, select Revenue in the Column dropdown, make sure Values and Smallest to Largest are selected, and hit OK.

sort-by-numbers

When you select a column with numerical data in place of text, the Order dropdown will say Smallest to Largest instead of A to Z. The sorting works the same way.

Kasper Langmann, Co-founder of Spreadsheeto

That’s all there is to it!

You can do the exact same thing with dates or times. Excel will give you the options of Oldest to Newest and Newest to Oldest if you sort a column with temporal data.

Sorting by color

If you’ve read up on conditional formatting, you know that colors in Excel can be very useful. That’s also true when it comes to sorting.

When you click on the Sort On dropdown in the Sort menu, you’ll see that Values is only one of several options. The next two are Cell Color and Font Color, which you can easily apply with conditional formatting.

On the third sheet of the example workbook, you’ll find data that’s already been color-coded. But the colors are all over the place, so it’s not as useful as it could be. Let’s sort them to make it easier to read.

Open the Sort menu again, and set the Column dropdown to App name. Now, instead of Values in the next dropdown, select Cell Color:

sort-cell-color

Now you’ll see two options in the Order column. We’ll use these to tell Excel how we’d like the colors ordered.

First, select the green shade in the Order dropdown. Set the next dropdown to On Top:

sort-green-top

Now, Excel will place all the green cells at the top of the sorted column, but it won’t touch any of the others.

We need to add more information to tell it how to sort the rest of the data.

Click Add Level, then repeat the above steps, but select the yellow shade. Again, set it to On Top:

sort-yellow-on-top

Now, Excel will sort the green cells to the top, the yellow cells after that, and everything else last.

Tell Excel to put the red cells next:

sort-green-yellow-red

Finally, hit OK and see what happens.

sorted-color

As you can see, Excel has ordered all of the cells by color, with green on top, yellow next, and red on the bottom. Because we set red to On Top, uncolored cells are still below red.

If we would have set red to On Bottom, the uncolored cells would have been above the red ones.

You can do the same style of sorting with text color by selecting Font Color in the Sort On dropdown.

If you’re familiar with using cell icons in Excel, you can also sort by icon. Just select Cell Icon in the Sort On menu.

Kasper Langmann, Co-founder of Spreadsheeto

Sorting (faster) with filters

The Sort menu is very powerful—but it takes a few clicks to get it all set up. There’s a faster way.

Filters can be used to look at subsets of your data, but they can also be very useful in sorting.

Let’s go back to the first sheet in the workbook.

Click into any cell, and hit the Filter button in the Data tab.

filter-button

You’ll notice that the column headers now have arrows in them. Click one of those arrows, and you’ll see a menu.

In that menu are two buttons that activate A-to-Z and Z-to-A sorting:

filter-menu

Click the arrow next to “Manager” in cell B1 and click the Z-to-A sorting button.

z-to-a-sorting

Just like that, we’ve sorted the column.

You can also use the filter menu for sorting by color, though you’ll have to select each color individually and you’ll have fewer organizational options.

Keep in mind that if you want to run a complicated, multi-level filter, it’s a better idea to use the Sort menu.

Sorting (really fast) with two clicks

Filters still too slow for you? There’s an even faster option, though it provides even fewer options.

Just right-click on a column, mouse over Sort, and select a sorting method:

right-click-sorting

That’s all there is to it.

Like with filters, you can also get some limited color-based sorting from the right-click menu, too.

And if you click Custom Sort…, Excel will bring up the Sort menu so you can do all the powerful sorting you want.

Sorting rows

While you’ll usually want to sort columns, there might be some cases in which you want to sort a row.

On the fourth sheet in our example workbook, you’ll see data that’s organized in rows, instead of columns.

Let’s sort that data.

Remember that you can always transpose rows and columns by copying and pasting, too.

Kasper Langmann, Co-founder of Spreadsheeto

Select the values you’d like to sort to get started. (In our case, that’s B1:Z3.)

Click the Sort button, and click Options.

Activate the radio button next to Sort left to right:

sort-left-right

Click OK, and you’ll be back in the main sorting menu. Select the row you’d like to sort by. In our case, that’s Score. Then set the remaining settings to Values and Smallest to Largest.

Hit the OK button, and you’ll see that our data has been sorted by score.

row-sort

Wrapping things up…

Sorting is a relatively simple exercise, but Excel has some very powerful features that let you sort your data exactly how you want it.

From a quick right-click sort to the full power of the Sort menu and everything in between, you have access to a lot of sorting functionality…

… and as you explore sorting options, you’ll likely find even more!

2019-07-10T12:31:35+00:00