Sorting in Excel

Sorting in Excel is the process of rearranging data within a worksheet or table based on certain criteria, such as numerical order, alphabetical order, date order, or custom criteria. Let us read more about it in this blog.

Excel’s SORT function is useful for sorting data based on different criteria. You can choose the range of data, the main row or column for sorting, the order, and whether to sort by row or column. 

Data is the fundamental basis of spreadsheets, so after you have entered it, you must be able to rearrange it to your liking. The SORT function is a search and reference function in MS Excel that enables sorting the contents of a range of matrices based on four arguments: 

• range
• main row or column
• ascending or descending order
• sort direction by row or column. 

In this article, we will explain some ways or criteria that you can use to order your databases and be able to manage them effectively. 

Must Read – What is MS Excel?

The SORT function has the following syntax:

=SORT(array, sort_index, sort_order, by_col)

Where,

array = Range or array of cells to be sorted.

sort_index = Specifies the column or row to sort by (optional argument). If this argument is omitted, the first column is sorted by default.

sort_order = Optional argument specifying the sort order. The default sort order is ascending, but you can specify descending by typing “-1”.

by_col = Specifies whether the sort should be performed by columns (TRUE) or by rows (FALSE). The default is by columns. It is an optional argument.

Methods of Sorting in Excel

Here’s a breakdown of the methods of data sorting in Excel – 

• Sorting Data from the Home Tab
• Sorting Data from the Data Tab
• Date Sorting in Excel
• Data Sorting by Color

Must Explore – MS Excel Courses

Sorting Data from the Home Tab

Sorting data in Excel from the “Home” tab involves the sorting options available in the Excel ribbon under the “Home” tab. This method allows you to sort data without accessing the “Data” tab. Here are the steps to sort data from the “Home” tab.

1. Open the spreadsheet containing the data you want to sort.

2. Click on a cell or the cell range within the column you want to sort.
3. Go to the “Home” tab in the Excel ribbon. Hover on to the Editing => Sort & Filter

4. To sort the data in ascending order (A to Z or smallest to largest), click the “Sort A to Z” button (represented by an “A to Z” icon) to arrange the data in ascending order based on the selected column.

5. To sort the data in descending order (Z to A or largest to smallest), click the “Sort Z to A” button (represented by a “Z to A” icon).  

6. Excel may prompt you to expand the selection if your data has headers. Ensure the “Expand the selection” option is selected to sort the entire range, including headers. Click “Sort” to confirm your choice.

Sorting Data from the Data Tab

The option of sorting data from the “Data” tab in Excel is available in Excel’s “Sort & Filter” group. The Data tab involves using the more advanced sorting options. This method gives you greater control over sorting criteria and allows you to sort data with multiple levels of sorting. Here are the steps to sort data from the “Data” tab:

1. Launch Microsoft Excel and open the spreadsheet containing the data you want to sort.

2. Click on a cell within the column you want to sort.

3. Go to the “Data” tab in the Excel ribbon. Locate and click the “Sort & Filter” option.

4. You will get a prompt – Expand the Selection. Click Sort.

5. You can select the column in which you want to sort the data. Pick your preference and click OK.

6. Since the calorie data is numeric, Excel will ask you to sort the data from Largest to Smallest or Smallest to Largest. Choose the preferable option.

Date Sorting in Excel

You can also sort the date range using the Sort function. Here is how – 

1. Select any date of your data set.

2. Go to Sort & Filter and select A to Z or Z to A. 

3. Your dates are sorted accordingly

Sorting by Color 

1. Go to the header of the data range you want to sort by cell color. Apply Filter.

2. Right-click on the filter of your header and click the “Sort by Color” option.

3. Choose a column corresponding to the cell color you want to sort by. The column you select should contain the same background color for the cells you want to sort together.

Key Takeaways

• Excel allows you to sort data in ascending (A to Z, Largest to Smallest) or descending (Z to A, Smallest to Largest) order.
• You can sort data by single or multiple columns to create more complex sorting criteria.
• Excel offers custom sorting where you can define your own sorting rules.
• Excel understands numeric values and sorts them accordingly, not just alphabetically.
• You can sort dates chronologically, and Excel recognizes date formats.
• Text is sorted alphabetically, and it’s case-insensitive by default.
• You can sort by cell color or icon in conditional formatting.
• Ensure you have headers in your data before sorting to maintain context.
• Pivot tables can be sorted within the pivot table options.
• Sorting data can affect the order in charts, so be aware of this when charting sorted data.
• To avoid unexpected results, format numbers as text if you need to sort them as text.

Read More in MS Excel