W
hat makes a good spreadsheet? Two things: good design and good data.This may sound obvious, but it's all too easy to dive into creating a spreadsheet without putting much thought into its design. And a poorly designed spreadsheet is bound to make it harder to enter valid data. Without valid data, your spreadsheet will never yield useful results.
Good design doesn't mean making your spreadsheet look good – although spreadsheet appearance can definitely aid in usability and readability. Good spreadsheet design consists of organising your data logically and ensuring that all your formulae are correct.
A simple spreadsheet design
Let's take an ultra simple example: a share listing which displays the stock names, stockholding, and current share price of the stocks you own. Our spreadsheet (see Figure 1 below) will consist of four columns labelled Company, Current Shareholding, Current Share Price and Current Value. We'll enter the details for each company in a separate row under these columns.
Thus, if we're fortunate enough to have shares in Microsoft, IBM, Netscape, Apple (well, maybe not so fortunate!) and Yahoo, our spreadsheet will have column labels in cells A1 to D1 (remember cells are addressed by their column and row coordinates), thus:
A1: Company
B1: Current Shareholding
C1: Current Share Price
D1: Current Value
The details for Microsoft will be entered in the second row, that is, cells A2 to D2; for IBM in cells A3 to D3; for Netscape in cells A4 to D4; and so on.
Figure 1. (Click the image above to see a full-sized screenshot.)
When you're entering data, you simply click in a cell to position the cursor, type the value into the cell, and move to a new cell by using the arrow keys or (in most spreadsheets) by pressing the tab key.
Using formulae
Where do the values come from that appear in the Current Value column?
Well, you could manually multiply the Current Shareholding by the Current Share Price. But that's what your spreadsheet is for: doing the manual labour of calculating. So, instead of working out the value and typing it in the appropriate row in column D, we insert a formula in column D instead of a value.
The formula for the Current Value of our Microsoft stocks, for example, is B2*C2 (that is, the value contained in cell B2 multiplied by the value in cell C2) and the result is placed in cell D2. The value of our IBM stocks is derived by the formula B3*C3, and the formula is placed in cell D3.
Once you've placed a formula in a cell, you no longer have to bother about working out the calculation yourself. More importantly, any time you change the contents of a cell referenced by that formula, the contents of the cell containing the formula will be updated to show the new result.
For instance, if we have 1000 Microsoft shares (we wish!) which we bought yesterday at $142.00 per share, and the price rises today to $142.93, all we need to do is type in the new share price in cell C2 and the Current Value in cell D2 will be automatically updated to a healthy $142,930.00.
That's the benefit of using formulae: you enter the formula once in the appropriate cell, and from then on the value displayed in that cell will reflect the latest data typed into your spreadsheet.
Eliminating repetitive work
This is all very well, but it still seems like a fair amount of work.
What if we had a portfolio of 60 different shareholdings, instead of our measly (but valuable) five? Would we have to type in 60 different formulae in column D to find out the current value of each of our shareholdings?
No. Spreadsheets are much smarter than that. Instead of creating a series of formulae such as B2*C2, B3*C3, B4*C4 and so on, you create the first formula, and then copy and paste it. The spreadsheet will not only copy the formula to the destination cells you select, but it will update the formula to reflect its new position.
For example, we place the formula B2*C2 in cell D2 to get the Current Value of our Microsoft shareholding. We select that formula and copy it (the copying method differs slightly from one spreadsheet program to another), and then select cells D3 to D6 and paste the formula into them.
Of course, we don't want the exact formula (B2*C2) copied, or the Current Value column will repeatedly show the value of our Microsoft shareholding. Fortunately, spreadsheets are smart enough to adapt the formula to its current position, filling in the appropriate B3*C3, B4*C4 and so on. If we have 60 different stocks, all we do is create the formula, copy it, select the next 59 cells in column D, and paste the formula. The spreadsheet will do all the hard work of pasting the appropriate formula in each cell.
Automatic numbering and labelling
Spreadsheets are wonderful at eliminating repetitive work. Not only can you automatically copy formulae in this fashion, but you'll find most spreadsheets also have a feature called 'auto-series' which automatically inserts series of numbers, dates, days or months.
For instance, to place the months of the year across the top of a spreadsheet, type "January" in the first column, select the next 11 cells and click the auto-fill button (with some spreadsheets, you can simply click-and-drag a 'handle' in the bottom right corner of the cell to get the same effect).
Apart from these auto-series and formula shortcuts, every spreadsheet includes an auto-sum shortcut. Probably the most common type of spreadsheet task is the quick totting up of a row or column of numbers. Spreadsheets turn this calculation into a one-click task: click beside or beneath the numbers you want summed, and click the auto-sum button on the toolbar. A formula summing the numbers is placed in the cell immediately to the right or beneath the appropriate numbers.
Other spreadsheet shortcuts
Depending on the power of the spreadsheet program you're using, you'll find endless variations on such shortcuts. Most spreadsheets, for example, don't force you to type in cryptic ranges such as D9:L27. Instead, you can click and drag to select the range of cells your formula will reference, and the spreadsheet inserts that range automatically in the formula.
Other spreadsheets, such as Microsoft Excel and Lotus 1-2-3, get really fancy and let you choose names for individual cells or ranges of cells or refer to cells by their row and column labels.
For example, if we have the months listed across our columns and budget items such as Petrol, Rent, Phone and so on as our row labels, in Excel we can refer to a cell's contents as January Rent or March Phone. Not only does this make formulae much easier to understand, it also eliminates many errors that result from trying to get our cell addresses correct.
Useable spreadsheets
This leads us back to our starting point: good spreadsheet design.
As you can see, it's not at all difficult to get disoriented in the maze of cell references. You need to pay close attention to ensure that your spreadsheet formulae perform the right actions on the correct range of cells.
Before you start creating any spreadsheet, think ahead and decide how you want to label your columns and rows. Sometimes the number of rows or columns in a spreadsheet will be fixed, sometimes it will increase over time. In our share tracking spreadsheet, for example, the columns are fixed but we'll add (or subtract) rows as we change the stocks in our share portfolio. You can always insert or delete rows and columns later on, and copy formulae to the newly created cells.
When you enter formulae, check and double-check the row and column coordinates. You'll often find it easier to check your formulae if you enter some test data in your spreadsheet, check the results, alter the test data and re-check the results. If your software lets you, use descriptive labels wherever possible instead of column and row coordinates, so your formulae are easier to understand and check.
Checking your design
Make sure you not only check the cell's contents but also use the data entry and address boxes at the top of the screen to double-check your position and the entire contents of a cell. Sometimes the contents of a cell are not totally visible, and the data entry box will provide a quick means to see the entire contents.
If you find a cell contains a series of hash marks (#), it means the value the cell contains is too large to be displayed. You can remedy this by resizing the column (usually a matter of positioning the cursor at the top of the worksheet on the line separating one column from another, and clicking and dragging the double-headed arrow until the column is the desired width).
Finally, you can use different typefaces and colour to make your spreadsheet easier to read, as in Figure 2. You can choose different background and font colours for different segments of your spreadsheet, enabling you to highlight important information and distinguish different areas of the spreadsheet. Make sure you use these formatting features judiciously: the aim is not to dazzle, but to make your spreadsheet easier to use and understand.
Figure 2. (Click the image above to see a full-sized screenshot.)
If you take care in your spreadsheet design, the task of data entry will be that much easier. It's the combination of good design and correct data entry that will yield you useful results.
Coming up
So far, we've touched on only the very simplest type of spreadsheet. Even with the basic spreadsheet components you'll find in Microsoft Works and Claris Works you can do much more sophisticated tasks than we've discussed. And with the powerhouse programs such as Lotus 1-2-3, Corel's Quattro Pro and Microsoft Excel, there seem to be almost no limits to what you can do in a spreadsheet.
In the next article in this series we'll look at some more advanced spreadsheet tasks and features, and assess which is the spreadsheet for you.
© 1997, Rose Vines
Straight on to Part III: Charts & Choices
|
Host your site where I host mine |
 |
Support geekgirl'sDo you find the tutorialson this site useful? If so, please show your support by kicking in a few bucks to help buy computers for the wonderful orphanages run by the Afghan organisation, afceco.org. For a small amount, it is possible to make a difference in an area of the world which is hurting badly. |
|
|
| Want to know more? Read this post on my blog. |
| top | home |