Excel is one of the best spreadsheet programs for sorting data in ascending or descending order based on alphabets, numbers, colors, and more. Anyone can sort the dataset alphabetically within a few seconds using the “Sort & Filter” tool.
There are a lot of powerful features in Excel for organizing and visualizing datasets.
Numbers aren’t the only way for data visualization! Sometimes, you may wish to sort your spreadsheet by employee names, job titles, departments, products, categories, and more.
This is a quick guide about how to sort alphabetically in Excel. Make sure to read the article until the end, as it includes useful functions and tools prebuilt in Excel for sorting data.
Here is a list of methods for sorting datasets in Google Sheets by Alphabet. You can simply click on any link to jump to that particular section:
- Sort by Alphabet using Sort & Filter tool
- Sort by Alphabet using the SORT Function
- Sort by Alphabet using the Sort Tool
Sorting datasets is a critical skill in Excel. You may require it more than often. The steps discussed below are useful for alphabets, numbers, and color-based sorting as well.
Download the Example Excel Sheet
You can download the following Excel Sheet to practice what we are discussing in this article.
It will help you strengthen your knowledge of the formulas and functions discussed in the following sections.
If you have your own spreadsheet ready, skip downloading the above file. But make sure to follow me as I walk you through the article.
Now, without any further ado, let’s begin.
How to Sort Alphabetically in Excel using the Sort & Filter Tool
This is the easiest and fastest method of sorting data in Excel.
The Sort & Filter tool allows you to sort by column. You can sort the dataset in ascending or descending order by alphabets, numbers, and colors.
The steps are pretty straightforward. Even beginners can sort data alphabetically within a few seconds using the Sort & Filter tool.
Consider the following example where we have employee details, including their full name, department, and age.
Our task is to sort the first column named as “Full Name” in ascending order.
Here are the steps,
- Open the desired Microsoft Excel
- Select the entire table range by clicking on any cell and pressing the “CTRL + A” on your keyboard
- Now, hover over to the main menu
- Click on the “Home” tab
- Select the “Sort & Filter” icon from the list
- This will enable the filter for every column of the table, as shown below
- As we want to filter by “Full Name”, click on the dropdown beside the corresponding column header
- Select the “Sort A to Z” option from the list
(For sorting in descending order, you need to click on the “Sort Z to A” option) - Press the “OK” button
Excel will instantly sort the entire table in ascending order by the first column. Refer to the above GIF for detailed steps.
How to use the SORT function to Sort Alphabetically in Excel
This is the second method to sort the data alphabetically. It uses a built-in function to create a dynamic table that sorts the data automatically whenever the source data is updated.
Before we jump into the actual steps, let us first understand the SORT function.
Explained: The SORT Function in Excel
The SORT function is used to sort the contents of a table range in ascending or descending order. It returns a dynamic array of results, which updates automatically when anything new has been added or deleted.
Here’s the general syntax of the function,
=SORT(array, [sort_index], [sort_order], [by_col])You need to carefully replace each argument of the above formula with proper information.
- “array” – It represents the table range that you wish to sort
- “sort_index” – Here, you need to put the column number that is to be sorted
- “sort_order” – This argument is used to define the sorting order. You can type “1” to sort the data in ascending order and “-1” to sort it in descending order
- “by_col” – This is an optional argument that is used to indicate the direction of sorting. You need to enter “FALSE” to sort by row and “TRUE” to sort by column.
Compared to the other two methods, this method creates a dynamic table capable of automatically updating the sorting whenever we add a new record or change the existing ones.
We will consider the following table, which has three columns for employee full names, departments, and ages.
Our task is to sort the first column alphabetically in ascending order.
For the sake of simplicity, I have divided the entire process into two different steps.
STEP #1 – Create A New Table
The first step when using the SORT function is to create a table in the same spreadsheet. This ensures that the original information is preserved.
Simply put, you need two tables: source and destination.
Here’s how the new table should look.
Make sure the new table has the same headers to avoid confusion. Since the source table had three columns, we created a new table with three columns.
STEP #2 – Use the Sort Function
We have already discussed the general syntax for the function. The sort function is pretty straightforward.
Let’s begin,
- Select the first cell of the new table, as shown in the following image
- Type “=SORT”
- Choose the first option from the popup or press “Tab” on your keyboard
- Select the entire table as shown below. Make sure you exclude the headers
- Press “,” on your keyboard to move to the next argument
- Let us put “1” as we wish to sort the first column of the table
(The SORT function allows you to sort by only one criteria or condition) - Press “,” on your keyboard
- To ensure the sorting in ascending order, let us type “1”
(For sorting the dataset in descending order, you need to put “-1” in the place of the sort_order argument) - Complete the bracket using “)” on your keyboard
- Press “Enter” to see the result
Here’s how the final formula should look,
=SORT(A3:C21,1,1)If you wish to sort the data in ascending order, the formula would be as follows:
=SORT(A3:C21,1,-1)We are simply replacing the sort_order argument with the “-1”.
PRECAUTION: The common mistake while using the Sort function
Remember, the SORT function is an array formula, and the created table is a dynamic one.
Once you try to make changes in the dynamic table, the entire table will disappear within seconds. So, you must avoid changing or adding records to the new table.
For example, look at the image below. We have a false entry “asd$” in one of the cells that is part of our newly created dynamic table. Now, let’s use the SORT function and see what type of error it is returning.
It is showing a “#SPILL!” error. This occurs when a formula returns multiple results, and Excel cannot return the results to the grid. To resolve it, just delete the false entry and make sure the cells in the new table are empty.
What is another way to insert the Sort function?
If you do not prefer manually typing the SORT formula, you can insert it from the main menu. Follow the steps below to insert the SORT function.
- Go to the main menu and click on “Formulas”
- Choose “Insert formula”
- An “Insert function” window will be opened
- Select the formula that you want to insert from the available options or search for it in the Search bar at the top
- Once you select the formula, you will be prompted to enter the argument values also, as shown below
Note that the arguments remain the same as discussed in the previous section, where we manually typed the formula into the desired cell.
How to Sort Alphabetically In Excel using the Sort Tool
This method is very similar to the first method discussed above.
If you do not wish to create a dynamic table that could update automatically, then you can use the first or third method.
We will consider the same example, including employee full names, department, and age.
The task will remain the same, sorting the first column in ascending order.
Here are the steps,
- Open the desired Excel spreadsheet
- Select the entire table by pressing “CTRL + A” on your keyboard
- Go to the “Data” tab from the main menu
- Click on the “Sort” icon from the available options
- A new dialog box will appear on your screen, as shown below,
- If your dataset has headers, make sure “My data has headers” is selected in the dialog box. Refer to the following image,
- Choose the desired column containing the full names from the Sort by dropdown, as shown in the following image,
- Next, select “Cell Values” from the “Sort On” dropdown
- Choose “A to Z” for ascending order or “Z to A” for descending order under “Order” dropdown, as shown below
- Click on the “OK” button to see the results
The steps are simple. The Sort tool allows you to sort the dataset by multiple conditions (discussed in the following section), making it the most preferred tool for sorting huge datasets.
How to Sort Multiple Columns Alphabetically using the Sort Tool
There are two ways to sort datasets by multiple columns and criteria.
- Using the Sort Tool
- Using the SORT function
In this section, we will learn the steps to use the Sort tool to sort multiple columns alphabetically. Consider the following example,
Our task is to sort the first column named “Full Name” in ascending order and the second column named “Department” in descending order.
Let’s begin,
- Open the desired Excel spreadsheet
- Go to the main menu and click on the “Data” tab
- Click on the “Sort” icon as shown below
- A dialog box will appear, as shown in the following image,
- Next, in the “Sort by” dropdown, select the option “Full Name”
- In the “Sort On” dropdown, select the option “Cell Values”
- In the “Order” dropdown, select the option “A to Z”
- Now, for sorting the table by multiple criteria, you need to click on the “Add Level” button as shown below,
- A new set of options will be displayed as shown below,
- In the “Then by” dropdown, select the option “Department”
- In the “Sort On” dropdown, select the option “Cell Values”
- Select “Z to A” in the “Order” dropdown because we want to sort the “Department” column in descending order
- Click on the “OK” button
Here are the results obtained after following the above steps. As you can see, the first column is sorted in ascending order, and the second column is sorted in descending order.
FAQs: How To Sort Automatically In Excel
Q. How do I sort alphabetically in Excel without mixing data?
You can use various methods to sort data alphabetically in Excel. We have discussed all of them in the above sections of this article.
Please refer to the following steps to quickly sort the data in Excel,
- Go to the “Data” tab from the main menu
- Select the “Sort” option
- It will open a dialog box for you, as shown in the following image,
- Then, from the “Sort by “ dropdown, choose the column name you want to sort
- From the “Sort On” dropdown, select the “Cell Values” option
- Select “A to Z” for sorting your data in ascending order or “Z to A” for sorting the data in descending order from the “Order” dropdown
- Click the “OK” button
It is one of the quickest methods to sort the data in alphabetical order in Excel.
Note that you can sort the data by multiple criteria or columns using the “Add Level” button, as shown in the following image.
Q. What is the shortcut for Sort A to Z in Excel?
There is no direct shortcut to sort the dataset in ascending or descending order. However, you can use the keyboard shortcuts to enable filters or open the Sort tool.
- “CTRL + SHIFT + L” – It enables a filter for each column
- “Alt + A + S” – It opens the Sort tool from where you can add columns and decide the sorting order
Q. Why is Excel not sorting alphabetically correctly?
There can be various reasons why the sorting may not work. I have listed a few of them below,
- Leading Spaces or Invisible Characters
- Mixed Data types
- Case sensitivity
- Hidden characters or formatting
- Blank cells or Duplicate values
- Inconsistent data