Download the practice workbook 👉 HERE and follow along.
SORT & SORTBY Functions
Using the Excel SORT Function
The Excel SORT function lets you dynamically sort data in Excel using a formula.
Syntax
SORT(array, [sort_index], [sort_order], [by_col])- array: The range or array you want to sort (entire dataset or a part).
- [sort_index]: The column index to sort by (default is 1, the left-most column).
- [sort_order]: Defines the sort order. Enter 1 for ascending (default) and -1 for descending.
- [by_col]: Use TRUE if your data is set up with rows as categories and columns as transactions (default is FALSE).
Basic Example
Suppose you have a dataset with names, entry dates, and salaries. Your task is to sort the list by the yearly salary column in descending order.
First, convert the dataset into an Excel Table. Excel Tables use structured references, making formulas easier to read and maintain. They automatically expand as you add new rows or columns, ensuring your formulas always include the latest data.
To create an Excel Table, click anywhere inside the dataset and use the keyboard shortcut Ctrl + T. This will bring up the “Create Table” dialog box. Ensure the “My table has headers” box is checked, then click OK. This converts the dataset into an Excel Table.
Each table gets a default name, but it’s helpful to rename it for clarity. Click anywhere in the table to open the “Table Design” tab. On the left, you can change the table name. Let’s use “TableSalary”.
You can also adjust the table’s visual style using the “Table Styles” dropdown. To remove the styling, select “None.”
Now, with the table named “TableSalary,” you can sort by the yearly salary column in descending order. We’ll use the Excel SORT function and enter this formula in cell E4:
=SORT(TableSalary, 3, -1)This reads, “Sort the table named ‘TableSalary’ by the third column (Yearly Salary) in descending order.”
Sort By Multiple Criteria
You can also use the SORT function in Excel to sort data by multiple criteria.
For example, let’s say you want to first sort the list by Name in ascending order and then by Salary in descending order.
Formula:
=SORT(TableSalary, {1, 3}, {1, -1})
Explanation
- Curly Brackets: The curly brackets {} are used to define arrays in Excel. In this context, they allow you to specify multiple columns and their respective sort orders.
- {1, 3} indicates the columns to sort by. Here, 1 refers to the first column (Names) and 3 refers to the third column (Yearly Salary).
- {1, -1} indicates the sort order for each column. Here, 1 means ascending order for Names and -1 means descending order for Yearly Salary.
By using arrays, the SORT function can handle multiple sorting criteria in one formula, making it a powerful tool for complex data sorting tasks.
Using the Excel SORTBY Function
The Excel SORTBY function is perfect when you need to sort data by a column that won’t appear in the result.
Syntax
SORTBY(array, by_array1, [sort_order1]…)- array: The range you want to sort. This can be the entire dataset or a part of it.
- by_array1: The array you wish to sort by. This column won’t be part of your result.
- [sort_order1]: The sort order for the column in by_array1. Use 1 for ascending (default) and -1 for descending.
The SORTBY function can handle multiple sorting criteria, like sorting by country, then by region, then by state.
Example
Let’s use the same dataset as before. We want a list of names sorted by Yearly Salary in descending order, without showing the salaries.
=SORTBY(TableSalary[Name], TableSalary[Yearly Salary], -1)
Featured Course
Master Excel’s Essential Modern Functions
UNIQUE Function
How to Use the Excel UNIQUE Function
The UNIQUE function is great for generating lists with unique items from your dataset. Here’s why you might use it:
- Unique List: Get a list where each item appears only once.
- Distinct List: Get a list of items that occur only once in the dataset.
- Auto-Updates: Your unique list updates automatically without needing to refresh a query.
Unlike Pivot Tables, which require manual updates, the UNIQUE function is dynamic and updates automatically when the source data changes.
Syntax
UNIQUE(array, [by_col], [occurs_once])- array: The range from which you want to extract unique items.
- [by_col]: Direction for comparison. Use TRUE to read across columns and FALSE to read across rows (default).
- [occurs_once]: Set to TRUE for items that occur only once (distinct list). Set to FALSE for all unique items (default).
Example 1: Unique List
Given a dataset with regions in the table called “TableDivRev”, you want a unique list of regions:
Formula:
=UNIQUE(TableDivRev[Region])
Example 2: Unique Values with Multiple Criteria
=UNIQUE(TableDivRev[ [Division] : [Region] ] )
Example 3: Creating DISTINCT Lists
Suppose you want to generate a list of apps that only occur once in the dataset. You can achieve this also with the UNIQUE function.
Use the following formula, setting FALSE for the [by_col] argument (since we are reading by rows) and TRUE for the [occurs_once] argument:
Formula:
=UNIQUE(TableDivRev[App], FALSE, TRUE)
This will create a distinct list of apps, with only those that appear exactly once in the entire dataset.
Difference Between Unique and Distinct Values
- Unique Values: In examples 1 and 2, the UNIQUE function returned a list where each item appears only once, regardless of how many times it appears in the dataset. For example, if an app appears three times, it would still appear once in the unique list.
- Distinct Values: In example 3, the DISTINCT list includes only items that occur exactly once in the dataset. If an app appears more than once, it will not be included in the distinct list.
XLOOKUP Function
How to Use the Excel XLOOKUP Function
The XLOOKUP function is the successor to the older VLOOKUP function. XLOOKUP is more flexible and easier to use than VLOOKUP.
Syntax
XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])- lookup_value: The value you want to find.
- lookup_array: The range to search within for the lookup_value.
- return_array: The range to return values from upon finding a match.
- [if_not_found]: (Optional) The value to return if no match is found (e.g., a text message or default value).
- [match_mode]: (Optional) The type of match (0 = Exact match {default}, -1 = Exact match or next smaller, 1 = Exact match or next larger, 2 = Wildcard match).
- [search_mode]: (Optional) The search order (1 = Search first to last {default}, -1 = Search last to first, 2 = Binary search {ascending}, -2 = Binary search {descending}).
Example
Our goal is to select an App from a dropdown list in cell F3. Based on this selection, we want to return:
- The associated Division from column H.
- The associated Profit from column C.
Step 1: Get the Division
In cell F4, use the following formula to get the Division:
=XLOOKUP(F3, I4:I13, H4:H13, "Missing")
💡 Notice that the column from which we are returning data (H) is to the left of the column we are searching (I). This is not possible with VLOOKUP without complex workarounds.
Step 2: Get the Profit
In cell F5, use the following formula to get the Profit:
=XLOOKUP(F3, A4:A13, C4:C13)This formula looks up the selected App in cell F3, searches for it in column A, and returns the corresponding value from column C.
By using XLOOKUP, you can make your data searches in Excel more efficient and less error-prone. For a more detailed guide on using XLOOKUP, click here to read our comprehensive article.
FILTER Function
How to Use the Excel FILTER Function
The FILTER function is a powerful tool for returning multiple results for one or more lookup values. Here’s when you’ll benefit from using it:
- Multiple Results: Unlike VLOOKUP or INDEX/MATCH, FILTER returns all instances of qualifying data, not just the first.
- Automatic Refresh: The filter automatically updates without needing a data refresh.
- Sum or Count: You can use FILTER with SUMIF(S) or COUNTIF(S) for more flexible data analysis.
Syntax
FILTER(array, include, [if_empty])- array: The range you want to include in the result. This can be the whole dataset or a part of it.
- include: The criteria for including records. This is like the test portion of an IF function. For example, B4:B15 > E2 includes records where column B values are greater than E2.
- [if_empty]: (Optional) What to display if no records match the criteria (e.g., “No Data”). If not defined, it shows a #CALC! error when no data matches.
Example
Suppose you have a list of employees in column A and their salaries in column B. You want to list employees earning more than the value in cell E2 (e.g., 90,000).
=FILTER(A4:B15, B4:B15 > E2)
💡 Bonus Tip: Combine the FILTER function with the SORT function to sort the results by salary in descending order.
=SORT(FILTER(A4:B15, B4:B15 > E2), 2, -1)
By using the FILTER function, you can efficiently manage and analyze your data in Excel, making your tasks easier and more dynamic. For more details on using the FILTER function, click here to read our comprehensive guide.
SEQUENCE Function
How to Use the Excel SEQUENCE Function
The SEQUENCE function is great for generating lists and simulating data in Excel. Here’s when you’ll find it useful:
- Generate Index Numbers: Create a list of numbers in order.
- Simulate Dates: Generate dates based on specific intervals.
- Modeling and Simulations: Useful in various data models and simulations.
- Excel Calendar & Loan Amortization Tables: Create calendars and loan tables easily.
- Update Legacy Formulas: Transform old Excel formulas into powerful new ones.
The SEQUENCE function might seem simple on its own. However, its true power emerges when combined with other functions. Let’s explore its potential with some examples.
Syntax
SEQUENCE(rows, [columns], [start], [step])- rows: The number of rows to return.
- [columns]: The number of columns to return (default is 1).
- [start]: The first number in the sequence (default is 1).
- [step]: The increment for each subsequent value (default is 1).
Basic Example
Generate a list of numbers from 1 to 10.
=SEQUENCE(10)
Example: Create a two-column list of numbers from 1 to 20.
=SEQUENCE(10, 2)
Practical Example
Let’s combine the SEQUENCE function with the UNIQUE function. Suppose we have a dynamic, unique list using:
=UNIQUE(C4:C22)
We want to add item numbers to the left of this list, which updates as the list changes. Use the SEQUENCE formula in cell F4:
=SEQUENCE(COUNTA(G4#) )Here, the COUNTA function counts the items in the unique list. The # symbol refers to the entire spill range of the dynamic array starting from cell G4. This ensures that as the unique list changes, the SEQUENCE function updates automatically.
By mastering the SEQUENCE function, you can greatly enhance your Excel skills. For a detailed guide on using the SEQUENCE function, click here to read our comprehensive article.
Download the Workbook
Enhance your learning experience by downloading our workbook. Practice the techniques discussed in real-time and master the new Excel Functions with hands-on examples. Download the workbook here and start applying what you’ve learned directly in Excel.
Featured Bundle
Black Belt Excel Bundle
Leila Gharani
I’ve spent over 20 years helping businesses use data to improve their results. I've worked as an economist and a consultant. I spent 12 years in corporate roles across finance, operations, and IT—managing SAP and Oracle projects.
As a 7-time Microsoft MVP, I have deep knowledge of tools like Excel and Power BI.
I love making complex tech topics easy to understand. There’s nothing better than helping someone realize they can do it themselves. I’m always learning new things too and finding better ways to help others succeed.
