How long should your Excel formula be?

Use these tips to save time and reduce the risk of error in your Excel formula. Long formulae are difficult to understand five minutes after you’ve written them, let alone five months later. The general rule of thumb is that an Excel formula should be no longer than your screen is wide, but how can we achieve this when calculations can be so complex? Follow these tips below and you’ll be able to keep your formulae short and sweet.

1. No more than one IF statement

IF statements are great. They are the most commonly used method to transfer our thought process into Excel logic, but if we use too many at once, the cell becomes impossible to understand at a glance. The best alternative is to use an IFS function, which can replace nested IF functions, as can separating our logic out into multiple rows or columns.

2. No repeated operations in any Excel formula

If you are doing the same thing twice in a single formula, don’t. Separate that element out into another cell if need be and refer back to it. 

Your model will be faster, and your formula easier to understand. In the latest version of Excel, you can even use the LET function to define the calculations you will be using.

3. Remove the sheet name if the cell is on the same sheet as your formula

For example, we want to refer to two cells, one on the active sheet and one on another sheet. If we reference the other sheet first, Excel automatically states the active sheet name within the formula.

Remember to delete the sheet name: it’s not needed and it serves only to make your formula longer.

4. Use built-in functions where you can to avoid writing long formulae

Excel has a wealth of lesser-known functions, so it could be worth considering whether you’re replicating work that an existing function could do for you.

A common example of this problem is calculating depreciation manually when Excel’s SLN, SYD, DB functions could be used.

5. Avoid External Links

External links have many reasons to be avoided, but one of them is that your formulae will just be too long if each cell reference is prefixed with a long file path.

Wrapping Up

Keeping your formulae short is a great way to make sure your model is robust and easily understood, particularly if you’re building a model that’ll be used by others who may make their own additions. It’s these tips that can keep your modelling practices streamlined and ultimately save you time and money.

For in-depth training on applying the modelling principle of Simplicity, why not consider our Excel Best Practice Course. You can also see our full list of comprehensive courses here.