Excel Lesson 2: Part 5 (Copying Formulas to Other Cells)
Please Note: This session builds on, and assumes that you have completed, the previous Excel sessions. If that is not the case, please go back and complete them before returning to this session.
Excel Worksheets are made up of ‘cells’. Each cell can hold data of various types – as seen already – but each cell also has a unique ‘address’, made up of a row number and a column letter. When you click on a cell, that ‘address’ is shown in the top-left corner.
You can use these cell references directly in Excel formulas.
STEP 3: ‘Relative’ versus ‘Absolute’ Cell References
Let’s use an everyday example to convey these concepts.
Every house has an address – say: 25 Arcadia Avenue, Smiley Green, Happy Town, H11 2JK.
If you live a long way away and want to post a letter to that address through the mail, you must put the full address on the envelope to ensure delivery. That is an example of absolute addressing. It is ‘absolute’ in the sense that it does not relate to any other address – in this street or anywhere else.
By contrast, if you yourself live at 21 Arcadia Avenue, Smiley Green, Happy Town, H11 2JK and someone knocks on your door asking for the address of ‘Big Bob’ (who you know to live at number 25), you would not say to the enquirer “Oh yes, Big Bob lives at 25 Arcadia Avenue, Smiley Green, Happy Town, H11 2JK”. You would simply say “Sure, Bob lives two doors down” and point them in the right direction. This is an example of relative addressing because you are giving the address of Bob, ‘relative’ to your own address (two doors away).
The very same concepts apply with Excel cells. In this session we will focus on relative cell addressing in Excel formulas and absolute cell addressing in the next session. Let’s begin.
STEP 1: Getting Started
Launch Excel and create a new blank Workbook or open up an existing Workbook. We will use the familiar Car example:
STEP 2: Existing Formulas
We have spent a lot of time on formulas and how to construct them. Like data values, you can also copy formulas between different cells. In this example, we have a ‘Total’ and ‘Discounted Total’ formula only for the first sale (Row 4). Look at the following screenshots:
The other rows have no formulas at all:
STEP 3: Copying Formulas
Select the cell with the formula you wish to copy.
Put your mouse pointer on the bottom right corner of the highlighted box and, keeping the left mouse button depressed, drag the mouse down until you have covered all the cells you wish to copy the formula into.
The result is shown below.
We can now prove that the copied formula automatically updates its own cell references:
You can apply the same formula-copying process to the ‘Discounted Total’ column:
This formula-copying technique is obviously vastly more efficient than having to keep manually entering each formula in turn. Get into the habit of using it.
That completes this session.
AssignmentTutorOnline
Comments