Application software:Moving around the worksheet

Moving around the worksheet

You move the cursor from cell to cell within the spreadsheet window using the arrow keys; when the cursor reaches the edge of the window, any further movement forces the worksheet to scroll past the window. Bigger jumps through the worksheet are possible by using other keys, e.g. by pressing the PgUp or PgDn keys to jump a 'page' (i.e. window) up or down, or by pressing the CTRL key and an arrow key.

Spreadsheet tasks

To set up and use a worksheet, you have to carry out the following tasks.

Designing the worksheet. First, you have to design the template. This is a bit like setting up a template for a record­ keeping system. You have to:

• Decide how the data is to be organized.

• Insert appropriate column and row labels.

• Adjust the column widths as necessary.

• Set the number of decimal places for displaying the data.

• Program the template, i.e. enter programming state­ ments and formulae in appropriate cells.

As with a record-keeping system, this is a once-only job, and templates set up for one application can often be used for others, though perhaps with some modification. Public domain templates are available for the most popular spread­ sheet packages, and by using these you can set up many standard applications with the minimum of effort. For most spreadsheet applications it's not too difficult to do the job yourself from scratch.

Adding data. Once the template has been designed, you can enter the data for your application. Any results (totals, averages, etc.) will be automatically calculated, using the formulae stored in the results cells.

Altering data. You will often wish to alter numbers pre­viously entered into a cell. For example, if you are using a cash-flow forecast worksheet like that shown in Figure 6.3, you may wish to find out how changes in certain costs, or sales, affect your future cash flows. You have merely to enter the new figures, and the computer instantly calculates the results. This kind of activity is very useful for decision­ making and planning, because you can easily and quickly investigate the effects of alternative courses of action.

Goal-seeking. Sometimes, you may have certain ideal results which you wish to achieve. You could keep altering the data in your worksheet until what you want is produced in the results cell. Many spreadsheet packages, however, incorporate automatic goal-seeking: you say what you want the results to be, and the spreadsheet will work backwards to the starting data.

Charting. Most spreadsheet packages will produce various kinds of charts and graphs of the data in the worksheet.

Normally, column labels will be used to mark the X-axis, with a selection of data or results being plotted against the Y-axis.

Sorting and searching. Many spreadsheet packages provide limited database facilities such as sorting rows, e.g. into alpha­betical order or row labels, or searching for a particular entry.

Spreadsheets and databases compared

Some tasks can be carried out using either a spreadsheet or a database package. For example, you could keep your personal accounts on either. However, there are important differences in the two types of package:

• Database packages have sophisticated data retrieval and reporting facilities which are not normally found in spreadsheets.

• A spreadsheet provides a more flexible working environ­ment, for it is not limited to the kind of record structures described in the last chapter; for example, a row does not have to be a 'record', but can be programmed to show totals, averages, or other results.

So when should you use a spreadsheet, and when a data­ base? Spreadsheets are particularly good for handling numerical data and calculating results, and so they are appropriate for many financial and other numerical/calculat­ing applications. Database packages are more appropriate for conventional record-keeping tasks where the main requirement is to retrieve information and produce transac­tion documents and reports.