Unit 4. Basic. References and Names


References.

 

Working with Excel, specially when we use formulas and functions, is very likely that we will type references to a cell or group of cells that are not those where the formula is.

A references is a conection to something, that is to say, when we type in the formula =SUM(A1;B1) we refer to the sum of the content of A1 and the content of B1.

There are three types of references:

 

Relative Refence: Reference to rows and columns changes when we copy the formula in another cell. That is to say, the formula adapts to its environment because references relate to the distance between the formula and the cells that form the formula. This is the option Excel offers by default.

Let's see it in the following example:

  A B
1

15

20

2

=A1+2

30

3    

If we copy cell A2 in B3, because we move one column to the right and one row down, the formula will change to: =B2+2 . What varies is the reference to the cell A1 , because we have copy the formula in a cell that is in the column to the right, the name of the column will change from A to B and because we have gone one row down, 1 will become 2 , the result will be =B2+2 . This maintains the formula that adds two to the content of the cell above.

Absolute Reference: References to rows and columns do not change when you copy the formula to another cell, the formula's reference to cells are fixed.

Let's see it in the following example:

  A B
1

15

20

2

=$A$1+2

30

3    

If we copy now the cell A2 in B3 , even though we have copied it one column to the right and one row down, the formula will not change. The sign $ before the column and the row indicates that. So B3 will show =$A$1+2 .

 

Mixed Reference: This is a combination of both references. You may have relative references for the rows and absolute for the columns, or vice-versa.

Let's see it in the following example

  A B
1

15

20

2

=$A1+2

30

3    

If you copy the cell A2 in B3 , the sign $ before the column will mean that the formula will not change although you move to the right. But not having the sign before the row, when you copy the formula one row down it will change to 2 instead of 1 and the result will be  =$A2+2 .

Let's see it in the following example:

  A B
1

15

20

2

=A$1+2

30

3    

If you copy cell A2 in B3 , the sign $ before the row will mean that the formula will not change when you copy it one row down. But not having the sign $ before the column, when you copy it one column to the right it will change to B instead of A and the result will be =B$1+2 .

 

How to change the reference type

 

Once you can distinguish among the different types of reference and are able to know which one you need, you have the option of changing reference typing in the cell.*

Relative references are typed by means of the letter and the number that intersect on the cell (A1,B3,D1...).

In order to make a reference change to absolut, that is to say, in order to make it fixed, you must type before the column and the row the sign $ ($A$2, $B$3, $D$1...).

Mixed references, as we have seen, are a mixture of relative and absolute, therefore they can be like this: $A2, B$3, $D1...

 

Instead of indicating the type of reference typing it, you can do it when you edit the formula. When you include the cells referred to press the key F4 you will see how it changes to the possible types of references that you can have for a cell. *

 

References to other sheets or books

 

Another interesting feature of references is the possibility of typing references to cells that are in other sheets or even books.

References to other sheets.

To make reference to cells in other sheets you must indicate the sheet name and type next to it the exclamation mark sign and the cell's name.

For example: Sheet2!A2 , this reference is directing to cell A2 of Sheet 2.

If the sheet had a personalised name with spaces, the reference would be: 'Sheet name'!A2, and the sheet name will be typed between inverted comas (").

References to other books.

To refer to cells in other books you must indicate the book's name in square brackets and the rest as we have just seen.

For example: '[budget 2003]Sheet1'!B2, will indicate that the cell is in book "Budget 2003", in Sheet1 and in cell B2.

Very important: Please bare in mind that when you write a sequence of characters that includes spaces, you must always type them between single inverted comas.



   
   
Legal warning: Authorised on-line use only. It is not allowed the use of these courses in companies or private teaching centres.
© aulaClic. All rights reserved. Reproduction in any form whatsoever is prohibited.
November-2005.