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.

Before creating complex Excel Formulas, it is advised that you have a firm understanding of Basic Excel. 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:

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

Mathematical Operators

The table on the right lists the Mathematical Operators used in Excel

The power operator has the greatest precedence, followed by the multiplication and division operators, and then the addition and subtraction operators. This means that, when dealing with Excel formulas that contain more than one mathematical operator, the power operators are evaluated first, followed by multiplication and division operators, and finally, the addition and subtraction operators.

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 Up Complex Formulas

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 simpler, as well as being less prone to errors. It also avoids the duplicated call to the VLOOKUP function.

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:

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 located in different places, in different versions of Excel: