OpenOffice.org OpenOffice - 3.2 Grundlegende Anleitung

Calc GuideUsing Spreadsheets in OpenOffice.orgThis PDF is designed to be read onscreen, two pages at a time. If you want to print a copy, your PDF vie

What is Calc?Calc is the spreadsheet component of OpenOffice.org (OOo). You can enter data (usually numerical) in a spreadsheet and then manipulate th

Figure 76: A particularly volatile time in the world currency market.Bubble chartsA bubble chart is a variation of a scatter chart in which the data p

Net chartsA net chart is similar to a polar or radar chart. They are useful for comparing data that are not time series, but show different circumstan

green024681012Coloured light on moodFigure 79: Filled net or radar chartStock chartsA stock chart is a specialized column graph specifically for stock

A nice touch is that OpenOffice.org Chart color-codes the rising and falling shares: white for rising and black for falling in the candlestick chart,

For the background, highlight the chart wall, right-click and choose Format Wall. On the Area tab, change the drop-down box to show Gradient. Choose o

Chapter 4 Using Styles and Templates in CalcBringing uniformity to your spreadsheets

What is a template?A template is a model that you use to create other documents. For example, you can create a template for invoices that has your com

Styles help improve consistency in a document and can greatly speed up formatting. They also make major formatting changes easy. For example, you may

Figure 82: Calc cell style typesPage stylesPage styles in Calc are applied to sheets. Although one sheet may print on several pages (pieces of paper),

• Toolbar: Click the icon on the far left of the Formatting toolbar.The Styles and Formatting window can be docked at the left or right of the main

Parts of the main Calc windowWhen Calc is started, the main window looks similar to Figure 1.Figure 1: Parts of the Calc windowNoteIf any part of the

Using Fill Format modeThis method is quite useful when you need to apply the same style to many scattered cells.1) Open the Styles and Formatting wind

2) On the submenu, click Apply Style. The menus close and the Apply Style list now appears on the toolbar between the Styles and Formatting icon and t

The Style dialog has several tabs. The Organizer tab, shown in Figure86 for cell styles, is found in all components of OOo. It provides basic informat

Cell style optionsWhen editing or creating cell styles, you can set several options, which are similar to those for directly formatting cells. A more

PageUse the Page tab to edit the overall appearance of the page and its layout. The available options are shown in Figure 87.Figure 87: Page Style: Pa

Layout settings: FormatThis area specifies the page numbering style for this page style.Layout settings: Table alignmentThis option specifies the alig

Creating new (custom) stylesYou may want to add some new styles. You can do this in two ways:• Creating a new style using the Style dialog• Creating a

Figure 88: Naming a new style created from a selection.Creating a new style by dragging and droppingSelect a cell and drag it to the Styles and Format

Figure 89: Choosing to copy styles from a document, not a template.Figure 90: Copying a style from one document to another.118 OpenOffice.org 3.x Calc

Deleting stylesYou cannot remove (delete) any of Calc’s predefined styles, even if you are not using them.You can remove any user-defined (custom) sty

• File contains commands that apply to the entire document such as Open, Save, Wizards, Export as PDF, and Digital Signatures.• Edit contains commands

Figure 91: Templates and Documents dialog.Creating a templateYou can create a template from a document:1) Open a new or existing document of the type

Figure 92: Saving a new templateAny settings that can be added to or modified in a document can be saved in a template. For example, below are some of

Figure 93: Template management dialog2) In the box on the left, double-click the folder that contains the template that you want to edit. A list of al

Caution If you choose Keep Old Styles in the message box shown in Figure 94, that message will not appear again the next time you open the document af

2) In OOo, choose Tools > Extension Manager from the menu bar. In the Extension Manager dialog, click Add.3) A file browser window opens. Find and

To set a custom template as the default:1) From the main menu, choose File > Templates > Organize. The Template Management dialog opens.2) In th

Chapter 2 (Entering, Editing, and Formatting Data) for more about replacing styles using Find and Replace.1) Use File > New > Templates and Docu

To delete a template folder:1) In the Template Management dialog, select the folder that you want to delete.2) Click the Commands button and choose De

2) Click the Commands button and choose Import Template from the drop-down menu. A standard file browser window opens.3) Find and select the template

Chapter 5 Using Graphics in Calc

Figure 2: Apply Style, Font Name and Font Size listsNoteIf any of the icons (buttons) in Figure 2 is not shown, you can display it by clicking the sma

Graphics in CalcCalc is often used to present data and make forecasts and predictions. Graphics can turn an average document into a memorable one. Cal

To insert an image from a file, use either of the following methods:• Insert Picture dialog• Drag and dropInsert Picture dialog1) Click in the locatio

NoteYour Insert Picture dialog may look quite different from the one shown here, depending on your operating system and your choice in Tools > Opti

images in a subfolder named Images (under the folder containing the Writer document); the recipient of the Calc file needs to put the images in a subf

Inserting an image from the clipboardUsing the clipboard, you can copy images into a Calc document from another Calc document, from another component

By default, the Gallery is docked above the Calc workspace. To expand the Gallery, position the pointer over the line that divides it from the top of

Modifying imagesWhen you insert a new image, you may need to modify it to suit the document. This section describes the use of the Picture toolbar, re

Table 3: Picture toolbar functions (from left to right)Icon Name BehaviorFrom FileUse of this icon is described in “Inserting an image file” on page 1

Choosing a graphics modeYou can change color images to grayscale by selecting the image and then selecting Grayscale from the Graphics mode list.Table

Icon Name BehaviorPop Art Applies a Pop Art style to the image.Charcoal SketchApplies a Charcoal Sketch look to the image.Relief Displays a dialog for

Clicking the Function button inserts an equals (=) sign into the selected cell and the Input line, thereby enabling the cell to accept a formula.When

Cropping picturesWhen you are only interested in a section of the image for the purpose of your document, you may wish to crop (cut off) parts of it.

Left, Right, Top, and BottomThe image is cropped by the amount entered in these boxes. For example, a value of 3cm in the Left box cuts 3 cm from the

Resizing a bit-mapped (raster) image such as a photograph adversely affects the resolution, causing some degree of blurring. It is better to use a gra

text entered is part of the graphic, so if the graphic is moved the text will move with it.Position and SizeOpens the dialog shown below where you can

NameYou can add a custom name to be assigned to the image, to make it easier to find in the Navigator.TipWhen collaborating with a team on a large, mu

Using Calc’s drawing toolsCalc, like the other components of OOo, has a range of tools to create custom drawings. This chapter covers the default opti

Icon Name BehaviorFlowcharts Opens the Flowchart toolbar of shapes.Callouts Opens the Callouts toolbar of shapes.Stars Opens the Stars toolbar of shap

If support for Asian languages has been enabled (in Tools > Options > Language Settings > Languages), two more tools can be added to the Draw

1 Line 5 Line Color 9 Change Anchor 13 Alignment2 Arrow Style 6 Area 10 Bring to Front3 Line Style 7 Area Style / Filling 11 Send to Back4 Line Width

Arranging graphicsGraphics in a Calc document are maintained in a similar manner to a deck of cards. As you add more images to the document, each imag

Figure 5: Right end of Calc status barSheet sequence number ( )Shows the sequence number of the current sheet and the total number of sheets in the sp

To Background and To ForegroundAn image or a drawing object can be sent to the background as well. This is not the same as Bring Forward and Send Back

.Figure 106: Anchoring 2Aligning graphicsYou can align several graphics relative to each other. To do this:1) Select all of the graphics to be aligned

3) Use the tools and fields in the dialog (described on the next page) to define the hotspots and links necessary.4) Click the Apply icon to apply t

