2. Spreadsheet

Abstract

This section will give you a brief introduction to OpenOffice.org Calc's spreadsheet functions. We take for granted that you know why you intend to use a spreadsheet and will not delve deeply into application-specific (accounting, financial, simulation, etc.) considerations.

2.1. OpenOffice.org Calc

2.1.1. Starting

To launch OpenOffice.org Calc, select Office+SpreadsheetsOpenOffice.org Calc from the main menu. You can also open it from any other OpenOffice.org application screen, selecting File+NewSpreadsheet, which will open an OpenOffice.org Calc window with a blank spreadsheet on it.

When you first launch OpenOffice.org Calc, a dialog will show up asking you whether you prefer to use the Microsoft® or OpenOffice.org format to save your files.

Your decision depends on whether you plan to exchange a lot of files with people who use only Microsoft® tools. If this is the case, click Use the Microsoft® Excel file format, but be warned that it is not perfectly supported. Also note that this is only the default format and can always be overridden by changing the File type in the Save as dialog.

2.1.2. Interface

Figure 10.2. OpenOffice.org Calc's Main Window

OpenOffice.org Calc's Main Window
Format Bar

This is the standard format bar for all OpenOffice.org applications used to change fonts, colors, alignment, etc. of the application's data.

Formula Bar

Use it to enter, edit or delete formulas inside cells.

Work Area

Where you enter the data in the spreadsheet: numbers, dates, formulas, images, etc.

Select All

Clicking on this little area at the top left corner of the work area will select all cells at once. It's useful when you need to make changes which are “global” to the spreadsheet. For example, changing all font sizes in the cells to 10pts (points).

Sheet Changing Buttons and Tabs

Spreadsheets usually contain more than one sheet. Use these buttons to easily navigate through each of the spreadsheet's sheets. You may also use the tabs to switch between sheets.

2.2. Using the Spreadsheet

The following sections explore basic functions such as entering data and formulas in the spreadsheet and adding graphics to represent that data. An example of an imaginary company's monthly expenses and sales figures is used.

OpenOffice.org Calc is an enterprise-ready spreadsheet application and includes many features way beyond the scope of this document. Consult Section 2.3, “Going Further”, for more information on how to make full use of OpenOffice.org Calc.

2.2.1. Entering Data

To enter data into a cell navigate to the cell and type the data in it, pressing the Enter key when you are finished.

Auto-completion simplifies data entry “guessing” the next cell's value using the current cell's value as a base. It works for any kind of data which can be associated to a series of consecutive integral numbers.

Figure 10.3. Simplifying Data Entry Using Auto-Completion

Simplifying Data Entry Using Auto-Completion

To use auto-completion put your mouse over the cell “handle” (the little black square located at the bottom right of the cell border), click on it and drag the cell. The cell values will be shown in a tool-tip (see Figure 10.3, “Simplifying Data Entry Using Auto-Completion”). Release the mouse button to complete the cells once the desired final value is shown.

Cell data can also be sorted according to different criteria. Select the cells you want to sort and then open the sort options dialog choosing DataSort from the menu. Specify the sort criteria, order and additional options and click on the OK button to sort the cells.

[Tip]Tip

Make sure you also select columns and rows which act as “headers” for the data in order for those to “follow” the sorting of the data.

2.2.2. Adding Formulas

Formulas can be used to “automate” the spreadsheet allowing you, for example, to run complex simulations. Within cells, formulas are defined by preceding all cell data with the = sign. Anything else is treated as “static” data.

Operations are expressed using conventional algebraic notation. For example =3*A25+4*(A20+C34/B34) divides the value in cell C34 by the value in cell B34, adds the value in A20 to the result, multiplies that by 4 and adds to 3 times the value of cell A25. Thus, rather complex expressions can be made using simpler ones as a base.

OpenOffice.org Calc gives you many pre-defined functions which you can use in your formulas, explore them by choosing the InsertFunction menu.

2.2.3. Charts: Explaining Data in a Simpler Way

When a spreadsheet contains too much information it often becomes difficult to understand how pieces of data relate to one another: too many numbers and too little meaning. The best way to represent this kind of data is through a chart.

As in all data-analysis functions, you must select the region you intend to show in the chart. So, select a range of cells and then chose InsertChart from the menu to bring up the chart assistant.

Make your selections for the chart type, variant, title, axis titles, etc. and then click on Create to create and insert the chart in the spreadsheet (see Figure 10.4, “A 3D Chart Inside the Spreadsheet”).

Figure 10.4. A 3D Chart Inside the Spreadsheet

A 3D Chart Inside the Spreadsheet
[Tip]Tip

Charts are “dynamic” in the spreadsheet which means that when you change data in a cell belonging to a chart, the chart will be automatically updated.

[Tip]Tip

Clicking and then right-clicking on an inserted chart brings up a menu showing options to change many chart parameters. For instance, the chart's title can be changed by double-clicking on it.

2.3. Going Further

If you wish to learn more on the use of OpenOffice.org Calc, you should consult the tutorial available at the Tutorials for OpenOffice Web site.

Also, don't hesitate to refer to OpenOffice.org Calc's help accessible through the HelpContents menu, or by pressing the F1 key. There you are bound to find answers to your questions. Topics are accessible through a table of contents. An index is also available as well as a contextual search tool.