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…

=+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)