Javatpoint Logo
Javatpoint Logo

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.

How to sort in Excel

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
How to sort in Excel

Step3: Now, press the Enter key and see the sorted result has been automatically pasted into new cells (D and E).

How to sort in Excel

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
How to sort in Excel

Step 2: Now, press the Enter key and see the sorted result has been automatically pasted into new cells (D and E).

How to sort in Excel

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.
How to sort in Excel

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.

How to sort in Excel

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.

How to sort in Excel

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.

How to sort in Excel

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.

How to sort in Excel

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).

How to sort in Excel

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.

How to sort in Excel

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.

How to sort in Excel

Step 2: Navigate to the Data tab in the Excel menu bar, where you will get the Sort function option.

How to sort in Excel

Step 3: If you want to sort in lowest to highest sorting order, click on the A-to-Z sorting button to sort.

How to sort in Excel

Step 4: If you want to sort in lowest to highest alphabetical sorting order, click on the Z-to-A sorting button.

How to sort in Excel

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.

How to sort in Excel

Step 6: If you want to sort only selected column data, mark the Continue with the current selection option.

How to sort in Excel

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.

How to sort in Excel

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.

How to sort in Excel

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.

How to sort in Excel

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.

How to sort in Excel

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.

How to sort in Excel

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.

How to sort in Excel

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.

How to sort in Excel

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).

How to sort in Excel

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.

How to sort in Excel

Other columns have also been sorted with the selected column data in descending order of quantity purchased.






Youtube For Videos Join Our Youtube Channel: Join Now

Help Others, Please Share

facebook twitter pinterest

Learn Latest Tutorials


Preparation


Trending Technologies


B.Tech / MCA