How to alphabetize in Excel: A complete guide to sorting
One of Excel's most powerful features is the ability to quickly and easily sort data. This includes both alphabetizing lists of strings (i.e. putting them in alphabetical order), and ordering numerical values — both from largest to smallest and smallest to largest.
In this article, we'll take a look at how to do so, covering everything from standard alphabetization (sorting from A to Z) to reverse alphabetization (sorting from Z to A) to sorting numbers.
Defining the problem
Take a look at the spreadsheet below, which shows orders from SnackWorld's customers between the months of January and March:
As you can see, the list is pretty messy right now. If you wanted to look up a particular customer's order history, it'd be a real pain to comb through the whole list and note each individual instance of that customer's name.
But there's a fix: if we alphabetize the list, it'll be a lot easier for us to read — and a lot more user-friendly for other folks who want to use the same sheet. Alphabetizing will have numerous advantages:
- It will make it easier for a human to look up a particular value or customer name;
- It will help us manually scan for duplicates in case we've made a data entry error;
- It will help others who use the spreadsheet understand it faster and with less effort; and
- It will allow us to easily group the orders for any particular customer together so that we can see them side-by-side.
Quick alphabetization and sorting
To start off, we'll use an Excel feature called
To get started, make sure you've highlighted a cell in the column by which you'd like to sort. In this case, we want to sort by
Finally, press the
Notice that there's a very similar icon with a 'Z' first and then an 'A'; use this icon to
Let's say we actually want to sort by something else. For example, what if, rather than alphabetizing our list by customer, we wanted to sort it by the
In this case, we've sorted by our sales values in descending order. The sort buttons work on numerical values as well as text!
Alphabetization by multiple columns
Now, it's time to get fancy. We know how to sort and alphabetize by a single column. But what if we want to sort by multiple columns — for example, alphabetize our order list by customer name first, but within each "customer name" block sort from the highest-value order to the lowest-value order?
We can do this by using Excel's full sort functionality. First, we'll start by selecting the whole range of data we want to sort. You can do this by clicking and dragging with your mouse to select all the cells you want to sort, but there's a shortcut: make sure a cell within your data table is selected, then press Ctrl + A on a PC or ⌘ + A on a Mac.
When you make this selection, it's very important to check to ensure that your entire table is highlighted before proceeding. If there are blank rows, they may cause the Ctrl + A shortcut to only highlight part of tht table; this will mess up your sorting or only partially sort the data. Always double-check to ensure that your whole data range is highlighted before proceeding with alphabetization and sorting.
Next, press the large
Pressing this button will bring up the
You'll notice that Excel gives us a couple of options for alphabetization in this dialogue:
- Column. This is the column that you'd like Excel to alphabetize or sort by. In this case, let's select "Customer", since the first things we want to do is alphabetize our list by customer name.
- Sort on. This is the characteristic of the cells in the given column that we would like to sort based on. We'll almost always keep
Valuesselected in this dialogue; although once you start experimenting with more advanced sorts you can also choose to sort by Cell color, Font color, and Cell icon. For now, just leave Valuesselected.
- Order. This allows us to choose whether we want to
sort ascendingor sort descending. In this instance, let's leave the order set to A to Zto alphabetize our list in an ascending manner.
If we just pressed the
First, click the
Using this second line, we can choose a second level of sorting that will occur after our data is alphabetized by customer name. Input the following into the boxes on the second line, like so:
- Column. Use
Sales, because we want to order the lines in our table by sales value after we have alphabetized by customer name.
- Sort on. Leave this set to
Values, as we'll do in the vast majority of cases.
- Order. You'll notice that since we have the
Salescolumn selected, the options in this box have changed; Excel recognized Salesas a number, so it gives us a new set of options for sorting: Smallest to largestand Largest to smallest. Let's select Largest to smallestnow, since we want each customer's largest order to appear at the top of its section.
Finally, press the
Sorting or alphabetizing in a custom order
The traditional sorting methods we've been using so far present a small problem: what happens if we want to order our list chronologically by month? Our month names are entered as text strings rather than dates, so alphabetization won't work: if we sort from A to Z,
The answer is simple: by using a
This screen will allow us to specify our own custom order of columns by typing them into the
Once you've done this, hit
Notice that in the custom list dialogue, there are a number of presets, one of which uses the names of months in order. We can also use that preset rather than entering our own custom values. The
Those are the basics (and some more advanced details!) on how to alphabetize and sort by values in Excel. Questions or comments on what we've done above? Be sure to let us know in the Comments section below.
Save three hours of work a day with these 5 simple Excel hacks
Work smarter, not harder. Sign up for our 5-day mini-course to receive must-learn lessons on how to automate your work using Excel.
- Why the TRUE and FALSE functions are so important...
- Why to rethink the way you do VLOOKUPs...
- Plus, we'll reveal why you shouldn't use PivotTables and what to use instead...