A formula consists of special code entered into a cell. It performs a calculation of some type and returns a result, which is displayed in the cell. Formulas use a variety of operators and worksheet functions to work with values and text. The values and text used in formulas can be located in other cells, which makes changing data easy and gives worksheets their dynamic nature. For example, you can see multiple scenarios quickly by changing the data in a worksheet and letting your formulas do the work.
A formula can consist of any of these elements:
- Mathematical operators, such as + (for addition) and * (for multiplication)
- Cell references (including named cells and ranges)
- Values or text
- Worksheet functions (such as SUM or AVERAGE)
After you enter a formula, the cell displays the calculated result of the formula. The formula itself appears in the Formula bar when you select the cell, however.
Here are a few examples of formulas:
=150*.05 – Multiplies 150 times 0.05. This formula uses only values, and it always returns the same result.
=A3 – Returns the value in cell A3. No calculation is performed.
=A1+A2 – Adds the values in cells A1 and A2.
=Income–Expenses – Subtracts the value in the cell named Expenses from the value in the cell named Income.
=SUM(A1:A12) – Adds the values in the range A1:A12, using the SUM function.
=A1=C12 – Compares cell A1 with cell C12. If the cells are identical, the formula returns TRUE; otherwise, it returns FALSE.
Entering formulas manually
Entering a formula manually involves, well, entering a formula manually. In a selected cell, you simply type an equal sign (=) followed by the formula. Formula must begin with an equal sign(=). This will differentiate the formula entry from the normal data entry.
As you type, the characters appear in the cell and in the Formula bar. You can, of course, use all the normal editing keys when entering a formula.
Sometimes, when you enter a formula, Excel displays a value that begins with a hash mark (#). This is a signal that the formula is returning an error value. You have to correct the formula (or correct a cell that the formula references) to get rid of the error display.