Chapter 7
Formulas
Entering a Formula into a Cell
Excel offers two options for entering a formula into a cell.
Experienced Excel users prefer to type formulas directly into a cell, in the formula bar. This is a good technique for skilled users who are familiar with the syntax of formulas (where to place commas and parentheses, the order of arguments, and more). If you are not sure of the syntax of the formula, type = and the name of the function, and then press Ctrl+Shift+A. You will now see the syntax of the function.
| When typing a function in the formula bar, a yellow Tip box pops up, which contains the function’s syntactic structure. |
2002 |
© Entering data into the function’s edit boxes
The edit boxes guide you in handling the syntax of functions.
Shortcuts for opening the Formula Palette
Ctrl+A – opens the Formula Palette immediately after typing the name of the function. For example, type =SUM, and press Ctrl+A to open the Formula Palette.
Shift +F3 – opens the Paste Function dialog box. Alternatively, use the Paste Function icon (fx) on the toolbar.
|
Tip – Enter a formula more quickly by changing the names of the sheetsWhen you enter a formula that links cells in different sheets in the workbook and you do not use names for the references, the formulas are long and often take up several rows. They are also difficult to enter and edit. If you rename the sheets with shorter names, such as 1, 2 or 3, the formulas will be shorter. When you are done entering/editing, change the names of the sheets back to meaningful names. |
Copying a Formula, Relative and Absolute Reference
When a formula is copied, the relative reference is used. Relative reference is the distance, in rows and columns, between the reference and the cell containing the formula. For example, in Cell A1, type the number 100. In Cell B1, type the formula =A1. Cell B1 is one column to the right of Cell A1. When the formula is copied from Cell B1 to Cell B10, the distance between the reference and the cell containing the formula remains one column. The formula in Cell B10 is =A10.
Use the previous example, and select Cell B1. In the formula bar, select the reference A1, and press F4. The result is =$A$1.
Copy the contents of Cell B1 to Cell B10. Notice that the formula does not change; the formula reference remains constant as =$A$1.
This is an important shortcut. The F4 shortcut, which deals with relative and absolute references, has four states. Select Cell B1, and then select the formula bar after the = sign. Press F4 several times. Notice how the formula changes each time you press F4.
State
1 – absolute reference to the column and row, =$A$1.
State 2 – relative reference (column) and absolute reference (row), =A$1.
State 3 – absolute reference (column) and relative reference (row), =$A1.
State 4 – relative reference to the column and row, =A1.
Maintaining a relative reference when pasting a formula
In many cases, you copy a formula from cell to cell when you want to avoid changing the reference of the cell containing the formula. In this case, use F4 to change the formula to absolute, copy it and paste it; then use F4 to change the original formula back to relative.