OpenOffice.org OpenOffice - 3.3 Grundlegende Anleitung

OpenOffice.org 3.3 Calc GuideUsing Spreadsheets in OpenOffice.org 3.3

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

Cell stylesSimilar to paragraph styles in OOo Writer, cell styles are the most basic type of style in Calc. You can apply a cell style to a cell and t

• Menu: Choose Format > Styles and Formatting.• Toolbar: Click the icon on the far left of the Formatting toolbar.The Styles and Formatting windo

3) Position the moving icon on the cell to be styled and click the mouse button.4) To quit Fill Format mode, click the Fill Format mode icon again or

To find out which page style is in use for a selected sheet, look in status bar.Figure 99: Status bar showing location of page style information below

NameThis is the style’s name. You cannot change the name of a built-in style, but you can change the name of a custom style.Linked withThis option is

Cell protectionUse the Cell Protection options to protect cells against certain types of editing.Page style optionsSeveral of the page style options a

Mirrored formats the pages as if you want to bind the printed pages like a book.The first page of a document is assumed to be an odd page.Layout setti

Creating a new style using the Style dialogTo create a new style using the Style dialog, right-click in the Styles and Formatting window and choose Ne

2) In the Template Management dialog (Figure 103), set the lists at the bottom to either Templates or Documents, as needed. The default is Templates o

4) To copy a style, hold down the Ctrl key and drag the name of the style from one list to the other.5) Repeat for each style you want to copy. If the

Figure 1: Parts of the Calc windowTitle barThe Title bar, located at the top, shows the name of the current spreadsheet. When the spreadsheet is newly

4) Select the template that you want to use. You can preview the selected template or view the template’s properties:• To preview the template, click

Templates can also contain predefined text, saving you from having to type it every time you create a new document. For example, an invoice template m

Updating a spreadsheet from a changed templateThe next time you open a spreadsheet that was created from the changed template, the following message a

Adding templates using the Extension ManagerThe Extension Manager provides an easy way to install collections of templates, graphics, macros, or other

Setting a custom template as the defaultYou can set any template to be the default, as long as it is in one of the folders displayed in the Template M

Organizing templatesOOo can only use templates that are in OOo template folders. You can create new OOo template folders and use them to organize your

To delete a template:1) In the Template Management dialog, double-click the folder that contains the template you want to delete. A list of the templa

Chapter 5 Using Graphics in Calc

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

2) Choose Insert > Picture > From File from the menu bar, or click the Insert Picture icon on the Picture toolbar).3) On the Insert Picture di

• Window contains commands for the display window such as New Window, Split, and Freeze.• Help contains links to the Help file bundled with the softwa

Linking an image fileTo create a link to the file containing the image instead of saving a copy of the image in the Calc document, use the Insert pict

Figure 111: The Edit Links dialogNoteGoing the other way, from embedded to linked, is not so easy—you must delete and reinsert each image, one at a ti

The Gallery is available in all components of OOo. It does not come with many graphics, but you can add your own pictures or find extensions containin

To close the Gallery, choose Tools > Gallery to uncheck the Gallery entry, or click on the Gallery icon again.Modifying imagesWhen you insert a new

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

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 BehaviorMosaicJoins small groups of pixels into rectangular areas of the same color. The larger the individual rectangles are, the fewer det

Figure 114: The Crop dialog On the Crop dialog, you can control the following parameters:Keep scale / Keep image sizeWhen Keep scale is selected (defa

Resizing an imageTo resize an image.1) Click the picture, if necessary, to show the green resizing handles.2) Position the pointer over one of the gre

TextOpens a dialog where you can set the options for text that goes over a picture. To write text over a graphic, click on the graphic to select it, a

Figure 3: Example of a tear-off toolbarMoving toolbarsTo move a docked toolbar, place the mouse pointer over the toolbar handle, hold down the left mo

TipWhen collaborating with a team on a large, multi-page publication, it may be beneficial to give graphics, figures, and other objects meaningful nam

Table 6: Drawing toolbar functions (from left to right)Icon Name BehaviorSelect Selects objects.Line Draws a line.RectangleDraws a rectangle. To draw

Icon Name BehaviorPolygonDraws a line composed of a series of straight line segments. Hold down the Shift key to position new points at 45 degree angl

These default properties are applied only to the current document and session. They are is not retained when you close the document, and they do not a

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

Anchoring graphicsAnchors tell a graphic where to stay in relation to other items.Anchor to pageAnchoring a graphic to the page allows it to be positi

Aligning graphicsYou can align several graphics relative to each other. To do this:1) Select all of the graphics to be aligned (Shift+click on each in

3) Use the tools and fields in the dialog (described below) to define the hotspots and links necessary.4) Click the Apply icon to apply the settings

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

Figure 5: Control+double-click to dock or undockCustomizing toolbarsYou can customize toolbars in several ways, including choosing which icons are vis

Selecting general printing optionsOn the General tab of the Print dialog (Figure 122), you can choose:• The printer (from the printers available)• Whi

When printing more than 2 pages per sheet, you can choose the order in which they are printing across and down the paper. The pictures above and below

Printing a selection of cells:1) In the document, select the section of cells to print.a) Select the first cell.b) Hold down the Control key.c) Click

Removing a print rangeIt may become necessary to remove a defined print range, for example if the whole sheet needs to be printed later. Choose Format

Printing rows or columns on every pageIf a sheet is printed on multiple pages, you can set up certain rows or columns to repeat on each printed page.F

are equivalent) after the initial selection. Make sure that each group of cells is separated with a semicolon.5) Click OK.Figure 129: Define Names dia

