Copying Formulas and using Relative
and Absolute Cell References Tutorial
Free Online Microsoft Excel Tutorial
Excel 2010 -
Formulas & Functions
* The Fill Handle
* Copying and Pasting Formulas and Values
* Drag and Drop
* Absolute and Relative References
To get the math correct every time you have to understand Absolute and Relative cell references when creating, copying and moving formulas. Copying formulas using the fill handle saves a lot of time. So does knowing how to change the formula to its actual value.
Test your Excel skills with the corresponding FREE Online Multiple Choice
Excel Copying Formulas and using Relative and Absolute Cell References Test
* The Fill Handle
The Fill Handle is a wonderful hidden gem in Excel. You may even have already come across it and used it for copying down numbers, words or even series such as the days of the week. The Fill Handle can also be used to copy formulas to adjacent cells.
Study the screenshot below:
In this example, the formula entered in cell D2 multiplies the cost price in B2 with the number of units in C2 to give the total cost. The formula as displayed in the Formula Bar above is: =B2*C2.
The same formula is needed for cells D3:D6 (highlighted in yellow in the screenshot above). Instead of retyping the formula for each cell, the Fill Handle can be used to copy the formula created in D2 to D3:D6 in order to complete the table.
To use the Fill Handle:
1. Select the cell containing the value you wish to copy (in our example D2).
2. Hover your mouse cursor over the bottom right-hand corner of the selected cell until your cursor changes into a cross-hair (circled in blue in the screenshot below):
3. Drag the corner/cross-hair down through the cells you wish to copy the formula into by holding down the left mouse button.
4. The cells you drag the formula down through will automatically calculate using the cells that are in the same relative position to the cell containing the formula as what the original cell (D2) was.
An alternative to dragging the Fill Handle is to double click it. This will automatically fill cells in a logical sequence with the formula contained in the selected cell.
Using the Fill Handle to copy formulas down is not only a great timesaver, it also prevents formulas from inadvertently being retyped incorrectly.
* Copying & Pasting Formulas and Values
You may already be very familiar with copying and pasting content within Microsoft Office. The Copy and Paste function in Excel, however, has a few additional tricks up its proverbial sleeve, for example, the option of pasting formulas as formulas or as values.
Remember, even though a cell may display a numeric value, the real contents of the cell may be a formula. When you copy cells that contain formulas and you paste them elsewhere, the formulas will calculate adjacent cells where you past them or return an error if there are no cells to calculate.
To see what this means in practical terms, study the screenshot below:
The selected cell above (D6) displays the value $714 but the actual contents of the cell, a formula, is displayed in the Formula Bar (circled in yellow).
To copy and paste the formula to a different location:
1. Select the cell containing the formula that needs to be copied.
2. Press the Copy icon in the Clipboard group under the Home tab on the Ribbon or right click and select Copy from the menu list.
3. Select the cell you wish to paste the formula into.
4. Click the Paste button in the Clipboard group on the Ribbon to paste the formula into the selected cell.
The formula will automatically calculate the two cells that are in the same relative position to the selected cell as what the positons of the original formula and cells were.
The pasted content from our example above will display as =A11*B11 in the Formula Bar and as $2,464 in the cell. Study the screenshot below and compare the pasted value with the copied value in the screenshot example above. You will note that both formulas calculate the product of the two cells immediately to the left of the cell containing the formula.
When we want to paste the value of calculated cells, for example, $2,464 in the above screenshot, and not the formula, we need to instruct Excel to ignore the formula and paste the calculation as a static value.
To paste the value of a calculated cell only and not the formula:
1. Select the values you wish to copy.
2. Position your cursor in the first cell of the section where you wish to paste the values.
3. Press the dropdown arrow below the paste button and select Values (circled in yellow in the screenshot below) from the Paste Values section.
This will paste the copied cells which originally contained a formula, as values only.
An alternative method to change formulas to values after pressing the Paste button, is by clicking on the Paste Menu button (circled in yellow in the screenshot below) which appears immediately after pasting values, and selecting the Values button from this menu.
To quickly copy and paste content in Excel, use the following shortcut combinations:
- Ctrl+c to copy; and
- Ctrl+v to paste.
In Excel, after you press the Copy button or shortcut, the content you selected to copy will be outlined by a dotted border. In order to paste the selected cells, you have to do so immediately after copying and whilst the cells are still selected (ie has a dotted border around it). If you copy content in error and do not wish to proceed with pasting it, press the Escape key on your keyboard.
* Drag and Drop
Dragging and dropping is a great timesaver. It replaces the function of cutting and pasting content from one section of a worksheet to another and can even be used to organise a schedule or other project where you dynamically need to move items around.
Study the screenshot of part of a schedule created in Excel, below:
Imagine you wished to move the 9:15 appointment with Peter on Monday to Tuesday at 16:15.
This is easy:
1. Select the cell(s) which contain the values you wish to move.
2. Hover your mouse cursor over the borders of the selected cell(s) until the cursor changes into a four-pointed arrow.
3. Click and hold down your left mouse button whilst dragging the cell(s) to its new location.
4. Once you have positioned the contents in the correct cell(s), release the left mouse button to finalize the move.
If the cell(s) you are moving the content to already contains values, Excel will prompt you with a question asking you if you are sure you wished to replace the contents of the destination cells. Press OK to replace the contents in the destination cells with the contents of the cells you are moving or click Cancel to stop the move.
*Absolute and Relative References
Excel is divided into a grid containing columns and rows. These rows and columns intersect to form cells. Each cell in Excel has an address, for example A1 refers to a cell located in column A and row 1. Cell references are essential for formulas. You use the cell reference to instruct Excel of where the value(s) to include in a formula are located, for example, =A1-B1 tells Excel to take the values entered into cell A1 and subtract the value entered into cell B1 from it.
Problems in using cell references occurs when cells containing values or formulas are moved and their relative position to one another changes.
The screenshot below is a typical example of when additional assistance is needed to ensure cell references refer to the correct cells in a formula. To calculate each cell in the Price incl. tax column the Price excl. tax value needs to be multiplied with the Tax value located in Cell B1. The formula to achieve this has been entered into C4:
What do you expect will happen when we use the fill handle or copy and paste functions to copy the formula created in C4 down to the rest of the cells in the Price incl. tax column?
Study the screenshot below which displays the result of copying the formula down:
You may immediately have noticed the error message: #VALUE! in C6. Take note of the calculations for the other cells, they are clearly incorrect. What do you think happened here?
To assist you, below are the formulas as they appear in each cell instead of the values that are displayed above. Carefully study these and see if you can spot the problem:
If you answered the cell references in the formulas C5:C9 is incorrect, you are absolutely spot on.
When you copy a formula down, it takes its relative position and move that down in what is a logical sequence. This works perfectly until you reference a cell which is not part of the sequence and which must not change. In the example above that would be cell B1 which should not have changed to B2, B3, etc as the value to multiply by is ALWAYS going to be located in B1.
Excel makes provision for the above scenario with the introduction of absolute and relative references. In the screenshot above, relative references were used. By this we mean the cell address will change when pasted to match the new location.
Absolute references ensure that the cell address does not change when the formula is copied to a different location. In the example above, if we made B1 an absolute reference each time we use it in a formula, it would not change regardless of where the formula is copied to.
Relative references are typed as the column letter and row number, for example A1.
Absolute references are typed with a dollar sign preceding the column and row number, for example $A$1. This locks the cell reference to its absolute position. If rows and columns were inserted around the cell containing the absolute reference cell, the formula would automatically change to include the new location.
To demonstrate the above, if we used an Absolute reference to protect the reference to cell B1 so that it always refers to the value currently contained in B1, the formula for the copied cells would look like this:
The cell address for B4 is a relative reference. This means that if it is copied down into the blank cells below or even to a different location, it will include the reference that is in the same relative position to it than the original is. In the example above, if we copied the formula down, B4 will change to B5, B6, B7, B8 and B9 whilst the Absolute reference to B1 will remain unchanged regardless of where the formula is pasted. If the value contained in B1 is moved to a different location, the formula will automatically update the reference to match the new location of the value.
A shortcut for making cell references absolute without having to manually insert the dollar sign is to use the F4 key on your keyboard. First select the cell reference and then press F4 to make it an Absolute reference.
Now you have done the tutorial:Test your Excel skills with the corresponding FREE Online Multiple Choice
Excel Copying Formulas and using Relative and Absolute Cell References Test