Using OpenOffice.org Calc

To start OpenOffice.org Calc, choose Applications, Office, OpenOffice.org Calc, as shown in Figure 9.1.

Figure 9.1. OpenOffice.org Calc can be started by visiting the Applications menu.

If no other OpenOffice.org applications are running, the splash logo appears while the program loads, and then a new Calc application window opens, containing an untitled electronic spreadsheet (see Figure 9.2).

Figure 9.2. A new OpenOffice.org Calc window. Because no spreadsheet has been loaded yet, a new document named Untitled1 appears in the application window.

Before entering any data, familiarize yourself with the parts of the OpenOffice.org Calc window. The Calc window demonstrates what a rich feature list this application offers. Here's how to use the Calc onscreen tools and components:

• Click buttons in the toolbars to perform common tasks, such as saving or printing a file, changing text alignment, and formatting numeric data within a cell in a specific way based on the type of datafor example, two decimal places for currency.
  
  
• Click a cell within the spreadsheet to make it active and display its contents in the input line. Cells can contain text and numeric data, as well as formulas that calculate values in one or more other cells. The currently selected cell is surrounded by a bold black line that forms the cell boundary.
  
  
• Use the status bar to find your current cell position in a spreadsheet and perform quick calculations on the right side of the status bar by highlighting blocks of cells.
  
  
• Enter cell contents (text, numeric data, formulas) in the input line. The data you enter here appears in the active or selected cell.
  
  
• Use the scrollbars to change the viewing area of the spreadsheet display. Cells are identified by a row number and column letter.
  
  
• Use the worksheet tabs to switch between open worksheets; each worksheet is like a separate sheet of paper containing its own rows and columns of cells. You add data to a cell by first selecting a cell to make it active and then entering the numbers, text, or formula for the cell into the cell input text box at the top of the Calc screen. You learn how to enter each type of data into cells in the following sections. Active cells are surrounded by a thick black outline, called the selection box. You can move the box around the spreadsheet by using the arrow and Page Up/Page Down keys, or by positioning the text cursor over a cell and clicking the mouse. Cells are identified by row number and column letter; in Figure 9.3, cell H14 is selected.
  
  

  Figure 9.3. Each cell in a spreadsheet can be referenced with a unique combination of letters and numbers. Here, cell H14 is selected.

  
  
  

Although you're now visually familiar with Calc, the best way to learn to use a spreadsheet is to start a spreadsheet application and create one. We step through that process in the sections that follow.

Entering Text Labels

For our sample spreadsheet, let's create a simple sheet that helps keep track of the amount of money you've found lying around while walking down the street lately. Although this scenario is a little wishful thinking, it helps illustrate the use of spreadsheets. We start with some text labels to show what the spreadsheet is to be used for.

Text labels are essential in an electronic spreadsheet because they enable those readers studying the spreadsheet to understand what the numeric data represents. For example, by entering the words Race Times next to a list of race results or Monthly Income next to a series of numbers representing income on a month-by-month basis, you make the spreadsheet easier for readers to understand. In the sample spreadsheet, we create a title for the sheet, along with enough labels for days and weeks to represent one month of money-finding.

To enter a text label in a cell, select the cell where the text label should start and begin typing. The text you enter appears both in the cell and on the input line, as shown in Figure 9.4. When you are done, press Enter to accept the text label you entered.

Figure 9.4. Entering a text label is easy; select the cell where the label should start and begin typing. Here, a label has been entered in B2, although it is allowed to flow over other cells because it is several words long.

You can change the appearance of the text label's text to differentiate it from other entries in the worksheet. You can use a different typeface; alter the font size; apply bold, italic, and underlining; and change the color of the text. To change the appearance of a text label, select the cell containing the label and then use the tools and selection boxes in the Formatting bar to apply changes. All these tools appear in the roughly the same place in OpenOffice.org Calc that they did in OpenOffice.org Writer; once you have become familiar with one, you should be able to quickly find the same buttons in the other.

Row size is automatically adjusted for the size of text contained within the cells in a given row. Figure 9.5 shows the same text label as in Figure 9.4, now enlarged slightly and made boldface. A number of additional text labels have been entered as well, in anticipation of the numeric data that is to be entered next.

Figure 9.5. The title of the spreadsheet has been made large and boldfaced; additional text labels have been entered in B4 through H4 and A5 through A10.