• Properties: sets the hyperlink properties and adds the Name attribute to the hyperlink.Below the toolbar, specify for the selected hotspot:• Address

Chapter 6 Printing, Exporting, and E-mailing

Quick printingClick the Print File Directly icon to send the entire document to the default printer defined for your computer.NoteYou can change the

what paper size to print on. The properties available depend on the selected printer; consult the printer’s documentation for details.• What sheets an

3) Deselect Print only selected sheets. This affects the print preview, export, and printing of your spreadsheet. Click OK.NoteThe Options button is d

Where a sheet prints to more than one page of paper, it can be printed either by column, where the first column of pages prints, and then the second c

Using print rangesPrint ranges have several uses, including printing only a specific part of the data or printing selected rows or columns on every pa

When the cursor is on an object such as a picture or chart, the information shown includes the size of the object and its location.Zoom ( )—new in OOo

Ranges > Edit. If you have already selected a print range, the Edit Print Ranges dialog looks something like Figure 111.Figure 111: Edit a print ra

For example, if the top two rows of the sheet as well as column A need to be printed on all pages, do the following:1) Choose Format > Print Ranges

3) Click the More button in the dialog and then select the Print range option. Click the Add button.4) To include more than one group of cells in the

Page breaksWhile defining a print range can be a powerful tool, it may sometimes be necessary to manually adjust Calc’s printout. To do this, you can

NoteMultiple manual row and column breaks can exist on the same page. When you want to remove them, you have to remove each one individually. This may

Figure 115: Header dialogHeader or footer appearanceTo change the appearance of the header or footer, click the More button in the dialog. This opens

Setting the contents of the header or footerThe header or footer of a Calc spreadsheet has three columns for text. Each column can have different cont

Inserts the Time field. Inserts the current page number.Exporting to PDFCalc can export documents to PDF (Portable Document Format). This industry-st

Figure 118: General page of PDF Options dialogImages section• Lossless compression: Images are stored without any loss of quality. Tends to make large

NoteEPS images with embedded previews are exported only as previews. EPS images without embedded previews are exported as empty placeholders.General s

http://extensions.services.openoffice.org/ and installing them as described in Chapter 14 (Customizing Calc).Figure 6: Starting a new spreadsheet from

Figure 119: Initial View page of PDF Options dialogFigure 120: User Interface page of PDF Options dialogWindow options section• Resize window to initi

• Center window on screen: Causes the PDF viewer window to be centered on the computer screen.• Open in full screen mode: Causes the PDF viewer to ope

Export URLs relative to file systemIf you have defined relative links in a document, this option exports those links to the PDF.Cross-document linksDe

• With both the open password and permission password set, the PDF can only be opened with the correct password, and its permissions can be restricted

Saving as Web pages (HTML)Calc can save files as HTML documents. Use File > Save As and select HTML Document, or File > Wizards > Web Page.If

Digital signing of documentsTo sign a document digitally, you need a personal key, the certificate. A personal key is stored on your computer as a com

Removing personal dataYou may wish to ensure that personal data, versions, notes, hidden information, or recorded changes are removed from files befor

Chapter 7 Using Formulas and Functions

IntroductionIn previous chapters, we have been entering one of two basic types of data into each cell: numbers and text. However, we will not always k

more manageable parts, described below, also helps to minimise errors and aid troubleshooting.Lack of documentationLack of documentation is a very com

From the keyboardPress the key combination Control+O.Each of these options displays the Open dialog, where you can locate the spreadsheet that you wan

You can even set up a formula to calculate the difference between the two totals and report an error in case a non-zero result is returned (see Figure

Simple Calculation in 1 Cell Calculation by ReferenceFigure 125: A simple calculationTable 7: Common ways to enter formulasFormula Description=A1+10 D

Formula Description=B8-SUM(B10:B14) Calculates B8 minus the sum of the cells B10 to B14.=SUM(B8;SUM(B10:B14)) Calculates the sum of cells B10 to B14 a

characteristic of the number found in the cell, for example -37. The example for Exponentiation illustrates how to enter a number that is being multip

If cell A1 contains the numerical value 4 and cell B1 the numerical value 5, the above examples would yield results of FALSE, FALSE, TRUE, FALSE, TRUE

Figure 127: Defining a name for a range of cellsFigure 128: Naming a cell or range of cells for inclusion in a formulaChapter 7 Using Formulas and Fu

Figure 129: Defining Names on a worksheetReference operatorsIn its simplest form a reference refers to a single cell, but references can also refer to

Figure 130: Reference Operator for a rangeIn the upper left corner of Figure 130 the reference A1:D12 is shown, corresponding to the cells included in

Reference concatenation operatorThe concatenation operator is written as a tilde. An expression using the concatenation operator has the following syn

You can use the intersection operator to refer a cell in a cross tabulation in an understandable way. If you have columns labeled 'Temperature&ap

Figure 7: Text Import dialog, with Comma (,) selected as the separator and double quotation mark (“) as the text delimiter.Saving spreadsheetsSpreadsh

Figure 131: Relative referencesYou can think of a relative address as a pair of offsets to the current cell. Cell B1 is 1 column to the left of Cell C

Steps 2: Setting the exchange rate of Eur at 7.5, then copying it to E3Copying formula from E2 to E3 & changing the formula to read absolute refer

HintTo change references in formulas highlight the cell and press Shift-F4 to cycle through the four different types of references. This is of limited

identify where specific data may be found. A name such as Payroll or Boise Sales is much more meaningful than Sheet1. The function named SHEET() retur

Sheet containing data for Branch 3.Sheet containing combined data for all branches.Figure 133: Combining data from several sheets into a single sheetT

Figure 134: Copying a worksheet3) Enter the data for Branch 2 and Branch 3 into the respective sheets. Each sheet stands alone and reports the results

Figure 136: Linking sheets: pasting a formula to a cell range6) Click Yes. You have now copied the formulas into each cell while maintaining the forma

Understanding functionsCalc includes over 350 functions to help you analyze and reference data. Many of these functions are for use with numbers, but

Understanding the structure of functionsAll functions have a similar structure. If you use the right tool for entering a function, you can escape lear

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 q

CopyrightThis document is Copyright © 2005–2010 by its contributors as listed in the section titled Authors. You may distribute it and/or modify it un

NoteIf the spreadsheet has been previously saved, then saving it using the Save (or Save All) command will overwrite an existing copy. However, you ca

Figure 138: Function List docked to right side of Calc windowFunction WizardThe most commonly used input method is the Function Wizard (Figure139). To

Figure 140: Functions page of Function Wizard.To select cells, either click directly upon the cell or hold down the left mouse button and drag to sele

Figure 141: Structure page of Function WizardAfter you enter a function on the Input line, press the Enter key or click the Accept button on the Funct

Strategies for creating formulas and functionsFormulas that do more than a simple calculation or summation of rows or columns of values usually take a

months time. Use comments and notes liberally to document your work.You might be aware that you cannot use negative values or zero values for a partic

Finding and fixing errorsIt is common to find situations where errors are displayed. Even with all the tools available in Calc to help you to enter fo

502 The column, row, or sheet for the referenced cell is missing.Examples of common errorsErr:503 Division by zeroThis error is the result of dividing

Figure 145: Division by zero solution#VALUE Non-existent value and #REF! Incorrect referencesThe non-existent value error is also very common. The mos

The DetectiveIn a long or complicated spreadsheet, color coding becomes less useful. In these cases, consider using the the submenu under Tools > D

a) Initiate trace by clicking Trace Precedentsb) Source area highlighted in Blue, with arrow pointing to the calculation cell(continued): Tracing pre

Some users of Microsoft Excel may be unwilling or unable to receive *.ods files. (Perhaps their employer does not allow them to install the plug-in.)