Inserting a page breakTo insert a page break:1) Navigate to the cell where the page break will begin.2) Select Insert > Manual Break.3) Select Row

The Sheet tab of the Page Style dialog, Format > Page > Sheet (Figure 130), provides the following options.Page OrderYou can set the order in wh

ScaleUse the scale features to control the number of pages the data will print on.• Reduce/Enlarge printout—scales the data in the printout either lar

From here you can also set the margins, the spacing, and height for the header or footer. You can check the AutoFit height box to automatically adjust

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

Figure 133: Edit contents of header or footerAreasEach area in the header or footer is independent and can have different information in it.HeaderYou

Exporting to PDFCalc can export documents to PDF (Portable Document Format). This industry-standard file format is ideal for sending the file to someo

Figure 134: General page of PDF Options dialogGeneral section• PDF/A-1a: PDF/A is an ISO standard for long-term preservation of documents, by embeddin

• Create PDF form - Submit format: Choose the format for submitting forms from within the PDF file. This setting overrides the control’s URL property

User Interface page of PDF Options dialogOn the User Interface page (Figure 136), you can choose more settings to control how a PDF viewer displays th

Links page of PDF Options dialogOn this page you can choose how links are exported to PDF.Figure 137: Links page of PDF Options dialogExport bookmarks

Figure 138: Security page of PDF Options dialog.Figure 139: Setting a password to encrypt a PDF156 OpenOffice.org 3.3 Calc Guide

Exporting to XHTMLCalc can export spreadsheets to XHTML. Choose File > Export. On the Export dialog, specify a file name for the exported document,

must be kept secret, and a public key, which you add to your documents when you sign them. You can get a certificate from a certification authority, w

Chapter 7 Using Formulas and Functions

the Input line area, click in the area, then type your changes. To edit within the current cell, just double-click the cell.Right-click (context) menu

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

Error-checking formulasAdding up columns of data or selections of cells from a worksheet often results in errors due to omitting cells, wrongly specif

formula was shown as =B3+B4. The plus sign indicates that the contents of cells B3 and B4 are to be added together and then have the result in the cel

Table 7: Common ways to enter formulasFormula Description=A1+10 Displays the contents of cell A1 plus 10.=A1*16% Displays 16% of the contents of A1.=A

Arithmetic operatorsThe addition, subtraction, multiplication and division operators return numerical results. The Negation and Percent operators iden

Figure 142: Text concatenationIn this example, specific pieces of the text were found in three different cells. To join these segments together, the f

Figure 144: Naming a cell or range of cells for inclusion in a formulaFigure 145: Defining Names on a worksheetReference operatorsIn its simplest form

An individual cell is identified by the column identifier (letter) located along the top of the columns and a row identifier (number) found along the

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

Relative and absolute referencesReferences are the way that we refer to the location of a particular cell in Calc and can be either relative (to the c

Status barThe Calc status bar provides information about the spreadsheet and convenient ways to quickly change some of its features.Figure 11: Left en

Absolute referencingYou may want to multiply a column of numbers by a fixed amount. A column of figures might show amounts in US Dollars. To convert t

Cell references can be shown in four ways.Reference ExplanationD1 Relative, from cell E3 it is the cell one column to the left and two rows above$D$1

Calculations linking sheetsAnother powerful feature of Calc is the ability to link data through several worksheets. The naming of worksheets can be he

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

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

6) Click Yes. You have now copied the formulas into each cell while maintaining the format you set up in the original worksheet. Of course, in this ex

Each function has a number of arguments used in the calculations. These arguments may or may not have their own name. Your task is to enter the argume

Nested functionsFunctions can also be used as arguments within other functions. These are called nested functions.=SUM(2;PRODUCT(5;7))To get an idea o

Figure 154: Function List docked to right side of Calc windowFunction WizardThe most commonly used input method is the Function Wizard (Figure 155). T

Figure 156: 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

Cell or object information ( )Displays information about the selected items. When a group of cells is selected, the sum of the contents is displayed b

Figure 157: 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

For ease of presentation, it is good practice to set up a spreadsheet in a manner similar to that shown in Figure 159. In this example, the individual

Break formulas into parts and combine the partsThe second strategy is similar to the first, but instead you break down longer formulas into smaller pa

NAME? (525) No valid reference exists for the argument.REF (525) The column, row, or sheet for the referenced cell is missing.VALUE (519) The value fo

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

To use the Detective, select a cell with a formula, then start the Detective. On the spreadsheet, you will see lines ending in circles to indicate pre

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

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

format so that everyone who handles a spreadsheet becomes accustomed to a standard input.Simple statisticsAnother common use for spreadsheet functions

Some of these functions overlap; for example, MIN and MAX are both covered by QUARTILE. In other cases, a custom sort or filter might give much the sa

• From the Start Center. When OOo is open but no document is open (for example, if you close all the open documents but leave the program running), th

All three of these functions require the single argument of number—the cell or number to be rounded. Used with only this argument, all three functions

to 2. Change the number of decimal places, and, if necessary, uncheck the Precision as shown box on the same page, and the spreadsheet will display as

Regular expression searches within functions are always case insensitive, irrespective of the setting of the Case sensitive checkbox on the dialog in

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 the result of using complicated formulas and solutions to solve simple day to day problems. More ef

Practical problems and questions• To display additional values for May, June, July, and so on, you need to add extra columns; that is, you have to cha

You can create the sales volume overview by following these instructions:1) Select the cell A1 (or any other single cell within the list).2) Select Da

