Sort Lists in Excel

Sorting Excel Lists

One of the main reasons people elect to store lists in Excel is to be able to quickly sort them – to put them in a useful order, by one or more of the fields in the list, which makes it easier to find records that are of particular interest.

Using our list of US cities, let’s look at our sorting options. Excel allows us to sort by one field, quite easily – just click in any cell in the column you want to sort by, and on the Home tab, click the Sort button. You can choose to sort in ascending or descending order, which will appear as Sort A-to-Z (or Sort Z to A) if the column contains text, or Sort Smallest to Largest (or Largest to Smallest) if the column contains numbers.

Using the US Cities worksheet, let’s do some quick sorts. If I click in the State Abbreviation column – in any cell in the column, I can click the Sort & Filter button and then click the Sort A-to-Z button in the resulting menu. Note that now the records are in alphabetical order by state.

Now, I can sort by Population, using the Sort & Filter button again, and I’ll choose Largest to Smallest, putting the most populous cities first.

There’s also a set of Sort buttons on the Data tab. Here, fixed buttons – A-Z and Z-A Sort – appear, and you’ll use them to sort alphabetical or numerical fields in ascending or descending order. There’s also a Sort button that allows you to sort by more than one field.

To demonstrate that, we’ll sort by State, and then within each state, by Population. For this type of sort, it doesn’t matter which cell is active when you click the Sort button, though your active cell must be within the data. You’ll be picking which fields to sort by using a dialog box.

When I click the Sort button, the Sort dialog box opens – and from the first Sort By row, I’ll choose State, and then set the Order to A-to-Z. Note that all of the field names – the headings in Row 3 in the worksheet – appear in the drop list for me to choose from.

Next, I click the Add Level button, and a Then By row is added – and here I’ll choose Population. I also want to set the Order for that sort, and I’ll pick Smallest to Largest, so the least populous cities appear first this time.

I could continue to add levels – but that would only work if the Population sort created groups of records with the same exact population. Building multiple-field sorts requires that your sorting creates groups, so the next field you sort by has something to do.

So given that Population won’t create many – if any – groups, I’m just going to click OK, and now my list is sorted by state, and as promised, each group of cities by state is in order by their Population total.