SPREADSHEETS

Spreadsheet software is one of the most-used technologies for collecting, computing, and displaying data. Spreadsheets contain a rectangular array of cells in rows and columns that can hold data. Users can create business models, graphs and charts, and reports for financial, statistical, or other data. Most spreadsheet software allows a user to access real-time data from Web sites and to collaborate across teams and workgroups.

SPREADSHEET PACKAGES

Spreadsheet packages are available for various operating systems, such as Windows, Macintosh, UNIX, Java, Linux, and VMS. Spreadsheet capabilities are included in financial management packages and integrated software packages. The best-known spreadsheet software packages are Microsoft Excel, Lotus 1-2-3 from IBM Corporation, and Corel's Quattro Pro. These three packages are included as parts of office suites from Microsoft, IBM, and Corel.

In addition, dozens of other spreadsheet packages are available. Many of these offer users an opportunity to try the product for a limited period and then pay a fee for permission to use the package beyond the evaluation period. Many spreadsheets are listed on the Internet by their developers, either as shareware or for purchase, and some are available for downloading.

SPREADSHEET APPLICATIONS

A spreadsheet is a table representing information in worksheet form. The spreadsheet can be visualized as a large sheet of paper with rows and columns and is based on the worksheets used by accountants for manual computations.

Spreadsheets range from small, simple text tables to large documents that can carry out complex computations and statistical analysis of thousands of data entries.

Simple spreadsheets can be displayed on screen; more complex spreadsheets extend into vast numbers of cells and can be displayed in part on one screen. The power of a spreadsheet is in its ability to store formulas and display their results. A recalculation feature in spreadsheets allows a user to enter new data into the spreadsheet—which can affect other sections of the spreadsheet—and see the results of new calculations. This "What If" feature of spreadsheets is a valuable tool for users.

FORMAT OF A SPREADSHEET

Spreadsheet software packages organize numeric data into table format, vertically in columns and horizontally in rows. Three types of data may be entered into a spreadsheet or worksheet: (1) values or numbers, (2) names or labels, and (3) formulas for calculation. Values may be used for basic arithmetic operations: addition, subtraction, multiplication, or division. Labels identify information in the worksheet and organize the information. Formulas perform calculations on data and display and store the resulting values. A cell, the intersection of a row and column, can contain a label, a value, or a formula for performing calculations. Cells are addressed by column number and row number, and a current cell address is displayed in an address box. The "home" cell is A1, located in column A, row 1.

Only a small part of a complex spreadsheet is displayed on the screen at one time. Spreadsheets can contain millions of cells in each spreadsheet, and a spreadsheet file can include multiple spreadsheets. For example, Lotus 1-2-3 and Microsoft Excel spreadsheets have 256 columns and 65,536 rows, or 16,777,216 cells. A spreadsheet file also may include multiple worksheets. More than one worksheet can be used to render three-dimensional charts of data. Spreadsheets are very powerful, extensive electronic worksheets.

A spreadsheet handles such simple functions as adding, subtracting, multiplying and dividing. Arithmetic operators are used to represent the functions: addition (+), subtraction (–), multiplication (*), and division (/). For example, an entry into cell D3 of "=B3+C3" would instruct the spreadsheet to add the contents of cells B3 and C3 and store the sum in cell D3. A symbol at the beginning of a formula identifies the entry as a formula instead of a label. In an entry of =B3+C3, the equal sign identifies the entry as a formula.

A simple spreadsheet can be enhanced with spreadsheet tools. Font faces (e.g., italic or bold), sizes, and types can be changed, color can be added to the background of cells or labels, and graphs can be used to illustrate data from the spreadsheet.

A spreadsheet is initially set up by default with a given column width, row height, and format for entries. If labels are longer than the column width allowed, the spreadsheet does not "lose" the extra characters; instead they are not displayed if the cell to their right has an entry. The user may change the column width and row height to enhance the appearance of the entries. Values are stored by the spreadsheet in their simplest form initially; an entry of $1,050.00, for example, can be stored as 1050. The user then has tools within the spreadsheet for formatting entries.

