# OpenOffice.org/Printable version

OpenOffice.org

The current, editable version of this book is available in Wikibooks, the open-content textbooks collection, at
https://en.wikibooks.org/wiki/OpenOffice.org

Permission is granted to copy, distribute, and/or modify this document under the terms of the Creative Commons Attribution-ShareAlike 3.0 License.

# Introduction

OpenOffice.org is a free, open source alternative to the costly Microsoft Office. It possesses many of the same day-to-day capabilities as Microsoft Office, except that it is free to use and it is much more cross-platform than Microsoft Office. This makes it ideal for students or home users and people that have non-Windows Platform (Linux, MacOS, Unix, etc).

OpenOffice.org is a free, full-fledged Office Suite based on Sun's StarOffice Suite. OpenOffice.org is composed of six tools: Writer, Calc, Impress, Draw, Math, and Base. Writer is a word-processor, it can be used to write text documents from a simple letter to novels. Calc is a spreadsheet program, it can be used to store, process, and present data and formats them in a table layout. Impress is a presentation software, it can be used to create various types of presentations with various animations (effects). Draw is a vector-based drawing program, the images are based on lines and shapes which creates an image with virtually unlimited resolution. Math is a formula editor, it can be used to write from your high-school algebra to a complex rocket science mathematical formulas. Base, the newest tool of the suite, is a database program, it can be used to design, create, and manipulate a database. OpenOffice also supports macro scripting through its OpenOffice Basic scripting language, which obviously is based on BASIC programming language. OpenOffice is also very extensible, OpenOffice has a plugin system which allows third-parties to add functionalities into the suite, available plugins include a somewhat tongue-in-the-cheek lorem ipsum generator to a professional database report generator to remote control add-on for Impress.

OpenOffice.org's project started on 13 October 2000, with Sun's announcement of the project. Today, OpenOffice is available on all major platforms including Windows, Linux, Mac OS X, UNIX, and of course Sun's Solaris, and it is available in over 80 languages and localizations. OpenOffice also pioneered the OASIS OpenDocument Standard and it has been its native format since OpenOffice 2.0. A number of other Office Suites has also adopted OpenDocument Standard to some degree, but unfortunately Microsoft Office, another Office Suite which has the de facto largest user base, declined to follow the standard and developed their own XML standard instead.

Due to its open source nature, there are many other office suites that has their codes in partial or in whole derived from OpenOffice.org. Sun's StarOffice, in particular, bases most of its code on OpenOffice's codes, but added some additional proprietary components. The free and open source nature (and its quality) also makes OpenOffice.org a favorite choice among Linux distros makers to include OpenOffice.org in their distros as the default Office software. OpenOffice.org is also favored among some governments due to its open nature, they claim that a closed, proprietary document format like Microsoft's binary .doc format is detrimental in the long-term, when the company owning the format it have gone out-of-business, the cost of conversion in the future would be much higher with a closed, proprietary format. In the short term, the cost deploying thousands of computers with an expensive, proprietary suite may also be higher than deploying free, open suite.

## How to Get It?

OK, now I know what OpenOffice is, so how do I get it?

There are three official ways of distribution of OpenOffice, all of which are free:

# Installation

### Microsoft Windows

For some functionality to work, OpenOffice need Java Runtime Environment (JRE) to be installed on the computer. If you're not sure you do have JRE installed, you could include JRE in your OpenOffice package.

Installation of both the JRE and OpenOffice.org is as simple as following a fairly standard installation routine in Windows. The installation wizard first explains that it will allow you to install OpenOffice.org. After you click on "Next", it prompts you to read and accept the terms of service. Clicking next again allows you to choose a directory in which to install the software. You are then offered a choice between full installation of all features and a screen which would allow you to choose which components to install. After this point, the installation wizard runs for a time, with several progress bars and a running list of which file is being copied.

You can now open OpenOffice.org from the task bar (if you set up quickstarter during install) or its program group. From the program group you then select the tool you need. To use quickstarter right-click on it and select the tool you need.

### GNU/Linux

Most GNU/Linux distributions come with OpenOffice.org preinstalled. It is simply necessary to choose the appropriate option for installation. Be aware, however, that some of the packaged files, such as RPMs, do not include some features that conflict with the distro licenses, as they are packaged by the distributors. Java support is one item frequently left out of these versions. However, some distributions configure it to run with an alternative free software Java Runtime Environment from the Free Software Foundation.

It can be installed in RPM or DEB form manually by navigating to the same download page as for installing under Windows, and selecting the operating system as Linux after choosing the language, then choosing whether you want to download an RPM (For Red Hat, Fedora Core, SUSE, Mandriva, etc.) or DEB (for Debian, Ubuntu, etc.) file. This can then be installed with the package manager provided with your distribution.

OpenOffice.org can also be downloaded in source code form by choosing 'Source and Solver' from the main download page, but this isn't necessary for most users not interested in development.

#### Debian and Derivatives

Debian and and its derivatives (e.g. Ubuntu) uses apt-get, aptitude, and synaptic as their package manager. You can install OpenOffice.Org by running either of these in the Terminal:

sudo apt-get install openoffice.org

or

sudo aptitude install openoffice.org

or by using the graphical package manager.

### Mac OS X

In order to run OpenOffice.org on MAC OS X, X11 is required. MAC OS X versions before Tiger (10.4.x) required a separate application to be downloaded from Apple. With Tiger X11 is available on the OS X Install Disk. You can find this in the System/Installation/packages/ folder called X11User.pkg - run this package to install X11.

There are community builds of Universal Binary OpenOffice.org for Intel based Macs; however the PPC versions will run in Rosetta anyway and are QA'd which the Intel builds are not at the time of writing. Once X11 is installed on the machine downloading and installing OpenOffice is like any other MAC application.

Go to the Openoffice.org web site and follow the instructions to download the application. Once this has finished, double click the "OOo_2.0.1_MacOSX_install_en-US.dmg" or ("OOo_2.0.3rc3_MacOSXIntel_en-US.dmg") package and then drag the OpenOffice.org Icon to your applications folder.

