Avoid Long Formulas
“The more the words, the less the meaning [Ecclesiastes 6:11]”
Most of you fervent Excel users will likely be bigger friends with numbers than 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. “But in my situation I really need a long formula!” we hear you think. You have to lookup a value, multiply in that exceptional case, round the answer, do something else entirely in case no match was found, and so on and so forth.
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. 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 disorganised 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: create space by making the formula bar big, then split up the formula into logical blocks using ALT-Enter. You can use spaces to align nested calculations. That should make it a lot easier to read.
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 🙂
In addition, 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 ‘em examples of difficult to read, tricky to follow, tough to check and hard to improve formulas.