1. TEXTSPLIT Function (Introduced in 2022)
The TEXTSPLIT() function is one of the most exciting new features for handling text data in Excel. It allows users to split a text string into multiple components based on a delimiter or pattern. This is particularly useful for breaking down large blocks of text, like splitting addresses, names, or any string of data that follows a consistent structure.
How to Use TEXTSPLIT
To use the TEXTSPLIT() function, you specify the text to be split and the delimiter that separates the text components. You can also define how the text should be split (by rows or columns).
Example:
=TEXTSPLIT("John, Mary, Alex", ", ")
This formula will return three values:
John
Mary
Alex
Use Case
Splitting customer names into first and last names.
Extracting tags or keywords from descriptions.
---
2. LET Function (Introduced in 2020)
The LET() function helps simplify complex formulas by allowing you to define and store intermediate calculations or values within a formula. This reduces redundancy, making formulas more readable and efficient. It’s especially useful when you need to reuse a value multiple times within a single formula.
How to Use LET
With LET(), you assign names to calculation results and then use them in subsequent parts of the formula.
Example:
=LET(x, 10, y, 5, x + y)
This will return 15 by defining x as 10 and y as 5, then calculating x + y.
Use Case
Simplifying nested formulas.
Improving performance by reducing repeated calculations.
---
3. XLOOKUP Function (Introduced in 2020)
One of the most anticipated functions introduced in Excel, XLOOKUP(), replaces the older VLOOKUP() and HLOOKUP() functions. It offers more flexibility, better error handling, and improved performance when looking up data in a table or array. With XLOOKUP(), users can search both horizontally and vertically, and the function automatically handles missing data more gracefully.
How to Use XLOOKUP
The XLOOKUP() function requires you to provide the lookup value, the array to search, the return array, and optional parameters for handling errors.
Example:
=XLOOKUP("John", A2:A10, B2:B10, "Not Found")
In this example, the function searches for "John" in the range A2:A10 and returns the corresponding value from the range B2:B10. If "John" is not found, the function will return "Not Found."
Use Case
Replacing VLOOKUP/HLOOKUP with a simpler, more powerful solution.
Searching for values in larger datasets where traditional lookups might fail.
---
4. FILTER Function (Introduced in 2020)
The FILTER() function is a game-changer for working with dynamic ranges. It allows users to filter data based on specified criteria and returns a dynamic array that updates automatically when the underlying data changes. It’s much more powerful than the traditional IF() function or manual filtering options, especially for handling large datasets.
How to Use FILTER
The function takes the range to filter, the condition (or criteria), and optionally, a value to return if no results are found.
Example:
=FILTER(A2:C10, B2:B10="Apple")
This formula will return all rows from A2:C10 where the value in column B is "Apple."
Use Case
Extracting rows that meet specific criteria.
Creating dynamic reports that update when new data is added.
---
5. UNIQUE Function (Introduced in 2020)
The UNIQUE() function extracts distinct or unique values from a range or array. This is incredibly useful when you need to remove duplicates or just get a list of distinct values from a dataset, especially when working with large tables or data that frequently changes.
How to Use UNIQUE
The UNIQUE() function requires a range or array as input and returns the unique values from that range.
Example:
=UNIQUE(A2:A10)
This will return only the distinct values from the range A2:A10, removing any duplicates.
Use Case
Getting a list of unique customers, products, or other entities from a database.
Simplifying data analysis by removing duplicates from large datasets.
---
6. SEQUENCE Function (Introduced in 2020)
The SEQUENCE() function generates an array of sequential numbers based on the specified number of rows and columns. It’s ideal for creating numbered lists, generating time periods (like months or days), or filling cells with incrementing data without manually entering each value.
How to Use SEQUENCE
The function can generate a series of numbers either in a single row or multiple rows and columns. You define the number of rows, columns, and optionally, the starting number and increment.
Example:
=SEQUENCE(5, 1, 1, 1)
This generates a vertical sequence of numbers starting from 1 with an increment of 1, producing the following values:
1
2
3
4
5
Use Case
Creating series of dates or numbers automatically.
Generating sequential data for time-based calculations or reports.
---
7. SORT and SORTBY Functions (Introduced in 2020)
The SORT() and SORTBY() functions allow users to sort data dynamically. SORT() sorts data in ascending or descending order, while SORTBY() sorts data based on one or more columns or arrays.
How to Use SORT and SORTBY
SORT() sorts a range or array based on the order specified (ascending or descending).
SORTBY() sorts a range or array based on one or more other ranges.
Example using SORT():
=SORT(A2:A10, 1, TRUE)
This sorts the values in A2:A10 in ascending order.
Example using SORTBY():
=SORTBY(A2:B10, B2:B10, -1)
This sorts the data in A2:B10 by column B in descending order.
Use Case
Sorting data dynamically when the data set changes.
Sorting multiple columns based on a different sorting criterion.
---
8. RANDARRAY Function (Introduced in 2020)
The RANDARRAY() function generates an array of random numbers based on the size and the range you specify. This is useful for creating random datasets, such as generating random sales numbers or even randomizing a list of names.
How to Use RANDARRAY
You can specify the number of rows and columns for the array and set the minimum and maximum values for the random numbers.
Example:
=RANDARRAY(5, 1, 1, 100, TRUE)
This generates a 5x1 array of random integers between 1 and 100.
Use Case
Generating random data for simulations or testing.
Randomizing lists or samples.
---
9. LAMBDA Function (Introduced in 2021)
The LAMBDA() function allows you to create custom, reusable functions in Excel. With LAMBDA(), you can define your own function using Excel formulas, and then call it just like any built-in function. This is ideal for users who need to implement complex calculations or logic frequently and want to avoid repetitive formula writing.
How to Use LAMBDA
You define a LAMBDA() function with input parameters and the formula for the custom calculation.
Example:
=LAMBDA(x, y, x + y)(5, 3)
This creates a custom function that adds two numbers and returns 8.
Use Case
Creating reusable formulas for complex calculations.
Improving formula readability and reducing errors by encapsulating logic.
---