Abstract
This section will give you a brief introduction to OpenOffice.org Calc's spreadsheet functions. Another very good option is gnumeric, the GNU project's spreadsheet application. However this section concentrates on OpenOffice.org Calc.
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 the electronic replacements for an accountant's ledger book and calculator. This software uses columns and rows to allow math calculations to be performed on data previously entered. 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 their “design” intention.
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.4).
Spreadsheets, as trivial as it may seem today, played a very important role in making office and home computers a reality. It was the “killer app” that justified buying a computer for many corporations.
OpenOffice.org (based on StarOffice) has been a popular office suite on GNU/Linux for a few years now. With the creation of OpenOffice.org, the authors re-engineered the foundation of StarOffice using open-components and introduced an XML-based file format. The potential of this office suite is now irrefutable. OpenOffice.org Calc is the part of that suite which provides the spreadsheet functions.
To launch OpenOffice.org Calc, select -> -> from the main menu.
You can also open it from any other OpenOffice.org application screen, selecting -> -> , which will open an OpenOffice.org Calc window with a blank spreadsheet on it.
This is the standard format bar for all OpenOffice.org applications used to change fonts, colors, alignment, etc. of the application's data.
Use it to introduce, edit or eliminate formulas inside cells.
Where you enter the data in the spreadsheet: numbers, dates, formulas, images, etc.
Clicking on this little area at the top left corner of the work area will select all cells at once. It is useful when you need to make changes which are “global” to the spreadsheet. For example, changing all font sizes in the cells to 10 points (pts).
A spreadsheet document can have more than one sheet. Use these buttons to easily navigate through each of the spreadsheet's sheets. From left to right they are: Go to the first sheet, Go to the previous sheet, Go to the next sheet and Go to the last sheet.
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 making full use of OpenOffice.org Calc.
The following sections will explore basic functions like 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 finish. 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 that 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.6). 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.
Make sure you select also columns and rows that 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 one in cell B34, adds the value in A20 to the result, multiplies that by 4 and adds that with 3 times the value of cell A25. Thus, rather complex expression can be made using simpler ones as a base.
OpenOffice.org Calc gives you a lot of pre-defined functions that you can use in your formulas. There are date&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.
Under KDE the Ctrl-F2 key combination switches to desktop number two, so you might want to redefine that in order to be able to invoke OpenOffice.org Calc's functions wizard using a keyboard shortcut.
Figure 11.7 shows the AVERAGE function applied to the selected range of cells to calculate their average value. Note the use of : 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.
Like all data-analysis functions, you must select the region that 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.8, a 3D side-by-side bar chart is chosen). Make your choices and click on the button to obtain variants on the type you have selected. Make your choices and click on the button to be be able to choose the final options for the chart, like chart title, axis titles, etc. Make your choices, and click on to create and insert the chart in the spreadsheet (see Figure 11.9).
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 inline help accessible through the -> menu. There you will find answers to all your questions. Topics are accessible through a table of contents, there is an index available and even a contextual search tool.
Spreadsheets simplify a lot accounting and other numeric-data-related tasks, and are used all over the world, from the corner-store manager who wants to manage schedules, to the biggest accounting firm which uses 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.