Figure 168: DataPilot result without grouping8) To group the columns, select cell B4 or any other cell that contains a date. Then select Data > Gro

Advantages1) No manual entering or adding of any values is necessary. There is less work and fewer errors.2) The layout is very flexible: months horiz

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

CopyrightThis document is Copyright © 2005–2011 by its contributors as listed below. You may distribute it and/or modify it under the terms of either

Figure 15: Starting a new spreadsheet from a templateOpening existing spreadsheetsWhen no document is open, the Start Center (Figure 14) provides an i

Figure 175: 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 176: Selection of subtotals for several employees.Example 2: TimekeepingThis example is often used by consultants and in several variations in

Figure 177: One month of timekeeping for one employeeFigure 178: Yearly sums for one employee202 OpenOffice.org 3.3 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

Figure 180: Part of DataPilot dialogThe result appears on a new sheet (Figure 181).Figure 181: The evaluation, done within seconds with DataPilotThe r

2) Choose Data > Group and Outline > Group . On the Grouping dialog, leave Start and End as Automatically; in the Group by section, choose Inter

Figure 184: Result with percentagesTo get a comparison between employees, start the DataPilot again from the output sheet:1) Select the cell A3 (or an

Figure 186: The DataPilot now shows accumulated valuesDifferences and advantagesThese examples show an important aspect of the DataPilot. Normally you

Figure 187: Raw data for the frequency of the messagesTo import the data into a table in Calc, choose Insert > Sheet From File and select the text

Figure 188: Import settingsFigure 189 shows the imported raw data with a row added at the top containing titles for each column.Figure 189: Raw data i

Opening CSV filesComma-separated-values (CSV) files are text files that contain the cell contents of a single sheet. Each line in a CSV file represent

Solution with a matrix formulaTo calculate the frequency you have to create 24 classes, one for each hour. In the next column you enter the number of

Figure 191: Part of DataPilot dialog1) Select the cell A1 (or any other cell within the list).2) Choose Data > DataPilot > Start and click OK.3)

NoteThis may be a very time-consuming process because of the large number of items. The time does not depend that much on the number of lines but rath

Figure 194: Data Field settings for relative valuesFigure 195: Frequency distribution with the DataPilot Figure 196: Relative occurrenceChapter 8 Usi

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

2) In the Sales list, instead of only one column for the amount, you made a column for the amounts for each employee. The amounts then had to be enter

Calc spreadsheetThe simplest and most often used case is analyzing a list in a Calc spreadsheet. The list might be updated regularly or the data might

Figure 198: 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

Selection fromShows the range of cells used in the DataPilot.Results toResults to defines where your result will be shown. If you do not enter anythin

Figure 202: DataPilot result without Identify categories selectedLogically, the behavior without category recognition is better. A list with the shown

6) In OOo 3.3, two new options are available when importing CSV files that contain data separated by specific characters. These options determine whet

Figure 203: Expanded dialog for a data fieldIn the Displayed value section, you can choose other possibilities for analysis by using the aggregate fun

Type Base field Base element Analysis% difference fromSelection of a field from the data source of the DataPilot (for example, employee)Selection of a

Figure 205: Division of the regions for employees (two row fields) without partial sumsFigure 206: Division of the regions for employees with partial

Choose the option Automatically to use the aggregate function for the partial results that can also be used for the data fields (see above). To set up

Figure 209: Setting “Show Items with no data”Page fieldsThe Options dialog for page fields is the same as for row and column fields, even though it ap

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

Grouping of categories with date or time valuesFor grouping date or time values select a single cell in the column or row of the category to be groupe

Figure 214: Database with nonscalar categories (departments)Figure 215: DataPilot with nonscalar categories For grouping of non scalar categories, sel

Figure 218: Renamed groups and partial results Figure 219: Reduced to the new groups NoteA well-structured database makes manual sorting within the Da

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

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

Drilling (showing details)Drill allows you to show the related detailed data for a single, compressed value in the DataPilot result. To activate a dri

To hide the details again, double-click on the cell golfing or choose Data > Group and Outline > Hide Details.The DataPilot remembers your selec

Figure 227: Dialog for defining the filterNoteEven if they are not called a filter, page fields are a practical way to filter the results. The advanta

You could, for example, list all the sales values per day and additionally give the number of entries per day. To do this, put both the sales field an

When using multiple data fields, the DataPilot result area contains a field called Data to allow for manipulating the existing data fields. You can mo

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

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

Figure 236: Formula reference to a cell of the DataPilotIf the underlying data or the layout of the DataPilot changes, then you must take into account

First syntax variationThe target field to specify which data field of the DataPilot is used within the function. If your DataPilot has only one data f

Figure 239: Second syntax variationChapter 8 Using the DataPilot 239

Saving as a CSV fileTo save a spreadsheet as a comma separated value (CSV) file:1) Choose File > Save As.2) In the File name box, type a name for t

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

Figure 240: Defining the data to be consolidated7) Specify where you want to display the result by selecting a target range from the Copy results to d

• Under Consolidate by, select either Row labels or Column labels if the cells of the source data range are not to be consolidated corresponding to th

Figure 241: Setting up subtotalsFigure 242: Choosing options for subtotals244 OpenOffice.org 3.3 Calc Guide

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

4) Optionally add some information to the Comment box. The example shows the default comment. This information is displayed in the Navigator when you

The extent to which either of these aspects can be changed is dependent upon both the existing properties of the scenario and the current protection s

Working with scenarios using the NavigatorAfter scenarios are added to a spreadsheet, you can jump to a particular scenario by selecting it from the l

