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.
Spreadsheets are electronic replacements for an accountant's ledger book and calculator. This software uses columns and rows to allow math operations to be performed on previously entered data. Nowadays, spreadsheets do a lot more as they are often used as (very) simple databases or as a charts and graphs application, even though that was not the original intention of such software.
Rows are named
1
, 2
, etc. Columns are named
A
, ..., Z
,
AA
, AB
, etc. The intersection
of a row and a column is a cell, and its name is composed of the
column and row attributes, for example: C3
(shown in Figure 10.2, “Rows, Columns and Cells”). OpenOffice.org Calc highlights
the active row and column names.
To launch OpenOffice.org Calc, select + -> from the main menu.
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 , 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.
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.
The following sections will 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 will be used.
To enter data into a cell (either text or numbers) use the arrow keys to navigate to that cell or click in the cell and type the data in it, pressing the Enter key when you are finished. You can also use the Tab key or the Shift-Tab keys to move to the cell on the right or on the left, respectively.
The auto-completion feature simplifies data entry. Auto-completion “guesses” the next cell's data using the current cell's value as a base. It works not only for numeric data, but also for the days of the week, the months of the year, and others. Generally speaking, any kind of data which can be associated to a series of consecutive integral numbers can be entered 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”). Once the desired final value is shown, release the mouse button and the cells will be completed.
Cell data can also be sorted according to different criteria (by column or row, depending on how you arrange your data). To do so, first select the cells you want to sort and then open the sort options dialog choosing -> from the menu.
![]() | Tip |
---|---|
Make sure you also
select columns and rows which act as “headers” for
the data (in our example, the column |
In the Sort Criteria tab select the columns/rows to sort data by, and the sort order Ascending or Descending. The Options tab contains custom sort order settings, whether to perform a case sensitive sort or not and the direction of the sorting (top to bottom sorts data disposed in columns and left to right sorts data disposed in rows), among others. Click on the button once you are satisfied with the options and the selected cells will be sorted.
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. There are date and time, mathematical, statistical, financial, logical and many other kinds of functions available. Explore them by invoking the AutoPilot function by choosing the -> menu or by pressing the Ctrl-F2 keys.
Figure 10.4, “Using a Function in a Formula” shows the
AVERAGE
function applied to the selected range
of cells to calculate their average value. Note the use of the
:
character to specify a range of contiguous
cells in the function.
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 -> from the menu to bring up the chart assistant.
After making your selections in the first page of the chart assistant and clicking on its button, you will see the chart-type selection page (in Figure 10.5, “Choosing the Chart Type”, a 3D side-by-side bar chart is chosen). Make your choices and click on to obtain variants on the type you have selected. Again, make your choices and click on to choose the final chart options, such as the chart's title, axis titles, etc. Make your choices, and click on to create and insert the chart in the spreadsheet (see Figure 10.6, “A 3D Chart Inside the Spreadsheet”).
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 -> 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.
Spreadsheets simplify many accounting and other numeric-data-related tasks. They are used all over the world, from the corner-store manager who wants to manage schedules, to the biggest accounting firms which use them to write extensive and consistent data reports.
OpenOffice.org Calc offers extensive features for advanced users. You can use it as a simple database, or even program complete interfaces. You can also convert formats, define templates, etc. OpenOffice.org Calc is a very powerful application and will surely be around for quite a while.