0

While word processing programs are the most commonly used office application and browsers are probably the most commonly used application of all, much of the success of personal computers is due to spreadsheets.

In 1979, two blokes – Dan Bricklin and Bob Frankston – developed the first spreadsheet. Their creation was called VisiCalc and it helped turn the Apple II computer into something more than simply a hobbyist’s plaything. In fact, VisiCalc was the first application developed for a microcomputer that really sold the computer. Four years later, the launch of Lotus 1-2-3 did the same thing for the IBM PC, turning the still developing new product into a must-have item in the business world.

What spreadsheets do

What made spreadsheets so hot? Well, to understand that you need to be able to picture the type of problems spreadsheets solve and what life was like before the advent of computer spreadsheets.

Imagine you own a business that manufactures widgets. As in any business, in order to make a profit you need to ensure your income exceeds your expenses. You need to track things such as the cost of your raw materials, labour costs, utilities, maintenance, employee benefits, government fees, advertising, distribution costs and so on. On the income side, you need to charge prices that cover your costs and allow for a profit, while maintaining your competitiveness in the market.

Trying to track this information by hand involves listing all the different expense categories, the amounts you spend in each, the cost of producing each item in your inventory, the margin you sell it at, your sales volume on each type of widget, and so on. Picture all these figures in a grid of rows and columns, with categories listed down the side and months across the top to track expenses over time. Numbers in one cell will depend on the values in others, and so the whole grid will be dynamic.

Before computer spreadsheets, companies needing to track this sort of information would create vast grids of figures, either on long blackboards (sometimes stretching the length of rooms) or on massive, unwieldy sheets of paper. As one figure was updated, any dependent numbers would need to be adjusted to reflect the changes, necessitating much rubbing out and rewriting. Because the grid contained not just simple figures but also relationships between figures (formulae) you can imagine how easy it was in a spreadsheet of any size to let an error slip in.

Computerised number crunching

Enter computer-based spreadsheets. In a way, spreadsheets and computers are the perfect fit. You’ve probably heard computers referred to as “number crunchers”. Well, with spreadsheets that’s exactly what they do: ingest masses of numbers, perform calculations at dazzling speed, and spit out the answer.

In a computer-based spreadsheet, you define your row and column categories, and then in each cell you type in the appropriate value, or a formula showing how the value in that cell should be derived from other values in the spreadsheet.

Once you’ve set up the relationships between figures in the spreadsheet (which could be as simple as summing a column of figures to arrive at a total, or could involve much more complex formulae) and entered your initial data, you’ve done the bulk of the work. If you have to update a number, you type the new value in the appropriate cell and the spreadsheet performs all the necessary recalculations. No rubbing out, no massive recalculations, no need to remember all the dependent relationships and make sure you update each one.

Getting the design right

Obviously what you do need to ensure is that your original spreadsheet design is correct, that you’ve used both accurate modelling of your information as well as the correct formulae applied to the correct numbers, and that data you type in is accurate.

While computers take the vast bulk of the grunt work out of creating and maintaining spreadsheets, the old GIGO adage (garbage in, garbage out) still applies, something you’ll need to focus on when you start creating your own spreadsheets.

What you can do with a spreadsheet

While tracking the income and expenditure of a large company may be out of your league, spreadsheets can be put to a myriad different uses.

You can use a spreadsheet to budget your personal finances or keep track of your investments. You can follow loan repayments or compare the benefits of different mortgages. You can analyse the weather patterns in your area, perform classroom or meeting room timetabling, assess the performance of students in a class, create invoices, and much, much more.

Not only are spreadsheets great for tracking your current financial position, they also excel at “what if?” scenarios. What will my bottom line look like if I take an extended holiday this year? How soon can I pay off that loan if I pay fortnightly instead of monthly?

The great thing for beginners is that most spreadsheet programs come with a swag of pre-designed ‘solutions’ which have all the basic layouts and formulae worked out for you. All you need to do is customise them (if need be) and enter your own data.

Finding your way around