Numeric data may be formatted as dollars and cents, with commas separating hundreds and thousands, in various formats for different countries, with a given number of decimal points, in exponential form, or in other formats. When a formula is entered, the cell displays the result of computation and retains the formula. To display the formula, not its results, in a cell, a user can choose a format for "text." A formula that is entered as +=C3+D3–E3, for example, might show a result of 25. If the cell is formatted to the "text" format, the formula will show instead of the computed answer. Spreadsheet packages usually have a "shortcut" keyboard method of displaying the formula version of entries.

A set of data can be described to the spreadsheet as a range by specifying the beginning cell, in the upper left corner of the data, and the ending cell, in the lower right corner of the data. For example, to identify a rectangle that begins with cell A1 and extends down to cell D3, one address for the range would be A1:D3. Spreadsheets identify the range with a symbol that means "through." In the example A1:D3, the format used by many spreadsheets, the range is interpreted as "cell A1 through cell D3."

An example of a spreadsheet is shown in Figure 1. Rows 1 through 7 show a spreadsheet. In row 1, "Sales by Region for First Quarter," the heading for the entire worksheet, is an example of a label. The column headings and items in column A are labels; columns B through D are

values, which are summed in column E with formulas. The formulas in column E sum the numbers for January, February, and March for each item. Across the bottom of the spreadsheet, the total line is also a result of using formulas to sum the columns. The formula for E3, for example, is =SUM(B3:D3).

The title, "Sales by Region for First Quarter," and the column headings (January, February, March, Totals) show how font changes can enhance the readability and attractiveness of a spreadsheet. Cells can be formatted to bold, underline, or italicize entries; background color or shading can be added; and font faces and sizes can be changed. In the sample worksheet, formatting has been used to aid readability.

Values can be formatted. In the sample spreadsheet, the values in rows 4 through 6 have been formatted to no decimal places with commas. Rows 3 and 7 have been formatted with a dollar sign for readability. A user can select the desired formatting from a menu.

Spreadsheet data can be selected for charts, or visual representations of those data. Cells are selected by highlighting them. Spreadsheet packages may chart one set of data in the form of a pie chart, or two or more sets of data in bar charts (with vertical bars, horizontal bars, or stacked bars), line charts, area charts, or mixed charts, which combine bars and lines to represent data. Data can be displayed in two-dimensional or three-dimensional form. Charts become part of the spreadsheet and may be stored on the same page as the spreadsheet or as a separate page or worksheet. Figure 2 shows a sample chart for the sales spreadsheet described above.

The chart depicts the figures from B3 through D6 in the spreadsheet, categorized by cells A3 through A6 (labels). A column (or bar) chart is only one of several choices of charts that a user can select. The software provides steps for adding a title to the chart, a legend, and/or a listing of labels for the charted data.

MACROS IN SPREADSHEETS

A macro is a series of commands that automate a spreadsheet task, streamline complex procedures, or create applications. A user can enter a macro into a worksheet file or into a macro library, a worksheet file that stores macros. To create a macro, the user enters the steps needed to carry out a task, gives the macro a name, and saves it in a file. To use the macro, the user selects it by name from a menu and runs the macro in the spreadsheet. The steps are carried out automatically. For complex tasks that are used often, a macro makes it easier for a user to avoid mistakes in the task, since the steps are stored as a file and recalled as needed.

SPREADSHEETS IN INTEGRATED PACKAGES

Integrated software packages, which contain several kinds of software within one, include a spreadsheet. Information can be copied from a spreadsheet into other software packages, such as a word-processing package. Spreadsheets can be linked to files in other software in the package so that changes made in the spreadsheet are automatically reflected in the linked document. A table from a spreadsheet can be linked to a word-processing document so that any changes in cell entries in the spreadsheet change the contents of the table in the word-processing document.

Embedded objects, such as a chart from a spreadsheet, can be embedded into a word-processing document. Unlike linking, changes in the embedded chart in the document are not reflected in the source file (spreadsheet file). These features make the spreadsheet a very powerful tool for analysis and reporting of data in various formats.

SUMMARY

Spreadsheets are used by almost every business firm that records data. Spreadsheets have also become a useful tool for personal record keeping. The data organization and graphics capabilities of spreadsheets make them a useful tool for all types of calculations, displays, and analysis.

see also Information Processing ; Software

bibliography

IBM Corporation. Lotus. Retrieved December 10, 2005, from http://www-306.ibm.com/software/lotus

Shelly, Gary, Cashman, Thomas J., and Vermaat, Misty (2003). Microsoft Office 2003: Introductory Concepts and Techniques (2nd ed.). Boston: Course Technology.

Webopedia. (n.d.). Spreadsheet. Retrieved December 7, 2005, from http://www.webopedia.com/TERM/s/spreadsheet.html

Betty J. Brown

Spreadsheets

Traditionally, accountants use a grid of rows and columns printed on special green paper to produce financial projects and reports. An electronic spreadsheet is software that simulates this accountant's paper pad or worksheet. Using spreadsheet software, a worksheet appears on the computer screen as a grid of rows and columns. The software performs calculations based on the numbers and mathematical formulas that users enter into this grid. While the terms worksheet and spreadsheet are used interchangeably, they really refer to different things. The spreadsheet actually refers to the software itself, whereas a worksheet denotes the actual numbers and formulas that underlie a particular task.

The intersection of a row and a column on the worksheet grid is called a cell. Cells are identified by a name that consists of a column letter (A–Z, AA–ZZ, and so on) and a row number. For example, D10 refers to the cell in the fourth column and the tenth row down on the worksheet, and AB45 refers to the twenty-eighth column and forty-fifth row down on the worksheet. Because a worksheet can have thousands of cells, the software allows the user to scroll through the worksheet horizontally or vertically to view the cells.

Cells on a worksheet can be filled with one of four things:

1. nothing (which means that the cell is blank)
2. labels
3. numeric values
4. formulas.

A label in a cell is a descriptive text. Examples of labels include: Units Sold, Revenue, Total Cost, Name, Address, or City. The labels are used to help organize the worksheet. Numeric values refer to the actual numeric data that are used on the worksheet in calculations. Numeric data can take the form of positive or negative numbers, integer numbers, decimal numbers, fractions, or scientific notations.

Formulas are used to indicate how the numeric values are to be manipulated. For example, on a household budget spreadsheet, a formula can be created to add the household monthly expenses and store the results in cell B20. The formula, SUM (B1:B19), would add the values stored in the range of cells from B1 through cell B19. Another formula, B21 B20, could be created to subtract the expenses (in cell B20) from the income (in cell B21) on a household budget spreadsheet. Most spreadsheets also offer a variety of pre-defined formulas, called functions, which effect powerful mathematical calculations. Typically, these functions are divided into a variety of categories, such as financial functions (e.g., depreciation, future value, net present value), date and time functions, mathematical and trigonometry functions (e.g., absolute value, sine, cosine, pi), statistical functions (e.g., average, median, variance, standard deviation), and database functions (e.g., average or count the values in a column).

In addition to these formulas and functions, spreadsheets provide modeling capabilities that allow users to describe real-world situations and then experiment with different numbers. This ability to change numbers involved in calculations and see the immediate results is called "what if" analysis. What-if analysis is a very powerful and useful tool and it is what makes spreadsheet software indispensable. Users can investigate what would happen if sales increase by 10 percent or decrease by 5 percent. Students could examine the implications of an "A" grade on their next examination or a "B" grade. If done manually, each change would require erasing the contents of every cell involved in a formula, changing their numeric data, and then recalculating!

Spreadsheet software also has the capability of charting or plotting data, providing database querying and extracting, and macro building. In 2002 spreadsheet software can chart data in many different ways including area, bar, column, pie, and XY charts. By visually presenting the data in chart format, the data can be readily analyzed and trends spotted. Spreadsheet software in the year 2002 also has elementary database functions. Specific rows or columns of the worksheet can be selected for report production or advanced analysis. A macro is a collection of commands that the spreadsheet software can execute. These commands are written by the user and stored with a particular worksheet. Macros are used to perform repetitive tasks on a worksheet, such as extracting all rows that have an overdue balance and producing a report about it.

