Introduction to Spreadsheets
The spreadsheet was invented by Dan Bricklin in 1979. The program he and Bob Frankston wrote was called VisiCalc, an abbreviation for “visible calculations”. VisiCalc was a novelty in computer software. Nothing like it existed on any computer, large or small. It kept track of tabular data, such as financial spreadsheets, using the computer's screen like a window through which a large table of data was viewed. The “window” could slide across a table, displaying different parts of it.
What Bricklin had invented was a thing of beauty; not the VisiCalc program itself, but the spreadsheet paradigm. The invention was the sliding window on an infinitely large blackboard.
The VisiCalc program simulated paper-and-pencil operations, but went dramatically beyond that. Data entered in rows and columns in a table could be interrelated so that changing one value in the table caused other values to change correspondingly. This “what-if” capability made VisiCalc appealing: one could enter budget figures , for instance, and see at once what would happen to the other values if one particular value were changed by a certain amount.
In the following examples we'll be using LibreOffice 3.33. Other spreadsheets may differ in small details.
Let's look at the basic spreadsheet. At the top are the column headings A, B, C and so on.
Down the left side are the line numbers, starting at 1. The highlighted cell is A1.
You can highlight a row by clicking on the number to the left of that row. You can highlight a column by clicking on the letter at the top of that column. You can highlight the whole spreadsheet by clicking on the unlabelled cell at the top left where the rows and columns intersect. These actions are useful when you want to change the formatting of a row, a column, or the whole spreadsheet.
I want to insert sums of money into my spreadsheet. To select an appropriate format, I click on Format | Cells | Numbers. One possible choice is Currency. What you see is determined by which Format you have selected: I have Australian. So Currency will show amounts with a leading dollar sign, two decimal places and a comma for thousands: $1,234.00.
I don't like showing the $ sign, so instead of using Currency I'll choose Number. By default my version shows zero decimal places, so to change that to two decimal places I first highlight the whole spreadsheet and then change decimal to two and press Enter. Now any numbers I enter anywhere will show like 1234.00.
Now that we have the formatting done, let's start setting up our spreadsheet. We'll put in three column headings: Date in A1, Description in B1, and Amount in C1. In my default font of Arial 10, the headings look too small and insignificant, so we'll change them. Highlight Row 1, and select 12-pt Bold. This looks better, but makes the heading too big to fit in B1.
Move the cursor to the line separating B and C in the row containing the row letters. See the cursor change to a double-headed arrow? Hold down the left mouse button and drag to the right until the full title is visible. Let go the mouse button and that column alone will be wider.
Notice that the headings are all left-justified. I prefer centred headings, so highlight the row and select Align Centre Horizontally from the Formatting Toolbar.1
Next we can enter some data: A2 21/09/11; B2 telephone; C2 127.
What's gone wrong? A2 shows 40807.00.
Right, we formatted the whole spreadsheet as Number. So we have to highlight column A and format it as Date. That fixes it.
Sometimes our spreadsheet becomes so big that it doesn't all fit on the screen. You can emulate this on our sample spreadsheet by pressing PageDown. Notice how our headings disappear. Similarly if we have a lot of columns we may not be able to see what we need to in column A, i.e. the date. To allow you to see the column headings and the first column at all times, click on cell B2. Then go to Window and click on Freeze. Notice how heavy lines mark off Row 1 and Column A. Now wherever you go in the spreadsheet these column headings and column 1 will always be visible.
To print headings on each page of a multi-page spreadsheet, go to Format | Print Range | Edit and specify the ranges you want to print on each page.
Every time you make a change to a spreadsheet a red exclamation point ! appears on the status bar below the spreadsheet. The help message, displayed by putting the cursor on the exclamation point, reads “The document has been modified. Double-click to save the document”. Also the save icon in the toolbar (the one that looks like a floppy disk) is greyed out when no changes have been made, but is restored when a change has been made.
To save changes automatically at a specified interval (the default is every fifteen minutes) go to Tools | Options | Load/Save | General and tick Save AutoRecovery Information every xx minutes.