Entering and Formatting Numeric Data in Cells

To enter numeric data in the spreadsheet, navigate to the cell you want and type in the numbers. After you finish entering a number, press Enter to accept the numeric data as the value for the cell.

Figure 9.6 shows the spreadsheet from Figures 9.4 and 9.5 with numeric data filled in. The numeric values in Figure 9.6 are clearly intended to be currency; however, OpenOffice.org Calc has dropped trailing zeros in decimals in some cells (C5, F5, D6, E6, and D8).

Figure 9.6. The numeric data for this spreadsheet has been filled in by hand, from B5 through F8.

An electronic spreadsheet provides structured formatting of numeric data to display numbers as a dollar value, percentage, or with a specific number of decimal places. The default numeric format in OpenOffice.org is the Standard format, in which trailing zeros after a decimal point are dropped and numbers are otherwise displayed as they were entered. The following are the most common other numeric formats:

• Currency format, which displays numbers with the assumption that they are dollar valueswith a leading currency symbol and two digits of precision after the decimal point

• Percentage format, which displays numbers multiplied by 100, with two digits of precision after the decimal point and a trailing percent sign

• Fixed-precision formats, which display numbers with a fixed number of digits of precision after the decimal point

All these types of numeric formats can be applied using the Formatting bar buttons shown in Figure 9.7.

Figure 9.7. The Formatting bar buttons for numeric formatting have been used to alter the appearance of the data in the cells in B5 through H10.

To format a single cell, select the cell and click the appropriate Formatting bar button. To format a group of cells, click the upper-left cell with your pointing device and drag to the lower-left cell before releasing. This action selects an entire range of cells. Then click the appropriate Formatting bar button.

Figure 9.7 shows the sample worksheet with a large range of cells selected and formatting applied. The Formatting bar button for currency format has already been clicked; all the numeric data has been reformatted to appear as currency. Any numeric data that later appears in the empty selected cells will also appear as currency.

Entering Formulas

Formulas are the essence of the electronic spreadsheet. Although formulas may be as simple as an addition or subtraction expression, they may also be extremely complex.

Typical uses for formulas in spreadsheets include finding sums or averages of long lists of numbers or calculating percentages based on known quantities, such as the number of respondents in a survey. Users with more specific needs can perform physics, engineering, or calculus computations in a spreadsheet.

In our sample worksheet, formulas must be used in several places. A number of cells have been reserved to hold column or row totals; these totals will be calculated using formulas. The same is true for the cells left for averages. For example, the total findings for week one are to be displayed in G5. Thus, the number that should eventually appear in G5 should be the result of B5 plus C5 plus D5 plus E5 plus F5.

To enter a formula that performs a series of simple calculations, select the cell in which the result should be displayed. Then follow these steps:

1.	Type an equal sign (=) to indicate to OpenOffice.org Calc that you are about to enter a formula.
2.	Enter a formula consisting of cell references (the letter and number that refer to a cell) and operators such as +, -, /, and *. You can enter a cell reference either by typing the letter and number of a cell or by clicking the cell in question.
3.	After you type the complete formula, press Enter to accept the formula and calculate the result.

The process of entering a simple formula to perform a basic calculation is shown in Figure 9.8.

Figure 9.8. Entering a formula. G5 will display the result of this calculation, rather than the formula, after you press Enter.

The Formula Is Still There!

Although the formula entered in G5 is no longer visible in the cell on the spreadsheet after you press Enter, you can still view and edit the formula. When you select G5, the formula appears in the Input line.

Even though hidden, formulas are always active. If you change the numeric data in a cell used in a formula calculation, the displayed result of the formula calculation is automatically updated to reflect the new numeric data you've entered.

Using Functions in Formulas

Although the formula in G5 produces the desired result, the method of entering cell references one at a time can become clunky when many cells are involved in a calculation. For many types of common calculations, predefined functions built in to OpenOffice.org Calc can operate on ranges of cells. For example, there is a simpler way to sum several cells and display the result. This simpler formula uses the sum() function, which totals the values in a range of cells. Some functions also provide the ability to perform calculations that have no operator like + or -; the trigonometric functions are examples of these: sin() represents the trigonometric Sine function; cos(), the cosine function; sinh(), the hyperbolic sine; and cosh(), the hyperbolic cosine.

