Excel Formula Tips

This page lists a few Excel formula tips that are useful to bear in mind when inputting large numbers of functions or complex 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 into different cells.

For example, instead of typing the following long formula:

Example of a Long Excel Formula

Break it down into the two shorter formulas, as shown below:

Example of Breaking Down an Excel Formula

This has the advantage of being simpler, as well as being less prone to errors. It is also more efficient, as it avoids the duplicated call to the VLOOKUP function.


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

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:


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

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

4. Automatic Calculation - Use with Caution!!

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.

Warning: If you do switch off the Automatic calculation option, make sure you remember to switch it back on again afterwards, or there is a risk that you will become very confused when your formulas fail to update!!

The option to activate or deactivate Excel Automatic cell calculation is located in different places, in different versions of Excel:

To Find the Automatic Cell Calculation Option:
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 view the Calculation options
In Excel 2010 : - Select the File tab from the top of the workbook
From the menu that appears, select Options
From within the Options menu, select Formulas to view the Calculation options
Return to the Excel Formulas Page

Return to the ExcelFunctions.net Home Page

Valid XHTML 1.0 Transitional Valid CSS!
Disclaimer   Privacy Policy
Copyright © 2008-2013 ExcelFunctions.net