We are concerned that the number shown in Cell C3 is incorrectly stated. The cause can be seen in the highlighted cells. In this case cell C16 contain

A similar choice is also available if you want to raise a number by the power of another. Instead of entering =POWER(A1;2), you can enter =A1^2.Moreov

For more flexibility in similar operations, you could use LARGE or SMALL, both of which add a specialized argument of rank. If the rank is 1 used with

Using these functionsIn some cases, you may be able to get similar results to some of these functions by setting up a filter or a custom sort. However

symmetric arithmetic rounding: a decimal place of .4 or less gets rounded down, while one of .5 or more gets rounded up. However, at times, you may no

mode is set to 0, and both the number and the significance are negative numbers, then the result of either function is rounded up; if it is set to 1,

Figure 147: Enabling regular expressions in formulasFor example =COUNTIF(A1:A6;"r.d") with Enable regular expressions in formulas selected w

Regular expressions will not work in simple comparisons. For example: A1="r.d" will always return FALSE if A1 contains red, even if regular

Chapter 8 Using the DataPilotThis chapter is adapted from a German original written by Stefan Weigel and translated into English by Sigrid Kronenberg

IntroductionMany requests for software support are caused by complicated formulas and solutions to simple day to day procedures. For more efficient an

TipTo have Calc save documents by default in a Microsoft Excel file format, go to Tools > Options > Load/Save > General. In the section named

the connection between cells through formulas, for example addition with the plus operator or the SUM function.This small exercise might be useful for

Figure 150: Basic data in Calc You can create the sales volume overview by following these instructions: 1) Select the cell A1 (or any other single ce

6) Click OK.Figure 151: DataPilot dialog7) The result appears on a new sheet. It has the desired structure, but the columns are not yet grouped into m

Figure 153: Grouping on monthsFigure 154: DataPilot result grouped for monthsIn this result you will recognize the beginners’ example. It is very easy

We will now demonstrate some of these advantages.Starting with the result of Figure 154, drag the Date field under the Category field, as shown in Fig

To transpose the table completely, just drag the Category field above the area of the displayed values, to cell C3 (see Figure 157). The result of thi

Figure 159: Additional subdivision into regions, added later In another variation you may want to add the employees.1) Select the cell A3 (or any othe

Figure 160: Selection of subtotals for several employees.Example 2: TimekeepingThis example is often used by consultants and in several variations in

Figure 161: One month of timekeeping for one employeeFigure 162: Yearly sums for one employee228 OpenOffice.org 3.x Calc Guide

SolutionTo use the DataPilot for this task, collect all the data into one single table. This can be done either manually or by importing a file from a

Saving in other formatsCalc can save spreadsheets in a range of formats, including HTML (Web pages), through the Save As dialog. Calc can also export

Figure 164: Part of DataPilot dialogThe result appears on a new sheet.Figure 165: The evaluation, done within seconds with DataPilot230 OpenOffice.org

The result is much more powerful than is possible with the classic formula-based calculation. For example, you can summarize the daily results to a mo

Figure 167: Properties of the data fieldFigure 168: Result with percentages To get a comparison between employees, start the DataPilot again:1) Select

Figure 169: Absolute comparison with BrigitteAs a final example we switch to an accumulated view; that is, continuing sums of all values:1) Choose Dat

Example 3: Frequency distributionFor showing the frequency of incidents, Calc uses the function FREQUENCY. This formula has to be used in a so-called

To import the data into a table in Calc, open the text file users.txt containing the raw data. The Text Import dialog appears. Here you can choose you

Figure 173: Raw data in CalcSolution with a matrix formulaTo calculate the frequency you have to create 24 classes, one for each hour. In the next col

The first argument is the cell area with the times of all approximately 38,000 emails. The second argument is the cell area E2:E25 that describes the

3) In the DataPilot:• Drag Time into the Row Fields area.• Drag Date into the Data Fields area.4) Click More to show more options in the lower part of

Figure 177: properties for grouping according to hours10) Figure 178 shows the Data Field dialog for the data field Number – Date. Click More and sele

Figure 10. (left) One selected cell and (right) a group of selected cellsUsing the mouseTo move the focus using the mouse, simply move the mouse po

Figure 179: Frequency distribution with the DataPilot Figure 180: Relative occurrence240 OpenOffice.org 3.x Calc Guide

DataPilot functions in detailThis part describes the use and options of the DataPilot in detail. The database (preconditions)The first thing needed to

RuleCalc lists must have the normal form; that is, they must have a simple linear structure.When entering the data, do not add outlines, groups, or su

Figure 181: After the start of the DataPilotData sourceThere are usually three possibilities for the DataPilot to have a data source: a Calc spreadshe

Calc can import data from a huge number of foreign data formats, for example from other spreadsheets (Excel, Lotus 1, 2, 3), from databases (like dBas

Figure 182: DataPilot dialogRow Fields and Column Fields indicate in which groups the result will be sorted in the rows and columns. If there are no e

Figure 183: Expanded dialog of the DataPilotResults toResults to defines where your result will be shown. If you do not enter anything, the DataPilot

Figure 185: DataPilot result with Identify categories selectedWithout category recognition, the DataPilot shows an (empty) category (Figure 186).Figur

More settings for the fieldsThe options discussed in the previous section are valid for the DataPilot in general. Additionally, you can change setting

Type Base field Base element AnalysisNormal — — Simple use of the chosen aggregate function (for example, sum) Difference from Selection of a field fr

TipUse one of the four Alt+Arrow key combinations to resize the height or width of a cell. (For example: Alt+↓ increases the height of a cell.)Table 1

Type Base field Base element Analysis% of column — — Result as relative part of the total column (for example, the column sum)% of total — — Result as

Figure 189: Division of the regions for employees without partial sumsFigure 190: Division of the regions for employees with partial sums Select the

set up the aggregate function for the partial results independently from the overall settings of the DataPilot, select User-defined.Figure 191: Prefer

Page fieldsThe preferences dialog for page fields is the same as for row and column fields, even though it appears to be useless to do the same settin

NoteBefore you can group, you have to produce a DataPilot with ungrouped data. The time needed for creating a DataPilot depends mostly on the number o

You can define in which value range (start/end) the grouping should take place. The default setting is the whole range from smallest to biggest value.

TipFor grouping the DataPilot in calendar weeks, define the beginning as a Sunday or Monday and manually enter the grouping interval of 7 days.Groupin

Figure 200: Summary of single categories in one groupFigure 201: Grouping finishedYou can change the automatically given names for the groups and the

Sort manually by using drag and dropYou can change the order within the categories by moving the cells with the category values in the result table of

For Sort by select how to display the information, either Ascending or Descending. On the left side is a drop-down list where you can select the field

Customizing the effects of the Enter keyYou can customize the direction in which the Enter key moves the focus, by selecting Tools > Options > O

Figure 207: After the drill downTo hide the details again, double-click on the cell containing golfing or choose Data > Group and Outline > Hide

NoteAn Autofilter or default filter used on the sheet has no effect for the analysis of the DataPilot. The DataPilot always uses the complete list tha

2) You have added or deleted data sets in the original list.In this case the change means that the DataPilot has to use a different area of the spread

Figure 211: Multiple data fields in the DataPilotFigure 212: DataPilot shows sales value and number of entriesFor manipulating the existing data field

Figure 213: Layout option for presenting the sums and numbers of the sales valuesFigure 214: Another layout option for presenting the sums and numbers

Figure 216: Disabled column sumsA frequent use case for multiple data fields is the aggregation of one value according to different aggregate function

Figure 218: Multiple identical fields for sales value, that have been created as duplicates within your source.ShortcutsIf you use the DataPilot very

