When you have a lot of different rows in your spreadsheet, you may want to sort the data
so you can view it more easily.
Sorting can re-order things alphabetically, numerically, by date, or even by color.
Depending on your preference, you can either sort from the Home tab, using the Sort & Filter
drop-down menu.
Or you can use the commands in the Data tab.
In this example, I have a worksheet with a list of t-shirt orders, and I'd like to sort
it alphabetically by last name.
Just select any cell in the column that you want to sort… and then click the ascending
command—it's the one that says AZ.
You'll notice that when you sort one column, Excel also re-orders the other columns, so
that each row still has the correct information.
If we wanted these in reverse order, we could click the descending command (the one that
says ZA).
Now, this method doesn't always work if your worksheet has an unusual layout.
Watch what happens when I try to sort the data here on the right.
The header row is included in the sorting area by mistake.
In a situation like this, you might need to manually select the cells you want, instead
of relying on Excel.
This is called sorting a cell range.
To get started, select the range you want to sort (including the header row if you have
one).
Then click the Sort command… and a dialog box will appear.
Use the drop-down menu on the left to choose the column you want to sort by… then select
a sorting order.
In this example, we'll choose Largest to Smallest.
When you're done, click OK… and the range will be sorted by the selected column.
Notice that the rest of the worksheet wasn't affected by the sort.
Sometimes the default sorting options won't work for what you have in mind.
For example, if we sort the column that contains the t-shirt sizes in ascending order… the
order will be Large, Medium, Small, then X-Large because the column is being sorted alphabetically.
It would be better if the sizes were listed in a more logical order—in other words,
Small, Medium, Large, then X-Large.
For this, we'll need to create a custom sort.
To begin, click the Sort command.
And on the right, you can see that it's ordering the column from A to Z.
If we click this drop-down menu, and select Custom List… we can enter the t-shirt sizes
in the order that we want.
So I'll click the box and start typing Small…
Medium…
Large… and X-Large.
And I'm pressing Enter after each one.
Then click the Add button.
Make sure that your list is selected over here on the left.
Then click OK to confirm… and OK again… and now the worksheet is ordered the way that
we want.
The only problem with this is every other column is completely out of order.
What I would really like is if the homeroom numbers were more organized within each size.
I can do this by sorting multiple levels.
This will let us sort first by t-shirt size, and then by homeroom.
Click the Sort command… then click the button that says Add Level.
Now we just have to select the Homeroom column… and we'll leave the other two fields the way
they are, sorting the values from smallest to largest.
When you click OK… you'll see that within each t-shirt size, the homeroom numbers are
listed in numerical order.
If we wanted to sort by homeroom first and then by t-shirt size, we could go back to
the Sort dialog box… select one of these rows… and then use these arrows to change
the order.
To get the results the you want, sometimes you need to create multiple levels, or incorporate
different types of custom sorting.
Let's take a look at one more example.
You may have noticed that some of the cells in this column are colored red, indicating
that there's a problem with the student's payment.
We can make sure these appear at the top if we sort the worksheet by cell formatting.
To do this, click the Sort command again.
I want to continue using the sorting options that we've just set up, so I'm going to add
a new level for the Payment Method column.
I'm also going to move it to the top of the list so that sort happens first.
To sort by cell formatting, go to the drop-down menu under Sort On.
Here, in addition to Values, you can choose Cell Color, Font Color, or Cell Icon.
Next, make your selections in the Order field depending on what you have in mind.
In this example, we want all the red cells… to appear on top.
When you're ready, click OK.
Now we can easily see which orders still have outstanding payments.
There are a lot of different ways of sorting your data, depending on how you want to organize
it.
So take some time to experiment with the different options to find the ones that best suit your
needs.