Using other “what if” toolsLike scenarios, Data > Multiple Operations is a planning tool for “what if” questions. Unlike a scenario, the Multiple O

1) Use File > Save As when saving the document. (You can also use File > Save the first time you save a new document.)2) On the Save As dialog,

NoteBefore you choose the Data > Multiple Operations option, be sure to select not only your list of alternative values but also the adjacent cells

Figure 245: Sheet and Multiple operations dialog showing inputFigure 246: Sheet showing results of multiple operations calculationsCalculating with se

4) Choose Data > Multiple Operations.5) With the cursor in the Formulas field of the Multiple operations dialog, select cells B5 and C5.6) Set the

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

4) Set the cursor in the Row input cell field and click cell B1. This means that B1, the selling price, is the horizontally entered variable (with the

Enter each of the values mentioned above into adjacent cells (for Capital, C, an arbitrary value like $100,000 or it can be left blank; for number of

Using the SolverTools > Solver amounts to a more elaborate form of Goal Seek. The difference is that the Solver deals with equations with multiple

Figure 253: Example setup for Solver4) Choose Tools > Solver. The Solver dialog opens.Figure 254: The Solver dialog5) Click in the Target cell fiel

9) Click OK. A dialog appears informing you that the Solving successfully finished. Click Keep Result to enter the result in the cell with the variabl

Chapter 10 Linking Calc DataSharing data in and out of Calc

• To write-protect the document but allow selected people to edit it, select the Open file read-only checkbox and type a password in the two boxes at

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

NoteIf you want to save the spreadsheet to Microsoft Excel format, the following characters are not allowed in sheet names: \ / ? * [ ] : and ' a

To insert just one sheet, choose whether before or after then currently selected sheet, give it a new name if desired, and click OK. The new sheet wil

Figure 260: Checking ledgerInserting sheets from a different spreadsheetOn the Insert Sheet dialog, you can also add a sheet from a different spreadsh

Balance column. We want to place the reference for the checking account balance in cell B3.Figure 261: Blank summaryTo make the cell reference in cell

Figure 265: Finished checking account referenceCreating the reference with the keyboardFrom Figure 265, you can deduce how the cell reference is const

Referencing other documents: links to sheets in other spreadsheetsJohn decides to keep his family account information in a different spreadsheet file

Creating the reference with the keyboardTyping the reference is simple once you know the format the reference takes. The reference has three parts:• P

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

For a Mail and News hyperlink, specify whether it is a mail or news link, the receiver address and for email, also the subject.For a Document hyperlin

Figure 20. (left) One selected cell and (right) a group of selected cellsUsing the Tab and Enter keys• Pressing Enter or Shift+Enter moves the focu

Editing hyperlinksTo edit an existing link, place the cursor anywhere in the link and click the Hyperlink icon on the Standard toolbar or select Edi

Figure 271: Selecting a table or range in a source document from the WebNotes1) The Available tables/ranges list remains empty until you press Enter a

Figure 272: Opening a file using the Web Page Query filter6) In the Navigator, select the Insert as Link drag mode, as shown in Figure273.7) Select th

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

Figure 276: Using the Navigator to find a data range nameLinking to registered data sourcesYou can access a variety of databases and other data source

Figure 277: Registering databasesTo register a data source that is not in *.odb format:1) Choose File > New > Database to open the Database Wiza

3) Click Next. Type the path to the database file or click Browse and use the Open dialog to navigate to and select the database file before clicking

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

7) Click the arrows on the Form Navigation toolbar to view the different records of the table. The number in the Record box changes as you move throug

To create a new object:1) Select Create new and select the object type from the available options.Note“Further objects” is only available if you are u

Key Combination MovementControl+↑To the next row above containing data in that column or to Row 1Control+↓To the next row below containing data in tha

Figure 283: Inserting an OLE object under Windows 2) Select Create New to insert a new object of the type selected in the Object Type list, or select

Linked OLE objectWhen the spreadsheet OLE object is linked, if you change it in Writer it will change in Calc; if you change it in Calc, it will chang

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,

in unshared mode, you need to save the shared document using another name or path. This creates a copy of the spreadsheet that is not shared.Opening a

– If the changes conflict, the Resolve Conflicts dialog is shown. You must decide for each conflict which version to keep, yours or the other person’s

Preparing a document for review (optional)When you send a document to someone else to review or edit, you may want to protect it first so that the edi

Looking over the figures, you see a few places where money could be saved:• Post-game snacks can be bought by parents.• New uniforms can wait; only bu

To add a comment to a change:1) Make the change to the spreadsheet.2) Select the cell with the change.3) Choose Edit > Changes > Comments. The d

Using the keyboardPressing Control+Page Down moves one sheet to the right and pressing Control+Page Up moves one sheet to the left.Using the mouseClic

TipYou can view your comments one at a time using the left and right arrows located on the right hand side of the Comment dialog. You do not need to c

Figure 296: Viewing a commentEditing commentsYou can edit and format the text of a comment, just as you do for any other text.1) Right-click on the ce

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

Accepting or rejecting changesWhen you receive a document back with changes, the beauty of the recording changes system becomes evident. Now, as the o

Merging documentsYou submitted your budget proposal to your sponsor, but you sent it to one of your assistant coaches as well. Both of them returned t

Figure 300: Merged documents with different author colorsComparing documentsSometimes when sharing documents, reviewers may forget to record the chang

To use version management in Calc:1) Choose File > Versions. The Versions dialog opens.Figure 301: Version management dialog2) Click the Save New V

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

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

ContentsCopyright... 2Note for Mac use

