5.8 Cell Referencing

You are familiar with formulas now. You are also aware how to create them. Now you will learn to copy formulas from one cell to another and see its effect on the calculated results

First of all, lets create a new worksheet as shown in figure: -

5.8.1 Copying Formulas

5.8.1.1 Fill A Formula Into A Range

You can copy or fill formulas into a range of cells just as you can fill data into a range. To fill a formula into a range, select the cell that contains the formula and then drags the fill handle downward, upward, right or left as far as you need. The formula is automatically copied into the new cell.

1.       To continue further, in cell E1 type 4.

2.        In cell D3 type another multiplication formula =C3*E1.

3.       As you press ENTER, computed value 40 appears in cell D3. In order to have the computed value appearing in remaining cells, copy down the formula.

4.       After you fill the formula into cells D3 to D7. The result is appearance of 0 in remaining cells. Surely something has gone wrong somewhere. But there is nothing wrong in the ay you have copied the formula. To find out the problem lets do a little exercise: -

•       Keep the cell pointer over cell D3. Read the formula. It reads  =C3*E1.

•       Now move over to cell D4. The formula reads =C4*E2. Here is the catch! WE have a value in C4 but no value in E2. So the result in cell D4 is zero.

•       Continue with C5. You will find the same problem as above.

Did we really wanted excel to pick the values in cell E2, E3 and so on or actually you to pick fixed value of 4 from cell E1 only. So how to narrate this to Excel?

5.       Place the cell pointer in cell D3.

6.       Press the function key F2 which is also called as  the edit key.

7.       Edit the formula by placing $ between E and 1. It should read =C3*E$1.

8.       Press Enter. No change happens in the cell D3.

9.       Copy this formula down the column as you did earlier.

10.   As you are through with your copy process, the column has the computed results.

5.8.2 Relative vs Absolute Referencing.

A relative reference describes the location of cells in terms of its distance, in rows and columns, from another cell. Relative references are analogous to giving directions.

References that change automatically when you copy them to a new cell are called relative references. When you copy a formula containing relative references, the references are adjusted to reflect the new location of the formula. However, you can also use formulas with absolute references. These are the references that always refer to the same cell, regardless of the location of the formula.

In the last example formula applied on Column C is an example of relative Referencing and formula applied on Column D is an example of Absolute referencing.

5.8.3 Moving Formulas

Just as you have copied the formula and saw the effect of relative and absolute references, same applies to moving the formulas.

To move the formulas you will first cut it and subsequently Pate is using Cut and Paste buttons respectively. You will find when you move the formula from a cell to another the formula remain correct. This is because the reference in the formula is relative i.e. it identifies cells according to their location relative to the formula cell.

5.8.3.1 Steps To Move A Formula

1.    Select the cell C3.

2.    Click on the cut Button. A dotted line start circling the cell C3.

3.    Now select Fe.

4.    Click on paste button. You will see that cell F3 retains the value 10 in it.

5.    Pull the handle and copy the formula in cell F3 down the column till cell F7 and behold. Even the formula  is copied now. You can see the calculated results in the cells.

Copyright © 2001 Selfonline-Education. All rights reserved.