Spreadsheets

A spreadsheet application lets you work on tables of information, including columns containing calculations. This kind of document is ideal for simple records, where the data is best seen in a single window. One of the most common spreadsheet applications is Microsoft’s Excel program, although multipurpose applications, such as AppleWorks for the Mac OS, can also be used.

  Inside a Spreadsheet Application

A spreadsheet document stores data in cells that are within a grid, as shown in this example:-

Each cell, which is usually enclosed by dotted lines, contains formatted text, a number or a formula. The latter calculations create text or numbers, usually from the contents of other cells.

The borders of each cell can often be customised or made invisible, while the headings of rows or columns can be hidden. The height and width of each row or column can be adjusted, or even shrunk to zero if you don’t want to see those cells that are used purely for calculation purposes.

Formulae

Most formulae need to refer to other cells. They do this by means of a cell reference, consisting of the default column heading letter followed by the row heading number. In the example shown above, the cell B7 contains the text TOTAL while cell C7 contains the formula =SUM(C2…C5).

This example formula uses a relative reference, meaning that if you insert or remove a row or column it stills work with the original data. However, if you want a formula to refer to a cell at a fixed position (as opposed to the data’s position) you must use an absolute reference in either or both of its parts. This is shown by means of a dollar prefix, as in, for example, $B$7, C$8 or $D5.

Spreadsheet Files

Most spreadsheet applications use a specific file format that’s only understood by the one program. However such files can be opened in other applications if you have a suitable file translator.

If you can’t find a file translator you can an Excel or generic file, as listed below in order of preference. The filename extensions and Classic Mac OS type codes are shown for completeness.

Excel Document  .xls  XLS3/XLS5

As generated by Microsoft’s Excel application, which is the ‘industry standard’ program for making spreadsheets. Note that older versions of this application may not be able to open newer files.

Symbolic Link (SYLK)  .sk/.slk/.syk/.syl/.sylk  TEXT

SYLK is a useful generic file that retains formatted text and numbers, formulae, column widths and cell alignments. Many applications support it, including Excel, Resolve and Wingz.

dBase File (DBF)  .dbf  F+DB

This kind of file, created by dBase II, dBase III and other applications, conveys unformatted data. It can easily be converted into a CSV file (see below) for use with Excel or database applications.

If you examine the contents of a DBF using a text editor you’ll discover that it contains dBase field types, which are defined as follows:-

Field TypeContents
CCharacter (text field)
NNumeric (decimals and integers)
DDate (in the form:YYYY/MM/DD)
LLogic (? = undefined, Y/N, T/F )
MMemo

Document Interchange File (DIF)  .dif  TEXT

This format is used for unformatted data. It’s employed on PCs, in the original AppleWorks, as used on the Apple II computer, and in the VisiCalc application.

Plain Text — Tab Separated Values (TSV)  .txt/.tsv/.tdf  TEXT

This kind of file, also known as tab delimited text, only conveys unformatted data. Any non-ASCII characters and line endings must be corrected for use on other computer platforms.

When viewed in a text editor the content of this kind of file will look something like this:-

Smith<HT>35.77<CR>

Jones<HT>44.50<CR>

Wilson<HT>65.89<CR>

Harris<HT>39.23<CR>

<HT><CR>

TOTAL<HT>185.39<CR>

where <HT> indicates a HT (Horizontal Tab) control code and <CR> represents a CR (carriage return). As you can see, a tab delimits each column, except at the end of a row, where a CR is used.

Plain Text — Comma Separated Values (CSV)  .txt/.csv  TEXT

This kind of file is similar to a TSV file (see above), except that a , (comma) is used as a delimiter. Unfortunately, this means that commas can’t be used in the entries themselves, which is highly inconvenient, especially for those countries that use a comma as a decimal point.

When viewed in a text editor the content of this kind of file will look something like this:-

Smith,35.77<CR>

Jones,44.50<CR>

Wilson,65.89<CR>

Harris,39.23<CR>

,<CR>

TOTAL,185.39<CR>

which shows each column delimited by a comma, except at the end of a row, where a CR is used.

Transfer Tips

Sometimes, when transferring data between spreadsheets and other applications, you can encounter unexpected results. Here are a few hints:-

©Ray White 2004.