Spreadsheet Concepts

What is a Spreadsheet?
A spreadsheet (or spreadsheet program) is software that permits numerical data to be used and to perform automatic calculations on numbers contained in a table. It is also possible to automate complex calculations by using a large number of parameters and by creating tables called worksheets.
In addition, spreadsheets can also easily produce graphic representations of the data entered:
• histograms
• curves
• sector charts
• ...

Therefore, the spreadsheet is a multi-use tool that works as well for secretarial activities that involve organising large quantities of data, as at the strategic and decisional level by creating graphical representation of synthesised information.
The Main Spreadsheets
Numerous spreadsheets have been produced by the main software companies. The main spreadsheets are:
• Microsoft Excel, in the Microsoft Office office suite
• Sun StarOffice Calc, in the StarOffice suite
• OpenCalc, in the OpenOffice suite
• IBM/Lotus 1-2-3 in the SmartSuite suite
• Corel Quattro Pro in the WordPerfect suite
• KSpread in the KOffice free suite from Linux
Examples in the following articles are based on the Microsoft Excel spreadsheet, but the other spreadsheets contain the same functionalities.

The Concept of a Worksheet
Spreadsheets display data and formulas in a table form (lines and columns) called a worksheet.

A worksheet is made of lines (numbered with numbers) and of columns (numbered with letters). The intersection of a line and a column is called a cell. A cell is therefore represented by a number and a letter.
A worksheet can contain up to 65,536 lines and 256 columns, more that 17 million cells.

Each of the cells on a worksheet may contain values. These values are entered directly (numbers, text, date, etc.) or are automatically calculated by the spreadsheet. This is referred to as formulas, meaning expressions that calculate a value in function of one or more values in other cells on the worksheet.
Spreadsheets have a large number of integrated functions for math calculations, statistics, etc.
Here is an example of a worksheet that contains student's grades for which the spreadsheet automatically calculates the averages:

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".

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.

Cell Content
A cell of a worksheet can contain a value or be empty. The value of a cell has two essential characteristics:
• a type, which means the intrinsic type of the data. There are generally three types of values:
o numeric values, for example 3.1415927, 58%, or 1984.
o alphanumeric values, for example Table2, Title or Name.
o formulas, which means expressions that represent calculations based on data and performed by the spreadsheet.
• a format that represents the way the spreadsheet displays the data. For example, if it is a number, the spreadsheet can be set to display it as an integer, as a decimal number (as well as the number of decimal places displayed), etc.
The cell may have a style, which means a particular visual appearance (font, size, color, border, etc.), independent of the value it contains.
Numeric Data Entry
When entering numbers in a cell (including monetary signs, percentages, etc.), the spreadsheet interprets the data as being numbers, which makes the use of mathematical formulas on the data possible. By default (without action on your part), the data recognized as being numbers will be right justified in the cell.

Alphanumeric Data Entry
When entering letters in a cell, the spreadsheet interprets the data as being alphanumeric, thus blocking the option of using mathematical tools on the data. By default (without action on your part), the data recognized as being alphanumeric will be left justified in the cell.

Entering a Formula
Formulas are expressions that, when they are interpreted by the spreadsheet, allow calculations to be performed using data present in other cells.
To enter a formula in a cell, select the cell and start entering the formula starting with the "equal" sign (=)!
If the syntax of the formula entered after the equal sign (=) is not correct, the spreadsheet will display an error message preceded with the # character! In case of an invalid cell reference, the spreadsheet will display the #REF! value.

A formula may contain references to other cells, expressions, which means operators that allow simple operations to be performed on the values of other cells, as well as functions.
The integrated functions of the spreadsheet allow elaborated calculations to be performed based on values in the spreadsheet, like averages, rounding, etc.
Defining the Format of a Cell
The format of a cell defines the way its value will be displayed by the spreadsheet. To define the format of a cell, click on the cell, go to the menu Format > Cell and choose the Number tab (or Numbers in StarOffice). The spreadsheet will offer a series of formats divided by category:
Excel StarOffice

http://en.kioskea.net/contents/tableur/tablintro.php3