3) Move the mouse around the screen.4) Once the desired block of cells is highlighted, release the left mouse button.To select a range of cells withou

Figure 306: Paste Special dialogFigure 307: Cells multiplied by 39) Select the current document (see Figure 308). For this example, the current Calc d

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 309: 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 311: OpenOffice.org Basic Macro Organizer4) Click New to open the New Library dialog.Figure 312: New Library dialog5) Enter a descriptive libra

Figure 314: Basic Integrated Development Environment (IDE)7) Modify the code so that it is the same as that shown in Listing 2.The important addition

Figure 315: Use the NumberFive() Macro as a Calc functionTipFunction names are not case sensitive. In Figure 315, you can enter =NumberFive() and Calc

If you choose to disable macros, then when the document loads, Calc can no longer find the function.Figure 318: The function is goneWhen a document is

2) Change the name of NumberFive to NumberFive_Implementation (Listing 3).Listing 3. Change the name of NumberFive to NumberFive_ImplementationFunctio

The macro in Listing 5 demonstrates some important techniques:1) The argument x is optional. When an argument is not optional and the function is call

Multiple columns or rowsTo select multiple columns or rows that are contiguous:1) Click on the first column or row in the group.2) Hold down the Shift

Accessing cells directlyYou can access the OOo internal objects directly to manipulate a Calc document. For example, the macro in Listing 7 adds the v

For iRow = LBound(oRows()) To UBound(oRows()) oRow() = oRows(iRow) For iCol = LBound(oRow()) To UBound(oRow()) TheSum = TheSum +

REM Select the range to sort. REM The only purpose would be to emphasize the sorted data. 'ThisComponent.getCurrentController.select(oCellRan

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

TipAlthough the choice to associate a row to a record rather than a column is arbitrary, it is almost universal. In other words, you are not likely to

In a macro, a named range is accessed, created, and deleted using the NamedRanges property of a Calc document. Use the methods hasByName(name) and get

The method addNewByname() accepts four arguments; the name, content, position, and type. The fourth argument to the method addNewByName() is a combina

Select the range containing the headers and the data and then use Insert > Names > Create to open the Create Names dialog (see Figure 322), whic

Caution It is possible to create multiple named ranges with the same name. Creating multiple ranges with a single command increases the likelihood tha

All sheetsRight-click any one of the sheet tabs and choose Select All Sheets from the pop-up menu.Working with columns and rowsInserting columns and r

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 325) to set the sort options. Check the Range contains column labels checkbox to prevent column headers from bein

FiltersUse filters to limit the visible rows in a spreadsheet. Generic filters, common to all sorts of data manipulations, are automatically provided

Figure 326: Use an auto filter with column CRemove an auto filter by repeating the steps to create the auto filter—in other words, the menu option act

Figure 327: 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

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

Listing 16. A simple sheet filter using two columns.Sub SimpleSheetFilter_2() Dim oSheet ' Sheet to filter. Dim oRange '

2) Duplicate the column headings from the area to be filtered into the area that will contain the filter criteria.3) Enter the filter criteria underne

Dim oCritRange 'Range that contains the filter criteria. Dim oDataRange 'Range that contains the data to filter. Dim oFiltDesc 'Fi

Table 16. Advanced filter properties.Property CommentContainsHeader Boolean (true or false) that specifies if the first row (or column) contains heade

Multiple columns or rowsMultiple columns or rows can be deleted at once rather than deleting them one at a time.1) Highlight the required columns or r

Calc functions similar to database functionsAlthough every Calc function can be used for database manipulation, the functions in Table 17 are more com

Function DescriptionSTDEV Estimate the standard deviation based on a sample.STDEVA Estimate the standard deviation based on a sample. The value of tex

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

TipDo not forget that the SUBTOTAL function ignores cells that use the SUBTOTAL function. Say you have a spreadsheet that tracks investments. The reti

Use VLOOKUP when:• The data is arranged in rows and you want to return data from the same row. For example, student names with test and quiz scores to

The search value and search range are the same as for LOOKUP. The final argument, search type, controls how the search is performed. A search type of

ADDRESS returns a string with a cell’s addressUse ADDRESS to return a text representation of a cell address based on the row, column, and sheet; ADDRE

point. The second and third arguments specify the number of rows and columns to move from the reference point; in other words, where the new range sta

Table 24. Breakdown of Listing 20.Function DescriptionMATCH("Bob";A1:A16; 0)-1 Return 3 because Bob is the fourth entry in column A.OFFSET(A

TipA simple range contains one contiguous rectangular region of cells. It is possible to define a multi-range that contains multiple simple ranges. If

Figure 25: Insert Sheet dialogRenaming sheetsThe default name for the a new sheet is SheetX, where X is a number. While this works for a small spreads

Function DescriptionDVARP Calculatesthe variance using the fields that match the search criteria. The fields are treated as the entire population.The

Chapter 14 Setting up and Customizing Calc

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

3) Modify the Name as required.4) Click the Modify button. The newly defined color is now listed in the Color table.Alternatively, click the Edit butt

Figure 331: Choosing security options for opening and saving documentsSecurity options and warningsIf you record changes, save multiple versions, or i

Figure 332: Security options and warnings dialogAppearance optionsOn the OpenOffice.org – Appearance page, you can specify which items are visible and

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

features may be lost. Two notable examples are cross-references to headings and the formatting of numbered lists. If you plan to share documents with

Microsoft Office Load/Save optionsOn the Load/Save – Microsoft Office page, you can choose what to do when importing and exporting Microsoft Office OL

Figure 26. Zoom dialogOptimalResizes the display to fit the width of the selected cells. To use this option, you must first highlight a range of cells

