Leading the way in Microsoft Office Development

What is a Formula ?

A formula is basically a simple mathematical calculation similar that you want to be evaluated. All formulas must start with an equal sign (=) .

The simple formulas contain mathematical operators which are just symbols to denote the mathematical operations.

The most common operators are addition, subtraction, multiplication and division.

Formulas are cell entries that perform calculations and return a result.

Using formulas can be useful if you want to perform calculations on values in other cells.

This can be very useful especially if the values in other cells are likely to change.

You can either use the Formula Bar, which is located above the column headings or you could use the mouse to select the various cells.

The total number of characters in a formula cannot exceed 1,024.

Using the Formula Bar

This is located below the toolbars and contains a Name box and Formula bar.

The Formula bar is the area where you can enter text, numbers, dates, formulas etc.

 Cancel - Cancels the immediate changes made to the active cell.

 Enter - Enters the number, text or formula into the active cell.

The Name box displays the name of the active cell or selected cells and can be used to apply a name to a particular cell range.

The drop-down menu next to the name box can be used to quickly navigate to particular named ranges.

As you type your formula the text will appear in both the cell and the formula bar.

Inserting a Formula

Rather than entering your formulas through the Formula Bar you can just enter them directly into the cells.

This is the preferred method for a lot of people who are familiar with the function arguments.

Try to avoid hard coding values into formulas. Enter the data into cells and then reference the cells.

In this very simple example a formula is used in cell D2 to calculate the total of the values in cell B2 and C2.

The advantage of using a formula is that it is updated automatically, when the cells that it refers to change.

Once you have finished the formula, press Enter to confirm.

After you have pressed Enter the cell will display the result of the formula as opposed to the actual formula.

If we change the value in cell B2 to 9, the formula is re-calculated automatically and the correct result is displayed.

The table below shows the formula entered in column B and the result obtained in column C.

All the formulas contain basic operations such as addition, subtraction, multiplication and division.

To represent these operations you use what are called Operators in between your numbers.

When you create a formula you need to include at least one operator.

What are Operators ?

Operators are just symbols that represent mathematical operations.

There is a strict order of precedence among the operators which means that certain operations are performed before others, assuming that parentheses are not used.

Operators that have a high precedence are evaluated first. Negation is always the first operator to be evaluated.

If you are not sure about the order in which operations are carried out then use parentheses to define the order.

Notice that some operators have the same precedence. In this case they will be evaluated from left to right.

Using Parentheses

Parentheses are basically brackets. Using brackets in your formulas is a good habit to get into even when they are not strictly needed because they help to clarify the order and make the formula easier to understand.

It is also possible to put parentheses within parentheses. When parentheses are nested the ones on the inside are evaluated first.

When you enter parentheses you will notice that Excel tries to help you by making the matching parentheses bold as you pass over it with the mouse.

Your parentheses must balance. For every open parentheses there must be a corresponding close parentheses. If you try to submit a formula which does not contain the same number of open and close parentheses, Excel will prompt you and tell you that the formula you have entered is incorrect. Excel will make a suggestion as to what it thinks the formula should be. You can accept the recommendation or press "No" to edit the formula yourself.

The table below shows the more formulas entered in column B and the result obtained in column C.

String Concatenation Operator

The string concatenation "&" operator is also known as the ampersand.

This operator can be used to join several text strings together into a single string.

When a text string is used within a formula the characters must be enclosed in speech marks " ".

You can use this operator to join any two values (text or numeric) in order to create a single text string.

The table below shows the more formulas entered in column B and the result obtained in column C.

Note that all the logical operators return either True or False.

Things to Remember

All formulas must start with an equal sign (=). If you type an equal sign into a cell, Excel assumes that you are about to enter a formula. If you do not enter an equal sign then Excel will assume you want to enter a text string.

Formulas can contain simple mathematical operators, values, cell references and functions.

There is a strictly order of precedence among the operators which means that certain operations are performed before others.

Any expression in a formula that is enclosed within a parentheses is calculated first.

When copying formulas Relative cell references is the default.

Workbooks that contain a large number of formulas require a large amount of memory and hence take longer to open.

Formulas that contain links (especially to other workbooks) can often cause a real headache.