Quick Links
Summary
To use the Excel SORT function, insert the following formula into a cell: SORT(range, index, order, by_column). The SORT function will sort your data without disturbing the original data set.
While Microsoft Excel offers a built-in tool for sorting your data, you may prefer the flexibility of afunction and formula. We’ll show you how to use the Excel SORT function with helpful examples.
The benefit of using the SORT function is that you cansort the datain a different spot. If you want to manipulate the items without disturbing the original data set, you’ll like the sorting function in Excel. However, if you prefer to sort the items in place, you shoulduse the sort featureinstead.
The syntax for the Excel sort formula is
where only the first argument is required.
If you’re interested in using the optional arguments, here’s how they work:
Now, let’s look at examples using the SORT function in Excel.
Use the Excel SORT Function
You can use the SORT function for a range or array in Excel. Again, this does notsort your itemsin place but in the spot where you enter the formula.
Related:How to List and Sort Unique Values and Text in Microsoft Excel
For a basic example, we’ll simply sort the items in cells A2 through A6 using the defaults for the optional arguments:
To sort a wider range, we’ll include the cells B2 through B6 as well:
As you can see, the items remain coupled with their attributes.
Now we’ll sort our range by the second column rather than the first. So, we enter 2 for theindexargument:
you’re able to see that our items are sorted in ascending order by the second column, with Green first and Yellow last.
Next, we’ll use an example with theorderargument and sort our array in descending order by including -1:
Notice that we leave theindexargument empty because Excel uses the first row and column by default. As intended, we have Tangerine first and Apple last.
To sort in descending order by the second column, you’d use this formula:
Here, we include a 2 for theindexargument and a -1 for theorderargument. As expected, we see Yellow first and Green last.
For one final example, we’ll include a value for each argument so you may see how they all work together. We’ll enter a larger array of A2 through C6, a 3 to sort by the third column, a 1 for ascending order, and False for sorting by row direction.
Our items are sorted by the Rating column and, like the other examples above, stay with their attributes.
By using the Excel SORT formula, you may get a different view of your data based on the order you want to see your items. This makes it a handydata analysis tool.
For more, check out how tosort by coloror how tosort by datein Excel.
Basic·Budgeting·Data Entry·Logical·Text·Time and Date
Explained
Copying Formulas·Evaluating Formulas·Finding Functions·Fixing Formula Errors·Functions vs Formulas·Comparing Lookup Functions·Locking Formulas·Structuring Formulas·Translating Formulas
