Contents
Excel SORT Function
When to Use the Excel SORT Function in Excel
The SORT function1 in Excel is a powerful tool that comes in handy when you need to arrange the data in a specific order. Whether you are dealing with numerical values, dates, or text, the SORT function allows you to organize your data in ascending or descending order, making it easier to analyze and draw insights.
What Excel SORT Function Returns
The Excel SORT function returns a sorted array or range based on the specified criteria. It can be applied to both single columns and multiple columns, providing flexibility in sorting data according to your requirements.
Syntax of Excel SORT Function
The syntax of the Excel SORT function is as follows:
=SORT(array, [sort_index], [sort_order], [by_col])
Input Arguments
Here are the input arguments for the Excel SORT function:
● array: The range or array that you want to sort.
● sort_index: (Optional) The column or row index by which to sort the array. If omitted, the function uses the first column.
● sort_order: (Optional) The order in which to sort the array. Use 1 for ascending order (default) or -1 for descending order.
● by_col: (Optional) A logical value that specifies whether to sort by columns (TRUE) or rows (FALSE). If omitted, the default is FALSE.
Examples of the Excel SORT Function
Here are few examples of using the Excel SORT function in Excel:
Example 1: Basic Sorting
In the following example, you will see that we have used only the first argument of the SORT function, which is an array (A2:D13)
By default, the Excel SORT function will sort the first column of the selected array (A2:D13) in ascending order.
Example 2: Sorting by a Specific Column
As we saw in the example above, just by providing the area argument, it takes the first column and sorts it. But if you have more columns, you can sort the data by specifying the column number, The SORT function below sorts by the fourth column.
Note: Make changes only to the formula in cell F2. Excel will handle the rest.
Example 3: Sorting in Descending Order
Now, we use the sort_order argument (the third part of the formula) in SORT function to put the data in descending order. The formula below sorts the array based on the fourth column, from biggest to smallest.
We used -1 for the sort_order argument to arrange the data in descending order.
Example 4: Sorting on multiple columns
To sort by multiple columns, just provide an array. For example, you can sort first by Country and then by Sales.
Example 5: Advanced Sorting on Multiple Columns
The advanced SORT function below showcases a sophisticated sorting approach. It begins by sorting data based on Country in ascending order, providing a primary level of organization. Subsequently, it fine-tunes the sorting criteria by incorporating Sales in descending order as a secondary parameter.
This two-step sorting process demonstrates the versatility and advanced capabilities of the SORT function in Excel.
Example 6: Conditional Sorting with Excel Filter Function
Suppose you have a dataset containing quarterly sales information for various countries in different regions, and your goal is to extract and sort data specifically for Qtr 2 sales. You can use the following formula:
Let’s break down the formula: =SORT(FILTER(A2:D13, C2:C13=”Qtr 2″), 4)
- FILTER(A2:D13, C2:C13=”Qtr 2″): This part of the formula filters the data in the range A2:D13 based on the condition specified in C2:C13. It selects only the rows where the value in column C is equal to “Qtr 2”. So, it filters out the rows that don’t match the condition, effectively creating a subset of the original data.
- SORT(…, 4): This part of the formula takes the result of the FILTER function and sorts it based on the values in the fourth column D (Sales) in ascending order by default. The number ‘4’ indicates the column number by which to sort.
Extra Notes
● Excel SORT function is exclusively accessible in Microsoft 365’s Excel and Excel for the web. It is not supported in pre-dynamic array versions of Excel (Excel 2019 and earlier) because they do not support array constants. To learn more about how to use Excel for Web for free. Watch 👇 this video
How to Get Microsoft Excel for Free | Free Microsoft Office | Microsoft Office 365
● If the sort_index argument is beyond the valid range, the SORT function returns a #VALUE! error.
● Additionally, if any cells within the spill range are not entirely blank or are merged, a #SPILL! error occurs. To resolve this issue, simply clear any obstructions in the affected cells.
Time to Apply Your Knowledge.
Take a hands-on approach to mastering the Excel FILTER function. Utilize the provided Excel file to experiment and witness its functionality with sample data.
Should you have any questions or stumble upon intriguing insights, don’t hesitate to share your thoughts in the comments. Embrace the learning journey with practical experience!
Conclusion
In conclusion, the Excel SORT function is a valuable tool for organizing and arranging data in a systematic manner. Whether you are dealing with simple lists or complex tables, the SORT function provides a versatile solution for customizing the order of your data.
Remember, exploring and experimenting with the SORT function in different scenarios will enhance your proficiency in Excel. Start incorporating the SORT function into your projects today, and streamline the way you analyze and present your data.
Frequently Asked Questions (FAQs)
Q1: Can the SORT function be used for sorting alphanumeric data?
A1: Yes, the SORT function can handle both numeric and text values, providing a comprehensive sorting solution.
Q2: Is it possible to sort data based on multiple columns?
A2: Absolutely, you can specify multiple columns for sorting.
Q3: What happens if the sort_index exceeds the number of columns in the array?
A3: If the specified sort_index is greater than the number of columns in the array, the function returns a #VALUE! error.
Q4: Can the SORT function be used to sort data in a specific order, such as a custom list?
A4: The SORT function primarily sorts data in ascending or descending order. If you need a custom sort order, additional techniques or helper columns may be required.
Q5: Does the SORT function modify the original data, or does it create a new sorted array?
A5: The SORT function returns a new array with the sorted data and does not modify the original data.
Other Related Excel Functions
Mastering Excel VLOOKUP Function: A Comprehensive Guide to Enhance Your Data Analysis Skills
Mastering Excel FILTER Function: A Comprehensive Guide for Data Filtering
Join me on Instagram, YouTube and WhatApp Channel for your daily dose of valuable tips and tricks! Catch insightful videos that will enhance your knowledge and skills. Don’t miss out – follow me now!