A spreadsheet program such as OpenOffice.org Calc includes hundreds of functions that can be used to perform many different types of calculations in user formulas. You can get a listing of the available functions in OpenOffice.org Calc and some information on how to use each of them by choosing Insert, Function List and then choosing All from the drop-down list that appears, as shown in Figure 9.9.

Figure 9.9. The Functions sidebar enables you to search through and find descriptions of each function in the list of OpenOffice.org functions. Click the small right-arrow icon or choose Insert, Function List again to hide the sidebar.

To use a function such as sum() within a formula, follow these steps:

1.	Type an equal sign (=) to indicate to OpenOffice.org Calc that you are about to enter a formula.
2.	Enter the name of the function and a left parenthesis. Then select a cell or a range of cells upon which the function is to act by clicking or clicking and dragging, as shown in Figure 9.10. Figure 9.10. Entering a function as part of a formula. Enter the name of the function, in this case, sum(, and then select a range of cells using the pointing device.
3.	Close the function by entering a closing parenthesis.
4.	After you type the complete formula, press Enter to accept the formula and calculate the result.

Our sample spreadsheet also includes a number of cells that are designed to hold an average of the amount of found money for a specific day of the week or a specific week of the month. The average() function, which calculates an average over a range of cells, can be used to fill these cells. Figure 9.11 shows the spreadsheet with a few more formulas entered. Remember that because these cells were previously formatted for currency, all the values now appear in dollars and cents.

Figure 9.11. The sample spreadsheet with a few more formulas filled in.

Copying Formulas

It is quite common for a single formula to be used over and over, needing to be adjusted only to account for differences in the position of its cell in the spreadsheet. This is the case in our sample spreadsheet: Most of the total and average cells use the same basic calculations (sum or average) over and over again. In cases like this, you can copy a formula from one cell to another, and OpenOffice.org Calc automatically adjusts the cell references to compensate.

In our sample spreadsheet, for example, the formula in C9 is essentially the same formula used in B9, except that Calc adds values in column C instead of column B. This is an ideal candidate for formula copying.

To copy a formula, follow these steps:

1.	Select the cell containing the formula you want to copy.
2.	Choose Edit, Copy to copy the selected formula.
3.	Select the cell or range of cells where you would like to use the same formula.
4.	Choose Edit, Paste to copy the formula to the cells; the same formula appears in each destination cell, but with adjustments for position in the spreadsheet.

Figure 9.12 shows the result of copying the formula in B9 and pasting it into a selected range of cells from C9 through F9. Each cell contains the correct calculation for its column.

Figure 9.12. The formula from B9 has been copied to cells C9 through F9.

Figure 9.13 shows the sample spreadsheet completed, by following these steps:

1.	The average formula in B10 was copied to the range of cells from C10 through F10.
2.	The total formula in G6 was copied to the range of cells from G7 through G8.
3.	The average formula in H5 was copied to the range of cells from H6 through H8.

Figure 9.13. The sample spreadsheet is now complete; average money findings and total money findings have been computed by day and by week.

Printing, Saving, and Opening Spreadsheets

The steps involved in printing, saving, and loading spreadsheets in OpenOffice.org Calc are nearly identical to the steps involved in printing, saving, and loading word processing documents in OpenOffice.org Writer.

To print a spreadsheet, choose File, Print and click OK in the Print dialog box.

To print only a range of cells that you select, follow these steps:

1.	Select the range of cells that you want to print.
2.	Choose File, Print to display the Print dialog box.
3.	Choose the Selection option in the Print Range area of the dialog box.
4.	Click OK to print your selection.

To save a spreadsheet, follow these steps:

1.	Choose File, Save.
2.	When the Save As dialog box is displayed, select a location and enter a name for your file.
3.	If you want to be able to exchange the file with users of MS Office, be sure to select one of the Microsoft Excel formats from the File Type drop-down list, visible when you select Browse for Other Folders. If you want to protect your file with a password, check the Save With password box.
4.	Click the Save button to save your file.

To load a saved spreadsheet, follow these steps:

1.	Choose File, Open.
2.	When the Open dialog box is displayed, browse to the location of the file that you want to open.
3.	Double-click the file's name. A new OpenOffice.org Calc window appears with the loaded spreadsheet in it.