# Long Excel Formula – 10 Practical Tips to Avoid It

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:

• Hard to debug
• Not easy to write

Follow these 10 handy tips to avoid a Long Excel Formula…

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″)

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?

1. Create a named range
2. Define the name labcost
3. 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…

=+IF(\$A15<1500,0,

IF(\$B15<5000,0,

IF(AND(\$B15>=5000,\$B15<=10000),(\$C1/7),

IF(AND(\$B15>=10001<=100000),\$C1/21,

IF(\$B15>100000,*\$C1/7)))))

## 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:

=FUNCTIONNAME(arg1,arg2,arg3)