Function GETPIVOTDATAThe function GETPIVOTDATA can be used with formulas in Calc if you want to reuse the results from the DataPilot elsewhere in your

Figure 220: The value that you really want to use can be found now in a different location.The function GETPIVOTDATA allows you to have a reference t

If you enter only the first two arguments, then the function returns the total result of the DataPilot.You can add more arguments as pairs with field

Figure 12. Sheet tab arrowsNotice that the sheets here are not numbered in order. Sheet numbering is arbitrary; you can name a sheet as you wish.NoteT

Figure 222: Second syntax variation270 OpenOffice.org 3.x Calc Guide

Chapter 9 Data AnalysisUsing Scenarios, Goal Seek, Solver, others

IntroductionOnce you are familiar with functions and formulas, the next step is to learn how to use Calc's automated processes to quickly perform

1) Open the document containing the cell ranges to be consolidated.2) Choose Data > Consolidate to open the Consolidate dialog. Figure 223 shows th

cell of the target range. Copy results to takes only the first cell of the target range instead of the entire range as is the case for Source data ran

Creating subtotalsSUBTOTAL is a function listed under the Mathematical category when you use the Function Wizard (Insert > Function). Because of it

4) In the Calculate subtotals for box, select the columns containing the values that you want to create subtotals for. If the contents of the selected

Using “what if” scenariosScenarios are a tool to test “what-if” questions. Each scenario is named, and can be edited and formatted separately. When yo

Figure 226: Creating a scenarioSettingsThe lower portion of the Create Scenario dialog contains several options. The default settings (as shown in Fig

CautionIf you are viewing a scenario which has Copy back enabled and then create a new scenario by changing the values and selecting Tools > Scenar

3) Hold down the Shift key and click.TipYou can also select a contiguous range of cells by first clicking in the STD field on the status bar and chang

Changing scenario cell valuesTable 11 summarizes the interaction of various settings in preventing or allowing changes in scenario cell values.Table 1

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 nam

formula. Although this tool is not listed among the functions, it is really a function that acts on other functions, allowing you to calculate differe

Multiple operations in columns or rowsIn your spreadsheet, enter a formula to calculate a result from values that are stored in other cells. Then, set

6) Set the cursor in the Column input cell field and click cell B4. This means that B4, the quantity, is the variable in the formula, which is to be r

Figure 229: Sheet showing results of multiple operations calculationsCalculating with several formulas simultaneously1) In the sheet from the previous

Figure 230: Sheet and dialog showing input7) Click OK. Now the profits are listed in column E and the annual profit per item in column F.Figure 231: R

Multiple operations across rows and columnsYou can carry out multiple operations simultaneously for both columns and rows in so-called cross-tables. T

Figure 232: Sheet and dialog showing input6) Click OK. The profits for the different selling prices are now shown in the range E2:H11.Figure 233: Resu

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?

Selecting columns and rowsEntire columns and rows can be selected very quickly in OOo.Single column or rowTo select a single column, click on the colu

Figure 234: Example setup for goal seek5) Click OK. A dialog appears informing you that the Goal Seek was successful. Click Yes to enter the goal valu

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).On

4) Choose Tools > Solver. The Solver dialog opens.Figure 237: The Solver dialog5) Click in the Target cell field. In the sheet, click in the cell t

Figure 238: Result of Solver operationNoteThe default solver supports only linear equations. For nonlinear programming requirements, try the EuroOffic

Chapter 10 Linking Calc DataSharing data in and out of Calc

Why use multiple sheets?Chapter 1 introduced the concept of multiple sheets in a spreadsheet. Multiple sheets help keep information organized; once yo

Inserting new sheetsThere are several ways to insert a new sheet. The first step, in all cases, is to select the sheet that will be next to the new sh

We need 6 sheets, one for each of the 5 accounts and one as a summary sheet so we will add 3 more. We also want to name each of these sheets for the a

TipFor a shortcut to inserting a sheet from another file, choose Insert > Sheet from file from the menu bar. The Insert Sheet dialog opens with the

Figure 243: Checking ledgerReferencing other sheetsOn the Summary sheet we display the balance from each of the other sheets. If you copy the example

ContentsChapter 1 Introducing Calc...9What is Calc?...

Multiple contiguous sheetsTo select multiple contiguous sheets:1) Click on the sheet tab for the first desired sheet.2) Move the mouse pointer over th

Figure 245: Equal sign in input line2) Now, click on the sheet tab for the sheet containing the cell to be referenced. In this case, that is the Check

Creating the reference with the keyboardFrom Figure 248, you can deduce how the cell reference is constructed. The reference has two parts: the sheet

2) Switch to the other spreadsheet (the process to do this will vary depending on which operating system you are using).3) Select the sheet (Savings A

NoteThe reference for a file has three forward slashes /// and the reference for a hyperlink has two forward slashes //.Hyperlinks and URLsHyperlinks

Creating hyperlinksWhen you type text that can be used as a hyperlink (such as a website address or URL), Calc formats it automatically, creating the

The top right part of the dialog changes according to the choice made for the hyperlink category from the left panel. A full description of all the ch

• Name is applicable to HTML documents. It specifies text that will be added as a NAME attribute in the HTML code behind the hyperlink.• Events button

Linking to external dataYou can insert tables from HTML documents, and data located within named ranges from an OpenOffice.org Calc or Microsoft Excel

Figure 254: Selecting a table or range in a source document from the WebUsing the Navigator1) Open the OpenOffice.org Calc spreadsheet in which the ex

3) In the target document, press F5 to open the Navigator.4) At the bottom of the Navigator, select the source document. (In Figure 256, the source is

2) Choose either Insert > Columns or Insert > Rows.Using the mouse:1) Select the cell, column, or row where you want the new column or row inser

Figure 257: Linked areas in target spreadsheetHow to find the required data range or tableThe examples above show that the import filter gave names to

Figure 258: Using the Navigator to find a data range nameFigure 259: Using the Name box to find a data range nameChapter 10 Linking Calc Data 311

Linking to registered data sourcesYou can access a variety of databases and other data sources and link them into Calc documents.First you need to reg

Microsoft Access and other Microsoft products are not among the choices if you are using Linux. In our example, we chose dBASE.Figure 261: Registering

Viewing data sourcesOpen a document in Calc. To view the data sources available, press F4 or select View > Data Sources from the menu bar. The Data

Launching Base to work on data sourcesYou can launch OOo Base at any time from the Data Source View pane. Right-click on a database or the Tables or Q

2) Drag and drop the gray box to where you want the record to appear in the spreadsheet.3) Repeat until you have moved all of the fields you need to w

it. You can also insert a link to the object that will appear as an icon rather than an area showing the contents itself.OLE objects can be linked to

To insert an existing object:1) To create from a file, select Create from file. The dialog changes to look like Figure 265.2) To insert the object as

2) Select Create New to insert a new object of the type selected in the Object Type list, or select Create from File to create a new object from a fil

Working with sheetsLike any other Calc element, sheets can be inserted, deleted, and renamed.Inserting new sheetsThere are several ways to insert a ne

Dynamic Data Exchange (DDE)DDE is an acronym for Dynamic Data Exchange, a mechanism whereby selected data in document A can be pasted into document B

DDE link in WriterThe process for creating a DDE link from Calc to Writer is similar to creating a link within Calc.1) In Calc, select the cells to ma

Chapter 11 Sharing and Reviewing Documents

IntroductionThis chapter covers methods for editing shared documents: sharing (collaboration), recording changes, adding comments, reviewing changes,

To enable sharing, select the box at the top of the dialog, and then click OK. A message appears stating that you must save the document to activate s

Saving a shared spreadsheetWhen you save a shared spreadsheet, one of several situations may occur:• If the document was not modified and saved by ano

