A Long Excel Formula is really a headache; especially in the long term when you forget its purpose or because it is impossible to reach a gone-author.
In brief, a long formula may be correct but it is:
- Difficult to read/modify
- Hard to debug
- Not easy to write
Follow these 10 handy tips to avoid a Long Excel Formula…
1. Increase Your Knowledge about Excel Functions And Formula Coding
This guideline may sound quite obvious but it is the most organic way to create cleaner solutions. Here are two examples:
- This formula is evidently correct: = A1+A2+A3+A4+AN but this one is correct and shorter =SUM(A1:AN)
- This formula: =A1*B1+A2*B2+A3*B3+A4*B4+A5*B5+A6*B6 can be written this way: =SUMPRODUCT(A1:A6,B1:B6)
The more you learn the more elegant formulas you will be able to conceive and write.
A final example: you cannot shorten below formula by using Math or other Functions…=IF(F$1=”Shift 1″;IF($B2<16;IF($C2>16;16- $B2;$C2-$B2);0);IF(F$1=”Shift 2″;IF($C2<16;0;IF($B2>=16;$C2-$B2;$C2- 16))))
But you may compact it to this one: =HOURSPERSHIFT(B2;C2;2) by using Excel Custom Functions (Which requires more knowledge).
What kind of knowledge may help you to write cleaner formulas? Here you have some directions:
- Exploring Excel Functions you don’t use (this way you build an mental-tools-inventory to pick functions from)
- Deepening your understanding of Functions you actually use. There is always more info or new ways to see tools
- Learning how Array formulas work
- Learning to use Logical and lookup/reference functions: IF, VLOOKUP, INDEX and MATCH, etc
- Learning to build Excel custom functions: VB and VBA
- Diagramming and mapping skills: flowcharts and mind mapping
Finally, you will find this article inspiring: Knowledge makes everything simpler – 4th rule of simplicity
2. Prune a Long Excel Formula Constantly: Use Named Ranges, replace + with SUM, Remove Unnecessary/Obvious Items
Focus on improvement each time you face an awful solution, ask yourself how this part can be enhanced?
Here are some tips:
- Replace the math in formulas with a corresponding function if available: replace + with SUM, replace the calculated mean with AVERAGE function, etc
- Break links of consolidated Workbooks
- Consolidate partials-picks-formulas with consolidated values, see example below:
You can replace this construct:=’C:2009 BudgetReports[M1.xls]M1 REPORT’!F67+’C:2009 BudgetReports[M2.xls]M2 REPORT’!F67+’C:2009 BudgetReports[M3.xls]M3 REPORT’!F67+’C:2009 BudgetReports[M4.xls]M4 REPORT’!F67+’C:2009 BudgetReports[M5.xls]M5 REPORT’!F67+’C:2009 BudgetReports[M6.xls]M6 REPORT’!F67+’C:2009 BudgetReports[M7.xls]M7 REPORT’!F67+’C:2009 BudgetReports[M8.xls]M8 REPORT’!F67+’C:2009 BudgetReports[MD.XLS]MD REPORT’!F67+’C:2009 BudgetReports[M9.xls]M9 REPORT’!F67+’C:2009 BudgetReports[M10.xls]M10 REPORT’!F67
By this other one:
=’C:2009 BudgetReports[MTOTALS.xls]MTOTALS REPORT’!F67
3. Limit the Nesting Level by Adding Intermediate Steps Through Helper Columns
A complex formula may have up to 64 functions nested, up to 255 arguments per function but limited to 8,192 characters of content length (Excel 2007); this may cause abuse of users.
Here’s how to avoid deep nesting: Evaluate formulas in separate cells.
For example, this formula: =IF(AND(C2=”HP”,B2>10000,B2<100000,TEXT(YEAR(D2),”0″)=”2005″),6%,”Not applicable”) could be break into this manageable version: =IF(D2,6%,”Not applicable”)
Where D2 contains this formula: =AND(C2=”HP”,B2>10000,B2<100000,TEXT(YEAR(D2),”0″)=”2005″)
4. Don’t Use External Worksheets/Workbooks Links in Your Arguments
Keep your spreadsheet free of external references as much as you can. If you need to bring data from outside, here are two solutions:
- Create input sheets instead of an input workbook
- Use model import sheets when you import data from external source
5. Open the External Source Workbooks when You Have Long Excel Formulas with Links
If you already have a workbook with links, open the source books so you can turn this long formula…
=’C:First tunneling study (soft conditions)EquipmentTechnical DataCaterpillarLabor hoursAtlas Copco ST1030[Labor costs.xls]Rocks I and II’!$H$13
Into this other one: =’ [Labor costs.xls]Rocks I and II’!$H$13
If the source file is gone, you can break the link.
That’s not all, you can even shorten the above formula more, see how to do it below…
6. Use Worksheet/Workbook Named Ranges when Linking is Necessary
You can shorten this formula:=’ [Labor costs.xls]Rocks I and II’!$H$13
To this one: =labcost
How do you do it?
- Create a named range
- Define the name labcost
- Set the range “Refers to” to =’ [Labor costs.xls]Rocks I and II’!$H$13
7. Use Array Formulas (CSE) Instead of a Long Excel Formula
Array formulas avoid you to create long versions of Excel FormulasFor example, you can replace this one: =C2*B2+C3*B3+C4*B4+C5*B5+C6*B6+C7*B7+C8*B8+C9*B9+C10*B10
With this other one: =SUMPRODUCT(C2:C10;B2:B10)
The above formula is a kind of built-in array formula, you can create your own versions by pressing CTRL+SHIFT+ENTER. Read about Excel Array Formulas
8. Wrap Formula by Pressing ALT + ENTER in the Formula Bar
This method makes your formula shorter but for the sake of reading.
Go the Excel Formula Bar, place at the correct position and press ALT + ENTER to break each line, see example below…
9. Use Named Ranges
Naming ranges does not necessarily make a formula shorter but makes it more readable and digestible.
In short, replace references with Named Ranges but keep names short.
10. Write your own Excel UDF
Sometimes long formulas are inevitable, for these cases the best approach is to create your own function.
This is the most efficient way to shorten a formula because you diminish it to its most fundamental construct, see illustration below: