Spreadsheets
The steps you follow to create a spreadsheet will depend on what it is you want to accomplish. This objective uses an example to explain the most common steps in creating a large spreadsheet.
The first step is to know what it is you want to accomplish. You may want to track some data (e.g., expenses) or answer a specific question (e.g., is it better to buy or lease a new car?). For this example, let's assume we are running a retail outlet and want to record and track the sales figures for the first quarter. We would also like to know the the salary expense by employee and for all employees.
The next step is to identify the input data you have to work with. The input data for most problems consists of raw data and reference values. Raw data is normally the data you want to record and track. Examples of raw data include course grades, personal expenses, and investments. Reference values are usually constant values used in calculations with the raw data. Examples of reference values are monthly income, interest rate, and commission rate. The raw data for our example are the monthly sales figures per employee. The reference values are base pay and commission rate.
The next step is to decide how to organize and format the data on the page. As you are deciding on the layout of the data, keep in mind any charts you may want to make from the data. This will have an influence on how your data is organized into rows and columns. The more experience you have with charting data the easier it will be to determine what a good layout is. The layout should also make it easy to add/change data and find the information you are looking for. The following figure shows the layout for our example:
Part
of formatting a spreadsheet is deciding how to label your data and results.
Labels should be clear and easy for someone else to understand (and easy
for you to understand when you look at it again in three months!). Labels are
also important because, as the figure to the right shows, they show up
on any charts you make from the data.
Spreadsheets can be used to record data but the real reason to use a spreadsheet (rather than a database) is to make calculations and ultimately decisions based on existing data. The feature in spreadsheets that makes this possible is formulas. Formulas allow you to calculate new data from existing data. You can create your own formulas using the built-in operators or use one of the predefined formulas in Excel. Excel has predefined formulas for summing, standard deviation, and net present value, just to name a few.
The calculations you perform on your data will likely fall into two categories: first-order calculations and second-order calculations. First-order calculations are those normally computed directly from the raw data. Second-order calculations use the results of first-order calculations as input. These second-order calculations are usually the ones used to make decisions. They allow you to identify patterns or trends in raw data. It is this larger, or more abstract understanding of the data that allows you to make decisions.
The first-order calculations in our retail sales example would be the gross pay of each employee. The second-order calculation is the total for the store. Perhaps a better example of a second-order calculation in our example would be the percent each employee contributed to the overall store sales.
The final step in creating a simple spreadsheet is to create any charts from your data. Charts can be created from first- or second-order calculations, or raw data. You may have to hide rows or columns, or copy information from your spreadsheet so the input for the chart is in the right form.
To summarize, the following steps are used to develop a large spreadsheet:
 |
 |