Force Byte
Knowledge and Techniques
of Data Input in Excel (12)
0 Photo
Last time, we talked about different types of "address references" in Excel. Can we switch among them?
The answer is positive. All you have to do is to click the target cell first, select the "address reference" in the "formula bar" that you would like to change the type, and then press <F4>. For example, if we have a "relative reference" called A1, when we press <F4>, changes will be made to it as follows:
Now I would like to use the example in the last episode to demonstrate how to use the
Change the type of address reference in the formula in cell B4 from "absolute reference" to "relative reference"
1.Click cell B4.
Copy the formula in cell B4 to range C4:F4
1.Click cell B4.
2.Drag the fill handle all the way through range C4:F4, release the mouse button and then cancel the range selection. Eventually, you will get the following result.
"Sharing IT as it applies to your daily life."
(E-mail address: ISW_ForceByte_Editor@police.gov.hk)
Besides, you may change the type of "address reference" directly from the "formula bar" after you have clicked the target cell.
The "formula bar" shows that the formula in cell B4 is "=MAX(Burglary)". No changes can be made to the type of "address reference" when it is defined by "name" in a formula, so you should first change "Burglary" to "$B$2:$B$3" and then press <Enter>. After clicking B4 again, you will get the following result.
2.Click "$B$2:$B$3" in the "formula bar".
3.Press <F4> thrice and "$B$2:$B$3" (absolute reference) in the "formula bar" will change to "B2:B3" (relative reference).
4.Press <Enter> to get the following result.
When the "address reference" in the formula in cell B4 is changed from "absolute reference" to "relative reference", it is very convenient to copy the formula in cell B4 to range C4:F4 by the fill handle for the calculation of the maximum value of the number of other cases. Please refer to the following illustration.
Having talked so much about the "address reference", I will introduce another interesting topic in the next episode - the application of "Relational Operators" in formulas. I believe that it will give you another pleasant surprise.