Home » Excel-Formulas

Although many people use the term 'Excel Formulas' to refer to Excel's Built-In Functions, the term 'Excel Formula' actually encompasses a wider range of operations in Excel.

Therefore on this site, when we use the term 'Excel Formulas' (or 'Excel Formulae'), we are generally referring to any combination of Excel Operators and/or Excel Functions. This may be as simple as a basic addition (eg. "=A1+B1"), or it could be a complex combination of Excel Operators and multiple nested Excel Functions, which extends over several lines of your Excel formula bar.

Before creating complex Excel Formulas, it is advised that you have a firm understanding of the basic features of Excel, which are discussed in the Basic Excel Section of ExcelFunctions.net. Also, you should be aware of function nesting limitations and the rules of precedence of mathematical operators. These are discussed below:

Function Nesting Limitations

It is likely that you will, at some time, want to nest Excel Functions. Commonly, the Excel IF Function is nested, as in the following example, which gives the values 1 - 10, in cell A1, a rating from Very Low to Very High :

In the above example, there are a total of 4 IF functions which are, in turn, nested to levels 1, 2, 3 and 4. In Excel 2003 you can only nest functions up to level 7. However, in Excel 2007, you can nest up to 64 levels.

Mathematical Operators

The Mathematical Operators used in Excel are shown in the table on the right.

The power operator has the greatest precedence, followed by the multiplication and division operators, and then the addition and subtraction operators. Therefore, when evaluating Excel Formulas that contain more than one mathematical operator, the power operators are evaluated first, followed by multiplication and division operators. Finally, the addition and subtraction operators are evaluated.

However, brackets can be used to override these precedence rules. If a part of a formula is encased in brackets, this will be evaluated before being subject to any other operators or functions.

Tips for Building Excel Formulas

1. Use Brackets

Many errors in Excel formulas are caused by a failure to fully understand the order in which mathematical operators are evaluated.

If in doubt, use brackets to isolate parts of the formula that you need to be executed first.

2. Break Complex Formulas Up

If you are getting confused with a long, complicated formula, break the formula down by evaluating part of it in a different cell. For example, instead of typing the following:

Type the function

into cell B1, and then the main formula becomes

This has the advantage of being less prone to errors (due to avoiding the duplication of the VLOOKUP function call), as well as simplifying the formula by breaking it down into 2 steps

3. Insert Line Breaks to Clarify Complex Formulas

When you are using creating complex Excel formulas, it is easy to quickly become confused and lose count of brackets. One way to clarify your Excel formulas is to insert line breaks between different parts of a formula. For example, the following formula is difficult to follow:

Note: Use ALT-ENTER to insert a line break in an Excel cell (ie. press the ALT key, and while this is pressed down, press the ENTER key)

However, if line breaks are inserted between each of the 'if' functions, the whole formula becomes much clearer:

if(A1>8,"Very High", if(A1>6,"High", if(A1>4,"Average", if(A1>2,"Low","Very Low") ) ) )

Excel Formulas - Practical Examples

The following links show practical examples in which Excel Formulas are used to produce useful working spreadsheets.

For more examples of Excel formulas, visit the Microsoft Office website (link opens in a new window)

Automatic Calculation

By default, Excel will recalculate the formulas on your spreadsheet every time you make a change to any of the cells that the formulas depend on. This is useful, as it means that your spreadsheet is always up-to-date.

However, if you are working with large amounts of data, this recalculation can take several seconds. In this case, it is not practical to allow the spreadsheet to freeze while it updates its formulas, every time you enter a new value. - You can quickly end up in the position where you simply cannot continue to work on the spreadsheet.

If you find yourself in this position, you may want to 'switch off' Excel's Automatic formula updates while you are working on your spreadsheet and then turn them back on again later. The option to activate or deactivate Excel Automatic cell calculation is found in the following parts of Excel:

In Excel 2003:	-	Select Tools -> Options... Select the Calculation Tab
In Excel 2007:	-	Select the main menu (from the Microsoft Excel Logo at the top left of the workbook) From the very bottom of this drop-down menu, select Excel Options Select the Formulas Option to access the Calculation options