Avoid long formulas in Excel
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.
“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
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.
When is a formula considered ‘long’
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 Excel 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.
Learn from software developers
Is a long formula absolutely unavoidable? Then at the very least we can borrow some wisdom from software developers: split up the formula into logical parts. This can be done by using ALT + Enter.
