New in Microsoft Excel 2024 (Standard or via 365)

Excel 2024 has a few new features, including 15 new functions with one of these being powerful and trickier to grasp but others being straightforward and handy that will probably be the newcomers that you are most likely to use. The other new features are fairly minimal or niche although one thing that should be apparent is that Microsoft have been working on speeding up Excel across the last few versions.

A couple of big points to note now though are:

If you have Microsoft 365 (what was called Office 365) then you will probably already have the new Excel functions for 2024 now. As always, if you want to share a spreadsheet with users who are not on Excel 2024 (or 365) then do not use the new functions. You need to make sure that your file only uses functions that are compatible with the earliest version of Excel that the people who open your file have.

Here are the new features we believe are most useful.

This is the one that's powerful but tricky to wrap your head around. It's a way of creating your own function by writing a formula that represents it. For example, suppose you pay commission to your staff by taking their sales, minus their cost and multiplying the result by a percentage rate. The formula for this would be (sales-cost)*rate so you might want to create a function called "commission" that used this formula. You would create a named range called "commission" and put =LAMBDA(sales, cost, rate,(sales-cost)*rate) in the Refers To box. To use the function for sales of 3000 and cost of 1560 and a rate of 20%, you would enter a formula =commission(3000,1560,20%) in a cell and out would pop the commission due (288). As with any Excel function, you can enter cell references or other formulas instead of typing in the actual values 3000, 1560 and 20%.

Still clear as mud? We'll be creating a tutorial video with free example downloads soon, so subscribe to our YouTube channel to be the first to know when it's released.

As to why you would use it, it enables you to use the same calculation consistently wherever you want to perform it, without having to go and check what the correct formula is and without making mistakes. It also means that if you want to change the way commission is calculated, in our example, you simply change the formula once in the name manager and it will update everywhere in your file (assuming the new calculation doesn't need extra inputs).

These are new functions for simplifying splitting text strings into parts. For example, suppose you want to extract the rest of the text that appears after the 2nd word in a sentence, your formula would be =TEXTAFTER("This is a short sentence"," ",2). As always, you wouldn't normally type the text itself into the formula, you would normally reference a cell containing it. (The example shown returns "a short sentence") Note that these functions always return text from the start or end of text.

This is a more sophisticated function that spills its results into the adjacent cells, so make sure it has space around it. An example corresponding to the one above would split a sentence into words across a row, but you could use it to split an address into lines, or more likely it would be used to extract each data record and field from an exported text file, which is very handy.

So, if you were to paste the entire contents of an extracted text file into cell A1 and then put the formula

=TEXTSPLIT(A1," | ","
")

into the cell below you'd end up with a grid of data below that. Pretty impressive! (Note that here, each column in the raw data has | as the separator, and each row is on a new line.)

Software-Matters have been experts in bespoke Excel systems since 1995.

Find out more about how we can help you with your project by emailing us via our Contact page or giving us a call on 01747 822616. We give free advice!

If you would like to read more about the different Excel versions follow the links below.

• What's new in Microsoft Excel 2016
• What's new in Microsoft Excel 2013
• What's new in Microsoft Excel 2010
• What's new in Microsoft Excel 2007
• Benefits of using Microsoft Excel

If you enjoyed this page or found it useful, why not tell others about it?