locked due to a merge-in in progress. You can choose to cancel the Save command for now, or retry saving later.When you successfully save a shared spr

Identifying copies of spreadsheetsWhen not using the document sharing feature, it is important to keep track of the different copies of the document.

• Buy cheaper baseballs.• Only buy 2 spare gloves.To make these changes, use the record changes feature in Calc. To start recording changes:1) Open th

Adding comments to changesCalc automatically adds to any recorded change a comment that describes what was changed (for example, Cell B4 changed from

Deleting sheetsSheets can be deleted individually or in groups.Single sheetRight-click on the tab of the sheet you want to delete and choose Delete Sh

Figure 276: Comment added to cell B3The comment also appears in the dialog when you are accepting and rejecting changes, as shown in the first line of

Figure 277: Inserting a commentNow the cell to which you added the comment has a colored dot in the upper right-hand corner, as shown in Figure 278. I

3) When done, click outside the comment to deselect it. To hide the comment again, right-click on the cell and deselect Show Comment on the pop-up men

You are the coach of a youth baseball team and you submitted a potential budget created in Calc to your team sponsor.Your sponsor has reviewed the doc

• Comment – Searches the content of the comments and only displays changes which have comments that match the search criteria.• Show accepted changes

Figure 281: Accept or Reject changes dialogOn the Filter tab of this dialog (not shown here), you can choose how to filter the list of changes: by dat

To merge documents, all of the edited documents need to have recorded changes in them.1) Open the original document.2) Select Edit > Changes > M

Comparing documentsSometimes when sharing documents, reviewers may forget to record the changes they make. This is not a problem with Calc, because Ca

Figure 284: Version management dialog2) Click the Save New Version button to save a new version.3) A dialog opens where you can enter comments about t

Now when you save the file, both versions are saved in the same file. From this point you can:• Open an old version – Select the version and click the

In addition to using the Zoom slider (new in OOo 3.1) on the Status bar (see page 16), you can open the Zoom dialog and make a selection on the left-h

Chapter 12 Calc MacrosAutomating repetitive tasks

IntroductionA 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

6) Use Edit > Paste Special to open the Paste Special dialog.Figure 289: Paste Special dialog7) Set the operation to Multiply and click OK. The cel

1 My Macros 5 Create new module in library2 OpenOffice.org Macros 6 Macros in selected library3 Open documents 7 Current document4 Create new library

Figure 292: Select the module and name the macroThe created macro is saved in Module1 of the Standard library in the Untitled 1 document. Listing 1 sh

More detail on recording macros is provided in Chapter 13 (Getting Started with Macros) in the Getting Started guide; we recommend you read it if you

Figure 294: OpenOffice.org Basic Macro Organizer4) Click New to open the New Library dialog.Figure 295: New Library dialog5) Enter a descriptive libra

6) Select AuthorsCalcMacros and click Edit to edit the library. Calc automatically creates a module named Module1 and a macro named Main.Figure 297: B

Using a macro as a functionUsing the newly created Calc document CalcTestMacros.ods, enter the formula =NumberFive() (see Figure 298). Calc finds the

Figure 300: Warning if macros are disabledIf you choose to disable macros, then when the document loads, Calc can no longer find the function.Figure 3

Figure 17 shows some frozen rows and columns. The heavier horizontal line between rows 3 and 14 and the heavier vertical line between columns C and H

a stub with the desired name is stored in the Standard library. The stub macro loads the library containing the implementation and then calls the impl

6) Save, close, and reopen the Calc document. This time, the NumberFive() function works.Listing 4. Change the name of NumberFive to NumberFive_Implem

3) IsArray checks to see if the argument is a single value, or an array. For example, =PositiveSum(7) or =PositiveSum(A4). In the first case, the numb

Writing macros that act like built-in functionsAlthough Calc finds and calls macros as normal functions, they do not really behave as built-in functio

which is more cumbersome than treating it as an array with two dimensions as is done in Listing 5.Listing 8. Add cell A2:C5 in every sheetFunction Sum

The example in Listing 9, however, demonstrates how to sort on two columns.Listing 9. Sort cells A1:C5 on Sheet 1.Sub SortRange Dim oSheet &ap

ConclusionThis chapter provides a brief overview on how to create libraries and modules, using the macro recorder, using macros as Calc functions, and

Chapter 13 Calc as a Simple DatabaseA guide for users and macro programmers

IntroductionA Calc document is a very capable database, providing sufficient functionality to satisfy the needs of many users. This chapter presents t

A B C D E F G8Ilsub 7085 97 79 82.759James45 65 97 85 7310Lisa100 97 100 93 97.511Michelle 10097 100 65 90.512Ravi87 92 86 93 89.513Sal45 65 100 92 75

Splitting the screenAnother way to change the view is by splitting the window, also known as splitting the screen. The screen can be split horizontall

a range named Scores, and then I used the following equation: =SUM(Scores). To create a named range, select the range to define. Use Insert > Names

If NOT oRanges.hasByName(sName$) Then REM I can obtain the cell address by obtaining the cell REM and then extracting the address from the cel

The third argument, a cell address, acts as the base address for cells referenced in a relative way. If the cell range is not specified as an absolute

Figure 305. Define a named range.The macro in Listing 12 creates three named ranges based on the top row of a named range.Listing 12. Create many name

Database rangeAlthough a database range can be used as a regular named range, a database range also defines a range of cells in a spreadsheet to be us

Listing 13. Create a database range and an auto filter.Sub AddNewDatabaseRange() Dim oRange 'DatabaseRange object. Dim oAddr 'Cell addres

Click on the Options tab (see Figure 308) to set the sort options. Check the Range contains column labels checkbox to prevent column headers from bein

Caution When a cell is moved during a sort operation, external references to that cell are not updated. If a cell that contains a relative reference t

To create an auto filter, first select the columns to filter. For example, using the data in Table 12, select data in columns B and C. If you do not s

Figure 310: Use the standard filterThe macro in Listing 14 creates a simple filter for the first sheet.Listing 14. Create a simple sheet filter.Sub Si

2) Immediately above this button, you will see a thick black line (Figure 19). Move the mouse pointer over this line, and it turns into a line with tw

.Field = 5 REM Compare using a numeric or a string? .IsNumeric = True REM The NumericValue property is used REM because .IsNumeric =

Listing 16 demonstrates a more advanced filter that filters two columns and uses regular expressions. I noticed some unexpected behavior while working

Advanced filtersAn advanced filter supports up to eight filter conditions, as opposed to the three supported by the simple filter. The criteria for an

Figure 311. Apply an advanced filter using a previously defined named range.Listing 17. Use an advanced filter.Sub UseAnAdvancedFilter() Dim oSheet

The filter created in Listing 17 filters the data in place. Modify the OutputPosition property to specify a different output position (see Listing 18)

= 2 does not work (because you set the Row on the copy to 2, but do not change the original).Manipulating filtered dataFiltered data copied to a new l

Function DescriptionHLOOKUP Search for a specific value across the columns in the first row of an array. Return the value from a different row in the

Function DescriptionSUMIF Calculate the sum for the cells that meet the search criteria.VAR Estimate the variance based on a sample.VARA Estimate the

Table 18. Examples of search criteria for the COUNTIF and SUMIF functions.Criteria TypeFunction Result DescriptionNumber =COUNTIF(B1:C16; 95) 3 Finds

Function index Function8 STDEVP9 SUM10 VAR11 VARPTipDo not forget that the SUBTOTAL function ignores cells that use the SUBTOTAL function. Say you hav

3) Hold down the left mouse button, and a gray line appears, running up the page. Drag the mouse to the left and this line follows.4) Release the mous