When the copying process has finished you can click the eject button in the finder and the dmg package can be deleted.

Click the OpenOffice.org icon to run the application, optionally register and you're ready to go.

### Solaris

Instructions for installing OpenOffice.org in Solaris?

### Others

Instructions for installing OpenOffice.org in other operating systems?

# Applications

### Writer

"WRITER has everything you would expect from a modern, fully equipped word processor." --Writer Website

Writer is the OpenOffice.org Word Proccessor. If you can use MS Word then you can use Writer. The basic interface is very similar. However, there are parts of writer that are unique to writer.

More information can be found in our Writer Tutorial

### Calc

"CALC is the spreadsheet program you've always wanted. Newcomers find it intuitive and easy to learn; professional data miners and number crunchers will appreciate the comprehensive range of advanced functions." --Calc Website Calc is the OpenOffice.org Spreadsheet Program.

More information can be found in our Calc Tutorial

### Impress

"IMPRESS is a truly outstanding tool for creating effective multimedia presentations. Your presentations will stand out with 2D and 3D clip art, special effects, animation, and high-impact drawing tools." --Impress Website

Impress is a presentation tool, similar to PowerPoint.

### Draw

"DRAW - from a quick sketch to a complex plan, DRAW gives you the tools to communicate with graphics and diagrams." --Draw Website

Draw is a vector-based graphic program, similar to Corel Draw or Inkscape. It can be used to insert graphics into the other OOo programs.

### Base

"New to Version 2, BASE enables you to manipulate database data seamlessly within OpenOffice.org. Create and modify tables, forms, queries, and reports, either using your own database or BASE’s own built-in HSQL database engine. BASE offers a choice of using Wizards, Design Views, or SQL Views for beginners, intermediate, and advanced users." -- Base website

The OpenOffice documentation for BASE does not have very much useful information for beginner users. However, a very useful walkthrough for BASE is available at the OpenOffice.org Forums here.

### Math

OpenOffice.Org Math
Math is a program for creating mathmetical equations, which can be inserted into the other OOo programs. It is similar to Equation Editor in Microsoft Word.
Instruction on using Math?

### Others

Instruction on using other applications in the suite?

# Writer

OOo Writer
OpenOffice.org's Writer is a very useful word processor. Writer's native file format is .odt, which is an open document format (Older versions of OOo used the native format .sxw). Saving in native format is always recommended even if you're planning to save it to another Office format like Word's .doc format, because this ensures that no formatting would be lost because of .doc's inability to save certain formatting elements. (more about Open Document Format)

## Tutorials

### Your First Document

Open OpenOffice Writer. In Windows you can do this from the taskbar (if you set up quickstarter during install) or its program group. To open from the quickstarter right-click on the icon and select "Writer". In Linux you can usually open it from the OpenOffice Writer Icon on the taskbar or dock, from its entry in your "start" menu, or with the shell command "openoffice -writer &".

Type "Hello World" and save. To save click on the disk icon or go File > Save As... Save the document as hello.odt (if you are using 2.0.0 or 1.1.5) or hello.sxw (if you are using an older version). Don't worry about the extension. Just type the name you want and OpenOffice will automatically use its default extension. We will go over saving in different extensions later.

You have just completed your first document. That was easy, but OpenOffice writer has so much more to offer. Want to do more? Let's turn this document into an Adobe PDF file, a common method of distributing printed material over the internet.

### Your First PDF

