Excel is an awesome tool for keeping long lists of sortable, easily accessible data. This can include anything from book collections, to-do lists, budgets, invoices, roll call sheets, grocery lists, and more.
In some cases, you may find yourself with a sprawling record of names, titles, and tasks that need to be placed in alphabetical order to really be useful. Luckily for you, this is a pretty basic function, though not without its quirks. In this guide, we’ll go over how to alphabetize in Excel, and what to be aware of when sorting long lists of data.
For extra help, take an Excel training course aimed at beginners here!
Sorting a Basic List
The first thing you’re going to need is a list. In Excel, a spreadsheet consists of endless cells of data, organized into columns and rows. You could have a spreadsheet that consists of just one column, with multiple rows of data. This would just be a standard list, such as a list of guest names for an upcoming birthday party.
Let’s say you input a list of names into the spreadsheet as they popped into your head, but by the end you’d like to sort them out alphabetically just to make your life easier.
The first thing to do is select the data you want to be sorted, a simple task in this first example because our data is located in just one column. You can click on any row within your column of data – in this case, A1 through A15 – to select that column, or you can click the top or bottom most cell and drag your selection to highlight all the cells in the column.
In the image above, you can see “15R x 1C” displayed in the name box, to the left of the formula bar. This indicates that you have fifteen rows (15R) and one column (1C) highlighted.
Once your list is selected, all you have to do next is click on the Data tab at the top, also shown in the image above, and click the AZ icon with the down pointing arrow. This will sort your list in alphabetical order, from A to Z.
Done! But what if your list has more than one column?
Sorting a List with Multiple Columns
In some cases, you may have a list made up of several columns. The data in these other columns may or may not correspond to the column(s) you want to sort, which can make things a bit more complicated.
Let’s start out simple, and say you just have a basic birthday guest list again, only you want to use the same spreadsheet to keep track of a list of things you need to buy for the party. These items will have nothing to do with the guests, they’re just a separate list in a separate column. You’ve ordered them according to their location in the grocery store, so that it’ll be easier for you to find them when you go shopping.
You want to sort the names in Column A alphabetically, but keep the list in Column B in the same order in which you input it.
To do so, highlight the cells in Column A just as we did in the first example, select the Data tab, and click the AZ button with the down pointing arrow. This time, you’ll receive the following prompt:
Excel has detected the data in Column B, and wants to know if this data corresponds with the data in Column A. Because it doesn’t, click the option that says “Continue with the current selection” and press OK.
The names in Column A will be sorted alphabetically, and the list in Column B will remain in the order you originally input.
Sorting Multiple Lists With Corresponding Data
There may be times when your additional columns do contain information relevant to the one you want to sort. Sticking with the birthday guest example, let’s say you wrote out the names in the order the guests called you. This is your original unalphabetized list. The guests also let you know what food item they were bringing to the party.
You want to alphabetize the list of guests, but you want the food item they’re bringing to remain next to their name. You don’t want the food list to be alphabetized, because that will jumble everything up.
To do this, just highlight the data in both columns, and press the AZ button with the down pointing arrow. The two lists will sort themselves, keeping the guest names next to their corresponding food item. Or, you can highlight just Column A, click the AZ sort button, and select the “Expand the selection” option once you’re prompted.
If you highlight Column B – the list of food items – and “Expand the selection” after sorting, you’ll see the food items will alphabetize instead! The corresponding names will remain next to each item in Column A, though.
Sorting Parts of a List
Let’s say the last five people to call you – Alli, Leslie, Carston, Jose, and Ayumi – are all a family, and they had made plans before you invited them which may or may not fall through. Their attendance is iffy.
You want to keep them on the list as “maybes,” but you don’t want to get them mixed into the bigger one. You still need to alphabetize that guest list, though! To keep the family of five separate, bold their names (just as a visual aid), and highlight the cells in both columns excluding theirs.
Then, simply click the AZ sort button, and voila! The guest list is alphabetized, except for the last five names.
You can also sort cells horizontally, if you happen to have a list confined to one row and multiple columns, rather than multiple rows and one (or more) columns in the previous examples.
For this one, let’s stay simple. I’m getting tired of that birthday example, aren’t you?
To sort these columns alphabetically, and horizontally, highlight each cell in the list, and instead of clicking the AZ button with the down pointing arrow, click the box that says Sort in the Data tab.
Once you’re prompted with this box, click “Options…” and the following pop-up will appear:
Select the option that says “Sort left to right” and click OK.
In the original Sort pop-up, select Row 1 from the drop-down menu next to “Sort By,” make sure it’s sorting by “Values” and in “A to Z” order, and click OK!
This has been a basic tutorial for an extremely simple, yet versatile function in Microsoft Excel. To learn more about this feature, and more, consider taking some of the following Excel courses:
- Advanced Excel Training – Online Excel Course
- Microsoft Excel – Tables – Deep Drive
- Ultimate Excel Shortcut Guide Become a Power User in Days!