Free Intermediate Excel Training Online Lesson One: Cell Referencing in Formulas
2KO Africa welcomes you to our free Intermediate Excel training online. We are based in Cape Town, and have been offering classes from beginner to advanced level for 20 years. Our style of training is varied, and students can learn online, in class with an instructor, blended learning or we can do the training at your premises for groups.
What are the different methods of cell addressing in Excel formulas, and why do we need to know?
To begin with, we will look at the different types of Cell Addressing that Excel uses for formulas, namely Relative, Mixed and Absolute. We will not deal with relative addressing here, because we covered this in our beginner lesson 7. If you do not understand Relative addressing, please first check it out.
Relative, Absolute and Mixed
Excel is all
about formulas. Anyone can use any program to capture data; but it is in the
manipulation of the data that Excel literally excels! Relative cell
addressing allows formulas to be created in one cell, and then duplicated
elsewhere by simply using the copy command, or autofill (also covered in
lesson 7 of our beginner content).
What is an Absolute Cell Reference?
occasions, relative formulas are not what is required. Normally this is when
a rate is contained in a specific cell, whereby all formulas that are
copied, are required to refer to that specific cell. In such cases, Excel
requires that the user create an Absolute formula. Absolute referencing is
also used a lot when using advanced functions that refer to specific ranges.
- it is not necessary to fix a formula, or to make a reference absolute,
unless you intend copying the formula.
the following Table
In the example above, the conversion rate is displayed in cell H3. The first formula is entered into E3. See the formula in cell E3 above.
The problem is that if the formula is entered in E3 as =B3*H3, it cannot be copied, either down or across. Assume this formula is copied down, then the row number changes from 3 to 4. The resulting formula in E4 will be =B4*H4. We know this to be the case from studying Relative addressing previously.
part of the formula is correct, because the formula must pick up the new
value in B, which is 5 (see cell B4). However, the problem lies with the
second part of the formula. H4 is empty. Therefore =B4*H4 will give a result
of 0. (=5*0=0)
the second half of the initial formula must be fixed, so when copied down H3
does not become H4. This is done by fixing a $ sign in front of the row
number. The same applies when copying left to right. H3 would become I3 when
copied to the right. I3 is also blank.
Thus, both column letter and row number must be fixed with a $ sign. A $ in front of a row or column letter, means that when copied, the row or column remains fixed (it cannot change in a relative fashion, as it normally does with copied formulas).
- To create an absolute reference to cell H3, place a $ symbol in front of the column letter and row number of cell H3 ($H$3) in the formula of cell E3.
- One can quickly drag this formula to the other cells.
The reference to cell H3 is fixed (when the formula is dragged down and across). As a result, the correct lengths and widths in inches are calculated.
SHORTCUT - Press the Function key F4 when positioned on the cell that must be fixed, when compiling the original formula.
NOTE - Function key F4 cycles through various options each time it is pressed, switching between Relative, Mixed and Absolute addressing.
Let's look at another example
In the above table, this ice cream store discounts their ice creams by one quarter off on Mondays and they have a special on Thursdays, at half price. We wish to create a formula in C4 that we can copy down to row 11, that will calculate the price of ice creams when the 25% discount is applied. The discount is written into cell C2, so that we can use that cell in the formula. From time to time we do change the discounts, so we want them in their own cell.
One would normally create the formula as such
The resulting price is correct at 18.75. The issue is when we copy formulas down, we know that Excel uses Relative addressing, which means in cell C5, the formula will read =B5-B5*C3. The result will be an error. Look at the table below and the reason for the error will be evident.
The second part of the formula is wrong. The price part is correct, because Excel has taken the price of strawberry, but it has multiplied by the cell C3, which does not contain a number. The C2, should be fixed so that the formula can be copied down. This will require an ABSOLUTE reference, meaning $C$2, rather than C2.
Editing an Existing Formula
We have deleted the error formula, but we don't want to do the first formula again. Not a problem. We would select the formula in C4 which reads =B4-B4*C2. Then while on that cell, we will press function key F2, which is the Edit key, which places us in Edit Mode, and also positions the cursor at the end of the formula which happens to be on C2, in this case. We can then simply press function key F4 and it will add the $ signs to C2, and C2 will now read $C$2. Then we can press enter and then copy.
Mixed Cell References
A mixed reference is where either the row or the column is fixed, but not both. here is a summary:
Relative Reference - row and column are free to float / change in
resulting formula when copied from initial formula cell to another cell
Mixed Reference - Either the row or column is free to float / change in resulting formula when copied from initial formula cell to another cell.
Relative Reference - Neither row and column are free to float / change in resulting formula when copied from initial formula cell to another cell. Both are fixed and the resulting formula allways refers to the same cell as the original formula.
We will tackle some examples of mixed addressing in our advanced course.
Practice on your own
Open the file called IceCreamShop.xlsx by clicking here, to download it.
1. Use the steps above to edit the formula in C4 to make the C2 absolute
2. Copy down to row 11
3. Create a formula in D4 to calculate the price for Thursdays, at less 50% (using cell D2 in your formula)
4. Make sure D2 is absolute
5. Copy down to row 11
If any of the numbers reports an error, or a clearly wrong price, go back and check your work, and repeat if necessary.
Check your Answers:
If you would like to check your answers, click here (Note the file will open in Excel)
We hope you have enjoyed this lesson in Microsoft Excel.
To start lesson 2, click hereTo go back to the index of free online excel lessons from 2KO Africa in Cape Town, click here