If you want the macro to run automatically when the HTML document is opened, choose Tools > Customize > Events. See Chapter 12 (Calc Macros) for

General options for CalcIn the Options dialog, choose OpenOffice.org Calc > General.Figure 339: Selecting general options for CalcMetrics sectionCh

Expand references when new columns/rows are insertedSpecifies whether to expand references when inserting columns or rows adjacent to the reference ra

Visual aids sectionSpecifies which lines are displayed.Grid linesSpecifies whether to display grid lines between the cells when viewed onscreen. If th

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

Iterative references sectionIterative references are formulas that are continuously repeated until the problem is solved. In this section you can choo

This search: Has this result:win Finds win, but not win95, os2win, or upwindwin.* Finds win and win95, but not os2win or upwind.*win Finds win and os2

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

Grid sectionSnap to grid activates the snap function.Visible grid displays grid points on the screen. These points are not printed.Resolution sectionH

Customizing menu contentIn addition to changing the menu font, you can add and rearrange items on the menu bar, add items to menus, and make other cha

Figure 27. Frozen rows and columnsFreezing a row and a column1) Click into the cell that is immediately below the row you want frozen and immediately

5) To customize the selected menu, click on the Menu or Modify buttons. You can also add commands to a menu by clicking on the Add button. These actio

are easily identified in the Entries list by a small black triangle on the right hand side of the name.In addition to renaming, you can specify a keyb

To begin, select the menu or submenu in the Menu list near the top of the Customize page, then select the entry in the Entries list under Menu Content

To customize toolbars:1) In the Save In drop-down list, choose whether to save this changed toolbar for Calc or for a selected document.2) In the sect

To choose an icon for a command, select the command and click Modify > Change icon. On the Change Icon dialog (Figure 350), scroll through the avai

4) Now select the desired shortcut keys in the Shortcut keys list and click the Modify button at the upper right.5) Click OK to accept the change. Now

3) Choose the category of style. (This example uses a cell style, but you can also choose page styles.) The Function list now displays the names of th

Some extensions are free of charge; others are available for a fee. Check the descriptions to see what licenses and fees apply to the ones that intere

Using extensionsThis section describes a few of the extensions to Calc. In each case, you need to install the extension as described in the previous s

Appendix A Keyboard Shortcuts

Splitting the screen horizontallyTo split the screen horizontally:1) Move the mouse pointer into the vertical scroll bar, on the right-hand side of th

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+Page Down Moves one sheet to the right. In the page preview it moves to the next print page.Page Up Moves the viewable rows

