The Gnumeric Manual
PrevChapter 6. Entering dataNext

6.3. Cell Referencing

Each cell in a spreadsheet is named by its column and row labels. The row labels are numbers and the column labels are letters. The first cell, therefore, is called A1. One row down and one column over is cell B2, and so forth.

To reference the value stored in a single cell, enter its coordinates as a function argument. For example, to have the data in cell B1 appear in another cell, enter =B1 into that cell.

Example 6-6. Some examples of function syntax

      =EXP(B1)
      
      =COS(A2)

6.3.1. Absolute cell referencing

Cells can be referenced in the default way (relative referencing), or by using absolute referening. Absolute referencing means that when the cell is copied, the cell reference does not change. Normally, autofilling a cell range or moving cell will change its cell reference to so that it maintains a relation to the original cell. Absolute referencing prevents these changes.

TipWhen Does Relative Referencing Make a Difference?
 

The difference between absolute and relative cell references only matters if you are copying or moving cells that contain cell references. For cells that are going to remain in place, both the relative and absolute references have the same result.

Example 6-7. Relative References

For example, if =A1 is the formula entered into cell B2, cell B2 will displat the data in cell A1, which is one row up and one column left. Then, if you copy the contents of B2 to cell F6, cell F6 will contain the value from E5, which is also one row up and one column left.

For the copied cell to still refer to A1, specify absolute references using the $ character: $A$1 refers to cell A1, no matter where it is copied.

The format for absolute cell refencing is to use a '$' in front of the cell coordinate that the you want to stay constant. The column, row, sheet, or any combination of these can be held constant.

Example 6-8. Absolute cell referencing examples

What happens when a given formula is entered into cell B2, then copied to other cells?

=A1

=A1 is a normal, or relative, cell reference function. When =A1 is entered into cell B2, it refers to the value of data one cell up and one cell left from the cell it is in. Therefore the value displayed in cell C2 will be the value of data in cell B1. Copied to cell R19, the formula will display the data in cell Q18.

=$A1

In this case, the column value is absolute, but the row value is relative. Therefore, if =$A1 is entered into cell B2, the formula refers to the data in column A that is one row up from the current location. Copied to cell C2, the formula will refer to the data in cell A1. Copied to cell R19, it will refer to the data in A18.

=A$1

This formula uses a relative column value and an absolute row value. In cell B2, it refers to cell A1 as the data in the cell one column left and in row 1. Copied to cell C3, the formula will display the data in cell B1.

=$A$1

No matter where this formula is copied, it will always refer to the data in cell A1.

6.3.2. Referencing multiple cells

Many functions can take multiple cells as arguments. This can either be a comma separated list, an array, or any combination thereof.

6.3.2.1. Multiple individual cells

A comma separated list of cell references can be used to indicate cells that are discontinuous.

Example 6-9. Some examples of function syntax

	  =SUM(A1,B2,C4)
	  
	  =MIN(A1,B2, C4,C5,D6)

6.3.2.2. Referencing a continuous region of cells

For functions that take more than one argument, it is often easier to reference the cells as a group. This can include cells in sets horizontally, vertically, or in arrays.

The ':' operator is used to indicate a range of cells. The basic syntax is upper left corner:bottom right corner.

Example 6-10. Referencing blocks of cells

	  =SUM(A1:E1)
	  
	  =AVERAGE(B4:E7)
	  
	  =MIN(A1:A5)

6.3.2.3. Referencing non-continuous regions

For referencing cells that are in non-continuous regions, you can use any combination of the above methods to get the needed cells.

Example 6-11. Referencing blocks of cells

	  =SUM(A1:E1, B19, L14:L17)
	  
	  =AVERAGE(A1,A3, A5:C5)

6.3.3. Referencing cells on other sheets

It is possible to reference cells which are not part of the current sheet. This is done using the NAME!CELL syntax, where NAME is an identifier (usually a sheet name) and CELL is a regular cell reference as described in the previous sections.

Note that if NAME contains spaces, you need to quote the whole name to allow Gnumeric to group the separate words in NAME as single name. For example, you should use "Sheet 0" when referencing the default created "Sheet 0".

Example 6-12. Referencing values in other sheets

	='Sheet 0'!A1+'Sheet 3'!A5

	=SUM('Sheet 1'!A1:'Sheet 1'!A5)
PrevHomeNext
FormulasUpSelecting Cells While Entering Formulas