Bookmark page
Printable version
Course updates
Moving and copying formulas
Formulas can also be moved or copied using the same methods you have practiced using to move or copy text.
The only difference is that when formulas are copied, they do not stay the same like text or numbers - they change.
Try it!
On Sheet 1 of Sales report.xls, delete the formulas in cells G5 and J5. Copy the formula in cell D5 and paste it into cells G5 and J5. Note how the result of the formula changes when it is pasted into these two cells.
Why does this happen? Your trainer will explain.
Using the Fill handle
Using the Fill handle provides a quick way to copy formulas across a row or down a column.
Try it!
On Sheet 1 of Sales report.xls use the Fill handle to copy down the formulas created in
D5, G5, J5 and L5 to the next two rows (rows 6 and 7).
Why do you get incorrect formula results appearing in cells L6 and L7?
Discuss this with your trainer.
Handy hint:
Formulas can be displayed in a spreadsheet by using Ctrl+ the 'pipe' key (directly below the Esc key).
Relative references in formulas
Cell references (or cell names) that are used as part of a formula are by default relative cell references. 'Relative' in this case means that the cell references in the formula are viewed by Excel as being relative to the location of the formula itself.
In the example on the left, Excel reads the formula in cell D5 as 'take the value in the cell two columns to the left and minus the value in the cell one column to the left'.
So when the formula is copied, Excel changes the cell references in the formula relative to where the formula is copied to.
Therefore in cells D6 and D7, Excel will continue to 'take the value in the cell two columns to the left and minus the value in the cell one column to the left'.
Absolute references in formulas
Sometimes when you create a formula it will be necessary to 'fix' or 'lock' particular cell references into a formula, so that when the formula is copied the results that show are correct.
A cell reference that is fixed or locked into a formula is known as an absolute reference.
The example below shows the cell L2 as an absolute reference in the formula in
column L. An absolute reference has $ signs before the column letter and before the row number, so L2 becomes $L$2 in the formula shown below.
When the formula is copied down column L, cell L2 remains fixed or locked into the formula.
Handy hint:
Create an absolute reference by pressing the F4 key once, immediately after the cell has been entered into the formula.
Try it!
On Sheet 1 of Sales report.xls select cell L5.
Change cell L2 in the formula into an absolute reference. This can be done by clicking next to L2 in the formula and pressing the F4 key once so the formula reads =K5*$L$2.
Then use the Fill handle to copy the new formula down to cells L6 and L7. The correct formula results should now display.
Use Ctrl+ the 'pipe' key to display formulas and show the absolute reference in the formula in column L.
Save the changes made to Sales report.xls.