Shortcut Keys Effect F4 Shows or hides the Database Sources menu.Shift+F4 Rearranges the relative or absolute references (for example, A1, $A$1, $A1,

Cell formatting shortcutsCtrl+1 open the Format Cells dialog.NoteThe shortcuts listed in Table 29 do not work under any Linux desktop tested. If you w

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

Syntax DescriptionACOT(number)Returns the inverse cotangent of the given number in radians.ACOTH(number) Returns the inverse hyperbolic cotangent of t

Syntax DescriptionCOUNTBLANK(range)Returns the number of empty cells. Range is the cell range in which the empty cells are counted.COUNTIF(range; crit

Syntax DescriptionLOG(number; base)Returns the logarithm of the given number to the specified base. Base is the base for the logarithm calculation.LOG

Figure 31: Split bar on horizontal scroll bar2) Immediately to the right of this button is a thick black line (Figure 31). Move the mouse pointer over

Syntax DescriptionROUNDDOWN(number; count)Rounds the given number. Count (optional) is the number of digits to be rounded down to. If the count parame

Syntax DescriptionSUMSQ(number_1; number_2; ... number_30)Calculates the sum of the squares of numbers (totaling up of the squares of the arguments)

Table 35: Basis calculation typesBasis Calculation0 or missing US method (NASD), 12 months of 30 days each.1 Exact number of days in months, exact num

Syntax DescriptionAMORLINC(cost; date_purchased; first_period; salvage; period; rate; basis)Calculates the amount of depreciation for a settlement per

Syntax DescriptionCOUPPCD(settlement; maturity; frequency; basis)Returns the date of the interest date prior to the settlement date, and formats the r

Syntax DescriptionDB(cost; salvage; life; period; month)Returns the depreciation of an asset for a specified period using the double-declining balance

Syntax DescriptionDURATION_ADD (settlement; maturity; coupon; yield; frequency; basis)Calculates the duration of a fixed interest security in years. S

Syntax DescriptionIPMT(rate; period; NPER; PV; FV; type)Calculates the periodic amortization for an investment with regular payments and a constant in

Syntax DescriptionNOMINAL_ADD(effective_rate; Npery)Calculates the yearly nominal rate of interest, given the effective rate and the number of compoun

Syntax DescriptionODDLYIELD(settlement; maturity; last_interest; rate; price; redemption; frequency; basis)Calculates the yield of a security if the l

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

Syntax DescriptionPRICEMAT(settlement; maturity; issue; rate; yield; basis)Calculates the price per 100 currency units of par value of a security, tha

Syntax DescriptionSYD(cost; salvage; life; period)Returns the arithmetic-declining depreciation rate. Use this function to calculate the depreciation

Syntax DescriptionXNPV(rate; values; dates) Calculates the capital value (net present value) for a list of payments which take place on different date

Statistical analysis functionsCalc includes over 70 statistical functions which enable the evaluation of data from simple arithmetic calculations, suc

Syntax DescriptionCHIDIST(number; degrees_freedom)Returns the probability value that a hypothesis will be confirmed from the indicated chi square. The

Syntax DescriptionDEVSQ(number_1; number_2; ... number_30)Returns the sum of squares of deviations based on a sample mean. Number_1; number_2; ... num

Syntax DescriptionGAMMAINV(number; alpha; beta)Returns the inverse of the Gamma cumulative distribution. This function allows you to search for variab

Syntax DescriptionLOGNORMDIST(number; mean; STDEV)Returns the cumulative lognormal distribution for the given Number, a probability value. Mean is the

Syntax DescriptionPEARSON(data_1; data_2) Returns the Pearson product moment correlation coefficient r. Data_1 is the array of the first data set. Dat

Syntax DescriptionSLOPE(data_Y; data_X) Returns the slope of the linear regression line. Data_Y is the array or matrix of Y data. Data_X is the array

Adding drawing objects to charts...87Resizing and moving the chart...

• Use the Start and End icons to jump to the first or last cell in the selected data range.TipRanges, scenarios, pictures, and other objects are much

Syntax DescriptionTTEST(data_1; data_2; mode; type)Returns the probability associated with a Student’s t-Test. Data_1 is the dependent array or range

Date and time functionsUse these functions for inserting, editing, and manipulating dates and times. OpenOffice.org handles and computes a date/time v

Syntax DescriptionEDATE(start_date; months)The result is a date a number of Months away from the given Start_date. Only months are considered; days ar

Syntax DescriptionTODAY() Returns the current computer system date. The value is updated when your document recalculates. TODAY is a function without

Logical functionsUse the logical functions to test values and produce results based on the result of the test. These functions are conditional and pro

Informational functionsThese functions provide information (or feedback) regarding the results of a test for a specific condition, or a test for the t

Syntax DescriptionISLOGICAL(value) Returns TRUE if the cell contains a logical number format. The function is used in order to check for both TRUE and

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 DescriptionDMAX(database; database_field; search_criteria)Returns the maximum content of a cell (field) in a database (all records) that matche

Syntax DescriptionGROWTH(data_Y; data_X; new_data_X; function_type)Calculates the points of an exponential trend in an array. Data_Y is the Y Data arr

Figure 33: The Description page of the document’s Properties dialogUse the Custom Properties page (Figure 34) to store information that does not fit i

Syntax DescriptionSUMX2PY2(array_X; array_Y)Returns the sum of the sum of squares of corresponding values in two arrays. Array_X is the first array wh

Syntax DescriptionCOLUMN(reference)Returns the column number of a cell reference. If the reference is a cell, the column number of the cell is returne

Syntax DescriptionINDEX(reference; row; column; range)Returns the content of a cell, specified by row and column number or an optional range name. Ref

Syntax DescriptionOFFSET(reference; rows; columns; height; width)Returns the value of a cell offset by a certain number of rows and columns from a giv

Syntax DescriptionVLOOKUP(search_criterion; array; index; sort_order)Searches vertically with reference to adjacent cells to the right. If a specific

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 DescriptionLOWER(text) Converts all uppercase letters in a text string to lowercase. Text is the text to be converted.MID(text; start; number)

Syntax DescriptionSEARCH(find_text; text; position)Returns the position of a text segment within a character string. The start of the search can be se

Syntax DescriptionBESSELJ(x; n) Calculates the Bessel function Jn(x) (cylinder function). x is the value on which the function will be calculated. n i

Syntax DescriptionERFC(lower_limit) Returns complementary values of the Gaussian error integral between x and infinity. Lower limit is the lower limit

• In the Value column, type or select what you want to appear in the document where this field is used. Choices may be limited to specific data types

Syntax DescriptionIMLOG10(complex _number) Returns the common logarithm of a complex_number. The complex number is entered in the form "x + yi&qu

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 error513 String overflow An identifier in the formula exceeds 64 KB in size, or a result of a string operation exceeds

Code Message Explanation of the error528–531— Not used.532 Division by zero Division operator / if the denominator is 0.Some more functions return thi

Index3 3D chart appearance 83area chart 92bar chart 90choosing type 72elements 74formatting 82illumination 84pie chart 90resizing 81rotat

data labels 86data labels 76editing 74elements 74formatting 79, 82graphic background 82grids 73mean value lines 78moving elements 80perspect

deleting columns and rows 32sheets 33Detective 184digital rights management (DRM) 155digital signature 158digital signature status bar indicato

Chapter 2 Entering, Editing, and Formatting Data

functions add-in 417ADDRESS 336arguments 176, 309, 376array 408basic arithmetic 187CEILING 190CONCATENATE 165COUNTIF 331database 330, 407dat

navigation 370resetting to default values 366saving to a file 366selection 370L layout settings format 106page layout 105table alignment 106l

pictures linking 120resizing 128transparency 126pie chart 90Position and Size dialog 88Position and Size of graphics 129precision 355prepare

update from changed template 112spreadsheet functions 410spreadsheets comparing 295creating new 18description 10documentation, internal 160embe

# #REF! Incorrect references 184#VALUE Non-existent value 184434 OpenOffice.org 3.3 Calc Guide

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

CautionWhen a number is formatted as text, take care that the cell containing the number is not used in a formula because Calc will ignore the value.E

Text that you type: ResultA - B (A, space, minus, space, B) A – B (A, space, en-dash, space, B)A -- B (A, space, minus, minus, space, B) A – B (A, spa

CautionChoices that are not available are grayed out, but you can still choose the opposite direction from what you intend, which could cause you to o

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

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

Removing personal data...158Chapter 7 Using Formulas and Functio

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

Figure 42: Validity choices for a cell rangeTo provide input help for a cell, use the Input Help page of the Validity dialog (Figure 43). To show an e

Editing dataEditing data is done is in much the same way as entering it. The first step is to select the cell containing the data to be edited.Removin

Using the keyboardAfter selecting the appropriate cell, press the F2 key and the cursor is placed at the end of the cell. Then use the keyboard arrow

Figure 47: Format Cells > Alignment dialogUsing manual line breaksTo insert a manual line break while typing in a cell, press Ctrl+Enter. This meth

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

Setting cell alignment and orientationSome of the cell alignment and orientation icons are not shown by default on the Formatting toolbar. To show the

Figure 55: Types of vertical stackingFormatting the cell bordersTo quickly choose a line style and color for the borders of a cell, click the small ar

Autoformatting cells and sheetsYou can use the AutoFormat feature to quickly apply a set of cell formats to a sheet or a selected cell range.1) Select

Reviewing changes...291Merging documents...

Formatting spreadsheets using themesCalc comes with a predefined set of formatting themes that you can apply to your spreadsheets.It is not possible t

Parameter fieldEnter a reference, value, or formula in the parameter field, or in both parameter fields if you have selected a condition that requires

To hide or show sheets, rows, and columns, use the options on the Format menu or the right-click (context) menu. For example, to hide a row, first sel

Figure 59: Outline group controlsFiltering which cells are visibleA filter is a list of conditions that each entry has to meet in order to be displaye

toolbar buttons. Using the dialog, you can sort the selected cells using up to three columns, in either ascending (A-Z, 1-9) or descending (Z-A, 9-1)

Case sensitiveIf two entries are otherwise identical, one with an upper case letter is placed before one with a lower case letter in the same position

Text and numbers in cells may have been entered directly or may be the result of a calculation. The search method you use depends on the type of data

Finding and replacing formulas or valuesYou can use the Find & Replace dialog to search in formulas or in the displayed values that result from a

3) Click Find, Find All, Replace, or Replace All (not recommended).TipThe online help describes many of the regular expressions and their uses.The fol