History

In 1978 Daniel Bricklin, a student at Harvard Business School came up with the idea for a computerized interactive visible calculator. Bricklin and Robert Frankston created the first electronic spreadsheet software named VisiCalc, short for "visible calculator." VisiCalc and electronic spreadsheets might never have gained acceptance if it were not for a Massachusetts Institute of Technology (MIT) graduate named Daniel Fylstra. He suggested that VisiCalc should be run on an Apple microcomputer. Bricklin and Frankston programmed the software for Apple microcomputers and Fylstra and his firm, Personal Software, began marketing VisiCalc by placing an ad in Byte Magazine. VisiCalc became an almost instant success in the business community.

In the early 1980s, IBM introduced a microcomputer (IBM-PC) that used an Intel microprocessor. However, due to legal problems, Bricklin and Frankston did not develop VisiCalc for this new microcomputer. Rather, Mitchell Kapor developed another electronic spreadsheet named Lotus 1-2-3 that could be used on IBM's new computer. Kapor's Lotus 1-2-3 spreadsheet quickly replaced VisiCalc as the new industry standard. Lotus 1-2-3 was easier to use than VisiCalc and added charting, plotting, and database capabilities to the software. Lotus 1-2-3 was also the first spreadsheet to introduce naming of cells, cell ranges, and spreadsheet macros.

During the 1980s, many companies introduced new microcomputers, while others created new spreadsheet software. In 1984 Apple Computer introduced the Macintosh. The Excel spreadsheet from Microsoft Corporation was written for the Macintosh computer. Excel used a graphical user interface (GUI) and the mouse. This interface proved very easy to use, much easier than the command-line interface used by Lotus 1-2-3. In 1987 Microsoft introduced a new operating system for the IBM-PC. This operating system, called Windows, incorporated a graphical user interface. Excel was one of the first software products that ran under Windows. By 1989, when Windows had gained wide acceptance in the microcomputer market, Excel began to dominate the market.

In the late spring of 1995, IBM purchased Lotus Development Corporation. In 2001 Microsoft Excel was the market leader in spreadsheets.

see also Decision Support Systems; Information Systems; Productivity Software.

Charles R. Woratschek and Terri L. Lenox

Bibliography

Laudon, Kenneth C., Jane P. Laudon, Peter Weill, and Carey Butler. Solve it! For Windows, Version 2.9 Millennium. Hudson: Azimuth Corporation, 1992–2000.

Internet Resources

Power, D. J. "A Brief History of Spreadsheets." DSS Resources. <www.dssresources.com/history/sshistory.html>

spreadsheet A program that manipulates tables consisting of rows and columns of cells, and displays them on a screen; the cells contain numerical information and formulas, or text. Each cell has a unique row and column identifier, but different spreadsheets use different conventions so the top left-hand cell may be A1, 1A, or 1,1. The value in a numerical cell is either typed in or is calculated from a formula in the cell; this formula can involve other cells. Each time the value of a cell is changed by typing in a new value from the keyboard, the value of all other cells whose values depend on this one are recalculated. The ability of the cells to store text is used to annotate the table with column headings, titles, etc.

The spreadsheet is particularly suited to the personal computer since it requires the fast and flexible display handling that is a feature of such systems. The common characteristic of all spreadsheets is the way the display screen of the computer acts as a window onto the matrix of cells; if there are more rows and columns than will fit on the screen, then the spreadsheet can be scrolled horizontally or vertically to bring into view previously hidden rows or columns. To change a value it is only necessary to move the cursor into the required cell displayed on the screen and type in the new value.

Spreadsheets can be used for storing and amending accounts, “what if?” financial projections, and many other applications involving tables of numbers with interdependent rows and columns. A spreadsheet is often a component of an integrated office system. Examples include Excel, Lotus 1-2-3, and Quattro Pro. See also multidimensional spreadsheet.

spread·sheet / ˈspredˌshēt/ • n. a computer program used chiefly for accounting, in which figures arranged in the rows and columns of a grid can be manipulated and used in calculations.• v. [intr.] [usu. as n.] (spreadsheeting) use such a computer program.