Abstract
This section will give you a brief introduction to OpenOffice.org Calc's spreadsheet functions.
It takes 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 11.2, “Rows, Columns and Cells”).
OpenOffice.org Calc is an enterprise-ready spreadsheet application and includes many features way beyond the scope of this document. Consult the section called “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 11.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 B which contains the months) in order for those to “follow” the sorting of the data. |
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 a lot of 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 function AutoPilot by choosing -> from the menu or pressing the Ctrl-F2 keys.
Figure 11.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 becomes difficult to understand how data relates to other data: 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 11.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 11.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 OpenOffice Support Web site.
Also, do not hesitate to refer to the OpenOffice.org Calc on-disk help accessible through the -> menu. There you are bound to find answers to your questions. Topics are accessible through a table of contents, an index is 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 it 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.