Considering there are dozens of spreadsheet programs on the market, it’s a tribute to Bricklin and Frankston’s original design that almost all of them look pretty much the same and work in similar ways. Lotus 1-2-3 also set its stamp on how a spreadsheet should appear and function, so much so that it later tried to prosecute other spreadsheet developers for stealing its “look and feel”.

While you’ll find different features, twists and approaches in the various spreadsheets, if you get to know one you’ll have a flying start in using almost any other spreadsheet available. Even the simple spreadsheets included in low-end integrated products such as Microsoft Works have a lot in common with up-market offerings such as Microsoft Excel.

Spreadsheet jargon

All spreadsheets basically consist of a grid of cells. The columns are labelled across the top, usually from A to Z, and then from AA to AZ and so on. The rows are numbered down the side. Even simple spreadsheet programs let you have at least 256 columns and at least that many rows. The more powerful spreadsheets increase these limits to thousands of columns and rows (and yes, some people actually make use of such power).

Each cell in the spreadsheet can be uniquely identified by its cell address, which is the intersection of the column and row. For example, the first cell in column A is called A1. The 29th cell in column H is H29. When you click in a cell, you’ll see its address displayed in the address box, usually near the top left of the window.

You can also refer to a range of cells by specifying the first and last cells in the range, separated by a colon. Thus, L12:R19 refers to a range of cells starting in column L, row 12 and extending to column R row 19. To select a range of cells, click in the first cell and, keeping the mouse button depressed, drag the mouse pointer to the last cell you want to select and then release the mouse button. The address of the selected range will appear in the address box.

You can also select a whole column by clicking the column label (A, B, C and so on) or select an entire row by clicking the row label (1, 2, 3, et cetera). To select multiple rows or columns, click-and-drag from the first column or row to the final column or row you wish to select.

A window on your data

Obviously, you’re not going to be able to see at a glance any spreadsheet that spreads across even a few dozen columns or rows: that’s too much information to display in a single window on screen.

Instead, you should think of the screen as displaying a movable window that you can pan across your spreadsheet, using the scroll bars at the bottom and right sides of the window.

Advanced spreadsheets provide ways of hiding some of the details in your spreadsheet so you can quickly see summary or totals columns and get a better overall picture of your information. Such programs also let you split information across multiple “worksheets”, and then consolidate the figures into summary worksheets, so your information is viewable in more easily digested chunks.

Entering data

Near the top of the spreadsheet window, usually immediately below the toolbars, you’ll find a white box that stretches most of the way across the window. This is the data entry area.

What? Don’t you enter information directly into the grid of cells? Well, the answer is “yes and no”. Certainly you can type data and formulae directly into the cells themselves, but, as you’ll soon discover, the cells are often too small to display the complete contents of what you need to type.

For instance, if a cell contains a formula summing the values in cells C1 to C10 (which will appear something like =SUM(C1:C10), depending on which spreadsheet you’re using), you’ll find that there isn’t enough room to display this complete formula when you type in the cell. While it’s an easy task to change the width and height of cells, it’s even easier to use the data entry box to enter the contents of a cell.

You’ll find you can type a value or formula into either the data entry box or the cell itself – the choice is yours. In a way, it’s easiest to use both: type directly into the cells (as this helps you ‘position’ yourself mentally in the spreadsheet and ensures you are placing a value in the correct cell), but check the complete entry as it appears in the data entry box.

Values and contents

The other reason for having the data entry box is that usually cells display the final results of calculations, not the formulae on which they’re based. For instance, if you have a series of values in cells C1 to C10 and want to display the sum of those values in C11, the contents of C11 is SUM(C1:C10). While this formula remains the same and will be displayed in the data entry box whenever you select cell C11, the value of the contents in C11 will change depending on the numbers contained in cells C1 to C10.

Not only can spreadsheet cells contain numbers or formulae, they can also contain text, dates, times and currency values. You can alter the appearance of cells by changing their size, by selecting a specific type of number, currency or date format, by altering the alignment of the contents of cells, or by applying formatting such as bolding, italicising, and coloured text or backgrounds.