Open your first document, and check to make sure that you have a PDF viewer installed on your computer (you can download one from Adobe if you don't have one). You should have your document with "Hello World" written in it. Now go to File > Export as PDF... and it should come up with the same window as before. Title it hello again, and click on "Save". This will bring up the PDF Options window, and for now, all you'll need to press is "Export".

Export dialog

The various PDF Options are:

• Range - Select either "All", "Pages", or (if you have highlighted certain text) "Selection". This allows you to choose which pages to export as a PDF, usually all of them.
• Images - Informs OpenOffice.org how you want images compressed into PDF format. "Lossless compression" will be the best quality, but also the largest. "JPEG compression" produces lossy results, and it's according "Quality" option decides how 'liberally' the compression occurs. The "Reduce image resolution" brings the images DPI (Dots Per Inch) down, which could have an effect if the user zooms into an image, making it more pixelated.
• General - The "Tagged PDF" option produces a text document that is tagged, IE readable by screen readers for the disabled. To produce a properly formatted tagged PDF, you will have to use the internal stylizing of OOo, and not just concentrate on visual output. There is also "Export Notes", which includes the notes on the document in the PDF while exporting. If you use transitional effects in your document, you can choose whether to leave them intact or not by checking the "Use transition effects" option. "Submit forms in format" only decides how the forms for the document will be formatted.

To learn more about PDF formatting and documentation options, look up the PDF Wikipedia entry.

### Saving

saving a document for the first time

1. Open OpenOffice writer, and the program starts.
2. Click on the menu "File" , afterwards click on "Save as"
3. Type a filename, in this example "tree"
4. Hit the "save" button

checking which toolbars are enabled

1. Click on the menu "View", afterwards click on "Toolbars", afterwards click on "Standard" (if it isn't already checked).

## Basic Formatting Options

If you've used Microsoft Word, or any of most other modern word processing programs, you will have no trouble with making basic formatting changes, such as in changing fonts or changing color. To change the formatting, you'll have to block the texts that you want to format, then click on the appropriate formatting buttons.

The formatting toolbar contains most of the formatting options needed for day-to-day usage.

The formatting toolbar. If you can't find the toolbar, go to View > Toolbar > Formatting

Let's talk about things inside the formatting toolbar one by one. Tip: You can hover your mouse on the buttons on Open Office to know what each button do.

### Style Control

The leftmost button, is the Styles and Formatting Button. Click on it to show the Style and Formatting dialog. Next to it is a Style dropdown menu, which is basically a list of commonly used styles. Styles are basically predefined formatting, which you can apply on a section of text. Style is also used to determine the structure of your writing. The great thing about using style is that if you change the formatting of a style, all elements in your document that previously applied that style would reflect the formatting change, saving you lots of time from changing them one by one. For now, just digest that, we'll talk more about styling in a moment, after we've finished with the toolbar.

### Font Controls

The next two dropdown menus are for controlling Font Name (or Font Type or Font Face) and Font Size respectively. The Font Name dropdown, controls the type of font used whether it is Times New Roman, Arial, Courier New, or some other fonts you might have in your computer. The Font Size dropdown, as it says, controls how big or how small your text looks like.

### Standard Formatting Options (BIU)

The next three buttons might be familiar. They are the friendly Bold, Italic, and Underline buttons. If you've gotten more comfortable with OpenOffice, you can also use the shortcuts Ctrl+B, Ctrl+I, and Ctrl+U for Bold, Italic, and Underline respectively. You can also mix these formatting together, forming something like BoldedItalic, BoldedUnderline, ItalizedUnderline, BoldedItalizedUnderlined, etc.

### Paragraph Alignment

Ok, now on to the next section. The next four buttons control the text alignment. There are four types of alignment:

#### Align Left

"Align left" ensures that your paragraphs' leftmost words form a line on the left side of the paragraph. This is the most used alignment, and thus the default alignment for a new blank document. The example of align left is this paragraph. You'll see that the leftmost words make a straight line, while the rightmost words are jagged.

#### Centered

A centered paragraph ensures that your paragraph would appear in the center. This is usually used for titles. You'll see that the length of the leftmost word to the left margin is equal to the length of the rightmost word to the right margin. This paragraph is an example of centered paragraph.

#### Align Right

Align right ensures that your paragraphs' rightmost words form a line on the right side of the paragraph. This is usually used for signatures. The example of align right is this paragraph. You'll see that the rightmost words make a straight line, while the leftmost words are left uncontrolled as it fits.

#### Justified

Justify paragraphs is a combination of align right and align left. A "justified paragraph" would make the leftmost and the rightmost words make a line. To achieve this the spacings between the words or the character are usually adjusted. This is usually used to write in small columns, where the use of align left would make the columns look ugly. This paragraph is the example of justified paragraph.

### Bullets and Numbering

The next two buttons, the Bullets and the Numberings button are for creating lists. If you click the Bullets button, a small circle would show up on the start of the paragraph your cursor is in, or on the start of each paragraph you're blocking. The same for Numbering, although a number would show up instead of circle.

Finer control for making bullets and numbering can be found on the Bullet and Numbering Toolbar that would show up whenever your cursor is in a bulleted or numbered list. Feel free to explore what each of the buttons on the toolbar do.

There are also various collections of Bullets and Numbering style in Format > Bullets and Numbering. Here, you can change the bullets and numbering into Roman Number, for example, or into alphabetical instead of plain number, or even a mix of them. You could even control more in depth by changing the settings on the Position Tab and/or Options Tab.

### Indentation

The next two button controls the Indentation, the first button move the indent to the left, while the second button would move it to the right. Indenting a paragraph would make your paragraph written deeper into the paragraph or nearer to the paper's edge. You can also move the indentation marker on the ruler for finer movement. This paragraph, for example is indented.

### Color Control

The last three buttons give you full control for colors. The first button would change the font color, like this red text, the second button would highlight the text, like this cyan highlighted text, and the last button would change the background color, like this yellow-background paragraph. There are big differences between highlighting and background color, when changing the background color, you must change the background of the whole paragraph, while highlighting gives you more control for only highlighting certain part of the paragraph.

### Shifting Case of letters

Say you have a sentence "the quick brown fox" and you wish to change it to "THE QUICK BROWN FOX" without retyping it, that is to shift it from lower case to upper case. Writer provides a quick way to do this by shifting the case of a highlighted selection. Just highlight the selection of text you want to shift the case of then right click on that text and in the right click menu select "case/characters" and then select Upper case to make every letter in your selection capital or lower case to make it all lower case.

## The Stylist

If you write longer texts, however, you will quickly discover that manually assigning formatting attributes to text can be a tedious task. It's hard to keep over fifty citations or some twenty Headings in exactly the same font face, font size, character spacing and so on. Thankfully, Writer has a tool to help you keep everything clean, simple and consistent.

The Stylist is a small box that contains lots of predefined formatting styles like "Default" or "Heading 1". Let's work on the following example and use the stylist to touch up this chapter in Writer.

### Preparation

To do this, we first need to copy this text to Writer, which is very easy using the clipboard. If you did not use the clipboard yet, just do the following: Select the text of this chapter, by positioning the mouse cursor at the start of the chapter, right before the heading ("The Stylist"), then hold down the left mouse button and drag the mouse to the end of the chapter. The selected text becomes highlighted, indicating that whatever we decide to do now will influence this part of our document. Next we click on Edit > Copy to place the selected text in the clipboard. To insert this text in Writer, we click in our document where we want to insert the example text, then choose Edit > Paste. If everything went correctly, we will now have quite some text to work with. Now let's see what the Stylist can do for us.

### Applying Styles

If you cannot see the Stylist yet, either choose Format > Styles and Formatting or simply press F11. Among others categories, Writer differentiates between paragraph styles and character styles. We want to apply a paragraph style, so the default category, "Paragraph Styles" selected in the little toolbar at the top of the Stylist is just right.

Let's make the text a little easier to read and indent the first line of some paragraphs. To do this, we click somewhere in a paragraph that we want to apply a the new style to, then choose and double click the "First line indent" style. If everything worked alright, the first line of the selected paragraph is now indented.

"Wait a moment!", you might say, "Wouldn't it have been easier to just click on Format > Paragraph, then change the indentation?" Yes, for one paragraph it might indeed have been easier, but what if you indented lots of paragraphs, then decide that things would look much nicer if all of your manually indented paragraphs should also be a bit separated from each other, as well as regular paragraphs? With styles, this is a breeze - we just change the style you applied to the paragraphs in question.

To change the "First line indent" style, right click it and select "Modify". Switch to the "Indents & Spacing" tab and change the value of the "Above Paragraph" spacing to something higher. Click OK and you will see that immediately all paragraphs that had the "First line indent" style applied are now spaced a bit apart.

This concludes our short introduction to the Stylist. Go ahead, have some fun with it - there's lots of things to discover. Like outline numbering (check Tools > Outline Numbering...). Or styles for whole pages. Or - well, see for yourself!

## Insert Numbered Equation

Type fn and then press F3.

# Calc

OpenOffice User's Manual:
OpenOffice.org Calc

OOo Calc

## What is Calc?

Calc is the spreadsheet component of OpenOffice.org (OOo). You can enter data, usually numerical data, in a spreadsheet and then manipulate this data to produce certain results.

Alternatively you can enter data and then use Calc in a ‘What If...’ manner by changing some of the data and observing the results without having to retype the entire spreadsheet or sheet.

## Spreadsheets, sheets and cells

Calc works with elements called spreadsheets. Spreadsheets consist of a number of individual sheets, each containing a block of cells arranged in rows and columns.

These cells hold the individual elements—text, numbers, formulas etc.—which make up the data to be displayed and manipulated.

Each spreadsheet can have many sheets and each sheet can have many individual cells. In version 3.0 of OOo, each sheet can have a maximum of 65,536 rows and a maximum of 1024 columns.

You can enter data into Calc in several ways: using the keyboard, the mouse (dragging and dropping), the Fill tool, and selection lists. Calc also provides the ability to input information into multiple sheets of the same document at one time.

After entering data, you can format and display it in various ways. A function is a pre-defined calculation entered in a cell to help you analyze or manipulate data in a spreadsheet. All you have to do is add the arguments, and the calculation is automatically made for you. Functions are the main reason for spreadsheets. If you understand functions, then you can start to use the real power of a spreadsheet.

## Understanding functions

Calc includes over 350 functions to help you analyze and reference data. Many of these functions are for use with numbers, but many others are used with dates and times, or even text. A function may be as simple as adding two numbers together, or finding the average of a list of numbers. Alternatively, it may be as complex as calculating the standard deviation of a sample, or a hyperbolic tangent of a number.

Typically, the name of a function is an abbreviated description of what the function does. For instance, the FV function gives the future value of an investment, while BIN2HEX converts a binary number to a hexadecimal number. By tradition, functions are entered entirely in upper case letters, although Calc will read them correctly if they are in lower or mixed case, too.

A few basic functions are also represented by symbols. For instance, SUM, which adds arguments, can also be entered as + while PRODUCTION, which multiplies arguments, can also be entered as *.

Each function has a number of arguments used in the calculations. These arguments may or may not have their own name. Your job is to enter the arguments needed to run the function. In some cases, the arguments have pre-defined choices, and you may need to refer to the online help or Appendix B (Description of Functions) in this book to understand them. More often, however, an argument is a value that you enter manually, or one already entered in a cell or range of cells on the spreadsheet. In Calc, you can enter values from other cells by typing in their name or range, or—unlike the case in some spreadsheets—by selecting cells with the mouse. Should the values in the cells change, then the result of the function is automatically updated.

Strictly speaking, when all the arguments are entered and a function is ready to run, it becomes a formula. These terms are sometimes used interchangeably, but the distinction is worth preserving, because a formula can use functions as an argument.

For compatibility, functions and their arguments in Calc have almost identical names to their counterparts in Microsoft Excel. However, both Excel and Calc have functions that the other lacks. Occasionally, too, functions with the same names in Calc and Excel have different arguments, or slightly different names for the same argument—neither of which can be imported to the other. However, perhaps nine-tenths of functions can be imported between Calc and Excel without any problems.

## Understanding the structure of functions

Except for simple functions such as + or *, all functions have a similar structure. If you use the right tool for entering a function, you can escape learning this structure, but it is still worth knowing for troubleshooting.

To give a typical example, the structure of a function to find cells that match entered search criteria is:

= DCOUNT (Database;Database field;Search_criteria)


Like most functions, this one starts with an equal sign. It is followed by DCOUNT, the name of the function. After the name of the function comes its arguments. All arguments are required, unless specifically listed as optional.

Arguments are added within the brackets (parentheses) and separated by semicolons, with no space between the arguments and the semicolons. Many arguments are a number. A Calc function can take up to thirty numbers as an argument. That may not sound like much at first. However, when you realize that the number can be not only a number or a single cell, but also an array or range of cells that contain several or even hundreds of cells, then the apparent limitation vanishes.

Other arguments may be a column label, a mathematical constant, or a value unique to that function.

Depending on the function, arguments may have to be entered with straight quotation marks. However, this requirement is not consistent. Otherwise similar formulas may differ only in this requirement, and no simple rule tells you which is which. You simply have to know or check the requirements in the online help.

The only exception to these structural rules are basic arithmetical functions entered with symbols. For example, instead of entering =SUM(2;3), you can enter =2+3.

As well as being used on its own, a function can be an argument in a larger formula. A formula, however, is limited by the fact that it can only do one function at a time. You need to make sure that functions are done in the right order if the formula is going to work.

To help set the order for functions in a multiple-function formula, you use parentheses within parentheses. When the formula is run, Calc does the innermost function first, then works outwards. For example, in the simple calculation =2+(5*7), Calc multiples 5 by 7 first. Only then is 2 added to the result to get 37.

The placement of functions within sets of parentheses is called nesting. Basically, nesting reduces a function that could run on its own to an argument in the formula. For example, in =2+(5*7), the formula (5*7) is nested within the larger formula of =2+(5*7). In other words, the nested function becomes an argument of another function.

This relation is more obvious when doing a calculation using a function with a name. For all purposes,

=SUM(2;PRODUCT(5;7))


is the same formula as =2+(5*7). However, when SUM and PRODUCT are used, then the relation is clearer. The fact that the PRODUCT function comes after a semicolon and in a set of parentheses for the SUM function makes it clear that PRODUCT is an argument for SUM. In addition, the fact that the inner pair of parentheses is around (5;7) makes clear that this operation is done before the one defined by the outer pair of parentheses.

To get an idea of what nested functions can do, imagine that you are designing a self-directed learning module. During the module, students do three quizzes, and enter the results in cells A1, A2, and A3. In A4, you can create a nested formula that begins by averaging the results of the quizzes with the formula =AVERAGE(A1:A3). The formula then uses the IF function to give the student feedback that depends upon the average grade on the quizzes. The entire formula would read:

=IF(AVERAGE(A1:A3) >85; "Congratulations! You are ready to advance to the next module";
"Failed. Please review the material again. If necessary, contact your instructor for help")


Depending on the average, the student would receive the message for either congratulations or failure.

Notice that the nested formula for the average does not require its own equal sign. The one at the start of the equation is enough for both formulas.

If you are new to spreadsheets, the best way to think of functions is as a scripting language. We've used simple examples to explain more clearly, but, through nesting of functions, a Calc formula can quickly become complex.

### Formulas

See the more specific section: Formulas.

## The database (preconditions)

The basis for work with the DataPilot is always a list with your data in raw format. Such a list is comparable to a database table. The table consists of rows (data sets) and columns (data fields). The field names are in the first row above the list.

Later we will explain that the data source could be an external file or database. The simplest use case is that your data is contained in a Calc spreadsheet. For such a list, Calc offers sorting functions that do not depend on the DataPilot.

For processing data in lists, the program must know in which area of the spreadsheet the table is. A Calc table can be anywhere in the sheet, in any position. It is also possible, that a spreadsheet contains several unrelated tables.

Calc recognizes your lists automatically. It uses the following logic:

Starting from the cell you’ve selected (which must be within your list), Calc checks the surrounding cells in all 4 directions (left, right, above, below). The border is recognized if the program discovers an empty row or column, or if it hits the left or upper border of the spreadsheet.

This means that the described functions can only work correctly if there are no empty rows or columns in your list. Avoid empty lines (for example for formatting). You can format your list by using cell formats.

If you select more than one single cell before you start sorting, filtering or calling the DataPilot, then the automatic list recognition is switched off. Calc assumes that the list matches exactly the cells you have selected. This might be useful in only very few cases.

A relatively big source for errors is that you might declare a list by mistake and you sort your list. If you select multiple cells (for example, a whole column) then the sorting mixes up the data that should be together in one row.

In addition to these formal aspects, the logical structure of your table is very important for using the DataPilot.

When entering the data, do not add outlines, groups. or summaries. This becomes clear when we think about what we could have done wrong in our Sales list example. This will give you a list of bad ideas, that you can find very often among spreadsheet users who are not informed about the possibilities of processing lists within a spreadsheet.

First bad idea: You could have made several sheets. For example, you could have made a sheet for each group of articles. Analyses are then only possible within each group. Analyses for several groups would then be a lot of hassle.

Second bad idea: In the turnover list, instead of only one column for the amount, you could have made a column for the amounts for each employee. The amounts than had to be entered into the appropriate column. An analysis with the DataPilot would not be possible any more. In contrast, one result of the DataPilot is that you can get results for each employee if you have entered everything in one column.

Third bad idea: You could have entered the amounts in chronological order. At the end of each month you could have made a sum. In this case, a sorting of the list for different criteria is not possible, and you could not use the DataPilot. The rows with the sums would be treated by the DataPilot like any other amount you have entered. Getting monthly results is one of the very fast and easy features of the DataPilot.

You start the DataPilot with Data > DataPilot > Start. If the list to be analyzed is in a spreadsheet table, select one cell within this list. Calc recognizes and selects the list automatically for use with the DataPilot.

## Data Analysis

Once you are familiar with functions and formulas, the next step is to learn how to automate the processes and perform useful analyses of the data.

Calc includes several tools to help you manipulate functions and formulas, ranging from features for copying and reusing data, to creating subtotals automatically, to varying information to help you find the answers you need. These tools are divided between the Tools and Data menus.

If you are a newcomer to spreadsheets, these tools can be overwhelming at first. However, they become simpler if you remember that they all depend on input from either a cell or a range of cells that contain the data with which you are working.

You can always enter the cells or range manually, but in many cases, you can also use the Shrink/Maximize icon beside a field to change temporarily the size of the tool’s window while you select the cells with the mouse. Sometimes, you may have to experiment with which data goes into one field, but, once you have found out, the rest is simply setting a selection of options, many of which can be ignored in any given case. Just keep the basic purpose of each tool in mind, and you should have little trouble with Calc’s function tools.

You don’t need to learn them, especially if your spreadsheet use is simple, but as your manipulation of data becomes more sophisticated, they can save time in making calculations, especially as you start to deal with hypothetical situations. Just as importantly, they can allow you to preserve your work and to share it with other people—or yourself at a later session.

### Subtotal

SUBTOTAL is a function listed under the Mathematical category when you use the Function Wizard (Insert > Function). Because of its usefulness, the function has a graphical interface accessible from Data > Subtotals.

As the name suggests, SUBTOTAL totals data arranged in a array—that is, a group of cells with labels for columns and/or rows. Using the Subtotals dialog, you can select arrays, then choose a statistical function to apply to them. For efficiency, you can choose up to three groups of arrays to which to apply a function. When you click OK, Calc adds subtotals and grand totals to the selected arrays, using the Result and Result2 cell styles for them.

To insert subtotal values into a sheet:

1. Ensure that the columns have labels.
2. Select the range of cells that you want to calculate subtotals for, and then choose Data > Subtotals.
3. In the Subtotals dialog, in the Group by box, select the column that you want to add the subtotals to. If the contents of the selected column change, the subtotals are automatically recalculated.
4. In the Calculate subtotals for box, select the columns containing the values that you want to subtotal.
5. In the Use function box, select the function that you want to use to calculate the subtotals.
6. Click OK.

If you use more than one group, then you can also arrange the subtotals according to choices made on the dialog’s Options page, including ascending and descending order or using one of the predefined custom sorts defined in Tools > Options > OpenOffice.org Calc > Sort Lists.

## Scenarios

Scenarios are a tool to test “what-if” questions. Use Tools > Scenarios to enter variable contents—scenarios—in the same cell. Each scenario is named, and can be edited and formatted separately, and chosen from a drop-down list in the Navigator and the title bar of the scenario. When you print the spreadsheet, only the contents of the currently active scenario is printed.

By adding a scenario, you can quickly change the arguments of a formula and view the new results. For example, if you wanted to calculate different interest rates on an investment, you could add a scenario for each interest rate, and quickly view the results. If you had another formula that calculated your yearly income and included the result of the interest rate formula as an argument, it would also be updated. If all your sources of incomes used scenarios, you could efficiently build a complex model of your possible income.

### Creating scenarios

To create a scenario, select all the cells that provide the data for the scenario.

1. Select the cells that contain the values that will change between scenarios. To select multiple cells, hold down the Ctrl key as you click each cell.
2. Choose Tools > Scenarios.
3. On the Create Scenario dialog, enter a name for the new scenario. It’s best to use a name that clearly identifies the scenario, not the default name as shown in the illustration. This name is displayed in the Navigator and on the title bar of the scenario on the sheet itself.
4. Optionally add some information to the Comment box. The example shows the default comment. This information is displayed in the Navigator when you click the Scenarios icon and select the desired scenario.
5. Optionally select or deselect the options in the Settings section. See below for more information about these options.
6. Click OK to close the dialog. The new scenario is automatically activated.

### Settings

The lower portion of the Create Scenario dialog contains several options. In most cases the default settings (shown selected in the example) are suitable.

Display border

Highlights the scenario in your table with a border. The color for the border is specified in the field to the right of this option. The border has a title bar displaying the name of the last scenario. The button on the right of the scenario border offers you an overview of all the scenarios in this area, if several have been defined. You can choose any of the scenarios from this list without restrictions.

Copy back

Copies the values of cells that you change into the active scenario. If you do not select this option, the scenario is not changed when you change cell values. The behavior of the Copy back setting depends on the cell protection, the sheet protection, and the Prevent changes settings.

Copy entire sheet

Copies the entire sheet into an additional scenario sheet.

Prevent changes

Prevents changes to the active scenario.

• You can only change the scenario properties if the Prevent changes option is not selected and if the sheet is not protected.
• You can only edit cell values if the Prevent changes option is selected, if the Copy back option is not selected, and if the cells are not protected.
• You can only change scenario cell values and write them back into the scenario if the Prevent changes option is not selected, if the Copy back option is selected, and if the cells are not protected.

### Working with scenarios using the Navigator

After scenarios are added to a spreadsheet, you can jump to a particular scenario by using the Navigator, then selecting a scenario from the list. You can also color code scenarios to make them easier to distinguish from one another.

To select a scenario in the Navigator, click the Scenarios icon in the Navigator. The defined scenarios are listed, with the comments that were entered when the scenarios were created.

Double-click a scenario name in the Navigator to apply that scenario to the current sheet.

To delete a scenario, right-click the name in the Navigator and choose Delete.

To edit a scenario, including its name and comments, right-click the name in the Navigator and choose Properties. The Edit Properties dialog is the same as the Create Scenario dialog.

To learn which values in the scenario affect other values, choose Tools > Detective > Trace Dependents. Arrows point to the cells that are directly dependent on the current cell.

## Goalseeking

Tools > Goal Seek reverses the usual order for a formula. Usually, you run a formula to get the result when certain arguments are entered. By contrast, with Goal Seek, you work with a completed formula to see what values you need in an argument to get the results that you want.

To take a simple example, imagine that the Chief Financial Officer of a company is developing sales projections for each quarter of the forthcoming year. She knows what the company’s total income must be for the year to satisfy stockholders. She also has a good idea of the company’s income in the first three quarters, because of the contracts that are already signed. For the fourth quarter, however, no definite income is available. So how much must the company earn in Q4 to reach its goal? To answer, the CFO enters the projected earnings for each of the other three quarters and the projection for the entire year. Then she runs a goal seek on the cell for Q4 sales, and receives her answer.

Other uses of goal seek may be more complicated, but the method remains the same. To run a goal seek, at least one of the values for an argument must be a referenced cell or range. Only one argument can be altered in a single goal seek. After you get the result of a goal seek, you can replace the original value in the referenced cell with the result, or record the result elsewhere for later use, possibly as a scenario.

With the help of Goal Seek you can calculate a value that, as part of a formula, leads to the result you specify for the formula. You thus define the formula with several fixed values and one variable value and the result of the formula.

### Goal Seek example

To calculate annual interest (I), create a table with the values for the capital (C), number of years (n), and interest rate (i). The formula is I = C*n*i.

Let us assume that the interest rate i of 7.5% and the number of years n (1) will remain constant. However, you want to know how much the investment capital C would have to be modified in order to attain a particular return I. For this example, calculate how much capital C would be required if you want an annual return of $15,000. Enter each of the values for Capital C (an arbitrary value like$100,000), number of years n (1), and interest rate i (7.5%) in one cell each. Enter the formula to calculate the interest I in another cell. Instead of C, n, and i use the reference to the cell with the corresponding value. In our example, these are B1, B2, and B3.

## Solver

Tools > Solver amounts to a more elaborate form of Goal Seek. The difference is that the Solver deals with equations with multiple unknown variables. It is specifically designed to minimize or maximize the result according to a set of rules that you define.

Each of these rules sets up whether an argument in the formula should be greater than, lesser than, or equal to the figure you enter. If you want the argument to remain unchanged, you enter a rule that the cell that contains it should be equal to its current entry. For arguments that you would like to change, you need to add two rules to define a range of possible values: the limiting conditions. For example, you can set the constraint that one of the variables or cells must not be bigger than another variable, or not bigger than a given value. You can also define the constraint that one or more variables must be integers (values without decimals), or binary values (where only 0 and 1 are allowed).

Once you have finished setting up the rules, you can adjust the argument and the results by clicking the Solve button.

1. Place the cursor in the formula cell (the cell containing the interest I), and choose Tools > Goal Seek.
2. On the Goal Seek dialog, the correct cell is already entered in the Formula cell field.
3. Place the cursor in the Variable cell field. In the sheet, click in the cell that contains the value to be changed, in this example it is the cell with the capital value C.
4. Enter the desired result of the formula in the Target value field. In this example, the value is 15000. The figure below shows the cells and fields.
5. Click OK. A dialog appears informing you that the Goal Seek was successful. Click Yes to enter the result in the cell with the variable value. The result is shown below.

## Sharing

You can use several methods to keep track of changes you or others made to a document.

• Use change marks to show added or deleted material and changed formatting. Later, you or another person can review and accept or reject each change.
• Make changes to a copy of the document (stored in a different folder, under a different name, or both), then use Calc to compare the files and show the changes.
• Save versions that are stored as part of the original file.

Reviewers can leave notes in the document or make comments attached to specific changes.

### Preparing a document for review (optional)

When you send a document to someone else to review or edit, you may want to prepare it first so that the editor or reviewer does not have to remember to turn on the revision marks. After you have protected the document, any user must enter the correct password in order to turn off the function or accept or reject changes.

1. Open the document and make sure that the Edit > Changes > Record menu item has a check mark next to it, indicating that change recording is active.
2. (Optional) Click Edit > Changes > Protect Records. On the Protect Records dialog, type a password (twice) and click OK.

### Identifying copies of spreadsheets

When sharing documents, it is important to keep track of the different copies of the document. This can be done either in the file name or in the file title. If you have not provided a file title in the spreadsheet’s properties, the spreadsheet’s filename is displayed in the title bar. To set the title of the spreadsheet, select File > Properties > Description.

## = Cells

You have to protect the sheet first.To protect cells go to Format > Cells > Cell protection.

## Macros

Main page: [[:../Macro/|../Macro/]]

A macro is a saved sequence of commands or keystrokes that are stored for later use. An example of a simple macro is one that “types” your address. The OpenOffice.org (OOo) macro language is very flexible, allowing automation of both simple and complex tasks. Macros are especially useful to repeat a task the same way over and over again. Macros can be in many different languages, but the default is BASIC.[1] This section briefly discusses common problems related to macro programming using Calc.

The following steps create a macro that performs paste special with multiply.

1. Open a new spreadsheet.
2. Enter numbers into a sheet.
3. Select cell A3, which contains the number 3, and copy the value to the clipboard.
4. Select the range A1:C3.
5. Use Tools > Macros > Record Macro to start the macro recorder. The Record Macro dialog is displayed with a stop recording button.
6. Use Edit > Paste Special to open the Paste Special dialog.
7. Set the operation to Multiply and click OK. The cells are now multiplied by 3.
8. Click Stop Recording to stop the macro recorder and save the macro.
9. Select the current document. For this example, the current Calc document is Untitled. Click on the + next to the document to view the contained libraries. Prior to OOo version 3.0, new documents were created with a standard library; this is no longer true. In OOo version 3.0, the standard library is not created until the document is saved, or the library is needed. If desired, create a new library to contain the macro (but this is not necessary).
10. Click New Module to create a module in the Standard library. If no libraries exist, then the Standard library is automatically created and used.
11. Click OK to create a module named Module1.
12. Select the newly created Module1, enter the macro name PasteMultiply and click Save. The created macro is saved in Module1 of the Standard library in the Untitled2 document (see Listing 1).

Listing 1. Paste special with multiply.

 

 sub PasteMultiply rem -------------------------------------------------------------- rem define variables dim document as object dim dispatcher as object rem -------------------------------------------------------------- rem get access to the document document = ThisComponent.CurrentController.Frame dispatcher = createUnoService("com.sun.star.frame.DispatchHelper") rem -------------------------------------------------------------- dim args1(5) as new com.sun.star.beans.PropertyValue args1(0).Name = "Flags" args1(0).Value = "A" args1(1).Name = "FormulaCommand" args1(1).Value = 3 args1(2).Name = "SkipEmptyCells" args1(2).Value = false args1(3).Name = "Transpose" args1(3).Value = false args1(4).Name = "AsLink" args1(4).Value = false args1(5).Name = "MoveMode" args1(5).Value = 4 dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0, args1()) end sub 

Calc can call macros as Calc functions. Use the following steps to create a simple macro:

1. Create a new Calc document named CalcTestMacros.ods.
2. Use Tools > Macros > Organize Macros > OpenOffice.org Basic to open the OpenOffice.org Basic Macros dialog. The Macro from box lists available macro library containers. My Macros contains macros that you write or add to OOo. OpenOffice.org Macros contains macros included with OOo and should not be changed. All other library containers are currently open OOo documents.
3. Click Organizer to open the OpenOffice.org Basic Macro Organizer dialog.
4. Click the Libraries tab.
5. Select the document to contain the macro.
6. Click New to open the New Library dialog.
7. Enter a descriptive library name (such as AuthorsCalcMacros) and click OK to create the library. The new library name is shown the library list, but the dialog may show only a portion of the name.
8. Select AuthorsCalcMacros and click Edit to edit the library. OOo automatically creates a module named Module1 and a macro named Main.
9. Modify the code so that it is the same as that shown in Listing 2. The important addition is the creation of the NumberFive function, which returns the number five. The statement Option Explicit forces all variables to be declared before they are used. If Option Explicit is omitted, variables are automatically defined at first use as type Variant.

Listing 2. Function that returns five.

 

 REM ***** BASIC ***** Option Explicit Sub Main End Sub Function NumberFive() NumberFive = 5 End Function 

# Impress

OOo Impress
Impress is a open office tool to create presentations. It's very similar to Microsoft's PowerPoint. Impress can handle both Microsoft's PowerPoint format (ppt) as well as its own open standard format called open document presentation format (odp) which is internationally recognized.

## Working with impress

Launch impress and you will be presented with the following screen

Presentation Type

This is nothing but the presentation wizard which eases the job of creating presentations. You can either choose Empty Presentation or a template or you can choose to open a existing presentation.

Presentation Design
Slide transition
OOo Impress

## Viewing slideshow

The slideshow shortcut is "F5". When launched with the "Rehearse Timings" instead, it will record the time between each slide, in order to allow to replay it later.

## Saving the document

To save the Document, you simply go to File, and press Save As. A pop-up window will come up, and from there you can name your spreadsheet and specify its location. Also, there is a button on the toolbar to save the current spreadsheet.

# Draw

"DRAW - from a quick sketch to a complex plan, DRAW gives you the tools to communicate with graphics and diagrams." --Draw Website

OOo Draw
Draw is a vector-based graphic program, similar to Inkscape. It can be used to insert graphics into the other OOo programs.

# Base

OpenOffice User's Manual:
OpenOffice.org Base
OOo Base

OpenOffice.org Base, is a relational database management system, a competitor to Microsoft Access and even suitable for beginners to databases. Much documentation and books for Microsoft Access will apply similarly (but not necessarily identically) for OpenOffice.org Base. It is a member of the OpenOffice.org suite of applications. OpenOffice.org users can choose to connect to external full-featured SQL database such as MySQL, PostgreSQL and even Oracle through ODBC or JDBC drivers. OpenOffice.org Base can hence act as a GUI frontend for SQL views, table design and query. In addition, OpenOffice.org has its own Form wizard to create dialog windows for form filling and updates.

Base may not be included with your operating system, but you should be able to get it relatively easily, as it is free software. You will also need Java installed (and not disabled in OpenOffice.org) in order for Base to run.

## Data

A database is a structured collection of records or data that is stored in a computer system. The structure is achieved by organizing the data according to a database model. The model in most common use today is the relational model.

A database management system (DBMS) is computer software that manages databases. DBMSes may use any of a variety of database models, such as the network model or relational model. In large systems, a DBMS allows users and other software to store and retrieve data in a structured way.

## Connecting to Databases

If you have an existing database already created, you can connect to it either with the first screen you see in OpenOffice.org Base or by clicking File, New, Database then Connect to existing Database. There is limited support for Microsoft Access databases.

## Relations

Imagine you want to record the products that customers order from your company. You could use a spreadsheet with one customer per row, but one customer might order many products. You could use a spreadsheet with one order per row, but you may have to type in customer name and address repetitively for many orders. In situations such as this, you are better off using a database rather than a spreadsheet.

The difference between databases and spreadsheets is that while spreadsheets can contain records (often a row in a spreadsheet) and data fields (which is just a particular type of data - often a column header in a spreadsheet), databases can also contain relationships between records. These can be one-to-many relationships or many-to-many relationships.

When you specify the relationships between tables you can also specify referential integrity so that data can't be entered which contradicts related data in another table.

## Wizards

Wizards are simply a way of describing a step-by-step approach in making software easier for the user, usually choosing an option and clicking Next. On launching Base you are presented with a Database Wizard which offers you the choice of creating a database, opening or connecting to an existing one. Choose Create a Database to begin with and click Next. Choose to register the database, don't worry this is only done locally and can help other modules of OpenOffice.org (such as Calc) work with your database. Don't choose the Table wizard just yet and save (and name) your database in a convenient location. For now, use OpenDocument Database format, though this will probably be already automatically selected as the default. Why save it already? Because data such as records will be saved automatically as it is being entered into a database.

Databases do not use files in the normal sense, however a good database can output its content structured with SQL (Structured Query Language) – an ANSI/ISO standard. It is also important that it supports ODBC (Open Data Base Connectivity)

You should now be presented with the main interface. On the left is the Database column which contains four options (sometimes called modules); Tables, Queries, Forms and Reports.

## Tables

### Design View

Creating a table in design view is a more specific way of creating a table. Click on it and you will see a spreadsheet-like layout but this might be deceptive (don't confuse it with the table view). The left column is for the Field name which you choose (you can choose anything, this will be for your reference). The second column is for Field Type and contains a drop-down menu for the type of data that will be stored in this field. For example a name would be Text [VARCHAR], a date would be Date [DATE] and so on. When the Field type is selected, you will see a window at the bottom which is the Field Properties where can you specify things like whether you require data to be entered into that field type when logging records, the permitted character length of the data and decimal places. You can specify validation here which checks that data entered is among the permitted data. Unlike in the Table wizard you will need to save any changes in Design view that you want to keep, before closing it to return to the main window.

## Forms

Forms are used to enter data easily and quickly. Click on Forms on the left hand side of the screen (in Database column) and Use Wizard to Create Form... (Writer may briefly pop up but also the Form Wizard should be on top). Click on the double arrow >> to add all fields to the right hand column called Fields in the form, and then, (unless you want to change the layout and colors) just click Finish. To build a query for the data, first close the Form in the Writer window.

Controls are aspects of forms to configure each fields particular properties. The form is edited in a Writer window and the Form controls are accessed in the same way as in Writer, View, Toolbars then Form Controls.

Basic switchboards (as in Microsoft Access) can only be created in OpenOffice.org using Forms.

## Queries

Queries are a way of querying your data according to certain conditions or criteria. Complex calculations can be performed with queries.

Note: There are two modes of queries they can run in: Native and normal. In normal mode the query is processed by the gui that rewrites it for the specific SQL engine you use. In Native mode it does not. In Native mode you can use server-specific commands, in normal mode you can't.

## Reports

Reports are like queries but offer more layout options for outputting your query results. In the Report Wizard, you are offered to group your results, then you are offered to sort the results. You can change the formatting and set page breaks too.