Leading the way in Microsoft Office Development

Excel

Step 1 - Why should I use Cell References ?

Instead of hard typing all the values used in your spreadsheet it is possible to use cell references as well.

This means that your spreadsheet becomes more dynamic and will change accordingly when the values in the cells change.

Any formulas that contain cell references that are entered in lowercase or mixed case (i.e. B3:H6) will be automatically changed to uppercase.

An individual cell can be referenced by an unlimited number of formulas.

A cell reference does not have to contain an operator unless you want to use it to perform an operation.

Step 2 - Cell references on the same worksheet

It is possible to use references to other cells that are on the same worksheet.

You can insert a cell reference into a formula by either typing the address directly or by selecting the cell with the mouse.

When you use the mouse to enter a cell reference the address will appear automatically and a dotted line will appear around the cell.

Once the cell (or range of cells) has been selected click back into the formula bar to enter the rest of the formula.

You can use either the formula bar to add and edit your formulas or you can edit them directly in the cell.

Select cell B2, Enter an equal sign. You can then immediately select cell D2.

The cell addresses of any cells that you select will automatically be included in your formula.

You can easily include more cell references by inserting an operator and then selecting on another cell.

Excel uses colour coding to help you manage your cell references. Each cell reference and the cell it refers to are displayed in the same colour.

The colour coding makes it very easy to identify which references in the formula match which cells on the worksheet.

If you are using the mouse to select cell references to construct your formula, only press the Enter key to confirm the final formula and not the individual cell references.

Step 3 - Cell references to a different worksheet in the same workbook

It is possible to use references to other worksheets in your formulas.

Lets assume that you have another worksheet in your workbook, called Sheet2 and that this worksheet contains a number in cell D2.

Type your formula as usual and at the point where you want to include the cell reference select that worksheet using the tabs at the bottom.

You will notice that the corresponding prefix "Sheet2!" will be automatically inserted into your formula.

Select cell D2. Do not use the mouse to click back to the original worksheet.

Before you can select the original worksheet you must either insert another operator or you must press the Enter key to confirm the formula.

If you do not enter another operator before switching back the reference will switch back to the original worksheet.

When your worksheet name contains spaces the reference must be placed within single quotes.

Step 4 - Cell references to a worksheet in a different workbook

It is possible to use references to cells in other workbooks. These workbooks can either be open or closed.

When the referenced workbook is open the formula is displayed as below.

The name of the workbook must be surrounded by square brackets.

If the name of the worksheet contains any spaces then the worksheet name must be enclosed in single quotes.

Notice that all cell references that refer to other workbooks are inserted as absolute references by default (see later).

If the referenced workbook is not currently open then the full directory location of the file can be used.

In this case the directory location and the workbook and worksheet name must be enclosed in single quotes.

It is possible to type in the cell references directly into your formulas although it is much easier to use the mouse.

To create a cell reference to another workbook, open the other workbook first and then use the Window drop-down menu to switch to the other workbook and select the required cell.

When you create a cell reference to a different workbook the actual data is stored in the other workbook and a copy of the data is just displayed.

The cell references will still update even when the other workbook is closed.

Every time a workbook is opened that contains cell references (or links) to other workbooks you will be prompted as to whether you want to update these links.

Step 5 - 3D Cell References

You can use references to perform calculations on cells that span a range of worksheets in a workbook.

This technique is extremely useful if you want to summarise a group of worksheets that all have an identical layout.

=SUM(Sheet1:Sheet4!A2)

Only certain worksheet functions can be used in 3 dimensional formulas. Please refer to the 3D Formulas page for more details.

Step 6 - Named Ranges

It is possible to include named ranges in your formulas and using them in your formulas can often make your formulas a lot easier to understand.

Instead of referring to a cell "=D2" (or a range of cells) using the cell address, you can actually use a descriptive name.

In the example below a worksheet named range has been created for cell "D2" called "Named_Range_D2".

You can insert named ranges into your formula by selecting (Insert > Name > Paste).

For more information on named ranges, please refer to the Named Ranges section.

Step 7 - Types of Cell Reference

When you refer to cells you have two options.

You can either refer to cells using Relative References or you can use Absolute References.

Relative References will change so they refer to cells relative to the cell containing the formula.

Absolute References will always refer to the same cells.

You can change the type of cell reference by pressing F4 to toggle between the four different types of cell reference.

For more details about these different types of references, please refer to the Cell References - Types page.

Step 8 - Copying Formulas

When you copy and paste formulas containing Relative references, the references are adjusted automatically.

The Relative reference is defined by the number of cells between the row and column of the cell being referenced and that of the cell containing the formula.

When you cut and paste formulas containing Relative references, the references are NOT adjusted automatically.

When you copy and paste formulas containing Absolute references, the references are NOT adjusted automatically.

When you cut and paste formulas containing Absolute references, the references are NOT adjusted automatically.

A quick way to copy formulas is to use the AutoFill handle in the bottom right hand corner of the active cell.

When you copy a formula that contains mixed references the only part that is adjusted automatically is the row or column that does not have a $ sign infront of it.

When a formula is moved as opposed to copied, the relative references are not automatically adjusted.

Step 9 - Editing Cell References

Pressing F2 when a cell is selected allows you to edit the formula directly.

Any cell references used in the formula will be automatically highlighted and will appear in different colours.

These coloured squares are referred to as the Range Finders.

You can click and drag any of the coloured boxes to new cells to quickly adjust the cell references used in the formula.

The four squares in the corners allow you to increase or decrease the range of cells being referred to by dragging these squares with your mouse.

As you change the cell references you will see the formula changing automatically.

Step 10 - Updating Cell References

A workbook can be updated (or calculated) when it is opened, closed.

You can force a recalculation in all the open workbooks at any point by pressing the F9 key. This will only calculate formulas that have changed since the last calculation.

(Shift + F9) - This is the same as the F9 except that it only recalculates cells on the active worksheet.

There is currently no way to quickly recalculate all the cells in just the active workbook.

(Ctrl + Alt + F9) - Recalculates all cells in all open workbooks regardless of whether they need to be recalculated. This does not seem to work in Excel 2003.

You can also normally interrupt the calculation process by pressing Escape several times.

Step 11 - Inserting Cells

When you insert cells Excel will automatically adjust any existing formulas that refer to any cells that were moved.

When you delete cells though any formulas that contain references to those cells will be replaced with #REF.

SS

Step 12 - Things to Remember

A quick way to copy formulas is to replace the "=" with a "#" copy and paste the formula and then replace it back.

Excel automatically adjusts formulas so when you insert a new row(s) or column(s) the formulas are adjusted to include the new row(s) or column(s). Please refer to the Automatic Formula Expansion page for more details.

You can use the formula bar to copy a formula and then to paste the formula directly into another cell. Remembering to press Escape after you have copied the formula.

If you have a lot of complicated formulas within a workbook you should try and build-in as many cross-checks as possible so the data can help to check itself.

If you have linked any of your workbooks re-naming them afterwards will create problems. You should avoid renaming any files that are referenced by other workbooks.