7.7. Spreadsheets with OpenOffice.org Calc

Calc is the spreadsheet module of OpenOffice.org. Use this application, for example, to handle your private or business accounting data. If Writer is already running, start Calc by selecting File -> New -> Spreadsheet.

After starting, Calc presents an empty spreadsheet divided into rows and columns. Rows are numbered from top to bottom and columns are lettered from left to right. The intersection of a row and a column marks the location of a cell, so each cell has a unique address coordinate. For instance, the address B3 refers to the cell located in the second column (B) and the third row. This address is also shown at the top to the left of the entry field.

A cell may be active or inactive. The currently active cell has a thick black frame around it. To activate another cell, move the frame with the cursor keys or click another cell with the mouse. You can edit a cell if it is currently active.

7.7.1. Changing Cell Attributes

To enter something in a cell, simply write in that cell. By default, texts are aligned to the left and numbers to the right. To confirm your entry, hit Enter. To change the formatting of selected cells, right-click to open a context menu and select Format Cells…. This opens a dialog in which to change the cell attributes. The dialog, as shown in Figure 7.7. “The Cell Attributes Dialog”, has the tabs Numbers, Font, Font Effects, Alignment, Borders, Background, and Cell Protection. By enabling Protected under Cell Protection, prevent a cell from being modified.

Figure 7.7. The Cell Attributes Dialog

The Cell Attributes Dialog

7.7.2. A Practical Example: Monthly Expenses

Calculations can be done using formulas. Any numeric values entered in cells can be used in formulas by referring to the unique coordinates of each cell. For example, you may want to keep a record of your monthly expenses. This could be done by writing a few items into a very basic spreadsheet as in Figure 7.8. “A Spreadsheet Example for Calc”. The cell B3 contains the phone bill for January and B4 the fuel expenses. To add both amounts together, enter = B3+B4 in cell B5. Now cell B5 should display the corresponding result. If you have mistyped the numbers (or estimated your phone bill inaccurately), just reenter the amounts and Calc will automatically update the sum.

Figure 7.8. A Spreadsheet Example for Calc

A Spreadsheet Example for Calc

Calc offers many functions beyond the four fundamental arithmetic operations. A comprehensive list arranged in categories is available under Insert -> Function…. Any spreadsheet can be easily extended. For instance, to insert another row between Fuel and Sum, right-click the 5 button to the left and select Insert Row from the context menu. A new row is inserted below the current one and can immediately be used for additional input.

Entering formulas in the above way is cumbersome when many cells are involved. For example, if you have several items in your A column and want to add them, try the SUM function. In the field B6, enter the formula =SUM(B3:B5). An alternative possibility is to click the Sigma (Sigma) icon next to the input line and enter the range manually. This formula adds all numbers from B3 to B5. You can also specify several ranges at once.

As shown in the above formula, a range is defined by two cell addresses separated by a colon. Separate ranges by semicolons. Accordingly, the formula = SUM(B3:B5;D3:D5) adds everything from B3 to B5 and from D3 to D5. Basically, the formula is a short form of a longer one, which would read: =B3+B4+B5+D3+D4+D5.

7.7.3. Creating Charts

Now, add some more entries to the spreadsheet, for instance, by including some more months in row 2. After doing so, the table could look like Figure 7.9. “Expanded Example Spreadsheet”.

Figure 7.9. Expanded Example Spreadsheet

Expanded Example Spreadsheet

Select the range from A2 to E5. The text appears white on black.

To create a chart, select Insert -> Chart…. This opens a dialog window. The first page in this dialog gives the option to modify the original cell selection and to specify whether to use the first row or column as chart labels. Usually, the settings on this page can be accepted without change. Continue by clicking Next.

The dialog consists of four pages. The main page shows the available chart types. The types offered include line, area, column, and bar charts. To the left, the page displays a preview of your data according to the type selected. The most suitable type for our example is the line chart. Click Next to proceed to a page in which to choose from different variants of line charts: with or without symbols, stacked, percent, cubic spline, and so on. For the current example, select Symbols. If you enable Show text elements in preview, the column headers (January, February, etc.) are displayed on the X axis and the numerical values on the Y axis. Also, a chart legend is added on the right-hand side.

On the next page, give the chart, as well as its X axis and Y axis, a title. In this case, use Monthly Expenses as a chart title and Euro on the Y axis. The X axis title is disabled by default. Finally, after clicking Create, the chart is inserted into your spreadsheet. Figure 7.10. “A Chart in Calc” shows the sample chart in its final form.

Figure 7.10. A Chart in Calc

A Chart in Calc

7.7.4. Importing Spreadsheet Tables

There is often a need to import data available as a table, so it can be presented as a spreadsheet. In Calc, there are two ways to achieve this.

Importing Data from the Clipboard

For instance, you may want to import stock exchange data. Display the desired table in your web browser. Then select the table with the mouse or using Copy, depending on the browser. This copies the selected data to the clipboard.

After that, open a new OpenOffice.org document with File -> New -> Spreadsheet. Select the cell in which the inserted table should start. Then select Edit ->Paste. This inserts the table into the document with all the formatting specifications, hyperlinks, and other information included.

Importing Data with a Filter

Import a saved HTML file to Calc by selecting File -> Open. This opens a dialog window in which to select Web Page (OpenOffice.org Calc) under File type. Use the arrow keys to scroll the list of file types. Finally, select the file name then Open to import the table.