Dick Kusleika’s Excel formula tips

Courtesy of The Daily Dose of Excel blog (and a helpful tweet from @r1c1), we came across a helpful post from Excel MVP Dick Kusleika on cutting Excel formulas down to size.

The piece was prompted by a request for help with a formula based on a series of IF arguments that ran to several lines of code containing 316 characters. Kusleika cut it down to a third of the size using IF, TRIM and a series of nested SUBSTITUTE arguments. After doing so, he explained a few of the techniques he used and shared them to help other Excel users become better formula writers.

Here are the main tips in his post, but the follow up comments from other readers are just as useful. Find the full post here.

Formula Length
“Making a formula shorter doesn’t necessarily make it better, but usually it does,” comments Kusleika. A longer formula is hardly ever easier to understand than a shorter one – and places fewer demands on your memory if you’re trying to decipher what it’s supposed to do.

Nesting
Functions take arguments (sometimes called parameters) and spit out a return value. Arguments can be many things like numbers, strings, cell references, and even other functions. When a function is used as an argument in another function, it’s called nesting. The result of a function nested within another function is the first argument for the outer function, or as Kusleika explains it, “Excel always solves formulas from the inside out.”

Error Proof Functions
Some functions such as SUBSTITUTE are “error proof” and are useful to help make formulas leaner. TRIM is another function that will remove any spaces from the start and end of a string. You can use TRIM with any string you want and not worry that an error will be returned. “Knowing what functions return in error cases like this can help you write more succinct formulas,” he advises.

Booleans
Booleans are TRUE and FALSE and within IF statements, for example, Excel will expect a Boolean answer and do the conversion for you. So instead of:

=IF(ISERR(FIND("blah","Some Text"))=TRUE, "Not found", "Found")

You could write:

=IF(ISERR(FIND("blah","Some Text")), "Not found", "Found")

Patterns
Look for patterns in your formulas and try to call functions the fewest number of times. “If you find you’re using the same function over and over, ask yourself if you can’t move it more to the outside and call it less,” says Kusleika.

Multi-cell Formulas
“If you’re typing the same sequence over and over in a formula, move that sub-formula out to a new cell and refer to the cell in your main formula,” he advises. VLOOKUP is a classic culprit which people often use twice to avoid calling up an error if it can’t find the value. Putting the VLOOKUP in another cell can simplify your formula.

Further reading
ExcelZone Compendium: Functions and formulae