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 12th line and the 34th 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 27th column and the 17th 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".|
In order to work with data from cells when
performing calculations, it is necessary to reference the cells. Many
ways of referencing cells exist:
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.
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.
For example, $AC$34 represents the cell located at the intersection of the indicated column, AC, and line, 34.
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:
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:
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.
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.
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.|