How to sort in Excel?
Sorting of data is an essential part of data analysis. In Excel, you can rearrange the data by sorting to find the record quickly. Data can be sorted in various ways, such as alphabetically (A to Z, Z to A), highest to lowest, lowest to highest, date & time-wise, and using many other ways.
Sorting of data is always required for quick search; it arranges the data in a way that makes the data easy to search. If the data is sorted, it is easy to find any record in the table or Excel sheet. It saves the time of users by fast search in sorted data. Excel provides an in-built function named as SORT() to arrange the data in sorted manner.
This chapter will define how one can sort data in Excel.
How can be the different data sorted?
- The text data can be sorted alphabetically, i.e., A to Z or Z to A.
- Numeric data can be sorted by range, i.e., highest to lowest or lowest to highest.
- Date and Time wise, i.e., newest to oldest or oldest to newest.
You can follow any of the techniques to sort your Excel data. In an Excel sheet/table, you can sort one or more columns. For example, sort the employee table records, first by their salaries and then their last name.
How to sort in Excel?
Here, we will define you how one can sort data in Excel. Excel provides SORT function to arrange the data in a manner. You can manually sort the table data by entering the formula and providing the required parameters. Otherwise, Excel offers shortcuts to sort the data of Excel.
First, we will describe you what a sort() function is, then will show you the steps to sort the data by taking different examples.
What is sort() function?
SORT() is a function that is used in Excel to sort the columns and arrange the table data. It allows the users to sort the data alphabetical, numeric, or date-wise. You can also make the group and then apply this sort() function to these groups individually.
By default, it sorts the data of an Excel table in ascending order using the first column.
Syntax
Here is the syntax for the following sort() function having four parameters, in which one is permanent and the other three are optional:
Parameters
array: It is a range or array in an Excel table selected for sorting.
[sortIndex]: It is an optional parameter. In this parameter, specify the column number for sorting the table data. Its default value is 1, which means it choose first column of the Excel table.
[sortOrder]: It is also an optional parameter. Using this parameter, you can specify the order of sorting. Its default value is also 1 means ascending order sorting.
Pass 1 for ascending order sorting and -1 for descending order sorting.
[byColumn]: It indicates either sorting by column or by row. Its default value is FALSE.
TRUE – Sort by column
FALSE – Sort by row
Return Value
The sort() function returns the array after sorting the data.
Implementation of sort() formula (For Excel 365 subscription users)
Now, we will show you how you can implement this formula in your Excel sheet. But this can only be implemented by Excel 365 subscription users. In Excel 2016, 2019, this way of sorting does not work. We have another method for this discussed in this chapter below.
Step 1: We have this set of data containing Name and Age data in unsorted order. We will use the sort() function and rearrange it.
Ascending Order Sorting
Step 2: On the Excel home page, go to the formula bar and type this sort() formula to get the data sorted with respect to the Age column.
- Here, A2:B8 are the source/range of array for sorting
- 2 is the specified column2 (Age) for sorting
- 1 is ascending order of sorting
Step3: Now, press the Enter key and see the sorted result has been automatically pasted into new cells (D and E).
Descending Order Sorting
In this example, we will sort the Excel data by Name alphabetically in descending order.
Step 1: On the Excel home page, go to the formula bar and type this sort() formula to get the data sorted with respect to the Name column.
- Here, A2:B8 are the source/range of array for sorting
- 1 is the specified column1 (Name) for sorting
- -1 is descending order of sorting
Step 2: Now, press the Enter key and see the sorted result has been automatically pasted into new cells (D and E).
Excel Sort() function tip
- Currently, the sort() function cannot be manually typed and accessed in all Excel versions. It is only available for Microsoft 365 subscriptions.
- Excel 2016, 2019 does not support the sort() function. However, these versions have another way to use this formula from the Excel ribbon, which is discussed below in this chapter.
- If you will try to access this formula in other Excel except Microsoft 265, it will show this error.
Sort the Data from Excel ribbon (For all Excel)
It is not possible to remember all formulas of Excel and their parameters every time. So, Excel also provides one more way to use these formulas. Besides this method, we will also describe other ways of using the sort() function to sort the Excel data.
Excel has added some most needed formulas in its ribbon for fast and easy access, which is efficient for the non-excel user. Use any of them which you find simple and easy. Here, we have several examples using different parameter values.
Steps to sort the entire table data
We have a simple data Excel table with two columns, name and age. Data of the table is not arranged yet. Now, we will sort this table of data with respect to the Name column alphabetically.
Sort by text
Here, we will sort the entire table data by a text string. We have few simple steps to sort the data alphabetically.
Step 1: Create a table, same as ours or open your existing Excel sheet whose data you want to sort.
Step 2: Select all data of the sheet to sort the entire Excel sheet using one column value. It means that when column values sort, its associated column data will also be sorted.
Step 3: Now, navigate to the Data tab in the Excel menu bar, where you will get a Sort function option in Sort & Filter group.
Click on this Sort option that will open a popup dialogue box.
Step 5: In this dialogue box, specify/select a column from the list on the basis of which you want to sort the data. For Example, Product to sort the table.
Step 6: Specify the order of sorting either A-to-Z or Z-to-A and click on the OK button. We will select ascending order (A-to-Z).
Step 7: See that the entire table data has been sorted successfully according to your chosen column (Product) and order of sorting. Their associated row data also sorted.
Sort only one column
Sometimes, you only need to sort a single column data, but you do not want to entire table data. So, we will show one column can be sorted. Remember that sorting one column is easy than sorting the entire table.
Step 1: Open your existing Excel sheet to sort the data and select a column that you want to sort.
Step 2: Navigate to the Data tab in the Excel menu bar, where you will get the Sort function option.
Step 3: If you want to sort in lowest to highest sorting order, click on the A-to-Z sorting button to sort.
Step 4: If you want to sort in lowest to highest alphabetical sorting order, click on the Z-to-A sorting button.
Step 5: A popup dialogue box will open when you select the order of sorting, as shown below. If you want to expand the sorting and rearrange the entire table data, mark the Expand the selection option.
Step 6: If you want to sort only selected column data, mark the Continue with the current selection option.
This time, we will sort only a single column value. So, we will select the Continue with the current selection option in this dialogue box and click on the Sort button here.
Step 6: See the below screenshot; only selected column values have been sorted (A-to-Z order), and rest of the column data remained the same.
Sort by Number
Excel not only allows to sort the data alphabetically; it also allows to sort the table data using numeric data column data as well. So, we will sort data by number now. The process of sorting an Excel sheet using the number column is almost the same as sorting a table using string parameter. The means that it is almost the same as text sorting.
The following are some simple steps to sort the data by number, either in ascending or descending order.
Step 1: Create a table, same as ours or open your existing Excel sheet whose data you want to sort.
Step 2: Select the numeric data column to sort that column data as well as the associated column with it. This means the entire table data will be sorted. For example, we will sort this table data based on the quantity of purchased products.
Step 3: Now, navigate to the Data tab in the Excel menu bar, where you will get a Sort function option in Sort & Filter group.
Click on this Sort option that will help to find the values quickly by sorting the data.
Step 5: A dialogue box will open and ask either to expand the selection or continue with the current selection. Here, mark the Expand the selection radio button and click on the Sort button below.
Step 6: Another dialogue box will show where specify/select a column from the list on the basis of which you want to sort the data. For Example, Quantity to sort the table.
Step 6: Now, choose the order of sorting either smallest to largest or largest to smallest from the list and click on the OK button. We will select descending order (largest to smallest).
Step 7: See that the entire table data has been sorted successfully according to your chosen column (Product) and order of sorting. Their associated row data also sorted.
Other columns have also been sorted with the selected column data in descending order of quantity purchased.