Avoid Long Formulas
“The more the words, the less the meaning [Ecclesiastes 6:11]”
Most of you fervent Excel users will likely be more comfortable with numbers than with flamboyant expressions, but this topic really calls for a Biblical citation. Even Thomas Jefferson, 19th century president of the United States, remarked: “The most valuable of all talents is that of never using two words when one will do.”
Keep Formulas Short and Simple
Whenever possible, keep your Excel formulas short and simple. Long formulas are harder to read, harder to understand, difficult to check and tough to improve. You might say, “in my situation I really need a long formula!” and we hear you. You have to lookup a value, multiply it only if the result is a specific value, round the answer, and then do something else entirely in case no match was found, on and on it goes. Still we value simplicity and conciseness above most things.
What is a Long Formula?
As a rule of thumb, we state that any formula longer than one line is too long. Yes, you can easily drag the formula bar handle to make even multiline formulas visible at a glance, but at that point it really tends to have lost its comprehensibility anyway.
We know of three methods to avoid long formulas:
- Look for functions that better suit the needs of the calculation. Take for instance the function SUBTOTAL(), which gives you the ability to do (filtered) aggregations, a task that would require lots of terms otherwise. Many users overlook this option.
- Split up the formula into partial calculations and place these calculations into their own cells, rows, or columns. This greatly enhances the overview. Unfortunately, research shows that people don’t make fewer mistakes in chains of shorter formulas as opposed to one very long formula. That may be a reason to take this advice with a grain of salt.
- Review your data structure! Very often, long formulas are necessary simply because of a dodgy setup in an earlier stage. Problems like disorganized input data or incomplete tables are then “compensated” for by large formulas.
Software Developer Wisdom
Is a long formula absolutely unavoidable? Then at the very least we can borrow some software developer wisdom: split up the formula into logical parts. This can be done by using ALT + Enter or even better you can use the PerfectXL add-ins feature, “formula expander,” which does this for you.
What PerfectXL Can Do For You
Curious what the longest formula in your spreadsheet is? Create an account on PerfectXL, upload your spreadsheet and on the first page of the Excel sheet validation you’ll see the longest formula. For many a somewhat shameful moment, again you can use the formula Expander to split this formula into logical parts. Additionally, PerfectXL’s risk overview will also point you to problems with regards to “Conditional complexity” (too many nested IFs), “Many different operations” and “Referencing many cell groups”; all of these are examples of difficult to read, tricky to follow, tough to check and hard to improve formulas.
We also have a new “Expand Formula” feature which can be used to expand formulas and make them easier to read, maintain, and edit. Read more about that tool here.