The return_column_index identifies the column to return; a value of 1 returns the first column in the range. The statement =VLOOKUP("Bob"; A

Search a row or column using LOOKUPLOOKUP is similar to HLOOKUP and VLOOKUP. The search range for the LOOKUP function is a single sorted row or column

• The search data is in descending order and the data is large enough that the data must be searched assuming that it is sorted; because it is faster

The formula =VLOOKUP(83; $Sheet2.$A$2:$B$7; 2) is an obvious solution. Dollar signs are used so that the formula can be copied and pasted to a differe

INDIRECT converts a string to a cell or rangeUse INDIRECT to convert a string representation of a cell or range address to a reference to the cell or

Table 23. Breakdown of Listing 19.Function DescriptionMATCH("Bob";A1:A16; 0) Return 4 because Bob is the fourth entry in column A.ADDRESS(4;

returns the cell B2. Table 25 lists shows the syntax for using the INDEX function.Table 25. Syntax for INDEX.Syntax DescriptionINDEX(reference) Return

Table 27. Using INDEX with a multi-range.Function Returns=INDEX(B2:G2; 1; 2) 93=INDEX(B5:G5; 1; 2) 65=INDEX((B2:G2;B5:G5); 1; 2) 93=INDEX((B2:G2;B5:G5

Function DescriptionDVAR Calculate the variance using the fields that match the search criteria. The fields are treated as a sample.DVARP Calculatesth

Chapter 14 Setting up and Customizing Calc

Figure 22: The Navigator in CalcTable 2: Function of icons in the NavigatorIcon ActionData Range. Specifies the current data range denoted by the posi

IntroductionThis chapter describes some common customizations that you may wish to do. In addition to selecting options from those provided, you can c

User Data optionsCalc uses the first and last name stored in the OpenOffice.org – User Data page to fill in the Created and Modified fields in the doc

To modify a color:1) Select the color to modify from the list or the color table.2) Enter the new values that define the color. If necessary, change t

Another way to define or alter colors is through the Colors page of the Area dialog, where you can also save and load palettes, a feature that is not

Click the Options button to open a separate dialog with specific choices (Figure 315).Remove personal information on saving. Select this option to alw

Macro securityClick the Macro Security button to open the Macro Security dialog (not shown here), where you can adjust the security level for executin

Choosing options for loading and saving documentsYou can set the Load/Save options to suit the way you work. This chapter describes only a few of the

ODF format versionOpenOffice.org by default saves documents in OpenDocument Format (ODF) version 1.2. While this allows for improved functionality, th

• If you choose Save original Basic code, the macros will not work in OpenOffice.org but are retained if you save the file into Microsoft Office forma

HTML compatibility Load/Save optionsChoices made on the Load/Save – HTML Compatibility page (Figure 321) affect HTML pages imported into OpenOffice.or

Chapter 3 Creating Charts and Graphs...72Introduction...

Moving quickly through a documentThe Navigator provides several convenient ways to move around a document and find items in it:• To jump to a specific

Choosing options for CalcIn the Options dialog, click the + sign to the left of OpenOffice.org Calc on the left-hand side. A list of subsections drops

Updating sectionChoose whether to update links when opening a document always, only on request, or never. Regardless of this setting, you can manually

Use printer metrics for text formattingSpecifies that printer metrics are applied for printing and also for formatting the display on the screen. If t

For printing, choose Format > Page > Sheet and mark the Grid option.Page breaksSpecifies whether to view the page breaks within a defined print

Text overflowIf a cell contains text that is wider than the width of the cell, the text is displayed over empty neighboring cells in the same row. If

Figure 324: Calc calculation optionsIterative references sectionIterative references are formulas that are continuously repeated until the problem is

01/01/1904Sets 1/1/1904 as day zero. Use this setting for spreadsheets that are imported in a foreign format.Other optionsSpecify a variety of options

Automatically find column and row labelsSpecifies that you can use the text in any cell as a label for the column below the text or the row to the rig

Changes optionsIn the Options dialog, choose OpenOffice.org Calc > Changes.Figure 326: Calc options for highlighting changesOn this page you can sp

Figure 327: Calc grid optionsGrid sectionSnap to grid activates the snap function.Visible grid displays grid points on the screen. These points are no

Chapter 2 Entering, Editing, and Formatting Data

In Calc, this dialog has four tabs, as shown below. Options are described in the Help; many will be familiar to users of other office suites.Figure 32

menus that are contained under another menu. For example, in addition to File, Edit, View, and so on, there is File | Send and File |Templates. The co

Creating a new menuIn the Customize dialog, click New to display the dialog shown in Figure 330.1) Type a name for your new menu in the Menu name box.

To move submenus (such as File | Send), select the main menu (File) in the Menu list and then, in the Menu Content section of the dialog, select the s

On the Add Commands dialog, select a category and then the command, and click Add. The dialog remains open, so you can select several commands. When y

4) When you have finished making all your changes, click OK to save them.Figure 332: The Toolbars page of the Customize dialogCreating a new toolbarTo

The new toolbar now appears on the list of toolbars in the Customize dialog. After creating a new toolbar, you need to add some commands to it, as des

Figure 333: Change Icon dialogCautionBe careful when reassigning your operating system’s or OOo’s predefined shortcut keys. Many key assignments are u

NoteAll existing shortcut keys for the currently selected Function are listed in the Keys selection box. If the Keys list is empty, it indicates that

2) In the Functions section at the bottom of the dialog, scroll down in the Category list to Styles. Click the + sign to expand the list of styles.3)

IntroductionYou can enter data into Calc in several ways: using the keyboard, the mouse (dragging and dropping), the Fill tool, and selection lists. C

feature with care as no confirmation dialog will be displayed; the defaults will be set without any further notice or user input.Running macros from k

Figure 335: Installing an extensionUsing extensionsThis section describes a few of the extensions to Calc. In each case, you need to install the exten

Appendix A Keyboard Shortcuts

IntroductionYou can use Calc without a pointing device such as a mouse or trackball, by using its built-in keyboard shortcuts.OOo has a general set of

Shortcut Keys Effect Ctrl+Down Arrow Moves the cursor to the bottom edge of the current data range. If the row below the cell that contains the curso

Function and arrow key shortcutsTable 30: Function key shortcutsShortcut Keys Effect F1 Displays the OOo help browser. When the help browser is alrea

Shortcut Keys Effect Shift+Ctrl+F11 Updates the templates.F12 Groups the selected data range.Ctrl+F12 Ungroups the selected data range.Table 31. Arro

DataPilot shortcutsTable 33: DataPilot shortcut keysShortcut Keys Effect Tab Changes the focus by moving forward through the areas and buttons of the

Appendix B Description of Functions

Functions available in CalcCalc provides all of the commonly used functions found in modern spreadsheet applications. Since many of Calc’s functions r

NoteWhen a plain apostrophe is used to allow a leading 0 to be displayed, it is not visible in the cell after the Enter key is pressed. If “smart quot

Mathematical functionsTable 34: Mathematical functionsSyntax DescriptionABS(number)Returns the absolute value of the given number.ACOS(number)Returns

Syntax DescriptionCOMBINA(count_1; count_2)Returns the number of combinations for a given number of objects (repetition included). Count_1 is the tota

Syntax Descriptionand if the number and significance are negative, rounding up is carried out based on that value.GCD(numbers) Returns the greatest co

Syntax DescriptionMULTINOMIAL (number(s))Returns the factorial of the sum of the arguments divided by the product of the factorials of the arguments.

Syntax DescriptionROUNDUP(number; count)Rounds the given number up. Count (optional) is the number of digits to which rounding up is to be done. If t

