How to create your first spreadsheet. (Compute's Getting Started with Spreadsheets)
by Richard O. Mann
If you've never used a spreadsheet before, your first look at the screenful of empty rows and columns can be unnerving. What can you do with it? How does it work? Let's create a basic Lotus 1-2-3 spreadsheet to find out. (Don't worry if your spreadsheet isn't 1-2-3. All spreadsheets are similar enough in basic operations for you to follow this example.)
Step 1: Plan the Project
Until you get the hang of it, it's a good idea to sketch the layout of your sheet on paper before you start. This project won't require that, but if you'll refer to the accompanying illustration, you'll see what we're trying to create.
This is a teenager's simplified quarterly budget. Matt wants to have enough money on hand to finance a big prom date, including a chauffeured limousine. Using a spreadsheet allows Matt to see clearly what it will take to meet his goal.
Step 2: Enter Labels
The work sheet heading, the column headings, and the row captions are all merely typed into the spreadsheet. Move the cursor to the appropriate cell with the arrow keys, and type. You'll need to widen column A: type / to get the menu, W for Work sheet, C for Column, S for Set Width, and then either enter the width desired or use the arrow keys to widen it visually.
Step 3: Enter Formulas
The totals in column E and the numbers in rows 10, 12, 17, and 19 are computed by the spreadsheet from formulas that you enter in the appropriate cells.
Column E will be the sum of the amounts for the three months. Put the cursor in cell E7 and enter (B7..D7). You can also use the arrow keys to return. A 0 will appear in cell E7 because there are not yet any numbers in the cells it added. As we enter numbers in those cells, the total in E7 will change.
Then repeat this operation for cells E8, E9, and E12 to E16. There's a faster way, though. Type /C (for Copy), point of E7, hit return, and then point to the place you'd like the contents of E7 copied--in this case, E8. Look at the contents of E8 now, and you'll see that the spreadsheet copied the formula but changed it to refer to the current row 8. This is extremely handy and a great timesaver, as it means you can use the Copy function to fill all the cross-totaled cells.
Put the cursor in cell B10. We want this to be the total of the three items above it, so type @SUM(B7..B9) or use the arrow keys to point to the cells. (I almost always use the arrow keys.) Copy the formula into cells C10 through E10. Then go down and perform a similar operation on cell B17, which will be @SUM(B12..B16), and copy it to C17 through E17.
Cell B19's formula is +B10-B17. C19 through E19 follow the same pattern. Copying formulas is a fast way to build spreadsheets.
Now for the odd one. Row 12 reflects Matt's commitment to save 20 percent of all income for his college fund. He doesn't want to figure out the amount anew each time he changes something in his plan. The spreadsheet can do that. Go to B12 and enter +B10*.2. This tells the sheet to get the total income and multiply it by .2, or 20 percent. Every time anything that affects total income changes, his savings amount will change, as will everything on the sheet that depends on the savings amount. Follow the pattern to fill in C12 through D12.
Step 4: Enter Data
Now Matt enters his estimates, which include all the cells we haven't put a formula into yet. Note that he leaves B16 and C16 blank, since there are no prom expenses in those months.
As each number is entered, the information ripples through the sheet, and all the appropriate totals change. It's fun to watch.
Step 5: Evaluate the Results
Matt didn't start with the numbers you see on this spreadsheet. He started with a set of figures that he hoped would work, found that he needed to cut down his snacks and entertainment expenses, and worked with the numbers until he found a workable solution that gave him the money he needed to afford his date at the end of March.
It's important to look over the spreadsheet to be sure it makes sense when you first fill it with data. It's incredibly easy to make little mistakes in the formulas or the data, many of which you can see easily if you just do a little mental estimating of what the amounts in the total columns should be. If the first time he worked the sheet Matt had seen the figure $329 in cell E19, his common sense would have told him he'd made a mistake. Matt then would have checked his formulas to make sure each was correct.
As you become familiar with spreadsheets, you'll discover techniques for speeding around the sheet, copying formulas and data, centering labels on columns, and so forth. There's always something more to learn, but the basic spreadsheet process it easy.
As your work grows more complex, you may even begin to link the data found in two or more spreadsheets into summary sheets.
You could have created this analysis with pencil, paper, and calculator, but each change would have required several new calculations, which are easy to get wrong or forget. If your formulas are correct, the spreadsheet does it right every time--instantly.