May 11, 2011, 1:23 p.m.
posted by homene
Using OpenOffice.org Calc
Figure. 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).
Figure. 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:
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. When you are done, press Enter to accept the text label you entered.
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 shows the same text label as in Figure, 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.
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
Figure shows the spreadsheet from Figures 9.4 and 9.5 with numeric data filled in. The numeric values in Figure 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).
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:
All these types of numeric formats can be applied using the Formatting bar buttons shown in Figure.
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 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.
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.
The process of entering a simple formula to perform a basic calculation is shown in Figure.
8. Entering a formula. G5 will display the result of this calculation, rather than the formula, after you press Enter.
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.
Figure. 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.
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 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.
11. The sample spreadsheet with a few more formulas filled in.
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.
Figure 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.
12. The formula from B9 has been copied to cells C9 through F9.
Figure shows the sample spreadsheet completed, by following these steps:
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 only a range of cells that you select, follow these steps: