Excel online training course

excel lesson 5, Copy and Pasting Formulas

Absolute and relative cell reference

In Excel we can copy all data from cell(s) to one or multiple cells.
Cell with numerical and textdata can be pasted to any cell.
When we copy formulas to a new location in the workbook, excel will immediatly adjust this to the cell references of its new location.
For example:
When we have a formula a A4, which calculates the sum of A1 to A3 " =SUM(A1:A3) ", and we copy and paste this formula to B4, excel will adjust the references to " =SUM(B1:B3) ".

This is called a formula with a relative cell reference.

To keep the cell references we must place a $ sign infront of the colomn letter and the row number: " =SUM($A$1:$A$3) ".
We call this absolute cell reference.

It can be usefull to combine the two references:
Lets say we have three employees each with the same salary, 1500 dollar.
Each will get a bonus, depending on the amount of years they are employed.
So we use absolute cell reference to the salary they are getting: $B$1
And a relative cell reference to the bonus they will get: C4, D4 or E4.

The salary, cell B1, stays the same for everyone, so = absolute.
The percentage, cell C4, D4 or E4 is different for each person, so = relative.

We can also have a mixed cell reference.
For example:
$B1 (the column is absolute, the row is relative) or B$1 (the column is relative and the row is absolute).

Summary:

B1 = Relative
$B1 or B$1 = Mixed
$B$1 = Absolute

Cut, Copy and Paste information

To copy or paste information or formulas, first we select the cell which we want to copy, click on "Copy", select the cell where we want to paste our information and click on "Paste".
If you would like to copy multiple cells, we select all cells, click on "Copy", choose the most upperleft cell where we want to copy all and click on "Paste".
The cells will automaticly place themselves below and right of the selected cell.

When we click the "Paste" button we get to choose from some options:

In Excel XP and 2003 there is a big improvement in the clipboard.
In Excel 2000 the clipboard wasnt much help, but in XP and 2003 we got a complete task pane for this.

In Excel 2003 or XP you can view the clipboard by clicking on "Edit" and "Office clipboard" in the menubar.

We see the different commands we did before together with a description or image in the task pane.

We can click on one of these to repeat them.

We can remove them by selecting it, clicking on the black arrow that appears and clicking on "Delete".

We can also remove all of them by clicking on "Clear All" on top of the list.

By clicking on the "Options" button on the bottom we can adjust the settings of our clipboard to our needs.

Automaticly fill cells

With the "Autofiller" (remember lesson 3) we can also copy formulas.
Click and drag the autofiller over the required cells where you want to copy the formula.
Release the mousebutton and all formulas are being filled.

This is one of the most timesaving functions in Excel.

Cut, Copy and Paste through clicking and dragging

To cut and paste a cell to another location, we select the cell, and click and drag this to the required location in or worksheet.

To copy and paste a cell to another location, we select the cell, and click and drag this to the required location in our worksheet, but when doing this we hold down the "Ctrl" key on our keyboard.

The Undo button

The "Undo" button is used alot in Excel.
When we click on the "Undo" button our last action we did in our worksheet will be undone.
When we click on the black arrow next to this button we will get a list with the last "previous" actions.
We can undo everything that you see in this list.

The Redo button

This button does the opposite of the "Undo" button.