In any data analysis work, knowing how to alphabetize in Excel is one of the fundamental requirements. If your data is not ordered in a specific way, working with it becomes intolerable by the minute. More often than not, you also find that one method of alphabetizing does not match all the requirements.
That is why in this guide, we share with you the 9 most amazing methods for alphabetizing data. All of them are explained with visual demonstrations of the methods because we want to make the learning experience smooth and fruitful for you. This is the most complete guide on how to alphabetize in Excel you could ask for!
Let’s begin, shall we?
Contents
- How to Alphabetize in Excel: 9 Effortless Methods
- Method 1: Alphabetizing from Right-Click Menu
- Method 2: Quick Alphabetizing from Home or Data Ribbon
- Method 3: Alphabetizing Single Column
- Method 4: Alphabetizing a Range
- Method 5: Alt H S S and Alt A SA Button Combinations
- Method 6: Multi-Level or Nested Alphabetization
- Method 7: Row Alphabetization
- Method 8: Using the SORT Function
- Method 9: Using the SORTBY Function
- Concluding Remarks
How to Alphabetize in Excel: 9 Effortless Methods
In this guide to alphabetizing in Excel, we are demonstrating 9 excellent methods for you with extensive explanations. Some of the methods have more than one technique tucked in depending on their situations. And all of them together make this guide your one-stop destination for learning how to alphabetize in Excel.
We strongly recommend that you practice the methods as you read through the article. Use the practice workbook that we share with this guide. It will assist with understanding the methods more effectively.
Let’s get rolling then!
Method 1: Alphabetizing from Right-Click Menu
We begin with the most straightforward method for alphabetizing your column. It simply uses a right-click and a left-click on your mouse to have the job done. When a dataset needs to be alphabetized based on a single column’s content, this is the way to go.
We are going to work with the following dataset.
To alphabetize the dataset based on a column, simply select a cell on that column and then right-click the mouse button. It will bring up the right-click menu, also known as the context menu.
From the menu, the Sort option opens up a mini menu where the first two options, Sort A to Z and Sort Z to A offer you a way to alphabetize your column either in ascending order (Sort A to Z) or descending order (Sort Z to A).
As you can see, it can be done in two easy steps. See the demonstration below for a clearer understanding. We are going to alphabetize Column D’s content in ascending order (Sort A to Z) to show and instruct how it’s done.
1. Click on a cell in the column that you want to alphabetize. Then, while placing the mouse pointer above the intended column, click the right button on your mouse which will bring up the context menu.
Hover the mouse pointer above the Sort option to open the mini menu. From there, click on Sort A to Z for alphabetizing in ascending order.
2. As soon as you click the option, you will see your contents alphabetized. Notice Column D’s contents in the picture below.
The whole dataset is now ordered based on the alphabetization of Column D.
Quick and easy!
Method 2: Quick Alphabetizing from Home or Data Ribbon
If Method 1’s right-clicking seems troublesome because the mouse pointer has to be maintained in the right location, Method 2 is quicker and more precise in such cases. You only need to select a cell in the column and then click the correct option for alphabetization from either inside the Home ribbon or Data ribbon. It’s pretty easy. Let us show you how to do it in simple steps.
1. Click on a cell to make a selection in the column that you want to alphabetize. For this instance, we are going to alphabetize Column B.
2. Now, to do the alphabetization from the Home ribbon, click the Sort & Filter option in the Editing group found under the Home ribbon. In the opened drop-down menu, you will find Sort A to Z for alphabetizing in ascending order and Sort Z to A for alphabetizing in descending order.
If you want to do it from the Data ribbon, click on the Data tab in the menu bar to open the ribbon. From there, inside the Sort & Filter group, notice the two little options marked in the picture below.
The option on the top will do the alphabetization in ascending order (Sort A to Z). On the other hand, the one on the bottom will do it in descending order (Sort Z to A).
For this demonstration, we are clicking the one on the top to alphabetize Column B in ascending order.
3. Right after clicking the corresponding option, you will find that the intended column has been alphabetized. Also, the whole dataset got ordered based on that alphabetization.
Groovy, isn’t it?
Method 3: Alphabetizing Single Column
Let’s say you have a dataset where you want to alphabetize only a single column because you need information from that column only. The rest of the data is not needed. Therefore, you do not want the whole dataset to be ordered based on that alphabetization as it happened in the first two methods. Now, how do you do it?
In some easy steps, we are about to show you the way to perform this task. But first, let’s take a look at the dataset in the picture below.
For this demonstration, let’s say that we need to copy the students’ names but we need them alphabetized. So, we are going to alphabetize only Column B which contains the full name of the students.
1. First, select the range of cells that you want to alphabetize. For this, we are selecting B2 through B11 cells.
We are not including the header of this column in our selection because it will be considered just another cell with data and get mixed within the alphabetization order.
2. Now, you can start the alphabetization process in three different ways. You can either click the right mouse button to bring up the context menu. From there, you can select Sort A to Z from the Sort menu for ascending alphabetization. You can also go to the Sort & Filter option in the Editing group under the Home ribbon from where you can click the Sort A to Z option. The third option is going to the Data ribbon and clicking the AZ option in the Sort & Filter group. This is what we are going with for now.
3. As soon as you click that button, a Sort Warning window will pop open.
It will come up because Excel has detected data in adjacent columns and regarded them as part of your selection. That’s why it is asking you to Expand the selection with an assumption that you may have mistakenly selected only a part of your dataset. For now, let’s keep it as it is and then click the Sort button to see what happens if this option is kept selected.
You will see that the whole dataset has been ordered based on the alphabetization of your selected column. The same thing can be achieved in the first two methods. This is what you were trying to avoid here. So, when the Sort Warning window pops up for this method, make sure to click on the Continue with the current selection radio button and then click the Sort button.
4. Right after clicking the Sort button, you will see that only your selected column has been alphabetized. The rest of the dataset is unchanged.
Now you can copy your selection and be done with your work!
Method 4: Alphabetizing a Range
In the previous method, we have shown how to alphabetize a single column. But what if you need to alphabetize a range of columns? How do you select which columns you want to alphabetize in your range of selections? That’s what we are about to show you right now!
But before we begin with explaining the steps, let’s observe the dataset below.
Here, for our demonstration, let’s say we need to copy the students’ names only but need to alphabetize them based on their last names. Therefore, we are going to select the columns with Last Name and First Name (Columns B and C, respectively) here. Then we are going to alphabetize this range of selections based on the alphabetization of the Last Name column.
Follow the steps below to learn how to do it easily.
1. Begin by making the selection. For this demonstration, we are selecting the contents of Columns B and C. We are not including the headers in our selection for a specific reason which we will explain soon.
2. After selecting the range, there are three ways for alphabetization. You can click the right button on your mouse to open the context menu and from there, hover the mouse pointer over Sort, and then from the mini menu, click on Custom Sort…
Another way would be to click the Sort & Filter option in the Editing group under the Home ribbon. From the opened drop-down list, click on Custom Sort…
You can also go to the Data ribbon and click the marked Sort option in the picture below within the Sort & Filter group.
3. Now, a Sort window will pop open. Make sure to remove the check from My data has headers checkbox. We will show later what would happen if the box is checked.
4. Now, click the little button with the down arrowhead in the Sort by drop-down list. It will show the columns you have selected in your range. Click on the column you want to alphabetize.
For this demonstration, as we mentioned earlier, we are going with Column B because it has the Last Name and we want it to be alphabetized in our selected range.
Click OK after choosing the column for the alphabetization. You can change your order of alphabetization under the Order option. By default, it is in ascending order (A to Z / Smallest to Largest). And that’s what we want for this demonstration.
5. After clicking the OK button, you will see that your selected range has been alphabetized based on your selected column’s alphabetization.
In our case, it was Column B’s alphabetization.
6. If you want to do the alphabetization by selecting the header of a column and not the number of the column, there is a way. It is especially useful when a dataset has too much data so you want to select entire columns by clicking on column tabs. Let’s do it and see how it works.
First of all, select your data with headers.
7. Now use the Custom Sort… option to bring up the Sort window. The process is shown in Step 2 of this method. When the window comes up, make sure that this time, My data has headers checkbox is marked. You will see that your selection has automatically excluded the top cells with headers.
Now, when you click the Sort by option, you will see that header names are being shown instead of column numbers (as it did in Step 4 of this method).
Select the header for your alphabetization. In our case, it is the Last Name. Finally, click OK.
8. Immediately afterward, you will see that your data has been ordered according to the alphabetization of your chosen header column in the previous step.
Simply great!
Method 5: Alt H S S and Alt A SA Button Combinations
Working in Excel demands a user to be efficient and highly productive. Utilizing shortcut keys and access key combinations is one way that helps develop this efficient side of an Excel user. And the Alt button on your keyboard holds a great secret to that efficiency.
In Excel, the Alt key is also known as the access key. It opens access to the ribbons and menus which otherwise would require you to move your mouse and use a few clicks. How does it work, you ask? Let us show you with the help of a few pictures. First, let’s take a look at the menu bar in Excel in its default state.
As you can see, this is what the menu bar looks like all the time. Now, when you press the Alt button, it will look like the one in the picture below.
The difference is easily noticeable. There are now letters accompanying those menus in the bar. When you press a button with the letter showing in the menu, the corresponding ribbon will be activated and the options under that ribbon will now be accompanied by letters for you to select. This is why the Alt button is also known as the access button in Excel.
As we demonstrate how to utilize this Alt button and some more buttons with letters to alphabetize your column, you will further understand the process behind it.
Now, this alphabetizing can be done with two different Alt button combinations: Alt H S S and Alt A SA. We are going to show you the step-by-step process for both combinations. In a way, this method is an exact alternative or shortcut to Method 2. Because Alt H S S combinations do what Method 2 did using the Home ribbon. And, Alt A SA combinations do the same job from the Data ribbon.
If the talk of using buttons to alphabetize your content sounds too intimidating, we would like to assure you that it is a lot easier than it seems. After you use this method around 5-6 times, you will find that this is the easiest and fastest way you can alphabetize your data.
Let’s begin then.
5.1: Alt H S S Buttons Combination
In four easy steps, we will show you how to use Alt H S S buttons or keys combination to alphabetize your data in under two seconds. As you use it more, you will be able to do it even faster, pretty much in under a second at one point.
Follow the steps below and you will learn it in no time!
1. Select a cell in the column that you want to alphabetize. For this demonstration, we are going with the data in Column A to alphabetize or sort them in ascending order.
2. Then, press the Alt button on your keyboard to show the letters for menus in the menu bar. When it activates, press the H button on your keyboard to access the Home ribbon options.
3. After pressing the H key, you will now be shown letters for the options inside the Home ribbon. Take a look at the Editing group under the ribbon. You will find the letter S accompanying the Sort & Filter option. So, press the S button to open the drop-down menu for this option.
4. Once you do it, you will find that for alphabetizing A to Z or in ascending order, the Sort A to Z option is now accompanied by the letter S for your buttons. Press the S button again to use that option.
5. As soon as you press that S key, you will see that your column has been alphabetized!
You can see in the picture above that while the column marked in a blue rectangle has been alphabetized, other columns in the dataset have also followed that alphabetization to reorder themselves.
That’s fast, isn’t it?
5.2: Alt A SA Buttons Combination
This Alt A SA button combination is a shorter version of Method 2’s alphabetization through the Data ribbon. Let’s see the steps on how to do it.
1. As always, select a cell from the column you want to alphabetize. For this demonstration, we are going to alphabetize Column A and so, we are selecting a cell from it.
2. Now, hit the Alt button on your keyboard to activate access to the menu bar. After pressing the button, you will see the menus having letters for buttons associated with them. Press the A button on your keyboard to go into the Data ribbon.
3. Afterward, you will see that the Data ribbon has been activated and more letters are accompanying all the options under that ribbon. As our goal is to alphabetize the column in ascending order, we are going to hit the buttons SA (S key and A key. Pressing S followed by A). The SA can be taken as Sort Ascending. You can also see that the option for alphabetizing in a descending order has the buttons SD accompanying them which means Sort Descending.
Now, for this demonstration with SA, if these button combo presses are simplified, it will be like pressing them in the sequence of Alt then A then S, and then A button.
4. As you complete pressing the combination in the exact sequence, you will find that your intended column has been alphabetized!
This is much faster than most alphabetization methods, isn’t it?
Method 6: Multi-Level or Nested Alphabetization
When there is a requirement to do a nested alphabetization which is also known as multiple-level alphabetization, it differentiates a casual Excel user from a seasoned one. This is one of the most important abilities to have if you are to use Excel as one of your necessary tools.
But what does Multi-Level or Nested Alphabetization mean? The name already tells the whole story. It is alphabetizing on more than one level, alphabetization within alphabetization. Let’s take a look at a dataset to understand more distinctly.
Observe the dataset above. Let’s say you are tasked with alphabetizing the D column and then asked to alphabetize the B column without disturbing the alphabetization of column D. When you alphabetize that column with the preference for sports for each student, they all have more than one student for each sport. Your task is to alphabetize the students’ last names within a specific sport’s count.
And that’s where this nested alphabetization comes in. In this demonstration, the alphabetization happens on two levels. On the first level, it will alphabetize the sports column to make it ordered. On the second level afterward, it will alphabetize the students/players’ names within that alphabetization of a specific sport.
Let’s see how to do it in a few steps below.
1. Select a cell on your dataset. Then click on the Data tab to open the Data ribbon. From there, inside the Sort & Filter group, click the Sort option.
2. It will bring up the Sort mini-window. This is where you can set your alphabetization rules on more than one level.
Make sure that you have My data has headers checked. Then, in the Sort by option under the Column section, click the little down arrowhead on the right side of the box to open the drop-down menu for header selection.
3. As you can see, the list contains the headers’ names for your alphabetization.
For this demonstration, we are going to alphabetize the Sports column in ascending order for the first level. For the second level, we are going to alphabetize the Last Name within that alphabetized Sports data.
4. So, for the first level, let’s select the Sports column. Make sure that the Cell Values option is selected under the Sort On section and A to Z is selected under the Order section in that level.
Afterward, click the Add Level button to add the second level for the alphabetization.
5. After adding the second level, select Last Name from the drop-down menu in its Sort by option under the Column section.
6. After selecting, check to ensure that the Sort On section has Cell Values selected and the Order section has A to Z as its alphabetization order. When done, click the OK button to initiate and complete the alphabetizing process.
7. As you hit OK, you will see that your dataset has been alphabetized on two levels.
First-level happened in column D where the sports are alphabetized. The second level of alphabetization happened in column B where, for each sport’s players, the last names have been ordered ascendingly within that respective Sports’ datasets.
8. Now, if you want to change the order or specification for your alphabetization levels, simply go back to the Sort option and you will be shown the applied levels in the mini Sort window.
This method makes a lot of work easier, doesn’t it?
Method 7: Row Alphabetization
For all these six methods discussed so far, we have shown how to alphabetize in a column. But what if you need to alphabetize a row? Is there any other way to do it other than using the TRANSPOSE function to transform your row data into columns and then do the alphabetization?
Of course, there is a way! We understand the importance of working smartly with Excel. That’s why we are going to show you how to alphabetize your data in rows as well! The dataset we are going to work with in this method is shown below.
Let’s say the task is to alphabetize the Sports row. Yes, even this can be done pretty easily. Follow the steps demonstrated below.
1. Select only the range of data without the header.
2. Now, under the Data ribbon inside the Sort & Filter group, click the Sort option to bring up the Sort mini-window. In it, make sure that the My data has headers option is unchecked. Next, click the Options… button.
3. A little Sort Options window will pop open. By default, it will have Sort top to bottom selected. Make sure to change the selection to the Sort left to right option. Then click OK.
4. You will be taken back to the previous Sort window. But this time, you will find that the Column option for alphabetization has been changed to the Row. And when you click the little arrowhead on the right of the Sort by option, it will show options with row numbers for your alphabetization. See the marked picture below.
5. For this demonstration, we are going to select Row 3 from the list because we want to alphabetize the Sports residing in row 3. The Order should be A to Z because it is going to be an ascending alphabetization.
Finally, click OK to complete the process.
6. Now you will find that the row has been alphabetized according to your selection!
How cool is that?
Method 8: Using the SORT Function
Functions are an integral part of the Excel program. They make our job a lot easier and more interesting. The alphabetization is not an outsider in this game either. Two functions can be used for this: the SORT function, and the SORTBY function. We will discuss the SORTBY function in the next method. For this one, we are focusing on the SORT function.
Both SORT and SORTBY functions are available in Excel 2019 or later versions. Both of them work with dynamic formulas where, if any information is changed or new information is added to the provided array then the results will update themselves. We will discuss both with examples but now is the time to see how to work with the SORT function to alphabetize your dataset.
First, let’s take a look at how this function works. The formula for this SORT functions is =SORT(array,[sort_index],[sort_order],[by_col]). Let’s break it down.
Here,
- SORT : The function
- array : The area or range of cells that are to be alphabetized.
- sort_index : The nth column in the provided array for the alphabetization. This is optional. If not mentioned in the formula, the default will be 1 for the nth value.
- sort_order : 1 is for ascending alphabetization, and -1 is for descending alphabetization. This is optional as well. If nothing is mentioned, the ascending alphabetizing order will be used.
- by_col : TRUE or FALSE. This argument is optional as well. TRUE makes the provided array alphabetized by column whereas FALSE will make the alphabetization by row. If nothing is mentioned, the TRUE value is the default.
For this method, we are discussing the alphabetization by column only. So, in all the demonstrations here, we will not mention this argument in the formula since by column is the default option when it is not mentioned.
Now, we are discussing three different situations where this SORT function can be used for alphabetization: for a single column, selected range, and multi-level alphabetizations. You have already seen them separately in other methods discussed so far but now is the time to bring them all together under a specific function and work smarter and better with more efficiency.
We are going to use the following dataset for the demonstration of this method.
We will show one by one how you can do each type of alphabetization.
8.1: Single Column Alphabetization
For the first type, how to alphabetize a single column is going to be shown. We are going to alphabetize column B with Full Name. It is the easiest to do among all three different situations that are discussed in this method.
Let’s go through the process step-by-step.
1. First, select the column where you want to see the alphabetized data. Then start writing the formula.
2. After writing =SORT( , you are going to have to select the array or range of cells for your alphabetization. For this demonstration, we are selecting the contents of column B under the Full Name heading.
When the range has been selected, nothing further needs to be added since this array is a single column only. And if not mentioned in the formula, the default values alphabetize in ascending order and do it by column. So, after simply selecting the range, hit Enter.
3. Instantly, you will see the alphabetized data in the column where you have typed in the formula. You will see a light blue border around the alphabetized data which declares that this data is indeed dynamic.
4. Now, as a confirmation of the dynamic nature of this formula, let’s change something in the provided array or selected range. We are going to modify some data which are highlighted in the picture below. Notice the positions of that name in both the dataset and the alphabetized column.
5. We are going to change it to a name that begins with the letter “B”. See the immediate result below.
As you can see, the position of that specific data in the dataset has been kept the same and only the value has been modified. Yet the position of it in the alphabetized column has changed automatically following the alphabetization which uses the dynamic SORT function.
This is a lifesaver when you are working with a dataset containing thousands of cells with valuable data!
8.2: Selected Range Alphabetization
What if you are required to reorder a selected range based on the alphabetization of a particular column of data in your dataset? This is a pretty familiar occurrence. We have shown how to do it in Method 4. But that is not a dynamic process. Therefore, the SORT function comes into play.
We are going to demonstrate this in the dataset below.
Here, after selecting the range or array, we will alphabetize column C’s data in that range. Let’s follow the process below.
1. Select a column for your formula to write where you want to see the result of the formula. Then start writing the formula. Select the range or array for the formula. In this demonstration, we are selecting from A3 through C12.
2. Put a comma after the provided array. Now, write down the value of the nth column in your array that you want to be alphabetized. In our case here, we are putting 3 because we want to alphabetize the 3rd column in our array.
Finally, select the order for alphabetization. Although the default is 1 for alphabetizing in ascending order, we are still putting the value for it in this formula. Close the bracket to finish the process. The whole formula in this case looks like in the picture below. For ease of understanding, we have explained the used formula in the picture as well.
3. As you hit Enter after writing the formula, you will see the result instantly! In our case, the nth=3 in the provided array has been alphabetized in the result.
That is amazing, isn’t it?
8.3: Multiple-Level Alphabetization
Multiple-Level or Nested Alphabetization is a common requirement if you are used to working in Excel. We have already shown how to do it in Method 6. But depending on how much data you are dealing with, multi-alphabetizing your dataset becomes a lot more interesting and effective when done using the dynamic SORT function.
Let us take you through the step-by-step process with a succinct demonstration. Firstly, observe the picture below.
We are going to alphabetize column C in the first level. In the second level of alphabetization, we will take column B’s contents.
Time to see it in steps.
1. Begin by selecting the column where you want to write the formula. Then start writing the formula and select the range of area or array.
2. Now, as the alphabetization is going to apply on multiple levels, there are multiple values for the sort_index. For this, we are going to use the Array Constants in our formula. To write multiple values in an array formula, those multiple values are written within curly brackets.
In our array, we are going to alphabetize nth=3 on the first level and then nth=2 on the second level. So, the sort_index values are to be written within curly brackets here, {3,2}. You will choose your values regarding what you want to alphabetize.
3. Next, the sort_order is written. If only one type of sorting order between the ascending order and the descending order is selected for both the levels in this multi-level alphabetization then writing simply one value is sufficient. However, to make it fun, we are going to use the descending alphabetization for our first value and the ascending alphabetization for the second value.
For this reason, in sort_order, the array constant {-1,1} is written. -1 refers to the descending alphabetization which applies to the first sort index nth=3, and 1 is the ascending alphabetization which is for the sort index nth=2.
When writing the formula is done, close the brackets and hit Enter. For a clearer understanding, this demonstration’s formula has been explained in the above picture as well..
4. After hitting the Enter button, you will see the result before you. As instructed in the formula, the third column in the provided array has been alphabetized in descending order on the first level. On the second level, the second column has been alphabetized in ascending order.
5. On top of that, this array is dynamic despite being a nested alphabetization. See the demonstration below. We are going to change the value in the highlighted cell. Notice their positions before the change happens, especially on the right where the data has SORT formulas active.
6. Now see the change of positions on the right. The alphabetization happened automatically the moment the value was changed.
This is extremely useful when you are dealing with a huge amount of data!
Method 9: Using the SORTBY Function
The SORTBY function is yet another option that proves to be useful when you want to alphabetize your data. Although it sounds similar to the one discussed above, this SORTBY function is different in some ways from the SORT function. Some people prefer one to the other. We are discussing both in this guide so you can pick and choose the one you prefer!
The difference between SORT and SORTBY is that instead of mentioning sort_order for alphabetization like in the SORT function, you are going to have to select an array in the SORTBY function. Also, instead of using Array Constants like it is needed in the SORT function, you can simply stack multi-level alphabetization one after another in the SORTBY function.
The formula for this SORTBY function is =SORTBY(array,by_array1,[sort_order1],[by_array2],[sort_order2],……). Let’s break the whole thing down.
- SORTBY : The function.
- array : Range of cells that will be sorted according to the alphabetization.
- by_array1 : The array or range of cells that will be alphabetized.
- sort_order1 : The order in which alphabetizing will happen. This is optional. If not mentioned or 1 is used for the order, the selected array will be alphabetized in ascending order. For the descending order, put -1.
- by_array2 : The second range of cells or arrays which will be alphabetized. Useful in nested alphabetization. Not needed in single-column alphabetization.
- sort_order2 : The order for the second alphabetization. This is optional as well. Value 1 is for the ascending order and -1 for the descending order.
And it continues further if there are more arrays to be alphabetized.
Now we are going to show you how to use this formula to alphabetize your data for three different situations. We will use the dataset shown below.
Let’s begin with the single-column alphabetization.
9.1: Single Column Alphabetization
The SORTBY function will be your favorite if you would like to avoid writing too much formula. Because this function’s formula requires more selecting range of cells or arrays instead of writing (as we have seen in some cases for the SORT function).
Let’s see how to do it for a single column in steps. We are going to alphabetize column B in the dataset which contains the Full Name.
1. First, select the cell where you want to write your formula. Then start writing it. After you put the function name and the open bracket, select the range of cells for the array.
2. For this demonstration, the cells under Full Name have been selected. Put a comma, and then select the by_array1 for the alphabetization. Since we are dealing with a single column here, the array which was selected before should be selected again in the by_array1.
3. After selecting the by_array1 for alphabetization, put the preferable sort_order1 for that by_array1. In our case, we are inputting 1 because we want the alphabetization to happen in ascending order. Close the bracket once the formula is done and then press the Enter button.
The formula that we are using in this demonstration has also been explained in the picture above.
4. As soon as you hit Enter, you will see that the provided array has been alphabetized!
This SORTBY function is a dynamic one as well. Therefore, if you change a value in the provided source array, the result with the formulas will reflect that change immediately. In that case, the alphabetization will reorder automatically if the change in source requires it to do so.
Pretty cool, right?
9.2: Selected Range Alphabetization
The SORTBY function can be used to alphabetize data in a selected range as well. Here, the range of data will follow the alphabetical order of a particular array alphabetization. Let’s see it through a visual demonstration below.
1. Click on the cell where you want to write your formula.
2. Now start writing the formula. Select the range of cells or array first. Then put a comma for selecting the array for alphabetization.
3. Now select the array for your alphabetization. Then put a comma and write the sort_order1 for your alphabetization where 1 means ascending order and -1 means descending order. Finally, close the bracket and hit Enter.
As we are going to alphabetize Std ID contents, we have selected the A3:A12 range for alphabetization and put 1 for the ascending order in sort_order1.
4. Right as you press the Enter button after writing the formula, you will find your data to be alphabetized.
In our case, we picked column A’s content to be alphabetized in the provided array. So we selected those as by_array1 and got the result accordingly on the right.
Getting the hang of the SORTBY function? Let’s see the final one, the multi-level alphabetization for a more interesting touch!
9.3: Multiple-Level Alphabetization
In Method 6 and Method 8.3, we have seen how to do a nested or multi-level alphabetization where the first one takes a long route of selecting many options and the second one requires the use of complex Array Constants. If you are looking for something more straightforward, this nested alphabetization by using the SORTBY function is the approach for you!
Let’s see in a few steps how it works.
1. Begin by clicking on the column where you want to write the formula.
2. Now start writing the formula. After selecting the array, select your by_array1 for the first-level alphabetization. Next, put 1 for ascending order or -1 for descending order in the sort_order1, and put a comma for the next step to begin.
In our case, we are going to alphabetize the Sports column’s content in ascending order for our first-level alphabetization.
3. Now select the second-level array for alphabetization which is by_array2. Then put a comma followed by the value for sort_order2. When you are done, press the Enter button.
In our case, we are going to alphabetize the Full Name column’s contents in ascending order at the second level of alphabetization.
The formula that we have used in this demonstration has also been explained in the picture above. You can add more levels of alphabetization following the same process.
4. After hitting the Enter button, the nested alphabetization will happen and the data will be shown in the selected space.
In our case, for the first-level alphabetization, the Sports column was alphabetized. For the next level, the Full Name column was alphabetized within the Sports range. Notice the colored sections and the names within that range on the right against the dataset on the left to understand it clearly.
This is simply stunning, isn’t it?
Concluding Remarks
We have reached the last section of this visual guide on how to alphabetize in Excel. In brief, we have shown you 9 different methods for alphabetizing your data in Excel. Learning all of them will prove useful for any Excel user. It will help with choosing the right method in a given situation.
However, if you want something really quick, Method 1, Method 2, and Method 5 are the ones to learn. For an advanced level of alphabetization, Method 6, Method 8, and Method 9 will serve you most effectively. Even if you learn only Method 9 from all those we have demonstrated here, you will be able to alphabetize anything thrown at you at any level at all!
We strongly recommend that you practice all of them at least twice. Use the practice workbook we are sharing with you. This way, you will follow the instructions more easily and effectively.
Now is the time to learn. So, let’s get down to business!
Read Also: