“Absolute” and “Relative” cell references

In Excel we can copy all the data from one “Cell” to one or more “Cells”.

Whether the “Cells” have numeric or text data does not matter.

When we copy “Formulas” to a new location in the “Worksheet”, Excel will automatically adjust to the new location of the “Cell”.

For example. if we have a “Formula” in A5, which calculates the sum of A1 to A4 “= SUM (A1: A4)” and if we “Copy” and “Paste” this “Formula” to B5, Excel will adjust the references to = SUM (B1: B4).

This is called a “Formula” with a “Relative cell reference“.

For the cell to maintain reference, we should place a $ sign for the column letter and row number: = SUM ($ A $ 1: $ B $ 4)

We call this the “Absolute cell reference”.

It may be helpful to use the two references together:

Suppose we have three employees who each have the same wage, ie 1000 Euro

Also Each gets an extra, depending on the number of years they serve.

So we use an “Absolute cell reference” to the wages they receive: $ B $ 1

And a “Relative cell reference” to the extra they get: C3, D3 or E3.

The salary in cell B1, remains the same for everyone, so that is “Absolute”

The percentage in cell C3 or D3 cell or cell E3, differs from person to person, so it is “Relative”.

We can also have a “Mixed cell reference”, eg $ B1 (the column is “Absolute”, the row is “Relative”)
or B $ 1 (the column is “Relative”, the row is “Absolute”)

Summary:
A1 =	Relative
$ A1 or A $ 1 =	Mixed
$ A $ 1 =	Absolute

“Cut”, “Copy” and “Paste” data

If we want to “Copy” and “Paste” data or “Formulas”, we first select the cell that we want to “Copy”, click “Copy”, select the cell where we want to “Paste” data and click “Paste”.

If you want to “Copy” multiple cells, we select all cells, click “Copy”, and choose the leftmost and topmost cell where we want to copy all.

We click on “Paste”.

The cells will be posted automatically to the bottom and right of the selected cell.

When we click on the “Paste” button, we have a lot of options:

A summary:

Option	Definition
Keep Source Formatting	paste the contents of the clipboard with the formatting of the original cell
Match Destination Formatting	paste the contents of the clipboard with the formatting of the source cells
Values only	Pastes only the values from one column to the target column.
Values and Number Formatting	Paste values and number formatting
Values and Source Formatting	paste the clipboard contents and retain the formatting of the target cells
Keep Source Column Widths	Column width of target cells is adjusted to the column width of the source cells
Formatting only	paste only the formatting of the source cells in the target cells, not the values.
Link Cells	Shows the data from the source cells in the target cells, target cells are adjusted when the source cells change.

In Excel 2003 and XP there is a big improvement in the “Clipboard”

(“Clipboard”)

In Excel 2000, the “Clipboard” is not developed but in 2003, and XP, we have a fully developed “Tasks panel”.

In Excel XP or 2003, you can view the clipboard by clicking “Edit” – “Office clipboard”.

We see the various “Copy” edits we have already carried out, along with a description or picture in the “Tasks panel”.

We can click on them to repeat them.

We can remove them by selecting one, clicking on the black arrow that appears and click “Delete”.

We can also remove all by clicking “Clear All” on top of the list.

By clicking on the “Options” button at the bottom, we can adjust the “Clipboard” to our needs.

 

 

 

“AutoFill” for “Cells”

With the “Fill handle” (remember lesson 3) we can also “Copy” formulas.

Click and drag the “Fill handle” over the cells where you want to “Copy” the “Formula”.
Leave the mouse pointer, if all formulas are required.

This is one of the most time-saving features of Excel

“Cut”, “Copy” and “Paste” by clicking and dragging

To “Cut” and “Paste” a cell to another location, we select the cell and click and drag to the desired place in our “Worksheet”.

To copy and paste a cell to another location, we select the cell and click and drag to the desired place in our “Worksheet”, but meanwhile, keep the “Ctrl” button on your keyboard pressed.

The “Undo” button

The “Undo” button makes a lot of change in Excel.

When we press this, our lasst action that we have performed in our “Worksheet” gets undone.

Clicking on the black arrow next to this button gives us a list of the “last” actions.

We can “Undo” everything in this list.

The “Redo” button
Does just the opposite of what the “Undo” button does. It repeats the last action performed.

Featured Courses

• Downloading iPad Apps
• Lesson 1: Introduction to Publisher
• Lesson 65: File Formats