Formulas routinely throw up complexities for Excel users to deal with – so having a few hacks under your belt for undertaking relevant tasks will stand you in good stead. 

Here are six formula-related hacks from Excel guru Simon Hurst, a member of ICAEW’s Excel Community Advisory Group and speaker at SESCA – the professional training subsidiary of regional ICAEW society Chartered Accountants South East.

1) The dollar $ign

“This enables you to fix a cell reference to a particular cell, row or column,” Hurst says. “Without doing that in the first instance, some of the other, really useful formula tools on Excel become that much harder to use. For those that know how to fix references it might just seem like second nature, but it can save typing the same formula into hundreds of cells”.

2) ‘IF’ function

Simply put, the straightforward version of this tool enables users to zero in on a value in a cell and say: “If it’s this, do this, and if it’s not this, do that.”

Hurst notes that in recent years, various alternatives have sprung up – such as the IFS (plural) function, whereby users can examine multiple values under multiple conditions. 

Also in this genre of tools is the SUMIFS function, and the ability to summarise a collection of values that match a particular criterion. “For accountants, that may entail looking at a huge list of sales and picking out only sales to the UK, for example.”

3) True or false

Excel formulas do not just provide users with calculations – they can also provide true or false statements, which will enable accountants to automate calculations such as applying tax rates.

Hurst explains: “You can make a statement in Excel, such as – to take a very simple example – ‘One equals two?’ and Excel will return a True or False value. You can then use that value to choose between alternative calculations. So, if you multiply a value by the statement, if that statement is true, you get your value, and if it’s false, you get zero.”

In some cases, Hurst points out, True or False could be used as an alternative shortcut to IF functions.

4) Why VLOOKUP is now an XLOOKUP

If you have multiple columns in a table, the lookup functions can help you return the value from one column by finding a match to a criteria value in another column, Hurst says.

“So, let’s say you want to find out how many products you sold on 13 January,” he says. “You can ask a lookup function to look in the date column for 13 January, then give you the value in the same row from the value column next to it.”

Hurst points out that VLOOKUP caused some catastrophic spreadsheet errors because failing to fully understand how it worked, or just simply forgetting one component of the formula, could lead to VLOOKUP returning what looked like a random match rather than the exact match that you were expecting. Its replacement, XLOOKUP, is a much more flexible and capable function that doesn’t suffer from the same Achilles’ heel and can also be much more efficient.

5) Dynamic arrays

Hurst claims that Dynamic arrays are one of the most significant changes in the way that spreadsheets work since the introduction of Power Query.

Hurst points out that before the advent of dynamic arrays, an Excel formula could just return a value in a single cell so that typing in a range such as A1:A7 without using something like the SUM function, would typically result in an error value because Excel would not understand how to return a range into a single cell.

Now, though, Hurst says: “an Excel formula can expand or ‘spill’ into as many cells as are required to accommodate the range – as long as there were no cells already containing values in the way. If the source range is an Excel Table, then the formula will spill into additional cells to dynamically accommodate the changes in the source range.”

Hurst goes further: “Combined with a range of new functions designed to work with Dynamic Arrays, this feature enables Excel to do more than would previously have been possible and to automate many previously manual operations”

6) LAMBDA

For Hurst, LAMBDA is one of the most advanced tools that Excel has to offer and generally requires some expert knowledge, but it is still helpful for accountants to know about as it is set to become more widely used as more users learn how to make use of it”.

While Hurst calls this a “massive oversimplification”, he explains that essentially, LAMBDA enables users to write programs in cells. One feature that presents a particular advantage to accountants is the facility to encapsulate a chain of calculations, comprised of many cells’ worth of data, into a single formula and make that formula reusable.

Hurst says: “Someone who’s in control of a complex spreadsheet can make it much easier for others to use by enabling them to refer to a very complex calculation, rather than create it from scratch each time.”

In future years, Hurst says, “Not only will more people come across LAMBDA because they’ll inherit spreadsheets in which other people have used it, but recent enhancements are already making it easier to use.”