What Is a Spreadsheet?
Imagine an accountant's worksheet filled with columns and rows of numbers. Now put that worksheet inside a computer, add the ability to change inputted and calculated values instantly, and throw in a few extras—such as built-in formulas, charting capabilities, and database sorting. What you'll have when you're finished is a spreadsheet.
Spreadsheets Explained (ML)
A spreadsheet software package is like a word processor for numbers. You use it to organize, calculate, and present numerical information neatly. The resulting document is usually a spreadsheet—often called a worksheet—but can also be a chart.
Like a paper worksheet, an electronic worksheet is laid out in a grid. Rows, labeled with numbers, and columns, labeled with letters, intersect at cells. Each cell has an address or reference that consists of the column letter and the row number. So you'd find cell C16 at the intersection of column C and row 16.
To use a spreadsheet you enter values and formulas into cells. A value can be text, a number, a date, or a time. It's often called a constant value because it won't change unless you change it. A formula is a calculation that you want the spreadsheet to perform for you. You begin a formula with an equal sign (=) and follow it with a combination of values, cell references, operators, and functions—more on those later.
The beauty of a spreadsheet is that if properly constructed, it can calculate the results of complex formulas in less time than it takes to bat an eye. And if you change any of the values referenced by a formula, the results change instantly. It sure beats an accountant with pencil-stained fingers and a ten-key calculator.
Real-World Spreadsheets
The best way to see what spreadsheet software is all about is to look at some real-life examples. With spreadsheets like the ones on the next few pages, you can calculate totals and averages, create a loan amortization table, perform what-if analyses, manage and report data, and create charts. These examples are just a small sampling of what's possible—with a little imagination and practice, you'll soon be taking spreadsheets to their limits.
Simple calculations (CB).
Here's a simple little AppleWorks spreadsheet that does some straightforward mathematics (Figure 10.1). When you enter expense information into the white cells, the spreadsheet calculates totals and averages for each category (the light gray cells) and totals for each month (the darker gray cells). It does this by using formulas in the gray cells that refer to values entered in the white cells.
Figure 10.1. This simple AppleWorks spreadsheet calculates totals and averages for the numbers entered into the white cells.
Complex calculations (EG/ML).
Not every formula you write or spreadsheet you create will be simple. By combining simple formulas with more advanced functions, you can create more complex spreadsheet models, such as a loan amortization table (Figure 10.2). Using good spreadsheet design, the one in the figure includes an input, or assumption, area where you enter the amounts you already know: the amount or principal of the loan, the interest rate, and the loan term. The monthly payment is calculated based on Microsoft Excel's built-in PMT function.
Figure 10.2. You can build a loan amortization table by combining simple and complex formulas, taking advantage of Excel's built-in financial functions.
What-if analysis (EG/ML).
Probably the most powerful feature of a spreadsheet is its ability to recalculate results quickly when you make changes to referenced values. This is known as what-if analysis because by changing a value, you're saying “What happens if this number changes?” (Figure 10.3).
Figure 10.3. Using what-if analysis, you can change the amount of the loan in cell D2, and the entire worksheet changes instantly.
Based on the previous loan amortization table, what if you decide to borrow less money, change the loan term, or find a better interest rate? It's easy to see how these changes will affect the monthly payment. Just change the information in the appropriate cell and—presto—the spreadsheet reflects your change.
Using Data Tables for What-If Analysis (EG)Another way to approach the loan scenario is to build a data table showing ranges of interest rates and different terms. The spreadsheet in Figure 10.4, for instance, shows the different monthly payments for a loan based on interest rates ranging from 5 percent to 15 percent and loan terms ranging from one year to five years. Figure 10.4. If you want to see a range of loan monthly payments based on different interest rates and loan periods, use a data table.
|
Database management (DC/ML).
Spreadsheets are also useful for simple database functions. By setting up columns for different categories, or fields, of information and rows for the data, or records, you can organize, sort, summarize, and otherwise analyze data. Here's an example that shows the scores for the members of a bowling league (Figure 10.5).
Figure 10.5. This Excel worksheet contains a database of scores for members of a bowling league. By using database features like sorting and subtotaling, you can quickly analyze the information.
Charting (CB).
A picture is worth a thousand words, and when the picture stands in for numbers it's probably worth even more. You can use the charting features of spreadsheet software to make sense of a mystifying bunch of numbers. The column and pie charts in Figures 10.6 and 10.7 show two examples of how to display a spreadsheet's results graphically.
Figure 10.6. The column chart quickly tells you that airfare is the biggest expense, that telephones and hotels are the next biggest, and that the rest of the expenses hardly make a difference. Try getting information like that quickly from raw numbers!
Figure 10.7. The pie chart makes it easy to see that airfare is nearly half of the total expenses for January.
Basic Chart Types (SA/ML)With the wide range of chart types and styles that spreadsheets have to offer, you may find it tempting to go crazy. Colors and styles might be a matter of taste, but keep in mind that certain types of charts are best used for certain types of data. Here are the four basics:
|
Spreadsheet Software (ML)
If you're shopping for spreadsheet software, you won't have many choices to consider. It seems that every year, another spreadsheet package drops off the face of the earth. Only two viable options are left, so let's take a look at them.
Microsoft Excel is undoubtedly the most feature-packed spreadsheet package ever created. It goes beyond the basics of spreadsheets and charts by offering hundreds of built-in functions, extensive database features, drawing tools, a wide range of formatting capabilities, and a comprehensive macro language.
The power of Excel goes beyond what it can do to how it does it. This well-thought-out package includes many features that make data entry easier or more accurate. Its dialog boxes are well organized and easy to use and understand (with a few exceptions not worth detailing here). It includes extensive online help with hypertext links, making it easy to navigate from topic to topic and find the information you need. Surprisingly, its heavy feature load does not affect its performance.
Although some people think I'm partial to Excel because I've written seven books about it, that's not true. I can objectively look at Excel and its alternatives and tell you that Excel is the best and most powerful spreadsheet package around. But is it for everyone? Of course not! The vast majority of folks who use their Macs at home or school don't need even half of Excel's bells and whistles. But if you're in corporate finance, science, or other industries where heavy-duty number crunching and presentation is a must, I doubt if you'll find a better spreadsheet solution for your needs than Excel.
In case you're wondering what Excel will cost in terms of dollars, disk space, and RAM, hold on to your hat. You'll pay about $350 for the software, which will take up at least 32 MB of hard-disk space and 16 MB of RAM. (If you use Microsoft Word for word processing, you can save money by purchasing Microsoft Office, which includes both programs, plus PowerPoint and Entourage.) But serious number crunchers will probably think Excel is worth the price.
AppleWorks is an integrated software package that includes word processing, spreadsheet, database, drawing, painting, and presentation modules. A “do-it-all” package, AppleWorks handles spreadsheet functions well, but not as well as a specialist like Excel. Still, for most home and school users and small-business owners, the spreadsheet module of AppleWorks has everything needed to get the job done, including built-in functions, extensive formatting capabilities, and linked charting. Because it's an integrated package, you can “draw” word-processing and other types of documents right on your spreadsheet without additional software.
 | One of the most attractive features of AppleWorks is its price: less than $100. It requires a Mac with 24 MB of RAM and at least 40 MB of hard-disk space. But remember—that's for the entire package, not just the spreadsheet module. Seems like quite a bargain to me. |