Syntax Descriptionthe range to which the criteria are to be applied. Criteria is the cell in which the search criterion is shown, or the search criter

• As a percentage. To enter an interest rate as a percentage, type in the interest rate followed by the % key. For example, to compute a loan with a 3

Syntax Descriptionperiod; rate; basis) that is independent of the depreciable life is used here. Cost is the acquisition cost. Date_purchased is the d

Syntax Descriptionnumber of interest payments per year (1, 2 or 4). Basis indicates how the year is to be calculated.COUPNCD(settlement; maturity; fre

Syntax Descriptionsame unit, and thus both must be calculated annually or monthly. PV is the current value. Start_period the first payment period for

Figure 23: The Special Characters dialogInserting dashesTo enter en and em dashes, you can use the Replace dashes option under Tools > AutoCorrect

Syntax Descriptionmethod. Note that the book value will never reach zero under this calculation type. Cost fixes the initial cost of an asset. Salvage

Syntax Description(settlement; maturity; coupon; yield; frequency; basis)security in years. Settlement is the date of purchase of the security. Maturi

Syntax DescriptionMaturity is the date on which the security is sold. Investment is the purchase price. Redemption is the selling price. Basis indicat

Syntax DescriptionMIRR(values; investment; reinvest_rate)Calculates the modified internal rate of return of a series of investments. Values correspond

Syntax DescriptionRedemption is the redemption value per 100 currency units of par value. Frequency is the number of interest payments per year (1, 2

Syntax Descriptiontype)with constant interest rates. Rate is the periodic interest rate. NPER is the number of periods in which annuity is paid. PV is

Syntax Descriptionpurchase of the security. Maturity is the date on which the security matures (expires). Issue is the date of issue of the security.

Syntax DescriptionSLN(cost; salvage; life) Returns the straight-line depreciation of an asset for one period. The amount of the depreciation is consta

Syntax Descriptionasset at the end of the depreciation. Life is the depreciation duration of the asset. Start is the start of the depreciation entered

Syntax Descriptionis the price (purchase price) of the security per 100 currency units of par value. Redemption is the redemption value per 100 curren

On the Replace tab, you can also delete unwanted word pairs and add new ones as required.AutoInputWhen you are typing in a cell, Calc automatically su

Syntax Descriptionsuccess on each trial. T_1 defines the lower limit for the number of trials. T_2 (optional) defines the upper limit for the number o

Syntax Descriptionbe determined with CHIDIST, in which case the chi square of the random sample must then be passed as a parameter instead of the data

Syntax Descriptioncalculates the density function, and C = 1 calculates the distribution.FDIST(number; degrees_freedom_1; degrees_freedom_2)Calculates

Syntax Descriptiondistribution.GAMMAINV(number; alpha; beta)Returns the inverse of the Gamma cumulative distribution. This function allows you to sear

Syntax Descriptionset. Data is the cell range of data. Rank_c is the ranking of the value (2nd largest, 3rd largest, etc.) written as an integer.LOGIN

Syntax DescriptionSP)the value returned for unsuccessful tests. R is the value returned for successful tests. SP is the probability of the success of

Syntax DescriptionPOISSON(number; mean; C)Returns the Poisson distribution for the given Number. Mean is the middle value of the Poisson distribution.

Syntax DescriptionSTDEV(number_1; number_2; ... number_30)Estimates the standard deviation based on a sample. Number_1; number_2; ... number_30 are nu

Syntax Descriptionthe dependent array or range of data for the second record. Mode = 1 calculates the one-tailed test, Mode = 2 the two- tailed test.

time value, it is displayed as a number. For example, 01/01/2000 12:00 PM, converts to 36526.5. This is just a matter of formatting; the actual value

Figure 24: Using the Fill toolUsing a fill seriesA more complex use of the Fill tool is to use a fill series. The default lists are for the full and a

Syntax DescriptionDAYSINYEAR(date) Calculates the number of days in the year of the given date.EASTERSUNDAY(integer) Returns the date of Easter Sunday

Syntax DescriptionNOW() Returns the computer system date and time. The value is updated when your document recalculates. NOW is a function without arg

Syntax DescriptionWEEKSINYEAR(date) Calculates the number of weeks in a year until a certain date. A week that spans two years is added to the year in

Syntax DescriptionFALSE() Set the logical value to FALSE. The FALSE() function does not require any arguments.IF(test; then_value; otherwise_value)Spe

Syntax DescriptionCURRENT() Calculates the current value of a formula at the actual position.FORMULA(reference) Displays the formula of a formula cell

Syntax DescriptionISNA(value) Returns TRUE if a cell contains the #N/A (value not available) error value. If an error occurs, the function returns a l

Database functionsThis section deals with functions used with data organized as one row of data for one record. The Database category should not be co

Syntax DescriptionDCOUNTA(database; database_field; search_criteria)Counts the number of rows (records) in a database that match the specified search_

Syntax DescriptionDVARP(database; database_field; search_criteria)Calculates the variance of all cell values in a database field in all records that m

Syntax DescriptionMDETERM(array) Returns the array determinant of an array. This function returns a value in the current cell; it is not necessary to

Figure 26: Result of fill series selection shown in Figure 25You can also use Edit > Fill > Series to create a one-time fill series for numbers

