Top Excel formulas of 2024

Excel’s latest version comes with some exciting new features and enhancements.

In this blog, we’ll get to know six new Excel formulas that will help you save time in Excel, along with some useful resources to master them. From XLOOKUP to the brand-new text and array formulas, this page will give you the tools to improve your everyday life in Excel.

TEXTSPLIT

TEXTJOIN has been around for a little while as a text-combining formula… but have you met TEXTJOIN’s new counterpart: TEXTSPLIT?

TEXTSPLIT allows you to split text into multiple columns/rows. Like TEXTJOIN, this new Excel feature uses a given delimiter (i.e. space or comma) to separate the text over the desired cells.

Get familiar with TEXTSPLIT with this helpful content:

• In this blog from Ablebits, Alexander Frolov gives an in-depth tutorial on TEXTSPLIT: TEXTSPLIT function in Excel.
• This blog by Excel Campus compares the merits of TEXTSPLIT to TEXTBEFORE and TEXTAFTER: Best Way to Split Text in Excel.

TEXTBEFORE and TEXTAFTER

TEXTBEFORE and TEXTAFTER are great tools if you want to extract text from the start or end of a cell. TEXTBEFORE pulls out all text that comes before a given delimiter, while TEXTAFTER does the same with text after that delimiter.

Learn how to leverage these new Excel formulas with these short blogs below:

• This Excel-Exercise blog shows you how to use TEXTBEFORE and TEXTAFTER in just two minutes: TEXTBEFORE and TEXTAFTER.
• Another useful article by Alexander Frolov on how to use TEXTBEFORE: Excel TEXTBEFORE function.

TAKE and DROP

These new Excel features allow you to keep (TAKE) or remove (DROP) a given number of columns/rows from the start or end of an array.

• These tips from Microsoft Support will help you master them in minutes: TAKE function and DROP function.

CHOOSECOLS and CHOOSEROWS

These brand new Excel formulas are an easy way of extracting specific columns (CHOOSECOLS) or rows (CHOOSEROWS) from a range of datasets, whether they’re non-contiguous or right next to each other.

Check out these links to get to grips with CHOOSECOLS and CHOOSEROWS:

• This blog from Get Digital Help gives a quick overview of CHOOSECOLS: How to use the CHOOSECOLS function.
• These Microsoft Support pages are also good for mastering the basics: CHOOSECOLS and CHOOSEROWS.

VSTACK and HSTACK

VSTACK and HSTACK allow you to combine two or more sets of arrays vertically (VSTACK) or horizontally (HSTACK). Better yet, these new Excel functions even work with dynamic arrays and ranges with large source sizes!

• For a quick tutorial, these guides from ExtendOffice both go into a lot of detail on each new function: Excel VSTACK function and Excel HSTACK function.

XLOOKUP

If you’re an Excel aficionado, you must know the XLOOKUP formula. It’s been available in Office365 and Excel for web since 2020, but we still can’t get enough of this feature in 2022! XLOOKUP is a powerful look-up function that can go far beyond the limitations of VLOOKUP and the INDEX and MATCH functions.

Interested in learning more? Check out these articles below:

• This helpful blog by Inferogroup covers exactly why XLOOKUP is a more efficient function than VLOOKUP: XLOOKUP – The New Improved VLOOKUP.
• This guide from ExcelKid covers 5 common problems with XLOOKUP and how to solve them: 5 Reasons why XLOOKUP is Not Working.
• If you want to go further, you can also watch this video tutorial on XLOOKUP by LinkedIn Learning: Using the XLOOKUP function.

BONUS: Top Excel formulas videos 2024

If you prefer a video tutorial, we’ve also attached our favourite guide to new Excel formulas in 2024 here:

Thanks to Samina Ghori, you can get to grips with these new Excel functions in just 15 minutes.

For more efficiency tips and tricks for Excel in financial services, including how to link Excel data to PowerPoint or reduce your Excel file size, visit our Knowledge Hub.

What’s your favourite new Excel function? Tell us in the comments below!