How To Manage Long Formulas

// How To Manage Long Formulas

How to Manage Long Formulas

How long is too long?

By Jeff Robson

long_cat

There’s a general rule of thumb when it comes to Excel formulas: your formulas should be no longer than your thumb!

When you’re designing a spreadsheet, that’s what you should aim for.
But sometimes, you have to break this rule:

  • To make your file more compact
  • To make your formulas more efficient
  • To reduce the complexity of your worksheet

Here are some ideas that will help you when you’re dealing with long formulas: the ones that you’ve written and also those that were written by someone else.

1. Get Help

Don’t rely on Excel’s built-in functionality for reviewing long formulas (Formulas > Formula Auditing section).

These functions are somewhat useful, but generally, they’re more annoying than useful once you go beyond the basics.

Spreadsheet Auditing

If you’re doing anything more than just some very simple checks, get Spreadsheet Detective.

It makes your life so much easier as it identifies the components of a worksheet formula and allows the user to quickly identify and walk through each of these components.

It also allows the user to ‘drill-down’ into the formulas within the precedent ranges of a formula, plus a whole host of other fantastic auditing tools.

We use this whenever we’re doing spreadsheet audits for our clients.

2. Document your Formulas in the Formulas

Use the N function to document your logic and what each part of your formula is doing.

Here are 2 versions of the same formula:

=SUMIFS(Assumptions!D$56:D$108,Assumptions!$A$56:$A$108,AssmStaffScenario, Assumptions!$B$56:$B$108,Calculations!$D29)

=N(“Count how many employees there are in this month, in the scenario selected, for this position”)+SUMIFS(Assumptions!D$56:D$108, Assumptions!$A$56:$A$108, AssmStaffScenario, Assumptions!$B$56:$B$108, Calculations!$D29)

Both are identical but the second version documents what the formula does.

This is particularly useful when you have nested IF statements to help explain what each branch is doing (and help you get the logic right).

3. Space things out

A formula without spacing is much harder to read than one with spacing.
Using the example above, let’s now space this out a bit to make it much easier to read:

=N(“Count how many employees there are in this month, in the scenario selected, for this position”)+
       SUMIFS(Assumptions!D$56:D$108,
                       Assumptions!$A$56:$A$108, AssmStaffScenario,
                       Assumptions!$B$56:$B$108, Calculations!$D29)

It doesn’t impact Excel’s performance but it will impact yours!

So next time you find yourself writing a encyclopedic formula, think about using some of these techniques to help make it more manageable … for both you and anyone else that has to use your spreadsheet.

2017-03-08T03:54:06+00:00Functions|