Force Byte
Knowledge and Techniques
of Data Input in Excel (11)
0 Photo
First of all, let's take a look at the following example.
Drag the fill handle to copy the cell B4's formula, which calculates the maximum number of cases for "BurglaryÓ, to range C4:F4.
1. Click cell B4.
2. Move the cursor to the fill handle at the bottom right corner of cell B4. Now the cursor changes into a "cross".
3. Drag the fill handle over range C4:F4, release the mouse button and then cancel the selection. You will get the following result:
Notice that the copying does not give what we expect as in our last episode. Each cell in range C4:F4 shows the same maximum number of cases for "Burglary" instead, i.e. 9076. Why?
Well, as you can see the formula in cell B4 is now "= MAX (Burglary)" instead of "=MAX (B2:B3)" as in our last episode. In addition, the range name "Burglary" here is an "absolute reference", representing range B2:B3.
When you drag the fill handle to copy a formula, Excel will, by default, copy any "absolute reference" in the formula exactly to the target cell(s). That is why range C4:F4 gives the following results:
C4 = MAX (Burglary)
D4 = MAX (Burglary)
E4 = MAX (Burglary)
F4 = MAX (Burglary)
And their values are therefore all the same as "9076".
In fact, when you are working with formulas later, it can be considered that you are applying the "absolute reference" concept if you add a "$" sign on the left hand side of both the "column label" and the "row label" to stand for a cell address. For example, use "$A$1" to stand for the cell in column A and row 1.
When copying such a formula, Excel will copy the "absolute reference" in the formula exactly to the target cell(s). For example, the formula in cell A5 is "=$A$3+$A$4". If you now copy this formula to cell B5, cell B5 will contain the formula of "=$A$3+$A$4".
Besides, Excel does have one more address reference - the "mixed reference", which is simply a mix of the "relative reference" and the "absolute reference". In this case, only a "$" sign is added on the left hand side of either the "row label" or the "column label" to stand for the cell address. For example, for "$A1", it is an "absolute reference" for column A, and a "relative reference" for row 1. On the other hand, for "A$1", it is a "relative reference" for column A and an "absolute reference" for row 1.
When copying a formula with such "mixed reference", Excel will only copy the "absolute reference" in the formula exactly to the target cell(s). For instance, the formula in cell A5 is "=$A3+A$4". When this formula is copied to cell B5, cell B5 will contain the formula of "=$A3+B$4".
With such a number of address references in Excel, do you expect that you may switch among them? Let's find out the answer next time.
"Sharing IT as it applies to your daily life."
(E-mail address: ISW_ForceByte_Editor@police.gov.hk)