Chapter 3 Creating Charts and GraphsPresenting information visually

Logical functions... 404Informational functions...

IntroductionCharts and graphs are often powerful ways to convey information to the reader. OpenOffice.org Calc offers a variety of chart and graph for

Next, open the Chart Wizard dialog using one of two methods.• Choose Insert > Chart from the menu bar.• Or, click the Chart icon on the main toolba

Choosing a chart typeThe Chart Wizard includes a sample chart with your data. This sample chart updates to reflect the changes you make in the Chart W

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

You can leave out the legend or include it and place it to the left, right, top or bottom.To confirm your selections and complete the chart, click Fin

The default 3D chart also has the chart floor, which is not available in 2D charts.Figure 70: Elements of 2D chartFigure 71: Elements of 3D chartYou c

Data labelsData labels put information about each data point on the chart. They can be very useful for presenting detailed information, but you need t

PlacementSelects the placement of data labels relative to the objects.Figure 77 on page 83 shows examples of values as text (neither Show value as num

The trend line has the same color as the corresponding data series. To change the line properties, select the trend line and choose Format Trend Line.

• In the drop-down list:– Standard Error – calculates the error based on the numerical data you provide in the chart – Variance – shows error calcul

Note for Mac usersSome keystrokes and menu items are different on a Mac from those used in Windows and Linux. The table below gives some common substi

Data RangesExplained on page 72 (Figure 67 and Figure 68).3D ViewFormats 3D charts (see page 82).NoteChart Floor and 3D View are only available for a

NoteIf your chart graphic is 3D, round red handles appear which control the three-dimensional angle of the graphic. You cannot resize or reposition th

Changing the chart graphic backgroundThe chart wall is the area that contains the chart graphic.1) Double-click the chart so that it is enclosed by a

• An x value of 90, with y and z set to 0, provides a view from the top of the chart. With x set to –90, the view is from the bottom of the chart.• Th

IlluminationUse the Illumination page (Figure 78) to set the light sources for the 3D view. Refer to the Draw Guide for more details on setting the il

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 80: Formatting axis labelsFormating data labelsYou can choose properties for the labels of the data series. Carefully click on the chart elemen

Choosing and formatting symbolsIn line and scatter charts the symbols representing the points can be changed to a different symbol shape or color thro

To format the drawing objects, right-click and choose your changes from the context menu.Resizing and moving the chartYou can resize or move all eleme

Position is defined as a X,Y coordinate relative to a fixed point (the base point), typically located at the upper left of the document. You can tempo

Chapter 1 Introducing Calc

Bar charts are excellent for giving an immediate visual impact for data comparison in cases when time is not an important factor, for example when com

out from the rest of the pieces. The pieces will decrease in size, so you need to highlight the chart wall and drag it at a corner to increase the siz

Figure 86: Examples of donut chartsArea chartsAn area chart is a version of a line or column graph. It may be useful where you wish to emphasize volum

a picture of the sky. As you can see, the legend turns into labels on the z-axis. But overall, though it is visually more appealing, it is more diffic

compare other data. Examples of good scatter charts might include weather data, reactions under different acidity levels, conditions at altitude or an

Figure 91: Bubble chart showing three data seriesNet chartsA net chart is similar to a polar or radar chart. It is useful for comparing data that is n

Other varieties of a net chart can be made to show the data series as stacked numbers or stacked percentages. The series can also be filled with a col

Column and line chartsA column and line chart is a combination of two other chart types. It is useful for combining two distinct but related data seri

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