• Friday July 3, 2009 - 7:53:02 pm BST
• registered : 1500497
• online : 52779
• questions/day : 4706
• Answer rate : 76.68%

The Concept of a Cell

A "cell" is the intersection between a line (horizontal) and a column (vertical) on a worksheet. Thus, the name of the line combined with the name of the column gives the cell's coordinates (the term address is sometimes also used).

Coordinates (called reference styles) are generally grouped into two types, depending on the worksheet:

• The mode known as L1C1 (Line 1, Column 1), where the cell is located by the line number preceded by the letter L and the column number preceded by the letter C. L12C34 designates the cell at the intersection of the 12^th line and the 34^th column.

  

• The mode known as A1, where line numbers are designated by numbers and columns by letters. Thus AA17 designates the cell at the intersection of the 27^th column and the 17^th line.

  

Most spreadsheets have options to allow both modes to be used. With Excel and StarOffice, go into Tools > Options > General and click the box "L1C1 Reference Style".

Cell References

In order to work with data from cells when performing calculations, it is necessary to reference the cells. Many ways of referencing cells exist:

• absolute reference
• relative reference
• mixed reference
• named reference

Absolute References

An absolute reference represents the method of distinguishing a unique cell in a worksheet. Depending on the reference mode (L1C1 or A1), the absolute reference will be written differently:

• In L1C1 mode: a cell's absolute reference is written by preceding the number by a letter L and the column number by the letter C.

  LLineNumberCColumnNumber

  For example, L12C24 represents the cell located at the intersection of line 12 and column 24.
• In A1 mode: a cell's absolute reference is written by preceding the line number and the column number by the $ sign.

  $ColumnLetter$LineNumber

  For example, $AC$34 represents the cell located at the intersection of the indicated column, AC, and line, 34.

Relative References

A cell's relative reference is the expression of its position relative to another cell. Thus, the relative reference gives the difference (in terms of the number of lines and columns) between the cell (called reference) and the target cell (called referenced cell). By convention, upward difference along the vertical axis and difference towards the left along the horizontal axis are negative.

• In L1C1 mode: the relative difference of a cell is shown as cell coordinates in parenthesis:

  L(LineNumber) C(ColumnNumber)

  For example, L(3)C(-2) represents a cell located 3 lines lower and 2 columns left of the reference cell:

  

  When there is no difference, a zero is not required in the parenthesis. Thus, L(0)C(12) may be written as LC(12).

• In A1 mode, the expression of the difference between the cells is hidden. In effect, a relative reference in A1 mode is implicit: just click on the coordinates of the target cell (referenced) without writing the $ sign:

  

Mixed References

A mixed reference is a reference where the horizontal position is expressed in an absolute manner and the vertical position in a relative manner, or vice-versa.

• In L1C1 notation, a mixed reference could look like L2C(3) or L(4)C17.
• In A1 notation, a mixed reference could look like $C5 or F$18.

Named References

A name may be given to a cell or to a group of cells.

To name a cell in Excel, select the cell or the range of cells to be named and go to Insert > Name > Define

When a cell or the range of cells has a name (the term label is sometimes used), it may be referenced by name. This functionality is especially useful when certain cells or cell ranges contain characteristic data because they may be referenced by name even if the cell or the group of cells has been moved.

For example, on an invoice, using a cell name such as total_bt for the cell that gives the total of an order before taxes is a good idea. You can also create a cell called VAT that contains the value of the VAT. Thus, when you need to calculate the total with taxes included, it is as easy as multiplying the cell called total_bt with the cell called VAT.

Comments

Commentary (sometimes called an annotation) may be added to a cell to provide additional information that we do not wish (or that we cannot) show on the worksheet.

To add a comment to a cell in Excel, select the cell or the range of cells to be named and then go to Insert > Comment or click on the cell or group of cells with the right mouse button and choose Insert a comment.

The small red triangle on the upper right of the cell D5 shows that the cell has a comment. This encourages the user to slide the mouse over the cell to read its contents.