Syntax DescriptionTRANSPOSE(array) Transposes the rows and columns of an array. Array is the array in the spreadsheet that is to be transposed.TREND(d

Syntax Descriptionvalue30 is the list of values entered as a reference to a cell or as individual values.COLUMN(reference) Returns the column number o

Syntax Descriptionfunction returns a logical or numerical value. Reference contains the address of the cell in which the error occurs.HLOOKUP(search_c

Syntax Descriptionsearch will not return any usable results. The search supports regular expressions. Search_criterion is the value to be searched for

Syntax Descriptionused in an array formula, only the row number of the first range cell will be returned. Reference is a cell, an area, or the name of

Syntax DescriptionSort_order (optional) indicates whether the first column in the array is sorted in ascending order.Text functionsUse Calc’s text fun

Syntax DescriptionDECIMAL(text; radix) Converts text with characters from a number system to a positive integer in the base radix given. The radix mus

Syntax Descriptionequal to 0, the thousands separator is suppressed. If the parameter is equal to 0 or if it is missing altogether, the thousands sepa

Syntax Descriptionrepeated. Number is the number of repetitions. The result can be a maximum of 255 characters.RIGHT(text; number) Defines the last ch

Syntax DescriptionT(value) Converts a number to a blank text string. Value is the value to be converted. Also, a reference can be used as a parameter.

Figure 28: Defining a new fill seriesUsing selection listsSelection lists are available only for text, and are limited to using only text that has alr

Syntax Descriptionfunction. x is the value on which the function will be calculated. n is the order of the Bessel function.BIN2DEC(number) Returns the

Syntax DescriptionERF(lower_limit; upper_limit)Returns values of the Gaussian error integral. Lower_limit is the lower limit of integral. Upper_limit

Syntax DescriptionIMCOS(complex_number)Returns the cosine of a complex_number. The complex number is entered in the form "x + yi" or "x

Syntax Descriptionentered in the form "x + yi" or "x + yj"OCT2BIN(number; places) Returns the binary number for the octal number e

Appendix C Calc Error Codes

Introduction to Calc error codesCalc provides feedback for errors of miscalculation, incorrect use of functions, invalid cell references and values, a

Error codes displayed within cellsCell error Code Explanation of the error### N/A The column is too narrow to display the complete formatted contents

General error codesThe following table is an overview of the most common error messages for OOo Calc.NoteErrors described as Internal errors should no

Code Message Explanation of the error512 Formula overflowThe total number of internal tokens (that is, operators, variables, brackets) in the formula

Code Message Explanation of the error525 Invalid names (cell displays #NAME?)An identifier could not be evaluated; for example, no valid reference, no

Validating cell contentsWhen creating spreadsheets for other people to use, you may want to make sure they enter data that is valid or appropriate for

Index3 3D chart appearance 89area chart 98bar chart 96elements 79formatting 87illumination 89line chart 99moving elements 86pie chart 96rot

options 408recording 327reviewing 332viewing 333chart appearance of 3D 89area 79area background 86colors 87creating 73data labels 80, 92edi

dialog 244examples of use 219filter 260GETPIVOTDATA 267group date or time values 255group rows or columns 253group scalar values 254group witho

wildcards 69fixed values 178focus 23formatting chart 84data labels 92symbols 92formatting autoformatting cells and sheets 59cell background

grids in charts 78guides while moving 403H headers and footers 115Hide/Show button 135hiding and showing data 63HTML compatibility 399hyperlink

Microsoft Office file conversion 398moving chart elements 86moving from sheet to sheet 26Multiple Operations tool 281multiple sheets introduction

relative reference 189relief options 56remove personal information on saving 394removing data from a cell 51renaming sheets 33replacing cell data

strikethrough 56Style dialog 116style organizer 111styles accessing 108assign to shortcut keys 111assigning to shortcut keys 418cell 107copy

Modifying images...136Using the picture context menu...

Figure 29: Typical validity test choices.The validity test options vary with the type of data selected from the Allow list. For example, Figure 30 sho

Figure 31: Defining input help for a cellFigure 32: Defining an error message for a cell with invalid dataEditing dataEditing data is done is in much

the cell can be deleted. To delete everything in a cell (contents and format), check Delete all.Figure 33: Delete Contents dialogReplacing all the dat

Formatting dataThe data in Calc can be formatted in several ways. It can either be edited as part of a cell style so that it is automatically applied,

Figure 35: Automatic text wrapUsing manual line breaksTo insert a manual line break while typing in a cell, press Ctrl+Enter. This method does not wor

Formatting numbersSeveral different number formats can be applied to cells by using icons on the Formatting toolbar. Select the cell, then click the r

Formatting the fontTo quickly choose the font used in a cell, select the cell, then click the arrow next to the Font Name box on the Formatting toolba

Figure 40: Format Cells > Font EffectsSetting cell alignment and orientationSome of the cell alignment and orientation icons are not shown by defau

For more control and other choices, use the Alignment tab (Figure 34) of the Format Cells dialog to set the horizontal and vertical alignment and rota

NoteThe cell border properties apply to a cell, and can only be changed if you are editing that cell. For example, if cell C3 has a top border (which

DataPilot functions in detail...241Function GETPIVOTDATA...

NoteIf the selected cell range does not have column and row headers, AutoFormat is not available.3) To select which properties (number format, font, a

As soon as you select a theme, some of the properties of the custom styles are applied to the open spreadsheet and are immediately visible.3) Click OK

Figure 45: Conditional formatting dialogCell styleChoose the cell style to be applied if the specified condition matches. The style must have been def

Hiding and showing dataWhen elements are hidden, they are neither visible nor printed, but can still be selected for copying if you select the element

When you close the dialog, the outline group controls are visible between either the row or column headers and the edges of the editing window. The co

Standard filters are more complex than automatic filters. You can set as many as three conditions as a filter, combining them with the operators AND a

Figure 48: Choosing the criteria and order of sortingFigure 49: Options for sortingCase sensitiveIf two entries are otherwise identical, one with an u

Range contains column labelsDoes not include the column heading in the sort.Include formatsA cell's formatting is moved with its contents. If for

CautionUse Replace All with caution; otherwise, you may end up with some highly embarrassing mistakes. A mistake with Replace All might require a manu

3) Select Formulas or Values in the Search in drop-down list.• Formulas finds parts of the formulas.• Values finds the results of the calculations.4)

Chapter 12 Calc Macros...340Introduction...

To use wildcards and regular expressions when searching and replacing:1) On the Find & Replace dialog, click More Options to see more choices. On

• Using \n in the Replace with box will replace with the literal characters \n, not a hard line break.• The Find & Replace dialog has an option to

Chapter 3 Creating Charts and GraphsPresenting information visually

IntroductionCharts and graphs can be powerful ways to convey information to the reader. OpenOffice.org Calc offers a variety of different chart and gr

Figure 52: Selecting data for plottingNext, open the Chart Wizard dialog using one of two methods.• Choose Insert > Chart from the menu bar.• Or, c

Figure 54: Chart Wizard, Step 1—Choose a chart typeChoosing a chart typeThe Chart Wizard includes a sample chart with your data. This sample chart upd

the icons. For the moment, we will stick to the Column chart and click on Next again.Changing data ranges and axes labelsIn Step 2, Data Range, you ca

Selecting data seriesFigure 56: Amending data series and rangesOn the Data Series page, you can fine tune the data that you want to include in the cha

Adding or changing titles, legend, and gridsFigure 57: Titles, legend and grids On the Chart Elements page, you can give your chart a title and, if de

Changing the chart typeYou can change the chart type at any time. To do so:1) First select the chart by double-clicking on it. The chart should now be

Appendix B Description of Functions...428Functions available in Calc...

Figure 59: Elements of 3D chartYou can add other elements using the commands on the Insert menu. The various choices open dialogs in which you can spe

Figure 60: Data Labels dialogShow value as percentageDisplays the percentage value of the data points in each column. When selected, this option activ

Trend linesWhen you have a scattered grouping of points in a graph, you may want to show the relationship of the points. A trend line is what you need

If you insert a trend line on a chart type that uses categories, such as Line or Column, then the numbers 1, 2, 3, … are used as x-values to calculate

– Standard deviation – shows error calculated on standard deviation– Error margin – you designate the error• Cell range – calculates the error based o

ArrangementProvides two choices: Bring Forward and Send Backward, of which only one may be active for some items. Use these choices to arrange overlap

Moving chart elementsYou may wish to move or resize individual elements of a chart, independent of other chart elements. For example, you may wish to

Figure 63: Chart Area dialogChanging the chart graphic backgroundThe chart wall is the area that contains the chart graphic.1) Double-click the chart

Rotation and perspectiveTo rotate a 3D chart or view it in perspective, enter the required values on the Perspective page of the 3D View dialog. You c

AppearanceUse the Appearance page to modify some aspects of a 3D chart’s appearance.Figure 65: Modifying appearance of 3D chartSelect a scheme from th

Chapter 1 Introducing CalcUsing Spreadsheets in OpenOffice.org

seven normal, uniform light sources. The first light source projects a specular light with highlights.For the selected light source, you can then choo

Formatting the chart elementsDepending on the purpose of your document, for example a screen presentation or a printed document for a black and white

Figure 68: Formatting axis labelsFormating data labelsYou can choose properties for the labels of the data series. Carefully click on the chart elemen

the gallery, or if you have pictures you need to use instead, you can insert them using Select > From file.Figure 69: Symbol selectionResizing and

Using the Position and Size dialogTo resize or move a chart using the Position and Size dialog:1) Click on the chart to select it. Green sizing handle

TipIf you cannot move an object, check to see if its position is protected.Gallery of chart typesIts important to remember that while your data can be

• The second chart is the 3D option in the chart wizard with a simple border and the 3D chart area twisted around.• The third chart is an attempt to g

Data Labels and choose Show value as number. Then carefully select the piece you wish to highlight, move the cursor to the edge of the piece and click

As shown in Figure 73, an area chart is sometimes tricky to use. This may be one good reason to use transparency values in an area chart. After settin

Line chartsA line chart is a time series with a progression. It is ideal for raw data, and useful for charts with plentiful data that show trends or c