Cell reference allow you to use values from different parts of a worksheet and execute and enable you to do the desired calculation. You can use any cell or group of cell in a formula.
Suppose that you are in your office, and you want someone to take a letter to the post office.Using a relative reference, you want someone to take a letter to the post office. Using a relative reference,you tell the person, "Go out the front door; turn left cross the road and move two building then turn right and go to the second building." These direction are relative to your office location at the time you give the instructions. If you move to a difference location, these direction are no longer valid.
to make sure that the letter gets to the mailbox no matter where you are when you give the directions, you must say something like this "Take this letter to the I.P Estate post office." No matter where you are when you speak, the post office is at one absolute location : I.P ESTATE. Thus, the difference between relative and absolute reference is that in absolute reference, the cell is fixed once for all. In the relative reference it depends on the location where your pointer is.
Entering the Cell Reference by pointing
The easiest method of entering cell reference in a formula is by pointing to the cell you want to include in a formula. Although you can type an entire formula,you can often make a typing error or misread the row or column headings. Say for example, you may end up with D52 in a formula when it should be E52.When you point to a cell to include it in a formula, you actually move the pointer to the cell you want it in the formula. It is found out only when you select the correct cells.
- To enter a cell reference into a formula by pointing, do this:
- Select the cell for the formula.
- Type an equal sign(=)
- point to the cell you want it in the formula and click, or press the arrow keys to move the dashed marquee to the cell you want in the formula.
The address of the cell you point to appears at the cursor location in the formula bar. - Enter an operator, such as the + symbol.
- Point to the next cell.
- Repeat the steps from step 4 to continue the formula, or enter the formula by clicking the Enter box or pressing ENTER key.
The cell reference in a formula changes after you copy the formula to a new location or after you fill a range with a formula. You would usually want formulas to use relative cell reference,only.
Using Cell Reference on Formulas
You refer to a cell's location with a Relative reference or an absolute reference. Let us understand the difference in the two types of cell reference.Suppose that you are in your office, and you want someone to take a letter to the post office.Using a relative reference, you want someone to take a letter to the post office. Using a relative reference,you tell the person, "Go out the front door; turn left cross the road and move two building then turn right and go to the second building." These direction are relative to your office location at the time you give the instructions. If you move to a difference location, these direction are no longer valid.
to make sure that the letter gets to the mailbox no matter where you are when you give the directions, you must say something like this "Take this letter to the I.P Estate post office." No matter where you are when you speak, the post office is at one absolute location : I.P ESTATE. Thus, the difference between relative and absolute reference is that in absolute reference, the cell is fixed once for all. In the relative reference it depends on the location where your pointer is.
Using Relative Reference
A Relative Reference in a formula is based on the relative position of the cell that contains the formula and the cell the reference refers to. If the position of the cell that contains the formula changes, the reference is changed. if you copy or fill the formula across rows or down columns, the reference automatically adjusts.
Excel uses relative referencing for cell addresses when you enter formula.The formula in cell F9 is =C9+D9+E9. All these reference are relative. The formula, translated into English, would read as follows: "In cell F9 add the number in the 9th column to the left(C9) second column to the left (D9) and the adjacent column on the left (E9)" in the same row.
Suppose you copy formula across column G, the formulas adjust their cell reference to their new positions. The copied formulas are as follows:
You will notice that the formula changes to give the cell references with the relative position form the cell that contains the formula.
Using Absolute Reference
You use absolute only to keep the cell reference values from changing as and when you copy a formula to a new location in a worksheets.
To enter an absolute reference by using the F4 key, do this:
- Type an equal sing(=)and the cell reference you want to be absolute.
- Press F4, the absolute reference key, until the correct combination of dollar sign appears.
- type the next operator and continue to enter the formula,also. You can use the F4 key when editing an existing formula.
Indicate absolute reference by putting a dollar sign ($) in front of the columns letter or row number that you want to freeze or both
Using Mixed Reference
In some cases, you may want either the row to stay fixed or the column to stay fixed when coping the cells from one place to other in a worksheet. In such cases, use a mixed reference. such reference contain both absolute and relative reference. For example, the reference $B5 prevents the column from changing, but the row changes relative to new copied location. The dollar sign prevents the column from changing. In B$5, just the opposite occurs. The column adjusts to a new location but the row always stays fixed at 5 because the dollar sign prevents the row from changing.
You can create mixed reference the same way as you can create absolute references. Type the dollar sign or specific row and column numbers without brackets or press F4 key. Each press of F4 cycle the cell reference to a new combination.
Comments
Post a Comment