Openoffice Org 3 2 Calc Guide OpenOffice.org 3.x

OpenOffice - 3.2 - Calc Guide OOo_3.2_CalcG Free User Guide for OpenOffice Software, Manual

2015-07-27

: Openoffice-Org Openoffice-Org-Openoffice-3-2-Calc-Guide-777740 openoffice-org-openoffice-3-2-calc-guide-777740 openoffice-org pdf

Open the PDF directly: View PDF PDF.
Page Count: 497 [warning: Documents this large are best viewed by clicking the View PDF Link!]

Calc Guide
Using Spreadsheets in OpenOffice.org
This PDF is designed to be read onscreen, two pages at a
time. If you want to print a copy, your PDF viewer should
have an option for printing two pages on one sheet of
paper, but you may need to start with page 2 to get it to
print facing pages correctly. (Print this cover page
separately.)
Copyright
This document is Copyright © 2005–2010 by its contributors as listed
in the section titled Authors. You may distribute it and/or modify it
under the terms of either the GNU General Public License, version 3 or
later, or the Creative Commons Attribution License, version 3.0 or
later. Note that Chapter 8, Using the DataPilot, is licensed under the
Creative Commons Attribution-Share Alike License, version 3.0.
All trademarks within this guide belong to their legitimate owners.
Authors
Rick Barnes Peter Kupfer
James Andrew Krishna Aradhi
Andy Brown Stephen Buck
Bruce Byfield Martin J. Fox
T. J. Frazier Stigant Fyrwitful
Spencer E. Harpe Regina Henschel
Peter Hillier-Brook John Kane
Kirk Emma Kirsopp
Jared Kobos Sigrid Kronenberger
Shelagh Manton Alexandre Martins
Kashmira Patel Anthony Petrillo
Andrew Pitonyak Iain Roberts
Hazel Russman Gary Schnabl
Rob Scott Sowbhagya Sundaresan
Nikita Telang Barbara M Tobias
John Viestenz Jean Hollis Weber
Stefan Weigel Sharon Whiston
Claire Wood Linda Worthington
Michele Zarri Magnus Adielsson
Sandeep Samuel Medikonda
Feedback
Please direct any comments or suggestions about this document to:
authors@documentation.openoffice.org
Publication date and software version
Published 8 September 2010. Based on OpenOffice.org 3.2.
You can download
an editable version of this document from
http://oooauthors.org/english/userguide3/published/
Contents
Chapter 1
Introducing Calc.........................................................................9
What is Calc?....................................................................................10
Spreadsheets, sheets, and cells........................................................10
Parts of the main Calc window..........................................................11
Starting new spreadsheets...............................................................16
Opening existing spreadsheets.........................................................17
Opening CSV files.............................................................................18
Saving spreadsheets.........................................................................19
Navigating within spreadsheets........................................................23
Selecting items in a sheet or spreadsheet........................................27
Working with columns and rows.......................................................30
Working with sheets..........................................................................32
Viewing Calc.....................................................................................33
Using the Navigator..........................................................................38
Chapter 2
Entering, Editing, and Formatting Data...................................41
Introduction......................................................................................42
Entering data using the keyboard.....................................................42
Speeding up data entry.....................................................................45
Sharing content between sheets.......................................................48
Validating cell contents.....................................................................49
Editing data......................................................................................51
Formatting data................................................................................53
Autoformatting cells and sheets........................................................59
Formatting spreadsheets using themes............................................60
Using conditional formatting............................................................61
Hiding and showing data..................................................................63
Sorting records.................................................................................65
Finding and replacing in Calc...........................................................67
OpenOffice.org 3.x Calc Guide 3
Chapter 3
Creating Charts and Graphs.....................................................72
Introduction......................................................................................73
Creating a chart................................................................................73
Editing charts...................................................................................78
Formatting charts.............................................................................84
Formatting 3D charts........................................................................87
Formatting the chart elements.........................................................91
Resizing and moving the chart..........................................................93
Gallery of chart types........................................................................95
Chapter 4
Using Styles and Templates in Calc........................................105
What is a template?........................................................................106
What are styles?..............................................................................106
Types of styles in Calc.....................................................................107
Accessing styles..............................................................................108
Applying cell styles.........................................................................109
Applying page styles.......................................................................111
Modifying styles..............................................................................111
Creating new (custom) styles..........................................................116
Copying and moving styles.............................................................117
Deleting styles................................................................................119
Creating a spreadsheet from a template.........................................119
Creating a template........................................................................120
Editing a template...........................................................................121
Adding templates using the Extension Manager.............................123
Setting a default template..............................................................124
Associating a spreadsheet with a different template......................125
Organizing templates......................................................................126
Chapter 5
Using Graphics in Calc...........................................................129
Graphics in Calc..............................................................................130
Adding graphics (images)...............................................................130
4 OpenOffice.org 3.x Calc Guide
Modifying images............................................................................136
Using the picture context menu......................................................142
Using Calc’s drawing tools..............................................................145
Positioning graphics........................................................................148
Creating an image map...................................................................151
Chapter 6
Printing, Exporting, and E-mailing........................................154
Quick printing.................................................................................155
Controlling printing........................................................................155
Using print ranges..........................................................................159
Page breaks.....................................................................................163
Headers and footers........................................................................164
Exporting to PDF............................................................................167
Exporting to XHTML.......................................................................173
Saving as Web pages (HTML).........................................................174
E-mailing spreadsheets...................................................................174
Digital signing of documents..........................................................175
Removing personal data.................................................................176
Chapter 7
Using Formulas and Functions...............................................177
Introduction....................................................................................178
Setting up a spreadsheet................................................................178
Creating formulas...........................................................................180
Understanding functions.................................................................197
Strategies for creating formulas and functions...............................203
Finding and fixing errors................................................................205
Examples of functions.....................................................................210
Using regular expressions in functions...........................................215
Advanced functions.........................................................................217
Chapter 8
Using the DataPilot................................................................218
Introduction....................................................................................219
Examples with step by step instructions.........................................219
OpenOffice.org 3.x Calc Guide 5
DataPilot functions in detail............................................................241
Function GETPIVOTDATA...............................................................267
Chapter 9
Data Analysis..........................................................................271
Introduction....................................................................................272
Consolidating data..........................................................................272
Creating subtotals...........................................................................275
Using “what if scenarios...............................................................277
Using other “what if” tools.............................................................281
Working backwards using Goal Seek..............................................288
Using the Solver.............................................................................290
Chapter 10
Linking Calc Data....................................................................294
Why use multiple sheets?................................................................295
Setting up multiple sheets..............................................................295
Referencing other sheets................................................................299
Referencing other documents.........................................................301
Hyperlinks and URLs......................................................................303
Linking to external data..................................................................307
Linking to registered data sources.................................................312
Embedding spreadsheets................................................................316
Chapter 11
Sharing and Reviewing Documents.........................................322
Introduction....................................................................................323
Sharing documents (collaboration).................................................323
Recording changes..........................................................................326
Adding comments to changes.........................................................329
Adding other comments..................................................................330
Reviewing changes.........................................................................332
Merging documents........................................................................335
Comparing documents....................................................................337
Saving versions...............................................................................337
6 OpenOffice.org 3.x Calc Guide
Chapter 12
Calc Macros...........................................................................340
Introduction....................................................................................341
Using the macro recorder...............................................................341
Write your own functions................................................................345
Accessing cells directly...................................................................353
Sorting............................................................................................354
Conclusion......................................................................................356
Chapter 13
Calc as a Simple Database......................................................357
Introduction....................................................................................358
Associating a range with a name....................................................359
Sorting............................................................................................365
Filters.............................................................................................367
Calc functions similar to database functions..................................375
Database-specific functions............................................................387
Conclusion......................................................................................388
Chapter 14
Setting up and Customizing Calc...........................................389
Introduction....................................................................................390
Choosing options that affect all of OOo..........................................390
Choosing options for loading and saving documents......................396
Choosing options for Calc...............................................................400
Controlling Calc’s AutoCorrect functions.......................................409
Customizing the user interface.......................................................410
Adding functionality with extensions..............................................420
Appendix A
Keyboard Shortcuts................................................................422
Introduction....................................................................................423
Navigation and selection shortcuts.................................................423
Function and arrow key shortcuts..................................................425
Cell formatting shortcuts................................................................426
DataPilot shortcuts.........................................................................427
OpenOffice.org 3.x Calc Guide 7
Appendix B
Description of Functions........................................................428
Functions available in Calc.............................................................429
Mathematical functions..................................................................430
Financial analysis functions............................................................435
Statistical analysis functions...........................................................449
Date and time functions..................................................................458
Logical functions.............................................................................462
Informational functions...................................................................463
Database functions.........................................................................466
Array functions...............................................................................468
Spreadsheet functions....................................................................470
Text functions..................................................................................475
Add-in functions..............................................................................479
Appendix C
Calc Error Codes.....................................................................484
Introduction to Calc error codes.....................................................485
Error codes displayed within cells..................................................486
General error codes........................................................................487
Index.........................................................................................490
8 OpenOffice.org 3.x Calc Guide
Chapter 1
Introducing Calc
Using Spreadsheets in OpenOffice.org
What is Calc?
Calc is the spreadsheet component of OpenOffice.org (OOo). You can
enter data (usually numerical) in a spreadsheet and then manipulate
this data to produce certain results.
Alternatively, you can enter data and then use Calc in a ‘What if...’
manner by changing some of the data and observing the results
without having to retype the entire spreadsheet or sheet.
Other features provided by Calc include:
Functions, which can be used to create formulas to perform
complex calculations on data
Database functions, to arrange, store, and filter data
Dynamic charts; two new types of charts—Bubble Charts and
Filled Net Charts—have been introduced in OOo 3.2
Macros, for recording and executing repetitive tasks; scripting
languages supported include OpenOffice.org Basic, Python,
BeanShell, and JavaScript
Ability to open, edit, and save Microsoft Excel spreadsheets
Import and export of spreadsheets in multiple formats, including
HTML, CSV, PDF, and PostScript
Note
If you want to use macros written in Microsoft Excel using the
VBA macro code in OOo, you must first edit the code in the
OOo Basic IDE editor.
Spreadsheets, sheets, and cells
Calc works with elements called spreadsheets. Spreadsheets consist of
a number of individual sheets, each sheet containing cells arranged in
rows and columns. A particular cell is identified by its row number and
column letter.
Cells hold the individual elements—text, numbers, formulas, and so on
—that make up the data to display and manipulate.
Each spreadsheet can have many sheets, and each sheet can have
many individual cells. In Calc 3.x, each sheet can have a maximum of
65,536 rows and a maximum of 1024 columns, for a total of over 67
million cells.
10 OpenOffice.org 3.x Calc Guide
Parts of the main Calc window
When Calc is started, the main window looks similar to Figure 1.
Figure 1: Parts of the Calc window
Note
If any part of the Calc window in Figure 1 is not shown, you can
display it using the View menu. For example, View > Status
Bar will toggle (show or hide) the Status Bar. It is not always
necessary to display all the parts, as shown; show or hide any of
them, as desired.
Title bar
The Title bar, located at the top, shows the name of the current
spreadsheet. When the spreadsheet is newly created, its name is
Untitled X, where X is a number. When you save a spreadsheet for the
first time, you are prompted to enter a name of your choice.
Menu bar
Under the Title bar is the Menu bar. When you choose one of the
menus, a submenu appears with other options. You can modify the
Menu bar, as discussed in Chapter 14 (Setting up and Customizing
Calc).
Chapter 1 Introducing Calc 11
File contains commands that apply to the entire document such
as Open, Save, Wizards, Export as PDF, and Digital
Signatures.
Edit contains commands for editing the document such as Undo,
Changes, Compare Document, and Find and Replace.
View contains commands for modifying how the Calc user
interface looks such as Toolbars, Full Screen, and Zoom.
Insert contains commands for inserting elements such as cells,
rows, columns, sheets, and pictures into a spreadsheet.
Format contains commands for modifying the layout of a
spreadsheet such as Styles and Formatting, Paragraph, and
Merge Cells.
Tools contains functions such as Spelling, Share Document,
Cell Contents, Gallery, and Macros.
Data contains commands for manipulating data in your
spreadsheet such as Define Range, Sort, Filter, and DataPilot.
Window contains commands for the display window such as New
Window, Split, and Freeze.
Help contains links to the Help file bundled with the software,
What's This?, Support, Registration, and Check for Updates.
Toolbars
Three toolbars are located under the Menu bar by default: the
Standard toolbar, the Formatting toolbar, and the Formula Bar.
The icons (buttons) on these toolbars provide a wide range of common
commands and functions. You can also modify these toolbars, as
discussed in Chapter 14 (Setting up and Customizing Calc).
Placing the mouse pointer over any of the icons displays a small box,
called a tooltip. It gives a brief explanation of the icon’s function. For a
more detailed explanation, choose Help > What’s This? and hover
the mouse pointer over the icon. To turn this feature off again, click
once or press the Esc key twice. Tips and extended tips can be turned
on or off from Tools > Options > OpenOffice.org > General.
In the Formatting toolbar, the three boxes on the left are the Apply
Style, Font Name, and Font Size lists (see Figure 2). They show the
current settings for the selected cell or area. (The Apply Style list may
not be visible by default.) Click the down-arrow to the right of each box
to open the list.
12 OpenOffice.org 3.x Calc Guide
Figure 2: Apply Style, Font Name and Font Size lists
Note
If any of the icons (buttons) in Figure 2 is not shown, you can
display it by clicking the small triangle at the right end of the
Formatting toolbar, selecting Visible Buttons in the drop-
down menu, and selecting the desired icon (for example, Apply
Style) in the drop-down list. It is not always necessary to display
all the toolbar buttons, as shown; show or hide any of them, as
desired.
Formula Bar
On the left hand side of the Formula Bar is a small text box, called the
Name Box, with a letter and number combination in it, such as D7. This
combination, called the cell reference, is the column letter and row
number of the selected cell.
Figure 3: Formula Bar
To the right of the Name Box are the the Function Wizard, Sum, and
Function buttons.
Clicking the Function Wizard button opens a dialog from which you
can search through a list of available functions. This can be very useful
because it also shows how the functions are formatted.
In a spreadsheet the term function covers much more than just
mathematical functions. See Chapter 7 for more details.
Clicking the Sum button inserts a formula into the current cell that
totals the numbers in the cells above the current cell. If there are no
numbers above the current cell, then the cells to the left are placed in
the Sum formula.
Chapter 1 Introducing Calc 13
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 you enter new data into a cell, the Sum and Equals buttons
change to Cancel and Accept buttons .
The contents of the current cell (data, formula, or function) are
displayed in the Input line, which is the remainder of the Formula Bar.
You can either edit the cell contents of the current cell there, or you
can do that in the current cell. To edit inside the Input line area, click
in the area, then type your changes. To edit within the current cell, just
double-click the cell.
Individual cells
The main section of the screen displays the cells in the form of a grid,
with each cell being at the intersection of a column and a row.
At the top of the columns and at the left end of the rows are a series of
gray boxes containing letters and numbers. These are the column and
row headers. The columns start at A and go on to the right, and the
rows start at 1 and go down.
These column and row headers form the cell references that appear in
the Name Box on the Formula Bar (see Figure 3). You can turn these
headers off by selecting View > Column & Row Headers.
Sheet tabs
At the bottom of the grid of cells are the sheet tabs. These tabs enable
access to each individual sheet, with the visible (active) sheet having a
white tab. Clicking on another sheet tab displays that sheet, and its tab
turns white. You can also select multiple sheet tabs at once by holding
down the Control key while you click the names.
Status bar
The Calc status bar provides information about the spreadsheet and
convenient ways to quickly change some of its features.
Figure 4: Left end of Calc status bar
14 OpenOffice.org 3.x Calc Guide
Figure 5: Right end of Calc status bar
Sheet sequence number ( )
Shows the sequence number of the current sheet and the total
number of sheets in the spreadsheet. The sequence number may not
correspond with the name on the sheet tab.
Page style ( )
Shows the page style of the current sheet. To edit the page style,
double-click on this field. The Page Style dialog opens.
Insert mode ( )
Click to toggle between INSRT (Insert) and OVER (Overwrite)
modes when typing. This field is blank when the spreadsheet is not
in a typing mode (for example, when selecting cells).
Selection mode ( )
Click to toggle between STD (Standard), EXT (Extend), and ADD
(Add) selection. EXT is an alternative to Shift+click when selecting
cells. See page 27 for more information.
Unsaved changes ( )
An asterisk (*) appears here if changes to the spreadsheet have not
been saved.
Digital signature ( )
If the document has not been digitally signed, double-clicking in this
area opens the Digital Signatures dialog, where you can sign the
document. See Chapter 6 (Printing, Exporting, and E-mailing) for
more about digital signatures.
If the document has been digitally signed, an icon shows in this
area. You can double-click the icon to view the certificate. A
document can be digitally signed only after it has been saved.
Cell or object information ( )
Displays information about the selected items. When a group of cells
is selected, the sum of the contents is displayed by default; you can
right-click on this field and select other functions, such as the
average value, maximum value, minimum value, or count (number of
items selected).
Chapter 1 Introducing Calc 15
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 3.1
To change the view magnification, drag the Zoom slider or click on
the + and – signs. You can also right-click on the zoom level
percentage to select a magnification value or double-click to open
the Zoom & View Layout dialog.
Starting new spreadsheets
You can create a new, blank spreadsheet from the Start Center
(Welcome to OpenOffice.org), from within Calc, or from any other
component of OOo such as from Writer or Draw.
From the Start Center
Click the Spreadsheet icon.
From the Menu bar
Choose File > New > Spreadsheet.
From a toolbar
If a document is open in any component of OOo (for example,
Writer), you can use the New Document icon on the Standard
toolbar. If you already have a spreadsheet open, clicking this button
opens a new spreadsheet in a new window. From any other
component of OOo (for example, Writer), click the down-arrow and
choose spreadsheet.
From the keyboard
If you already have a spreadsheet open, you can press Control+N to
open a new spreadsheet in a new window.
From a template
Calc documents can also be created from templates. Follow the
above procedures, but instead of choosing Spreadsheet, choose the
Templates icon from the Start Center or File > New >Templates
and Documents from the Menu bar or toolbar. On the Templates
and Documents window, navigate to the appropriate folder and
double-click on the required template. A new spreadsheet, based on
the selected template, opens.
A new OpenOffice.org installation does not contain many templates,
but you can add more by downloading them from
16 OpenOffice.org 3.x Calc Guide
http://extensions.services.openoffice.org/ and installing them as
described in Chapter 14 (Customizing Calc).
Figure 6: Starting a new spreadsheet from a template
Opening existing spreadsheets
You can open an existing spreadsheet from the Start Center or from
any component of OOo. Calc can open spreadsheets in a wide range of
file formats, including Microsoft Excel (*.xls and *.xlsx).
From the Start Center
Click the Open a document icon.
From the Menu bar
Choose File > Open.
From a toolbar
Click the Open icon on the Standard toolbar.
Chapter 1 Introducing Calc 17
From the keyboard
Press the key combination Control+O.
Each of these options displays the Open dialog, where you can locate
the spreadsheet that you want to open.
Tip
You can also use the Recent Documents list to open a
spreadsheet. This list is located on the File menu, directly
below Open. The list displays the last 10 files that were
opened in any of the OOo components.
Opening CSV files
Comma-separated-values (CSV) files are text files that contain the cell
contents of a single sheet. Each line in a CSV file represents a row in a
spreadsheet. Commas, semicolons, or other characters are used to
separate the cells. Text is put in quotation marks; numbers are written
without quotation marks.
To open a CSV file in Calc:
1) Choose File > Open.
2) Locate the CSV file that you want to open.
3) If the file has a *.csv extension, select the file and click Open.
4) If the file has another extension (for example, *.txt), select the
file, select Text CSV (*csv;*txt;*xls) in the File type box (scroll
down into the spreadsheet section to find it) and then click Open.
5) On the Text Import dialog (Figure 7), select the Separator options
to divide the text in the file into columns.
You can preview the layout of the imported data at the bottom of
the dialog. Right-click a column in the preview to set the format
or to hide the column.
If the CSV file uses a text delimiter character that is not in the
Text delimiter list, click in the box, and type the character.
6) Click OK to open the file.
Caution If you do not select Text CSV (*csv;*txt;*xls) as the file type
when opening the file, the document opens in Writer, not Calc.
18 OpenOffice.org 3.x Calc Guide
Figure 7: Text Import dialog, with Comma (,) selected as the separator
and double quotation mark (“) as the text delimiter.
Saving spreadsheets
Spreadsheets can be saved in three ways.
From the Menu bar
Choose File > Save (or Save All or Save As).
From the toolbar
Click the Save button on the Standard toolbar. If the file has
been saved and no subsequent changes have been made, this button
is grayed-out and not clickable.
From the keyboard
Press the key combination Control+S.
If the spreadsheet has not been saved previously, then each of these
actions will open the Save As dialog. There you can specify the
spreadsheet name and the location in which to save it.
Chapter 1 Introducing Calc 19
Note
If the spreadsheet has been previously saved, then saving it
using the Save (or Save All) command will overwrite an
existing copy. However, you can save the spreadsheet in a
different location or with a different name by selecting File >
Save As.
Password protection
To protect an entire document from being viewable without a
password, use the option on the Save As dialog to enter a password.
This option is only available for files saved in OpenDocument formats
or the older OpenOffice.org 1.x formats.
On the Save As dialog, select the Save with password option, and
then click Save. You will be prompted to type the same password in
two fields. If the passwords match, the OK button becomes active.
Click OK to save the document as password-protected. If the
passwords do not match, you will be prompted to type the password
again.
OOo uses a very strong encryption mechanism that makes it almost
impossible to recover the contents of a document in case you lose the
password.
Saving a document automatically
You can choose to have Calc save your spreadsheet automatically at
regular intervals. Automatic saving, like manual saving, overwrites the
last saved state of the file. To set up automatic file saving:
1) Choose Tools > Options > Load/Save > General.
2) Click on Save AutoRecovery information every. This enables
the box to set the interval. The default value is 15 minutes. Enter
the value you want by typing it or by pressing the up or down
arrow keys.
Saving as a Microsoft Excel document
If you need to exchange files with users of Microsoft Excel, they may
not know how to open and save *.ods files. Only Microsoft Excel 2007
with Service Pack 2 (SP2) can do this. Users of Microsoft Excel 2007,
2003, XP, and 2000 can also download and install a free
OpenDocument Format (ODF) plugin from Sun Microsystems.
20 OpenOffice.org 3.x Calc Guide
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.) In this case, you can save a document as a Excel file (*.xls or
*.xlsx).
1) Important—First save your spreadsheet in the file format used
by OpenOffice.org, *.ods. If you do not, any changes you may have
made since the last time you saved it will only appear in the
Microsoft Excel version of the document.
2) Then choose File > Save As.
3) On the Save As dialog (Figure 8), in the File type (or Save as
type) drop-down menu, select the type of Excel format you need.
Click Save.
Figure 8. Saving a spreadsheet in Microsoft Excel format
Caution
From this point on, all changes you make to the spreadsheet
will occur only in the Microsoft Excel document. You have
actually changed the name of your document. If you want to go
back to working with the *.ods version of your spreadsheet, you
must open it again.
Chapter 1 Introducing Calc 21
Tip
To have Calc save documents by default in a Microsoft Excel
file format, go to Tools > Options > Load/Save > General.
In the section named Default file format and ODF settings,
under Document type, select Spreadsheet, then under Always
save as, select your preferred file format.
Saving as a CSV file
To save a spreadsheet as a comma separate value (CSV) file:
1) Choose File > Save As.
2) In the File name box, type a name for the file.
3) In the File type list, select Text CSV (*.csv;*.txt;*.xls) and click
Save.
You may see the message box shown below. Click Keep Current
Format.
4) In the Export of text files dialog (Figure 9), select the options you
want and then click OK.
Figure 9: Choosing options when exporting to Text CSV
22 OpenOffice.org 3.x Calc Guide
Saving in other formats
Calc can save spreadsheets in a range of formats, including HTML
(Web pages), through the Save As dialog. Calc can also export
spreadsheets to the PDF and XHTML file formats. See Chapter 6
(Printing, Exporting, and E-mailing) for more information.
Navigating within spreadsheets
Calc provides many ways to navigate within a spreadsheet from cell to
cell and sheet to sheet. You can generally use whatever method you
prefer.
Going to a particular cell
Using the mouse
Place the mouse pointer over the cell and click.
Using a cell reference
Click on the little inverted black triangle just to the right of the
Name Box (Figure 3). The existing cell reference will be highlighted.
Type the cell reference of the cell you want to go to and press Enter.
Cell references are case insensitive: a3 or A3, for example, are the
same. Or just click into the Name Box, backspace over the existing
cell reference, and type in the cell reference you want and press
Enter.
Using the Navigator
Click on the Navigator button in the Standard toolbar (or press F5)
to display the Navigator. Type the cell reference into the top two
fields, labeled Column and Row, and press Enter. In Figure 22 on
page 39, the Navigator would select cell A7. For more about using
the Navigator, see page 38.
Moving from cell to cell
In the spreadsheet, one cell normally has a darker black border. This
black border indicates where the focus is (see Figure 10). The focus
indicates which cell is enabled to receive input. If a group of cells is
selected, they have a highlight color (usually gray), with the focus cell
having a dark border.
Chapter 1 Introducing Calc 23
Figure 10. (left) One selected cell and (right) a group of
selected cells
Using the mouse
To move the focus using the mouse, simply move the mouse pointer
to the cell where you want the focus to be and click the left mouse
button. This action changes the focus to the new cell. This method is
most useful when the two cells are a large distance apart.
Using the Tab and Enter keys
Pressing Enter or Shift+Enter moves the focus down or up,
respectively.
Pressing Tab or Shift+Tab moves the focus to the right or to the
left, respectively.
Using the arrow keys
Pressing the arrow keys on the keyboard moves the focus in the
direction of the arrows.
Using Home, End, Page Up and Page Down
Home moves the focus to the start of a row.
End moves the focus to the column furthest to the right that
contains data.
Page Down moves the display down one complete screen and
Page Up moves the display up one complete screen.
Combinations of Control (often represented on keyboards as Ctrl)
and Alt with Home, End, Page Down (PgDn), Page Up (PgUp), and
the arrow keys move the focus of the current cell in other ways.
Table 1 describes the keyboard shortcuts for moving about a
spreadsheet.
24 OpenOffice.org 3.x Calc Guide
Tip
Use 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. Moving from cell to cell using the keyboard
Key Combination Movement
Right one cell
Left one cell
Up one cell
Down one cell
Control+To the next column to the right containing data in
that row or to Column AMJ
Control+To the next column to the left containing data in that
row or to Column A
Control+To the next row above containing data in that
column or to Row 1
Control+To the next row below containing data in that
column or to Row 65536
Control+Home To Cell A1
Control+End To lower right-hand corner of the rectangular area
containing data
Alt+Page Downn One screen to the right (if possible)
Alt+Page Up One screen to the left (if possible)
Control+Page Down One sheet to the right (in sheet tabs)
Control+Page Up One sheet to the left (in sheet tabs)
Tab To the next cell on the right
Shift+Tab To the next cell on the left
Enter Down one cell (unless changed by user)
Shift+Enter Up one cell (unless changed by user)
Chapter 1 Introducing Calc 25
Customizing the effects of the Enter key
You can customize the direction in which the Enter key moves the
focus, by selecting Tools > Options > OpenOffice.org Calc >
General.
The four choices for the direction of the Enter key are shown on the
right hand side of Figure 11. It can move the focus down, right, up, or
left. Depending on the file being used or on the type of data being
entered, setting a different direction can be useful.
Figure 11: Customizing the effect of the Enter key
The Enter key can also be used to switch into and out of the editing
mode. Use the first two options under Input settings in Figure 11 to
change the Enter key settings.
Moving from sheet to sheet
Each sheet in a spreadsheet is independent of the others, though they
can be linked with references from one sheet to another. There are
three ways to navigate between different sheets in a spreadsheet.
Using the keyboard
Pressing Control+Page Down moves one sheet to the right and
pressing Control+Page Up moves one sheet to the left.
Using the mouse
Clicking on one of the sheet tabs at the bottom of the spreadsheet
selects that sheet.
If you have a lot of sheets, then some of the sheet tabs may be
hidden behind the horizontal scroll bar at the bottom of the screen.
If this is the case, then the four buttons at the left of the sheet tabs
can move the tabs into view. Figure 12 shows how to do this.
26 OpenOffice.org 3.x Calc Guide
Figure 12. Sheet tab arrows
Notice that the sheets here are not numbered in order. Sheet
numbering is arbitrary; you can name a sheet as you wish.
Note
The sheet tab arrows that appear in Figure 12 only appear if
you have some sheet tabs that can not be seen. Otherwise,
they appear faded as in Figure 1.
Selecting items in a sheet or spreadsheet
Selecting cells
Cells can be selected in a variety of combinations and quantities.
Single cell
Left-click in the cell. The result will look like the left side of Figure 10.
You can verify your selection by looking in the Name Box.
Range of contiguous cells
A range of cells can be selected using the keyboard or the mouse.
To select a range of cells by dragging the mouse:
1) Click in a cell.
2) Press and hold down the left mouse button.
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 without dragging the mouse:
1) Click in the cell which is to be one corner of the range of cells.
2) Move the mouse to the opposite corner of the range of cells.
Chapter 1 Introducing Calc 27
Move to the first sheet
Move left one sheet
Move right one sheet
Move to the last sheet
Sheet tabs
3) Hold down the Shift key and click.
Tip
You can also select a contiguous range of cells by first clicking
in the STD field on the status bar and changing it to EXT,
before clicking in the opposite corner of the range of cells in
step 3 above. If you use this method, be sure to change EXT
back to STD or you may find yourself extending the selection
unintentionally.
To select a range of cells without using the mouse:
1) Select the cell that will be one of the corners in the range of cells.
2) While holding down the Shift key, use the cursor arrows to select
the rest of the range.
The result of any of these methods looks like the right side of Figure
10.
Tip
You can also directly select a range of cells using the Name
Box. Click into the Name Box as described in “Using a cell
reference” on page 23. To select a range of cells, enter the cell
reference for the upper left-hand cell, followed by a colon (:),
and then the lower right-hand cell reference. For example, to
select the range that would go from A3 to C6, you would enter
A3:C6.
Range of noncontiguous cells
1) Select the cell or range of cells using one of the methods above.
2) Move the mouse pointer to the start of the next range or single
cell.
3) Hold down the Control key and click or click-and-drag to select
another range of cells to add to the first range.
4) Repeat as necessary.
Tip
You can also select a noncontiguous range of cells by first
clicking twice in the STD field on the status bar to change it to
ADD, before clicking on a cell that you want to add to the
range of cells in step 3 above. This method works best when
adding single cells to a range. If you use this method, be sure
to change ADD back to STD or you may find yourself adding
more selections unintentionally.
28 OpenOffice.org 3.x Calc Guide
Selecting columns and rows
Entire columns and rows can be selected very quickly in OOo.
Single column or row
To select a single column, click on the column identifier letter (see
Figure 1).
To select a single row, click on the row identifier number.
Multiple columns or rows
To select multiple columns or rows that are contiguous:
1) Click on the first column or row in the group.
2) Hold down the Shift key.
3) Click the last column or row in the group.
To select multiple columns or rows that are not contiguous:
1) Click on the first column or row in the group.
2) Hold down the Control key.
3) Click on all of the subsequent columns or rows while holding
down the Control key.
Entire sheet
To select the entire sheet, click on the small box between the A column
header and the 1 row header.
Figure 13. Select All box
You can also press Control+A to select the entire sheet.
Selecting sheets
You can select either one or multiple sheets. It can be advantageous to
select multiple sheets at times when you want to make changes to
many sheets at once.
Single sheet
Click on the sheet tab for the sheet you want to select. The active sheet
becomes white (see Figure 12).
Chapter 1 Introducing Calc 29
Select All
Multiple contiguous sheets
To select multiple contiguous sheets:
1) Click on the sheet tab for the first desired sheet.
2) Move the mouse pointer over the sheet tab for the last desired
sheet.
3) Hold down the Shift key and click on the sheet tab.
All the tabs between these two sheets will turn white. Any actions that
you perform will now affect all highlighted sheets.
Multiple noncontiguous sheets
To select multiple noncontiguous sheets:
1) Click on the sheet tab for the first desired sheet.
2) Move the mouse pointer over the sheet tab for the second desired
sheet.
3) Hold down the Control key and click on the sheet tab.
4) Repeat as necessary.
The selected tabs will turn white. Any actions that you perform will
now affect all highlighted sheets.
All sheets
Right-click any one of the sheet tabs and choose Select All Sheets
from the pop-up menu.
Working with columns and rows
Inserting columns and rows
Columns and rows can be inserted individually or in groups.
Note
When you insert a single new column, it is inserted to the left
of the highlighted column. When you insert a single new row, it
is inserted above the highlighted row.
Cells in the new columns or rows are formatted like the
corresponding cells in the column or row before (or to the left
of) which the new column or row is inserted.
Single column or row
Using the Insert menu:
1) Select the cell, column, or row where you want the new column or
row inserted.
30 OpenOffice.org 3.x Calc Guide
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 inserted.
2) Right-click the header of the column or row.
3) Choose Insert Rows or Insert Columns.
Multiple columns or rows
Multiple columns or rows can be inserted at once rather than inserting
them one at a time.
1) Highlight the required number of columns or rows by holding
down the left mouse button on the first one and then dragging
across the required number of identifiers.
2) Proceed as for inserting a single column or row above.
Deleting columns and rows
Columns and rows can be deleted individually or in groups.
Single column or row
A single column or row can be deleted by using the mouse:
1) Select the column or row to be deleted.
2) Choose Edit > Delete Cells from the menu bar.
Or,
1) Right-click on the column or row header.
2) Choose Delete Columns or Delete Rows from the pop-up menu.
Multiple columns or rows
Multiple columns or rows can be deleted at once rather than deleting
them one at a time.
1) Highlight the required columns or rows by holding down the left
mouse button on the first one and then dragging across the
required number of identifiers.
2) Proceed as for deleting a single column or row above.
Tip
Instead of deleting a row or column, you may wish to delete
the contents of the cells but keep the empty row or column.
See Chapter 2 (Entering, Editing, and Formatting Data) for
instructions.
Chapter 1 Introducing Calc 31
Working with sheets
Like any other Calc element, sheets can be inserted, deleted, and
renamed.
Inserting new sheets
There are several ways to insert a new sheet. The first step for all of
the methods is to select the sheets that the new sheet will be inserted
next to. Then any of the following options can be used.
Choose Insert > Sheet from the menu bar.
Right-click on the sheet tab and choose Insert Sheet.
Click in an empty space at the end of the line of sheet tabs.
Figure 14. Creating a new sheet
Each method will open the Insert Sheet dialog. Here you can select
whether the new sheet is to go before or after the selected sheet and
how many sheets you want to insert. If you are inserting only one
sheet, there is the opportunity to give the sheet a name.
Figure 15: Insert Sheet dialog
32 OpenOffice.org 3.x Calc Guide
Click here to insert
a new sheet
Deleting sheets
Sheets can be deleted individually or in groups.
Single sheet
Right-click on the tab of the sheet you want to delete and choose
Delete Sheet from the pop-up menu, or choose Edit > Sheet >
Delete from the Menu bar. Either way, an alert will ask if you want
to delete the sheet permanently. Click Yes.
Multiple sheets
To delete multiple sheets, select them as described earlier, then
either right-click over one of the tabs and choose Delete Sheet
from the pop-up menu, or choose Edit > Sheet > Delete from the
Menu bar.
Renaming sheets
The default name for the a new sheet is SheetX, where X is a number.
While this works for a small spreadsheet with only a few sheets, it
becomes awkward when there are many sheets.
To give a sheet a more meaningful name, you can:
Enter the name in the Name box when you create the sheet, or
Right-click on a sheet tab and choose Rename Sheet from the
pop-up menu; replace the existing name with a different one.
(New in OOo3.1) Double-click on a sheet tab to pop up the
Rename Sheet dialog.
Note
Sheet names must start with either a letter or a number; other
characters including spaces are not allowed. Apart from the
first character of the sheet name, allowed characters are
letters, numbers, spaces, and the underscore character.
Attempting to rename a sheet with an invalid name will
produce an error message.
Viewing Calc
Using zoom
Use the zoom function to change the view to show more or fewer cells
in the window.
Chapter 1 Introducing Calc 33
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-hand side.
Choose View > Zoom from the Menu bar, or
Double-click on the percentage figure in the Status bar at the
bottom of the window.
Figure 16. Zoom dialog
Optimal
Resizes the display to fit the width of the selected cells. To use this
option, you must first highlight a range of cells.
Fit Width and Height
Displays the entire page on your screen.
Fit Width
Displays the complete width of the document page. The top and
bottom edges of the page may not be visible.
100%
Displays the document at its actual size.
Variable
Enter a zoom percentage of your choice.
Freezing rows and columns
Freezing locks a number of rows at the top of a spreadsheet or a
number of columns on the left of a spreadsheet or both. Then when
scrolling around within the sheet, any frozen columns and rows remain
in view.
34 OpenOffice.org 3.x Calc Guide
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 denote the frozen areas. Rows 4 through 13 and
columns D through G have been scrolled off the page. The first three
rows and columns remained because they are frozen into place.
Figure 17. Frozen rows and columns
You can set the freeze point at one row, one column, or both a row and
a column as in Figure 17.
Freezing single rows or columns
1) Click on the header for the row below where you want the freeze
or for the column to the right of where you want the freeze.
2) Choose Window > Freeze.
A dark line appears, indicating where the freeze is put.
Freezing a row and a column
1) Click into the cell that is immediately below the row you want
frozen and immediately to the right of the column you want
frozen.
2) Choose Window > Freeze.
Two lines appear on the screen, a horizontal line above this cell
and a vertical line to the left of this cell. Now as you scroll around
the screen, everything above and to the left of these lines will
remain in view.
Unfreezing
To unfreeze rows or columns, choose Window > Freeze. The check
mark by Freeze will vanish.
Chapter 1 Introducing Calc 35
Splitting the screen
Another way to change the view is by splitting the window, also known
as splitting the screen. The screen can be split horizontally, vertically,
or both. You can therefore have up to four portions of the spreadsheet
in view at any one time.
Figure 18. Split screen example
Why would you want to do this? Imagine you have a large spreadsheet
and one of the cells has a number in it that is used by three formulas in
other cells. Using the split-screen technique, you can position the cell
containing the number in one section and each of the cells with
formulas in the other sections. Then you can change the number in the
cell and watch how it affects each of the formulas.
Splitting the screen horizontally
To split the screen horizontally:
1) Move the mouse pointer into the vertical scroll bar, on the right-
hand side of the screen, and place it over the small button at the
top with the black triangle.
Figure 19. Split screen bar on
vertical scroll bar
36 OpenOffice.org 3.x Calc Guide
Split screen bar
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 two arrows (Figure 20).
Figure 20. Split-screen bar on
vertical scroll bar with cursor
3) Hold down the left mouse button. A gray line appears, running
across the page. Drag the mouse downwards and this line follows.
4) Release the mouse button and the screen splits into two views,
each with its own vertical scroll bar. You can scroll the upper and
lower parts independently.
Notice in Figure 18, the Beta and the A0 values are in the upper part
of the window and other calculations are in the lower part. Thus, you
can make changes to the Beta and A0 values and watch their effects on
the calculations in the lower half of the window.
Tip
You can also split the screen using a menu command. Click in a
cell immediately below and to the right of where you wish the
screen to be split, and choose Window > Split.
Splitting the screen vertically
To split the screen vertically:
1) Move the mouse pointer into the horizontal scroll bar at the
bottom of the screen and place it over the small button on the
right with the black triangle.
Figure 21: Split bar on
horizontal scroll bar
2) Immediately to the right of this button is a thick black line (Figure
21). Move the mouse pointer over this line and it turns into a line
with two arrows.
Chapter 1 Introducing Calc 37
Split screen bar
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 mouse button, and the screen is split into two views,
each with its own horizontal scroll bar. You can scroll the left and
right parts of the window independently.
Removing split views
To remove a split view, do any of the following:
Double-click on each split line.
Click on and drag the split lines back to their places at the ends of
the scroll bars.
Choose Window > Split to remove all split lines at the same
time.
Using the Navigator
In addition to the cell reference boxes (labeled Column and Row), the
Navigator provides several other ways to move quickly through a
spreadsheet and find specific items.
To open the Navigator, click its icon on the Standard toolbar, or
press F5, or choose View > Navigator on the Menu bar, or double-
click on the Sheet Sequence Number in the Status Bar. You
can dock the Navigator to either side of the main Calc window or leave
it floating. (To dock or float the Navigator, hold down the Control key
and double-click in an empty area near the icons at the top.)
The Navigator displays lists of all the objects in a spreadsheet
document, grouped into categories. If an indicator (plus sign or arrow)
appears next to a category, at least one object of this kind exists. To
open a category and see the list of items, click on the indicator.
To hide the list of categories and show only the icons at the top, click
the Contents icon . Click this icon again to show the list.
Table 2 summarizes the functions of the icons at the top of the
Navigator.
38 OpenOffice.org 3.x Calc Guide
Figure 22: The Navigator in Calc
Table 2: Function of icons in the Navigator
Icon Action
Data Range. Specifies the current data range denoted by the
position of the cell cursor.
Start/End. Moves to the cell at the beginning or end of the
current data range, which you can highlight using the Data
Range button.
Contents. Shows or hides the list of categories.
Toggle. Switches between showing all categories and showing
only the selected category.
Displays all available scenarios. Double-click a name to apply that
scenario. See Chapter 7 (Data Analysis) for more information.
Drag Mode. Choose hyperlink, link, or copy. See “Choosing a drag
mode” for details.
Chapter 1 Introducing Calc 39
Moving quickly through a document
The Navigator provides several convenient ways to move around a
document and find items in it:
To jump to a specific cell in the current sheet, type its cell
reference in the Column and Row boxes at the top of the
Navigator and press the Enter key; for example, in Figure 22 the
cell reference is A7.
When a category is showing the list of objects in it, double-click
on an object to jump directly to that object’s location in the
document.
To see the content in only one category, highlight that category
and click the Toggle icon. Click the icon again to display all the
categories.
Use the Start and End icons to jump to the first or last cell in the
selected data range.
Tip
Ranges, scenarios, pictures, and other objects are much easier
to find if you have given them informative names when creating
them, instead of keeping Calc’s default Graphics 1, Graphics 2,
Object 1, and so on, which may not correspond to the position of
the object in the document.
Choosing a drag mode
Sets the drag and drop options for inserting items into a document
using the Navigator.
Insert as Hyperlink
Creates a hyperlink when you drag and drop an item into the
current document.
Insert as Link
Inserts the selected item as a link where you drag and drop an
object into the current document.
Insert as Copy
Inserts a copy of the selected item where you drag and drop in the
current document. You cannot drag and drop copies of graphics,
OLE objects, or indexes.
40 OpenOffice.org 3.x Calc Guide
Chapter 2
Entering, Editing, and
Formatting Data
Introduction
You can enter data into Calc in several ways: using the keyboard, the
mouse (dragging and dropping), the Fill tool, and selection lists. Calc
also provides the ability to enter information into multiple sheets of the
same document at the same time.
After entering data, you can format and display it in various ways.
Entering data using the keyboard
Most data entry in Calc can be accomplished using the keyboard.
Entering numbers
Click in the cell and type in the number using the number keys on
either the main keyboard or the numeric keypad.
To enter a negative number, either type a minus (–) sign in front of it or
enclose it in parentheses (brackets), like this: (1234).
By default, numbers are right-aligned and negative numbers have a
leading minus symbol.
Entering text
Click in the cell and type the text. Text is left-aligned by default.
Entering numbers as text
If a number is entered in the format 01481, Calc will drop the leading
0. (Exception: see Tip below.) To preserve the leading zero, for example
for telephone area codes, type an apostrophe before the number, like
this: '01481.
The data is now treated as text and displayed exactly as entered.
Typically, formulas will treat the entry as a zero and functions will
ignore it.
Tip
Numbers can have leading zeros and still be regarded as
numbers (as opposed to text) if the cell is formatted
appropriately. Right-click on the cell and chose Format Cells
> Numbers. Adjust the leading zeros setting to add leading
zeros to numbers.
42 OpenOffice.org 3.x Calc Guide
Note
When 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 quotes” are used for apostrophes, the
apostrophe remains visible in the cell.
To choose the type of apostrophe, use Tools > AutoCorrect
Options > Custom Quotes. The selection of the apostrophe
type affects both Calc and Writer.
Caution When 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.
Entering dates and times
Select the cell and type the date or time. You can separate the date
elements with a slash (/) or a hyphen (–) or use text such as 10 Oct 03.
Calc recognizes a variety of date formats. You can separate time
elements with colons such as 10:43:45.
Entering special characters
A “special” character is one not found on a standard English keyboard.
For example, © ¾ æ ç ñ ö ø ¢ are all special characters. To insert a
special character:
1) Place the cursor in your document where you want the character
to appear.
2) Click Insert > Special Character to open the Special
Characters dialog (Figure 23).
3) Select the characters (from any font or mixture of fonts) you wish
to insert, in order; then click OK. The selected characters are
shown in the bottom left of the dialog. As you select each
character, it is shown alone at the bottom right, along with the
numerical code for that character.
Note
Different fonts include different special characters. If you do
not find a particular special character you want, try changing
the Font selection.
Chapter 2 Entering, Editing, and Formatting Data 43
Figure 23: The Special Characters dialog
Inserting dashes
To enter en and em dashes, you can use the Replace dashes option
under Tools > AutoCorrect Options. This option replaces two
hyphens, under certain conditions, with the corresponding dash.
In the following table, the A and B represent text consisting of letters A
to z or digits 0 to 9.
Text that you type: Result
A - 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, space, en-dash, space, B)
A--B (A, minus, minus, B) A—B (A, em-dash, B)
A-B (A, minus, B) A-B (unchanged)
A -B (A, space, minus, B) A -B (unchanged)
A --B (A, space, minus, minus, B) A –B (A, space, en-dash, B)
Deactivating automatic changes
Calc automatically applies many changes during data input, unless you
deactivate those changes. You can also immediately undo any
automatic changes with Ctrl+Z.
AutoCorrect changes
Automatic correction of typing errors, replacement of straight
quotation marks by curly (custom) quotes, and starting cell content
with an uppercase (capital) letter are controlled by Tools >
AutoCorrect Options. Go to the Custom Quotes, Options, or
Replace tabs to deactivate any of the features that you do not want.
44 OpenOffice.org 3.x Calc Guide
On the Replace tab, you can also delete unwanted word pairs and
add new ones as required.
AutoInput
When you are typing in a cell, Calc automatically suggests matching
input found in the same column. To turn the AutoInput on and off,
set or remove the check mark in front of Tools > Cell Contents >
AutoInput.
Automatic date conversion
Calc automatically converts certain entries to dates. To ensure that
an entry that looks like a date is interpreted as text, type an
apostrophe at the beginning of the entry. The apostrophe is not
displayed in the cell.
Speeding up data entry
Entering data into a spreadsheet can be very labor-intensive, but Calc
provides several tools for removing some of the drudgery from input.
The most basic ability is to drop and drag the contents of one cell to
another with a mouse. Many people also find AutoInput helpful. Calc
also includes several other tools for automating input, especially of
repetitive material. They include the Fill tool, selection lists, and the
ability to input information into multiple sheets of the same document.
Using the Fill tool on cells
At its simplest, the Fill tool is a way to duplicate existing content. Start
by selecting the cell to copy, then drag the mouse in any direction (or
hold down the Shift key and click in the last cell you want to fill), and
then choose Edit > Fill and the direction in which you want to copy:
Up, Down, Left or Right.
Caution Choices that are not available are grayed out, but you can still
choose the opposite direction from what you intend, which
could cause you to overwrite cells accidentally.
Tip
A shortcut way to fill cells is to grab the “handle” in the lower
right-hand corner of the cell and drag it in the direction you
want to fill. If the cell contains a number, the number will fill in
series. If the cell contains text, the same text will fill in the
direction you chose.
Chapter 2 Entering, Editing, and Formatting Data 45
Figure 24: Using the Fill tool
Using a fill series
A more complex use of the Fill tool is to use a fill series. The default
lists are for the full and abbreviated days of the week and the months
of the year, but you can create your own lists as well.
To add a fill series to a spreadsheet, select the cells to fill, choose Edit
> Fill > Series. In the Fill Series dialog, select AutoFill as the Series
type, and enter as the Start value an item from any defined series. The
selected cells then fill in the other items on the list sequentially,
repeating from the top of the list when they reach the end of the list.
Figure 25: Specifying the start of a fill series (result is in Figure 26)
46 OpenOffice.org 3.x Calc Guide
Figure 26: Result of fill series
selection shown in Figure 25
You can also use Edit > Fill > Series to create a one-time fill series
for numbers by entering the start and end values and the increment.
For example, if you entered start and end values of 1 and 7 with an
increment of 2, you would get the sequence of 1, 3, 5, 7.
In all these cases, the Fill tool creates only a momentary connection
between the cells. Once they are filled, the cells have no further
connection with one another.
Defining a fill series
To define your own fill series, go to Tools > Options >
OpenOffice.org Calc > Sort Lists. This dialog shows the previously-
defined series in the Lists box on the left, and the contents of the
highlighted list in the Entries box.
Figure 27: Predefined fill series
Click New. The Entries box is cleared. Type the series for the new list
in the Entries box (one entry per line), and then click Add.
Chapter 2 Entering, Editing, and Formatting Data 47
Figure 28: Defining a new fill series
Using selection lists
Selection lists are available only for text, and are
limited to using only text that has already been entered
in the same column.
To use a selection list, select a blank cell and press
Ctrl+D. A drop-down list appears of any cell in the same
column that either has at least one text character or
whose format is defined as Text. Click on the entry you
require.
Sharing content between sheets
You might want to enter the same information in the same cell on
multiple sheets, for example to set up standard listings for a group of
individuals or organizations. Instead of entering the list on each sheet
individually, you can enter it in all the sheets at once. To do this, select
all the sheets (Edit > Sheet > Select), then enter the information in
the current one.
Caution
This technique overwrites any information that is already in
the cells on the other sheets—without any warning. For this
reason, when you are finished, be sure to deselect all the
sheets except the one you want to edit. (Ctrl+click on a sheet
tab to select or deselect the sheet.)
48 OpenOffice.org 3.x Calc Guide
Validating cell contents
When creating spreadsheets for other people to use, you may want to
make sure they enter data that is valid or appropriate for the cell. You
can also use validation in your own work as a guide to entering data
that is either complex or rarely used.
Fill series and selection lists can handle some types of data, but they
are limited to predefined information. To validate new data entered by
a user, select a cell and use Data > Validity to define the type of
contents that can be entered in that cell. For example, a cell might
require a date or a whole number, with no alphabetic characters or
decimal points; or a cell may not be left empty.
Depending on how validation is set up, the tool can also define the
range of values that can be entered and provide help messages that
explain the content rules you have set up for the cell and what users
should do when they enter invalid content. You can also set the cell to
refuse invalid content, accept it with a warning, or—if you are
especially well-organized—start a macro when an error is entered.
Validation is most useful for cells containing functions. If cells are set
to accept invalid content with a warning, rather than refusing it, you
can use Tools > Detective > Mark Invalid Data to find the cells with
invalid data. The Detective marks with a circle any cells containing
invalid data.
Note that a validity rule is considered part of a cell’s format. If you
select Format or Delete All from the Delete Contents window, then it
is removed. (Repeating the Detective’s Mark Invalid Data command
then removes the invalid data circle, because the data is no longer
invalid.) If you want to copy a validity rule with the rest of the cell, use
Edit > Paste Special > Paste Formats or Paste All.
Figure 29 shows the choices for a typical validity test. Note the Allow
blank cells option under the Allow list.
Chapter 2 Entering, Editing, and Formatting Data 49
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 shows the choices when a cell must
contain a cell range.
Figure 30: Validity choices for a cell range.
To provide input help for a cell, use the Input Help page of the Validity
dialog (Figure 31). To show an error message when an invalid value is
entered, use the Error Alert page (Figure 32). Be sure to write
something helpful, explaining what a valid entry should contain—not
just “Invalid data—try again” or something similar.
50 OpenOffice.org 3.x Calc Guide
Figure 31: Defining input help for a cell
Figure 32: Defining an error message for a cell with
invalid data
Editing data
Editing 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.
Removing data from a cell
Data can be removed (deleted) from a cell in several ways.
Removing data only
The data alone can be removed from a cell without removing any of
the formatting of the cell. Click in the cell to select it, and then press
the Backspace key.
Removing data and formatting
The data and the formatting can be removed from a cell at the same
time. Press the Delete key (or right-click and choose Delete
Contents, or use Edit > Delete Contents) to open the Delete
Contents dialog (Figure 33). From this dialog, different aspects of
Chapter 2 Entering, Editing, and Formatting Data 51
the cell can be deleted. To delete everything in a cell (contents and
format), check Delete all.
Figure 33: Delete Contents dialog
Replacing all the data in a cell
To remove data and insert new data, simply type over the old data. The
new data will retain the original formatting.
Changing part of the data in a cell
Sometimes it is necessary to change the contents of cell without
removing all of the contents, for example if the phrase “See Dick run”
is in a cell and it needs to be changed to “See Dick run fast.” It is often
useful to do this without deleting the old cell contents first.
The process is the similar to the one described above, but you need to
place the cursor inside the cell. You can do this in two ways.
Using the keyboard
After selecting the appropriate cell, press the F2 key and the cursor
is placed at the end of the cell. Then use the keyboard arrow keys to
move the cursor through the text in the cell.
Using the mouse
Using the mouse, either double-click on the appropriate cell (to
select it and place the cursor in it for editing), or single-click to
select the cell and then move the mouse pointer up to the input line
and click into it to place the cursor for editing.
52 OpenOffice.org 3.x Calc Guide
Formatting data
The 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, or it can
be applied manually to the cell. Some manual formatting can be
applied using toolbar icons. For more control and extra options, select
the appropriate cell or cells range, right-click on it, and select Format
Cells. All of the format options are discussed below.
Note
All the settings discussed in this section can also be set as a
part of the cell style. See Chapter 4 (Using Styles and
Templates) for more information.
Formatting multiple lines of text
Multiple lines of text can be entered into a single cell using automatic
wrapping or manual line breaks. Each method is useful for different
situations.
Using automatic wrapping
To set text to wrap at the end of the cell, right-click on the cell and
select Format Cells (or choose Format > Cells from the menu bar, or
press Ctrl+1). On the Alignment tab (Figure 34), under Properties,
select Wrap text automatically. The results are shown in Figure 35.
Figure 34: Format Cells > Alignment dialog
Chapter 2 Entering, Editing, and Formatting Data 53
Figure 35: Automatic text wrap
Using manual line breaks
To insert a manual line break while typing in a cell, press Ctrl+Enter.
This method does not work with the cursor in the input line. When
editing text, first double-click the cell, then single-click at the position
where you want the line break.
When a manual line break is entered, the cell width does not change.
Figure 36 shows the results of using two manual line breaks after the
first line of text.
Figure 36: Cell with manual line breaks
Shrinking text to fit the cell
The font size of the data in a cell can automatically adjust to fit in a
cell. To do this, select the Shrink to fit cell option in the Format Cells
dialog (Figure 34). Figure 37 shows the results.
Figure 37: Shrinking font size to fit cells
54 OpenOffice.org 3.x Calc Guide
Formatting numbers
Several different number formats can be applied to cells by using icons
on the Formatting toolbar. Select the cell, then click the relevant icon.
Some icons may not be visible in a default setup; click the down-arrow
at the end of the Formatting bar and select other icons to display.
Figure 38: Number format icons. Left to right: currency, percentage,
date, exponential, standard, add decimal place, delete decimal place.
For more control or to select other number formats, use the Numbers
tab (Figure 39) of the Format Cells dialog.
Apply any of the data types in the Category list to the data.
Control the number of decimal places and leading zeros.
Enter a custom format code.
The Language setting controls the local settings for the different
formats such as the date order and the currency marker.
Figure 39: Format Cells > Numbers
Chapter 2 Entering, Editing, and Formatting Data 55
Formatting the font
To quickly choose the font used in a cell, select the cell, then click the
arrow next to the Font Name box on the Formatting toolbar and choose
a font from the list.
Tip
To choose whether to show the font names in their font or in
plain text, go to Tools > Options > OpenOffice.org > View
and select or deselect the Show preview of fonts option in the
Font Lists section. For more information, see Chapter 14
(Setting Up and Customizing Calc).
To choose the size of the font, click the arrow next
to the Font Size box on the Formatting toolbar. For
other formatting, you can use the Bold, Italic, or
Underline icons.
To choose a font color, click the arrow next to the
Font Color icon to display a color palette. Click on
the required color.
(To define custom colors, use Tools > Options >
OpenOffice.org > Colors. See Chapter 14 for
more information.)
To specify the language of the cell (useful because it allows different
languages to exist in the same document and be spell checked
correctly), use the Font tab of the Format Cells dialog. See Chapter 4
for more information.
Choosing font effects
The Font Effects tab (Figure 40) of the Format Cells dialog offers more
font options.
Overlining and underlining
You can choose from a variety of overlining and underlining options
(solid lines, dots, short and long dashes, in various combinations)
and the color of the line.
Strikethrough
The strikethrough options include lines, slashes, and Xs.
Relief
The relief options are embossed (raised text), engraved (sunken
text), outline, and shadow.
56 OpenOffice.org 3.x Calc Guide
Figure 40: Format Cells > Font Effects
Setting cell alignment and orientation
Some of the cell alignment and orientation icons are not shown by
default on the Formatting toolbar. To show them, click on the small
arrow at the right-hand end of the toolbar and select them from the list
of icons.
Figure 41: Cell alignment and orientation
Some of the alignment and orientation icons are available only if you
have Asian or CTL (Complex Text Layout) languages enabled (in Tools
> Options > Language Settings > Languages). If you choose an
unavailable icon from the list, it does not appear on the toolbar.
Chapter 2 Entering, Editing, and Formatting Data 57
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 rotate the text. If you have Asian languages enabled, then the Text
orientation section shows an extra option (labeled Asian layout
mode) under the Vertically stacked option, as shown in Figure 41.
Figure 42: Asian layout mode option
The difference in results between having Asian layout mode on or off
is shown in Figure 43.
Figure 43: Types of vertical stacking
Formatting the cell borders
To quickly choose a line style and color for the borders of a cell, click
the small arrows next to the Line Style and Line Color icons on the
Formatting toolbar. If the Line Style and Line Color icons are not
displayed in the formatting toolbar, select the down arrow on the right
side of the bar, then Visible Buttons. In each case, a palette of
choices is displayed.
For more control, including the spacing between the cell borders and
the text, use the Borders tab of the Format Cells dialog. There you can
also define a shadow. See Chapter 4 for details.
58 OpenOffice.org 3.x Calc Guide
Note
The 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 would be equivalent visually to a bottom
border on C2), that border can only be removed by selecting
C3. It cannot be removed in C2.
Formatting the cell background
To quickly choose a background color for a cell, click the small arrow
next to the Background Color icon on the Formatting toolbar. A palette
of color choices, similar to the Font Color palette, is displayed.
(To define custom colors, use Tools > Options > OpenOffice.org >
Colors. See Chapter 14 for more information.)
You can also use the Background tab of the Format Cells dialog. See
Chapter 4 for details.
Autoformatting cells and sheets
You can use the AutoFormat feature to quickly apply a set of cell
formats to a sheet or a selected cell range.
1) Select the cells that you want to format, including the column and
row headers.
2) Choose Format > AutoFormat.
Figure 44: Choosing an AutoFormat
Chapter 2 Entering, Editing, and Formatting Data 59
Note If the selected cell range does not have column and row
headers, AutoFormat is not available.
3) To select which properties (number format, font, alignment,
borders, pattern, autofit width and height) to include in an
AutoFormat, click More. Select or deselect the required options.
4) Click OK.
If you do not see any change in color of the cell contents, choose View
> Value Highlighting from the menu bar.
Defining a new AutoFormat
You can define a new AutoFormat that is available to all spreadsheets.
1) Format a sheet.
2) Choose Edit > Select All.
3) Choose Format > AutoFormat. The Add button is now active.
4) Click Add.
5) In the Name box of the Add AutoFormat dialog, type a meaningful
name for the new format.
6) Click OK to save. The new format is now available in the Format
list in the AutoFormat dialog.
Formatting spreadsheets using themes
Calc comes with a predefined set of formatting themes that you can
apply to your spreadsheets.
It is not possible to add themes to Calc, and they cannot be modified.
However, you can modify their styles after you apply them to a
spreadsheet.
To apply a theme to a spreadsheet:
1) Click the Choose Themes icon in the Tools toolbar. If this toolbar
is not visible, you can show it using View > Toolbars > Tools.
The Theme Selection dialog appears. This dialog lists the
available themes for the whole spreadsheet.
2) In the Theme Selection dialog, select the theme that you want to
apply to the spreadsheet.
60 OpenOffice.org 3.x Calc Guide
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. If you wish, you can now go to the Styles and
Formatting window to modify specific styles. These modifications
do not change the theme; they only change the appearance of this
specific spreadsheet document.
Using conditional formatting
You can set up cell formats to change depending on conditions that you
specify. For example, in a table of numbers, you can show all the values
above the average in green and all those below the average in red.
Note To apply conditional formatting, AutoCalculate must be
enabled. Choose Tools > Cell Contents > AutoCalculate.
Conditional formatting depends upon the use of styles. If you are not
familiar with styles, please refer to Chapter 4. An easy way to set up
the required styles is to format a cell the way you want it and click the
New Style from Selection icon in the Styles and Formatting window.
After the styles are set up, here is how to use them.
1) In your spreadsheet, select the cells to which you want to apply
conditional formatting.
2) Choose Format > Conditional Formatting from the menu bar.
3) On the Conditional Formatting dialog (Figure 45), enter the
conditions. Click OK to save. The selected cells are now
formatted in the relevant style.
Cell value is / Formula is
Specifies whether conditional formatting is dependent on a cell
value or on a formula. If you select cell value is, the Cell Value
Condition box is displayed, as shown in the example. Here you can
choose from conditions including less than, greater than, between,
and others.
Parameter field
Enter a reference, value, or formula in the parameter field, or in
both parameter fields if you have selected a condition that requires
two parameters. You can also enter formulas containing relative
references.
Chapter 2 Entering, Editing, and Formatting Data 61
Figure 45: Conditional formatting dialog
Cell style
Choose the cell style to be applied if the specified condition
matches. The style must have been defined previously.
See the Help for more information and examples of use.
To apply the same conditional formatting
later to other cells:
1) Select one of the cells that has been
assigned conditional formatting.
2) Copy the cell to the clipboard.
3) Select the cells that are to receive this
same formatting.
4) Choose Edit > Paste Special.
5) On the Paste Special dialog, in the
Selection area, select only the Formats
option. Make sure all other options are
not selected. Click OK.
62 OpenOffice.org 3.x Calc Guide
Hiding and showing data
When elements are hidden, they are neither visible nor printed, but
can still be selected for copying if you select the elements around
them. For example, if column B is hidden, it is copied when you select
columns A and C. When you need a hidden element again, you can
reverse the process, and show the element.
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 select the row, and then choose Format > Row > Hide (or
right-click and choose Hide).
To hide or show selected cells, choose Format > Cells from the menu
bar (or right-click and choose Format Cells). On the Format Cells
dialog, go to the Cell Protection tab.
Figure 46: Hiding or showing cells
Outline group controls
If you are continually hiding and showing the same cells, you can
simplify the process by creating outline groups, which add a set of
controls for hiding and showing the cells in the group that are quick to
use and always available.
If the contents of cells falls into a regular pattern, such as four cells
followed by a total, then you can use Data > Group and Outline >
AutoOutline to have Calc add outline controls based on the pattern.
Otherwise, you can set outline groups manually by selecting the cells
for grouping, then choosing Data > Group and Outline > Group. On
the Group dialog, you can choose whether to group the selected cells
by rows or columns.
Chapter 2 Entering, Editing, and Formatting Data 63
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 controls resemble the tree-structure of a file-manager in
appearance, and can be hidden by selecting Data > Group and
Outline > Hide Details. They are strictly for online use, and do not
print.
The basic outline controls have plus or minus signs at the start of the
group to show or hide hidden cells. However, if outline groups are
nested, the controls have numbered buttons for hiding the different
levels.
If you no longer need a group, place the mouse cursor in any cell in it
and select Data > Group and Outline > Ungroup. To remove all
groups on a sheet, select Data > Group and Outline > Remove.
Figure 47: Outline group controls
Filtering which cells are visible
A filter is a list of conditions that each entry has to meet in order to be
displayed. You can set three types of filters from the Data > Filter
sub-menu.
Automatic filters add a drop-down list to the top row of a column that
contains commonly used filters. They are quick and convenient and
almost as useful with text as with numbers, because the list includes
every unique entry in the selected cells.
In addition to these unique entries, automatic filters include the option
to display all entries, the ten highest numerical values, and all cells
that are empty or not empty, as well as a standard filterthat you can
customize (see below). However, they are somewhat limited. In
particular, they do not allow regular expressions, so you cannot use
them to display cell contents that are similar but not identical.
64 OpenOffice.org 3.x Calc Guide
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 and OR. Standard filters are mostly useful for numbers,
although a few of the conditional operators, such as = and < > can
also be used for text.
Other conditional operators for standard filters include options to
display the largest or smallest values, or a percentage of them. Useful
in themselves, standard filters take on added value when used to
further refine automatic filters.
Advanced filters are structured similarly to standard filters. The
differences are that advanced filters are not limited to three
conditions, and their criteria are not entered in a dialog. Instead,
advanced filters are entered in a blank area of a sheet, then referenced
by the advanced filter tool in order to apply them.
Sorting records
Sorting rearranges the visible cells on the sheet. In Calc, you can sort
by up to three criteria, which are applied one after another. Sorts are
handy when you are searching for a particular item, and become even
more powerful after you have filtered data.
In addition, sorting is often useful when you add new information.
When a list is long, it is usually easier to add new information at the
bottom of the sheet, rather than inserting rows in the proper places.
After you have added the information, you can sort it to update the
sheet.
Highlight the cells to be sorted, then select Data > Sort to open the
Sort dialog (Figure 48) or click the Sort Ascending or Sort
Descending 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) order.
Tip
You can define a custom sort order if the supplied
alphanumeric ones do not fit your requirements. See “Defining
a fill series” on page 47 for instructions.
On the Options tab of the Sort dialog (Figure 49), you can choose the
following options.
Chapter 2 Entering, Editing, and Formatting Data 65
Figure 48: Choosing the criteria and order of sorting
Figure 49: Options for sorting
Case sensitive
If two entries are otherwise identical, one with an upper case letter
is placed before one with a lower case letter in the same position if
the sort is descending; if the sort is ascending, then the entry with
an upper case letter is placed after one with a lower case letter in
the same position.
66 OpenOffice.org 3.x Calc Guide
Range contains column labels
Does not include the column heading in the sort.
Include formats
A cell's formatting is moved with its contents. If formatting is used
to distinguish different types of cells, then use this option.
Copy sort results to
Sets a spreadsheet address to which to copy the sort results. If a
range is specified that does not have the necessary number of cells,
then cells are added. If a range contains cells that already have
content, then the sort fails.
Custom sort order
Select the box, then choose from the drop-down list one of the sort
orders defined in Tools > Options > OpenOffice.org Calc >
Sort Lists.
Direction
Sets whether rows or columns are sorted. The default is to sort by
columns unless the selected cells are in a single column.
Finding and replacing in Calc
In spreadsheet documents you can search for text, formulas, and
styles. You can navigate from one occurrence to the next using Find,
or you can highlight all matching cells at once using Find All, then
apply another format or replace the cell contents by other content.
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 you are searching for.
Tip
Cell contents can be formatted in different ways. For example,
a number can be formatted as a currency, to be displayed with
a currency symbol. You see the currency symbol in the cell, but
you cannot search for it.
By default, Calc searches the current sheet. To search through all
sheets of the document, click More Options, then select Search in all
sheets option.
Chapter 2 Entering, Editing, and Formatting Data 67
Caution Use Replace All with caution; otherwise, you may end up with
some highly embarrassing mistakes. A mistake with Replace
All might require a manual, word-by-word search to fix, if it is
not discovered in time to undo it.
Figure 50: Expanded Find & Replace dialog
Finding and replacing formulas or values
You can use the Find & Replace dialog to search in formulas or in the
displayed values that result from a calculation.
1) To open the Find & Replace dialog, use the keyboard shortcut
Control+F or select Edit > Find & Replace.
2) Click More Options to expand the dialog.
68 OpenOffice.org 3.x Calc Guide
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) Type the text you want to find in the Search for box.
5) To replace the text with different text, type the new text in the
Replace with box.
6) When you have set up your search, click Find. To replace text,
click Replace instead.
Finding and replacing text
1) Open the Find & Replace dialog, click More Options to expand
the dialog, and select Values or Notes in the Search in drop-down
list.
2) Type the text you want to find in the Search for box.
3) To replace the text with different text, type the new text in the
Replace with box.
4) Click Find, Find All, Replace, or Replace All.
When you click Find, Calc selects the next cell that contains your text.
You can edit the text, then click Find again to advance to the next
found cell. If you closed the dialog, you can press Ctrl+Shift+F to find
the next cell without opening the dialog.
When you click Find All, Calc selects all cells that contain your entry.
Now you can, for example, apply a cell style to all of them at once.
Finding and replacing cell styles
To quickly change all the paragraphs of one (unwanted) style to
another (preferred) style:
1) On the expanded Find & Replace dialog, select Search for
Styles. The Search for and Replace with boxes now contain a list
of styles.
2) Select the styles you want to search for and replace.
3) Click Find, Find All, Replace, or Replace All.
Using wildcards (regular expressions)
Wildcards (also known as regular expressions) are combinations of
characters that instruct OOo how to search for something. Regular
expressions are very powerful but not very intuitive. They can save
time and effort by combining multiple finds into one.
Chapter 2 Entering, Editing, and Formatting Data 69
To use wildcards and regular expressions when searching and
replacing:
1) On the Find & Replace dialog, click More Options to see more
choices. On this expanded dialog, select the Regular
expressions option.
2) Type the search text, including the wildcards, in the Search for
box and the replacement text (if any) in the Replace with box.
3) Click Find, Find All, Replace, or Replace All (not
recommended).
Tip The online help describes many of the regular expressions and
their uses.
The following points are interesting to Calc users:
In Calc, regular expressions are applied separately to each cell.
So a search for r.d will match red in cell A1 but will not match r
in cell A2 and d (or ed) in cell A3. (The regular expression r.d
means “try to match r followed by any other character followed
by d.”)
When a match is found, the whole cell is shown highlighted, but
only the text found will be replaced. For example, searching for
brown will highlight a cell containing redbrown clay, and
replacing with nothing will leave the cell containing red clay.
If Find is used twice in a row, the second time with Current
selection only activated, then the second search will evaluate
the whole of each selected cell, not just the strings found which
caused the cells to be selected in the first search. For example,
searching for joh?n, then activating Current selection only and
searching for sm.th will find cells containing Jon Smith and
Smythers, Johnathon.
If a cell contains a hard line break (entered by Ctrl+Enter), this
may be found by \n. For example if a cell contains red
hard_line_break clay then searching for d\nc and replacing with
nothing leaves the cell containing relay.
The hard line break acts to mark “end of text” as understood by
the regular expression special character $ (in addition of course
to the end of text in the cell). For example, if a cell contains red
hard_line_break clay then a search for d$ replacing with al
leaves the cell with real hard_line_break clay. Note that with
this syntax the hard line break is not replaced—it simply marks
the end of text.
70 OpenOffice.org 3.x Calc Guide
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 search Formulas,
Values, or Notes. This applies to any search, not just one using
regular expressions. Searching with the Formulas option would
find SUM in a cell containing the formula =SUM(A1:A6). If a cell
contains text instead of a function, the text will still be found - so
that the simple text SUMMARY in a cell would also give a match
to SUM using the Formulas option.
Searching for the regular expression ^$ will not find empty cells.
This is intentional—the rationale being to avoid performance
issues when selecting a huge number of cells. Note that empty
cells will not be found even if you are only searching a selection.
Find .+ (or similar) and Replace with & effectively re-enters the
contents of cells. This can be used to strip formatting
automatically applied by Calc (often needed to clean data
imported from the clipboard or badly formatted files), for
example, to convert text strings consisting of digits into actual
numbers (the cells must first be correctly formatted numbers).
The leading apostrophes, telling Calc to treat the numbers as text,
are removed.
See Chapter 7 (Using Formulas and Functions) for the use of regular
expressions within formulas.
Chapter 2 Entering, Editing, and Formatting Data 71
Chapter 3
Creating Charts and
Graphs
Presenting information visually
Introduction
Charts and graphs can be powerful ways to convey information to the
reader. OpenOffice.org Calc offers a variety of different chart and
graph formats for your data.
Using Calc, you can customize charts and graphs to a considerable
extent. Many of these options enable you to present your information
in the best and clearest manner.
For readers who are interested in effective ways to present information
graphically, two excellent introductions to the topic are William S.
Cleveland’s The elements of graphing data, 2nd edition, Hobart Press
(1994) and Edward R. Tufte’s The Visual Display of Quantitative
Information, 2nd edition, Graphics Press (2001).
Creating a chart
To demonstrate the process of making charts and graphs in Calc, we
will use the small table of data in Figure 51.
Figure 51: Table of data for charting examples
To create a chart, first highlight (select) the data to be included in the
chart. The selection does not need to be in a single block, as shown in
Figure 52; you can also choose individual cells or groups of cells
(columns or rows). See Chapter 1 (Introducing Calc) for more about
selecting cells and ranges of cells.
Chapter 3 Creating Charts and Graphs 73
Figure 52: Selecting data for plotting
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 toolbar.
Figure 53: Insert chart from main toolbar
Either method inserts a sample chart on the worksheet, opens the
Formatting toolbar, and opens the Chart Wizard, as shown in Figure
54.
Tip
Before choosing the Chart Wizard, place the cursor anywhere in
the area of the data. The Chart Wizard will then do a fairly good
job of guessing the range of the data. Just be careful that you
have not included the title of your chart.
74 OpenOffice.org 3.x Calc Guide
Figure 54: Chart Wizard, Step 1—Choose a chart type
Choosing a chart type
The Chart Wizard includes a sample chart with your data. This sample
chart updates to reflect the changes you make in the Chart Wizard.
The Chart Wizard has three main parts: a list of steps involved in
setting up the chart, a list of chart types, and the options for each
chart type. At any time you can go back to a previous step and change
selections.
Calc offers a choice of 10 basic chart types, with a few options for each
type of chart. The options vary according to the type of chart you pick.
The first tier of choice is for two-dimensional (2D) charts. Only those
types which are suitable for 3D (Column, Bar, Pie, and Area) give you
an option to select a 3D look.
On the Choose a chart type page (Figure 54), select a type by clicking
on its icon. The preview updates every time you select a different type
of chart, and provides a good idea of what the finished chart will look
like.
The current selection is highlighted (shown with a surrounding box) on
the Choose a chart type page. The chart’s name is shown just below
Chapter 3 Creating Charts and Graphs 75
the icons. For the moment, we will stick to the Column chart and click
on Next again.
Changing data ranges and axes labels
In Step 2, Data Range, you can manually correct any mistakes you
have made in selecting the data.
On this page you can also change the way you are plotting the data by
using the rows—rather than the columns—as data series. This is useful
if you use a style of chart such as Donut or Pie to display your data.
Lastly, you can choose whether to use the first row or first column, or
both, as labels on the axes of the chart.
You can confirm what you have done so far by clicking the Finish
button, or click Next to change some more details of the chart.
We will click Next to see what we can do to our chart using the other
pages of the Wizard.
Figure 55: Changing data ranges and axes labels
76 OpenOffice.org 3.x Calc Guide
Selecting data series
Figure 56: Amending data series and ranges
On the Data Series page, you can fine tune the data that you want to
include in the chart. Perhaps you have decided that you do not want to
include the data for canoes. If so, highlight Canoes in the Data series
box and click on Remove. Each named data series has its ranges and
its individual Y-values listed. This is useful if you have very specific
requirements for data in your chart, as you can include or leave out
these ranges.
Tip You can click the Shrink button next to the Range for Name
box to work on the spreadsheet itself. This is handy if your data
ranges are larger than ours and the Chart Wizard is in the way.
Another way to plot any unconnected columns of data is to select the
first data series and then select the next series while holding down the
Ctrl key. Or you can type the columns in the text boxes. The columns
must be separated by semi-colons. Thus, to plot B3:B11 against
G3:G11, type the selection range as B3:B11;G3:G11.
The two data series you are selecting must be in separate columns or
rows. Otherwise Calc will assume that you are adding to the same data
series.
Click Next to deal with titles, legend and grids.
Chapter 3 Creating Charts and Graphs 77
Adding or changing titles, legend, and grids
Figure 57: Titles, legend and grids
On the Chart Elements page, you can give your chart a title and, if
desired, a subtitle. Use a title that draws the viewers’ attention to the
purpose of the chart: what you want them to see. For example, a better
title for this chart might be The Performance of Motor and Other
Rental Boats.
It may be of benefit to have labels for the x axis or the y axis. This is
where you give viewers an idea as to the proportion of your data. For
example, if we put Thousands in the y axis label of our graph, it
changes the scope of the chart entirely. For ease of estimating data you
can also display the x or y axis grids by selecting the Display grids
options.
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 Finish.
Editing charts
After you have created a chart, you may find things you would like to
change. Calc provides tools for changing the chart type, chart
elements, data ranges, fonts, colors, and many other options, through
the Insert and Format menus, the right-click (context) menu, and the
Chart toolbar.
78 OpenOffice.org 3.x Calc Guide
Changing the chart type
You 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 surrounded by a gray border.
2) Then do one of the following:
Choose Format > Chart Type from the menu bar.
Click the chart type icon on the Formatting toolbar.
Right-click on the chart and choose Chart Type.
In each case, a dialog similar to the one in Figure 54 opens. See page
75 for more information.
Adding or removing chart elements
Figures 58 and 59 show the elements of 2D and 3D charts.
The default 2D chart includes only two of those elements:
Chart wall contains the graphic of the chart displaying the data.
Chart area is the area surrounding the chart graphic. The
(optional) chart title and the legend (key) are in the chart area.
The default 3D chart also has the chart floor, which is not available in
2D charts.
Figure 58: Elements of 2D chart
Chapter 3 Creating Charts and Graphs 79
Chart area
Chart wall
Chart title
Legend
Axis labels
Figure 59: Elements of 3D chart
You can add other elements using the commands on the Insert menu.
The various choices open dialogs in which you can specify details.
First select the chart so the green sizing handles are visible. This is
done with a single click on the chart.
The dialogs for Titles, Legend, Axes, and Grids are self-explanatory.
The others are a bit more complicated, so we’ll take a look at them
here.
Data labels
Data labels put information about each data point on the chart. They
can be very useful for presenting detailed information, but you need to
be careful to not create a chart that is too cluttered to read.
Choose Insert > Data Labels. The options are as follows.
Show value as number
Displays the numeric values of the data points. When selected, this
option activates the Number format... button.
Number format...
Opens the Number Format dialog, where you can select the number
format. This dialog is very similar to the one for formatting numbers
in cells, described in Chapter 2 (Entering, Editing, and Formatting
Data).
80 OpenOffice.org 3.x Calc Guide
Chart floor
Figure 60: Data Labels dialog
Show value as percentage
Displays the percentage value of the data points in each column.
When selected, this option activates the Percentage format...
button.
Percentage format...
Opens the Number Format dialog, where you can select the
percentage format.
Show category
Shows the data point text labels.
Show legend key
Displays the legend icons next to each data point label.
Separator
Selects the separator between multiple text strings for the same
object.
Placement
Selects the placement of data labels relative to the objects.
Figure 71 on page 95 shows examples of values as text (neither Show
value as number nor Show value as percentage selected) and values as
percentages, as well as when data values are used as substitutes for
legends or in conjunction with them.
Chapter 3 Creating Charts and Graphs 81
Trend lines
When 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. Calc has a good selection of regression types you can use for
trend lines: linear, logarithm, exponential, and power. Choose the type
that comes closest to passing through all of the points.
To insert trend lines for all data series, double-click the chart to enter
edit mode. Choose Insert > Trend Lines, then select the type of trend
line from None, Linear, Logarithmic, Exponential, or Power. You can
also choose whether to show the equation for the trend line and the
coefficient of determination (R2).
To insert a trend line for a single data series, first select the data series
in the chart, and then right-click and choose Insert > Trend Line
from the context menu. The dialog for a single trend line is similar to
the one below but has a second tab (Line), where you can choose
attributes (style, color, width, and transparency) of the line.
To delete a single trend line or mean value line, click the line, then
press the Del key.
To delete all trend lines, choose Insert > Trend Lines, then select
None.
A trend line is shown in the legend automatically.
Figure 61: Trend Lines dialog
82 OpenOffice.org 3.x Calc Guide
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 the trend line.
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. This opens the Line tab of the Trend Lines dialog.
To show the trend line equation, select the trend line in the chart,
right-click to open the context menu, and choose Insert Trend Line
Equation.
When the chart is in edit mode, OpenOffice.org gives you the equation
of the trend line and the correlation coefficient. Click on the trend line
to see the information in the status bar. To show the equation and the
correlation coefficient, select the line and choose Insert R2 and
Trend Line Equation.
For more details on the regression equations, see the topic Trend lines
in charts in the Help.
Mean value lines
If you select mean value lines, Calc calculates the average of each
selected data series and places a colored line at the correct level in the
chart.
Y error bars
If you are presenting data that has a known possibility of error, such as
social surveys using a particular sampling method, or you want to show
the measuring accuracy of the tool you used, you may wish to show
error bars on the chart. Select the chart and choose Insert > Y Error
Bars.
Several options are provided on the Y Error Bars dialog (Figure 62).
You can only choose one option at a time. You can also choose whether
the error indicator shows both positive and negative errors, or only
positive or only negative.
Constant value – you can have separate positive and negative
values.
Percentage – choose the error as a percentage of the data points.
In the drop-down list:
Standard error
Variance – shows error calculated on the size of the biggest and
smallest data points
Chapter 3 Creating Charts and Graphs 83
Standard deviation – shows error calculated on standard
deviation
Error margin – you designate the error
Cell range – calculates the error based on cell ranges you select.
The Parameters section at the bottom of the dialog changes to
allow selection of the cell ranges.
Figure 62: Specifying the parameters of error bars
Formatting charts
The Format menu has many options for formatting and fine-tuning the
appearance of your charts.
Double-click the chart so that it is enclosed by a gray border indicating
edit mode; then, select the chart element that you want to format.
Choose Format from the menu bar, or right-click to display a pop-up
(context) menu relevant to the selected element. The formatting
choices are as follows.
Format Selection
Opens a dialog in which you can specify the area fill, borders,
transparency, characters, font effects, and other attributes of the
selected element of the chart (see page 91).
Position and Size
Opens a dialog (see page 94).
84 OpenOffice.org 3.x Calc Guide
Arrangement
Provides two choices: Bring Forward and Send Backward, of
which only one may be active for some items. Use these choices to
arrange overlapping data series.
Title
Formats the titles of the chart and its axes.
Legend
Formats the location, borders, background, and type of the legend.
Axis
Formats the lines that create the chart as well as the font of the text
that appears on both the X and Y axes.
Grid
Formats the lines that create a grid for the chart.
Chart Wall, Chart Floor, Chart Area
Described in the following sections.
Chart Type
Changes what kind of chart is displayed and whether it is two- or
three-dimensional.
Data Ranges
Explained on page 76 (Figure 55 and Figure 56).
3D View
Formats 3D charts (see page 87).
Note
Chart Floor and 3D View are only available for a 3D chart.
These options are unavailable (grayed out) if a 2D chart is
selected.
In most cases you need to select the exact element you want to format.
Sometimes this can be tricky to do with the mouse, if the chart has
many elements, especially if some of them are small or overlapping. If
you have Tooltips turned on (in Tools > Options > OpenOffice.org >
General > Help, select Tips), then as you move the mouse over each
element, its name appears in the Tooltip. Once you have selected one
element, you can press Tab to move through the other elements until
you find the one you want. The name of the selected element appears
in the Status Bar.
Chapter 3 Creating Charts and Graphs 85
Moving chart elements
You may wish to move or resize individual elements of a chart,
independent of other chart elements. For example, you may wish to
move the legend to a different place. Pie charts allow moving of
individual wedges of the pie (in addition to the choice of “exploding”
the entire pie).
1) Double-click the chart so that it is enclosed by a gray border.
2) Double-click any of the elements—the title, the legend, or the
chart graphic. Click and drag to move the element. If the element
is already selected, then move the pointer over the element to get
the move icon (small hand), then click, drag and move the
element.
3) Release the mouse button when the element is in the desired
position.
Note
If your graphic is 3D, round red handles appear which control
the three-dimensional angle of the graphic. You cannot resize or
reposition the graphic while the round red handles are showing.
With the round red handles showing, Shift + Click to get the
green resizing handles. You can now resize and reposition your
3D chart graphic. See the following tip.
Tip
You can resize the chart graphic using its green resizing handles
(Shift + Click, then drag a corner handle to maintain the
proportions). However, you cannot resize the title or the key.
Changing the chart area background
The chart area is the area surrounding the chart graphic, including the
(optional) main title and key.
1) Double-click the chart so that it is enclosed by a gray border.
2) Choose Format > Chart Area.
3) On the Chart Area dialog (Figure 63), choose the desired format
settings.
On the Area tab, you can change the color, or choose a hatch pattern,
bitmap or some preset gradients. Click on the drop-down box to see
the options. Patterns are probably more useful than color if you have to
print out your chart in black and white.
You can also use the Transparency tab to change the area’s
transparency. If you used a preset gradient from the Area tab, you can
see the different parameters of which it is composed.
86 OpenOffice.org 3.x Calc Guide
Figure 63: Chart Area dialog
Changing the chart graphic background
The chart wall is the area that contains the chart graphic.
1) Double-click the chart so that it is enclosed by a gray border.
2) Choose Format > Chart Wall. The Chart Wall dialog has the
same formatting options as described in “Changing the chart area
background” above.
3) Choose your settings and click OK.
Changing colors
If you need a different color scheme from the default for the charts in
all your documents, go to Tools > Options > Charts > Default
Colors, which has a much wider range of colors to choose from.
Changes made in this dialog affect the default chart colors for any
chart you make in future.
Formatting 3D charts
Use Format > 3D View to fine tune 3D charts. The 3D View dialog has
three pages, where you can change the perspective of the chart,
whether the chart uses the simple or realistic schemes, or your own
custom scheme, and the illumination which controls where the
shadows will fall.
Chapter 3 Creating Charts and Graphs 87
Rotation and perspective
To rotate a 3D chart or view it in perspective, enter the required values
on the Perspective page of the 3D View dialog. You can also rotate 3D
charts interactively; see page 90.
Figure 64: Rotating a chart
Some hints for using the Perspective page:
Set all angles to 0 for a front view of the chart. Pie charts and
donut charts are shown as circles.
With Right-angled axes enabled, you can rotate the chart contents
only in the X and Y direction; that is, parallel to the chart borders.
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.
The rotations are applied in the following order: x first, then y,
and z last.
When shading is enabled and you rotate a chart, the lights are
rotated as if they are fixed to the chart.
The rotation axes always relate to the page, not to the chart’s
axes. This is different from some other chart programs.
Select the Perspective option to view the chart in central
perspective as through a camera lens instead of using a parallel
projection.
Set the focus length with the spin button or type a number in the
box. 100% gives a perspective view where a far edge in the chart
looks approximately half as big as a near edge.
88 OpenOffice.org 3.x Calc Guide
Appearance
Use the Appearance page to modify some aspects of a 3D chart’s
appearance.
Figure 65: Modifying appearance of 3D chart
Select a scheme from the list box. When you select a scheme, the
options and the light sources are set accordingly. If you select or
deselect a combination of options that is not given by the Realistic or
Simple schemes, you create a Custom scheme.
Select Shading to use the Gouraud method for rendering the surface.
Otherwise, a flat method is used. The flat method sets a single color
and brightness for each polygon. The edges are visible, soft gradients
and spot lights are not possible. The Gouraud method applies gradients
for a smoother, more realistic look. Refer to the Draw Guide for more
details on shading.
Select Object Borders to draw lines along the edges.
Select Rounded Edges to smooth the edges of box shapes. In some
cases this option is not available.
Illumination
Use the Illumination page (Figure 66) to set the light sources for the
3D view. Refer to the Draw Guide for more details on setting the
illumination.
Click any of the eight buttons to switch a directed light source on or
off. By default, the second light source is switched on. It is the first of
Chapter 3 Creating Charts and Graphs 89
seven normal, uniform light sources. The first light source projects a
specular light with highlights.
For the selected light source, you can then choose a color and intensity
in the list just below the eight buttons. The brightness values of all
lights are added, so use dark colors when you enable multiple lights.
Figure 66: Setting the illumination
Each light source always points at the middle of the object initially. To
change the position of the light source, use the small preview inside
this page. It has two sliders to set the vertical and horizontal position
of the selected light source.
The button in the corner of the small preview switches the internal
illumination model between a sphere and a cube.
Use the Ambient light list to define the ambient light which shines with
a uniform intensity from all directions.
Rotating 3D charts interactively
In addition to using the Perspective page of the 3D View dialog to
rotate 3D charts, you can also rotate them interactively.
Select the Chart Wall, then hover the mouse pointer over a corner
handle or the rotation symbol found somewhere on the chart. The
cursor changes to a rotation icon.
Press and hold the left mouse button and drag the corner in the
direction you wish. A dashed outline of the chart is visible while you
drag, to help you see how the result will look.
90 OpenOffice.org 3.x Calc Guide
Formatting the chart elements
Depending on the purpose of your document, for example a screen
presentation or a printed document for a black and white publication,
you might wish to use more detailed control over the different chart
elements to give you what you need.
To format an element, left-click on the element that you wish to
change, for example one of the axes. The element will be highlighted
with green squares. Then, right-click and choose an item from the
context menu. Each chart element has its own selection of items. In the
next few sections we explore some of the options.
Formatting axes and inserting grids
Sometimes you need to have a special scale for one of the axes of your
chart, or you need smaller grid intervals, or you want to change the
formating of the labels on the axis. After highlighting the axis you wish
to change, right-click and choose one of the items from the pop-up
menu.
Choosing Format Axis opens the dialog shown in (Figure 67). On the
Scale tab, you can choose a logarithmic or linear scale (default), how
many marks you need on the line, where the marks are to appear, and
the increments (intervals) of the scale. You must first deselect the
Automatic option in order to modify the value for any scale.
On the Label tab (Figure 68), you can choose whether to show or hide
the labels and how to handle them when they won’t all fit neatly into
one row (for example, if the words are too long).
Not shown here are the tab with options for choosing a font,
formatting the lines, and positioning the elements of the line and
interval marks.
Figure 67: Formatting axis scales
Chapter 3 Creating Charts and Graphs 91
Figure 68: Formatting axis labels
Formating data labels
You can choose properties for the labels of the data series. Carefully
click on the chart element, then right-click and choose the property
you want to change. This opens a dialog with several tabs where you
can change the color of the label text, the size of the font, and other
attributes. The Label tab is shown in Figure 68.
On the Data Labels tab, you can choose whether to:
Show the labels as text
Show numeric values as a percentage or a number
Include the legend box as part of the label
These choices are the same as those shown in Figure 60 on page 81.
The text for labels is taken from the column labels and it cannot be
changed here. If the text needs to be abbreviated, or if it did not label
your graph as you were expecting, you need to change it in the original
data table.
Choosing and formatting symbols
In line and scatter charts the symbols representing the points can be
changed to a different symbol shape or color through the object
properties dialog. Select the data series you wish to change, right-
click, and choose Format > Data Series from the context menu.
On the Line tab of the Data Series dialog, in the Icon section, choose
from the drop-down list Select > Symbols. Here you can choose no
symbol, a symbol from an inbuilt selection, a more exciting range from
92 OpenOffice.org 3.x Calc Guide
the gallery, or if you have pictures you need to use instead, you can
insert them using Select > From file.
Figure 69: Symbol selection
Resizing and moving the chart
You can resize or move all elements of a chart at the same time, in two
ways: interactively, or by using the Position and Size dialog. You may
wish to use a combination of both methods: interactive for quick and
easy change, then the dialog for precise sizing and positioning.
To resize a chart interactively:
1) Click once on the chart to select it. Green sizing handles appear
around the chart.
2) To increase or decrease the size of the chart, click and drag one
of the markers in one of the four corners of the chart. To maintain
the correct ratio of the sides, hold the Shift key down while you
click and drag.
To move a chart interactively:
1) Click on the chart to select it. Green sizing handles appear around
the chart.
2) Hover the mouse pointer anywhere over the chart. When it
changes to the move icon, click and drag the chart to its new
location.
3) Release the mouse button when the element is in the desired
position.
Chapter 3 Creating Charts and Graphs 93
Using the Position and Size dialog
To resize or move a chart using the Position and Size dialog:
1) Click on the chart to select it. Green sizing handles appear around
the chart.
2) Right-click and choose Position and Size from the pop-up menu.
3) Make your choices on this dialog.
Figure 70: Defining the position and size of an object
Position is defined as an X,Y coordinate relative to a fixed point (the
base point), typically located at the upper left of the document. You can
temporarily change this base point to make positioning or
dimensioning simpler (click on the spot corresponding to the location
of the base point in either of the two selection windows on the right
side of the dialogupper for positioning or lower for dimensioning).
The possible base point positions correspond to the handles on the
selection frame plus a central point. The change in position lasts only
as long as you have the dialog open; when you close this dialog, Calc
resets the base point to the standard position.
Tip The Keep ratio option is very useful. Select it to keep the ratio
of width to height fixed while you change the size of an object.
Either or both the size and position can be protected so that they
cannot be accidentally changed. Select the appropriate options.
94 OpenOffice.org 3.x Calc Guide
Tip If you cannot move an object, check to see if its position is
protected.
Gallery of chart types
Its important to remember that while your data can be presented with
a number of different charts, the message you want to convey to your
audience dictates the chart you ultimately use. The following sections
present examples of the types of charts that Calc provides, with some
of the tweaks that each sort can have and some notes as to what
purpose you might have for that chart type. For details, see the Help.
Column charts
Column charts are commonly used for data that shows trends over
time. They are best for charts that have a relatively small number of
data points. (For large time series a line chart would be better.) It is
the default chart type, as it is one of the most useful charts and the
easiest to understand.
Bar charts
Figure 71: Three bar graph treatments.
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 comparing the popularity of a few products in a marketplace.
The first chart is achieved quite simply by using the chart wizard
with Insert > Grids, deselecting y-axis, and using Insert >
Mean Value Lines.
Chapter 3 Creating Charts and Graphs 95
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 get rid of the legend and put
labels showing the names of the companies on the axis instead.
We also changed the colors to a hatch pattern.
Pie charts
Pie charts are excellent when you need to compare proportions. For
example, comparisons of departmental spending: what the department
spent on different items or what different departments spent. They
work best with smaller numbers of values, about half a dozen; more
than this and the visual impact begins to fade.
As the Chart Wizard guesses the series that you wish to include in your
pie chart, you might need to adjust this initially on the Wizard’s Data
Ranges page if you know you want a pie chart, or by using the Format
> Data Ranges > Data Series dialog.
You can do some interesting things with a pie chart, especially if you
make it into a 3D chart. It can then be tilted, given shadows, and
generally turned into a work of art. Just don’t clutter it so much that
your message is lost, and be careful that tilting does not distort the
relatively size of the segments.
You can choose in the Chart Wizard to explode the pie chart, but this is
an all or nothing option. If your aim is to accentuate one piece of the
pie, you can separate out one piece by carefully highlighting it after
you have finished with the Chart Wizard, and dragging it out of the
group. When you do this you might need to enlarge the chart area
again to regain the original size of the pieces.
Figure 72: Pie charts
The effects achieved in Figure 72 are explained below:
2D pie chart with one part of the pie exploded: Choose Insert >
Legend and deselect the Display legend box. Choose Insert >
96 OpenOffice.org 3.x Calc Guide
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 (the piece will have nine green
highlight squares to mark it), and then drag it 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
size.
3D pie chart with realistic schema and illumination: Choose
Format > 3D view > Illumination where you can change the
direction of the light, the color of the ambient light, and the depth
of the shade. We also adjusted the 3D angle of the disc in the
Perspective dialog on the same set of tabs.
The chart updates as you make changes, so you can immediately
see the effects.
If you want to separate out one of the pieces, click on it carefully;
you should see a wire frame highlight. Drag it out with the mouse
and then, if necessary, increase the size of the chart wall.
3D pie chart with different fill effects in each portion of the pie:
Choose Insert > Data labels and select show value as
percentage. Then carefully select each of the pieces so that it has
a wire frame highlight and right-click to get the object properties
dialog; choose the Area tab. For one we chose a bitmap, for
another a gradient and for the third we used the Transparency
tab and adjusted the transparency to 50%.
Area charts
An area chart is a version of a line or column graph. It may be useful
where you wish to emphasize volume of change. Area charts have a
greater visual impact than a line chart, but the data you use will make
a difference.
Figure 73: Area charts—the good, the bad, and the ugly
Chapter 3 Creating Charts and Graphs 97
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 setting up the basic chart using the Chart Wizard, do this:
Right-click on the Y axis and choose Delete Major Grid. As the
data overlaps, some of it is missing behind the first data series.
This is not what you want. A better solution is shown in Chart 2.
After deselecting the Y axis grid, right-click on each data series in
turn and choose Format Data Series. On the Transparency tab,
set Transparency to 50%. The transparency makes it easy to see
the data hidden behind the first data series. Now, right-click on
the X axis and choose Format Axis. On the Label tab, choose Tile
in the Order section and set the Text orientation to 55 degrees.
This places the long labels at an angle.
To create the third variation, after doing the steps above, right-
click and choose Chart Type. Choose the 3D Look option and
select Realistic from the drop-down list. We also twisted the
chart area around and gave the chart wall 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 difficult to see the
point you are trying to make with the data.
Other ways of visualizing the same data series are represented by the
stacked area chart or the percentage stacked area chart. The first does
what it says: each number of each series is added to the others so that
it shows an overall volume but not a comparison of the data. The
percentage stacked chart shows each value in the series as a part of
the whole. For example in June all three values are added together and
that number represents 100%. The individual values are a percentage
of that. Many charts have varieties which have this option.
Figure 74: Stacked and percentage stacked area charts
98 OpenOffice.org 3.x Calc Guide
Line charts
A 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 changes
over time where you want to emphasize continuity. On line charts, the
x-axis is ideal to represent time series data.
Things to do with lines: thicken them, make them 3D, smooth the
contours, just use points.
3D lines confuse the viewer, so just using a thicker line often works
better.
Figure 75: Line charts
Scatter or XY charts
Scatter charts are great for visualizing data that you have not had time
to analyze, and they may be the best for data when you have a constant
value against which to compare the data; for example, weather data,
reactions under different acidity levels, conditions at altitude, or any
data which matches two series of numeric data. In contrast to line
charts, the x-axis are the left to right labels, which usually indicate a
time series.
Scatter charts may surprise those unfamiliar with how they work.
While constructing the chart, if you choose Data Range > Data
series in rows, the first row of data represents the x-axis. The rest of
the rows of data are then compared against the first row data. Figure
76 shows a comparison of three currencies with the Japanese Yen.
Even though the table presents the monthly series, the chart does not.
In fact the Japanese Yen does not appear; it is merely used as the
constant series that all the other data series are compared against.
Chapter 3 Creating Charts and Graphs 99
Figure 76: A particularly volatile time in the world currency market.
Bubble charts
A bubble chart is a variation of a scatter chart in which the data points
are replaced with bubbles. It shows the relations of three variables in
two dimensions. Two variables are used for the position on the X-axis
and Y-axis, while the third is shown as the relative size of each bubble.
One or more data series can be included in a single chart.
Bubble charts are often used to present financial data. The data series
dialog for a bubble chart has an entry to define the data range for the
bubbles and their sizes.
Figure 77: Bubble chart showing three data series
100 OpenOffice.org 3.x Calc Guide
Net charts
A net chart is similar to a polar or radar chart. They are useful for
comparing data that are not time series, but show different
circumstances, such as variables in a scientific experiment or
direction. The poles of the net chart are equivalent to the y-axes of
other charts. Generally, between three and eight axes are best; any
more and this type of chart becomes confusing. Before and after values
can be plotted on the same chart, or perhaps expected and real results,
so that differences can be compared.
Types of net charts:
Figure 78 - Part 1: Plain net chart without grids and with just
points, no lines.
Figure 79 - Part 2: Net chart with lines, points and grid. Axes
colors and labels changed. Chart area color = gradient. Points
changed to fancy 3D ones.
Figure 78: Two net diagrams showing totally fabricated data from
totally fictional experiments.
Other varieties of net chart show the data series as stacked numbers
or stacked percentages. The series can also be filled with a color.
Partial transparency is often best for showing all the series.
Chapter 3 Creating Charts and Graphs 101
green
0
2
4
6
8
10
12
Coloured light on mood
Figure 79: Filled net or radar chart
Stock charts
A stock chart is a specialized column graph specifically for stocks and
shares. You can choose traditional lines, candlestick, and two-column
type charts. The data required for these charts is quite specialized,
with series for opening price, closing price, and high and low prices. Of
course the x-axis represents a time series.
When you set up a stock chart in the Chart Wizard, the Data Series
dialog is very important. You need to tell it which series is for the
opening price, closing price, high and low price of the stock and so on.
Otherwise the chart may be indecipherable. The sample table for this
chapter needed to be changed to fit the data series.
Figure 80: Adjusting data series for stock charts.
102 OpenOffice.org 3.x Calc Guide
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, and red and blue in the traditional line chart.
Column and line charts
A column and line chart is a combination of two other chart types. It is
useful for combining two distinct but related data series, for example
sales over time (column) and the profit margin trends (line).
You can choose the number of columns and lines in the Chart Wizard.
So for example you might have two columns with two lines to
represent two product lines with the sales figures and profit margins of
both.
Figure 81: Column and line chart
This chart has manufacturing cost and profit data for two products,
over a period of time (six months in 2007). To create this chart, first
highlight the table and start the Chart Wizard. Choose Column and
Line chart type with two lines and the data series in rows. Then give it
a title to highlight the aspect you want to show. The lines are different
colors at this stage and don’t reflect the product relationships. When
you finish with the Chart Wizard, highlight the chart, click on the line,
right-click and chose Format Data Series.
On this tab there are a few things to change: The colors should match
the products. So both Ark Manufacturing and profit are blue and Prall
is red. The lines need to be more noticeable so make the lines thicker
by increasing the width to 0.08.
Chapter 3 Creating Charts and Graphs 103
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 one of the preset gradient patterns and make it
lighter by going to the Transparency tab and making the gradient 50%
transparent.
To make the chart look cleaner without the grid, go to Insert > Grids
and deselect the X-axis option.
104 OpenOffice.org 3.x Calc Guide
Chapter 4
Using Styles and
Templates in Calc
Bringing 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
company’s logo and address at the top of the page. New spreadsheets
created from this template will all have your company’s logo and
address on the first page.
Templates can contain anything that regular documents can contain,
such as text, graphics, styles, and user-specific setup information such
as measurement units, language, the default printer, and toolbar and
menu customization.
All documents—text, spreadsheets, presentations, drawings—in
OpenOffice.org are based on templates. You can create, or download
and install, as many templates as you wish. When you start a new
spreadsheet without choosing a specific template (as described in
“Creating a spreadsheet from a template“ on page 119), the new
spreadsheet is based on the default template for spreadsheets. If you
have not specified a default template, OOo uses the blank spreadsheet
template that is installed with OOo. See “Setting a default template”
on page 124.
This chapter describes how to create, use, modify, and organize
templates. We begin by describing styles and their use, as styles are a
major component of templates.
What are styles?
A style is a set of formats that you can apply to selected elements in a
document to quickly change their appearance. When you apply a style,
you apply a whole group of formats at the same time.
Many people manually format spreadsheet cells and pages without
paying any attention to styles. They are used to formatting documents
according to physical attributes. For example, for the contents of a cell
you might specify the font family, font size, and any formatting such as
bold or italic.
Styles are logical attributes. Using styles means that you stop saying
“font size 14pt, Times New Roman, bold, centered”, and you start
saying “Title” because you have defined the “Title” style to have those
characteristics. In other words, styles means that you shift the
emphasis from what the text (or page, or other element) looks like, to
what the text is.
106 OpenOffice.org 3.x Calc Guide
Styles help improve consistency in a document and can greatly speed
up formatting. They also make major formatting changes easy. For
example, you may decide to change the appearance of all subtotals in
your spreadsheet to be 10 pt. Arial instead of 8 pt. Times New Roman
after you have created a 15-page spreadsheet; you can change all of
the subtotals in the document by simply changing the properties for
the subtotal style.
Page styles assist with printing, so you don’t need to define margins,
headers and footers, and other printing attributes each time you print
a spreadsheet.
This chapter describes the styles OOo Calc offers, what they might be
used for, and how to apply styles, change existing styles, and create
new styles.
Types of styles in Calc
While some components of OOo offer many style types, Calc offers only
two:
Cell styles include fonts, alignment, borders, background, number
formats (for example, currency, date, number), and cell
protection.
Page styles include margins, headers and footers, borders and
backgrounds, and the sequence for printing sheets. The page size,
orientation, and other attributes of a page style apply only when a
spreadsheet is printed; they are not displayed onscreen.
Cell styles
Similar 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 that
cell will follow the formatting rules of the style. Five cell styles are
supplied with OOo: Default, Heading, Heading1, Result, and Result2.
Initially, the styles are configured so that if you change the font family
of Default, then all of the other styles will change to match. We will
discuss how to set this up in “Creating new (custom) styles on page
116. The five standard styles can be seen in use in Figure 82.
Chapter 4 Using Styles and Templates in Calc 107
Figure 82: Calc cell style types
Page styles
Page styles in Calc are applied to sheets. Although one sheet may print
on several pages (pieces of paper), only one page style can be applied
to a sheet. If a spreadsheet file contains more than one sheet, the
different sheets can have different page styles applied to them. So, for
example, a spreadsheet might contain one sheet to be printed in
landscape orientation (using the Default page style) and another sheet
to be printed in portrait orientation (using the Report page style).
Two page styles are supplied with Calc: Default and Report. The major
difference between these two styles is that Report is portrait-oriented
and Default is landscape-oriented. You can adjust many settings using
page styles. You can also define as many page styles as you wish.
Because spreadsheets are primarily used onscreen and not printed,
Calc does not display the page style on the screen. If you want a
spreadsheet to fit on a certain page size, you have to carefully control
the column width and row height, with only File > Page Preview to
guide you.
Despite this limitation, it’s well worth defining page styles for any
spreadsheets that you are likely to print. Otherwise, if a need for
printing does arise, you may lose time to trial and error.
Accessing styles
The main way to access styles is through the Styles and Formatting
window (shown in Figure 83). You can open this window in several
ways.
Keyboard: Press the F11 key.
Menu: Choose Format > Styles and Formatting.
108 OpenOffice.org 3.x Calc Guide
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 Calc window. To dock or undock the window, hold down the
Ctrl key and double-click a gray part of the window next to the icons at
the top.
The first button on the top left of the window, , is for cell styles and
the second, , is for page styles.
Figure 83: Styles and Formatting window
Applying cell styles
Calc provides several ways to apply cell styles:
Using the Styles and Formatting window
Using Fill Format mode
Using the Apply Style list
Assigning styles to shortcut keys
Using the Styles and Formatting window
1) Ensure the Styles and Formatting window is open by pressing
F11 or by selecting Format > Styles and Formatting. Choose
the Cell Styles list by clicking the icon.
2) Highlight the cell or group of cells to which the styles should be
applied.
3) Double-click on the cell style name.
Chapter 4 Using Styles and Templates in Calc 109
Using Fill Format mode
This method is quite useful when you need to apply the same style to
many scattered cells.
1) Open the Styles and Formatting window and select the style you
want to apply.
2) Click the Fill Format mode icon . The mouse pointer changes
to this icon.
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 close the Styles and Formatting window.
Caution When this mode is active, a right-click anywhere in the
document undoes the last Fill Format action. Be careful not to
accidentally right-click and thus undo actions you want to
keep.
Using the Apply Style list
You can also add an Apply Style drop-down list to the Formatting
toolbar and select a style from the list to apply it to the selected cells:
1) Click the down-arrow at the right-hand end of the Formatting
toolbar. On the drop-down menu, click Visible Buttons.
Figure 84: Adding an Apply Style list to the Formatting toolbar
110 OpenOffice.org 3.x Calc Guide
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 the Font Name list.
Assigning styles to shortcut keys
You can create keyboard shortcuts to apply commonly-used cell or
page styles, including custom styles that you have created. See
Chapter 14 (Setting up and Customizing Calc) for instructions.
Applying page styles
1) Select the sheet to be styled (click on its sheet tab at the bottom
of the screen).
2) In the Styles and Formatting window, choose the Page Styles list
by clicking the icon. Double-click on the required page style.
To find out which page style is in use for a selected sheet, look in
status bar.
Figure 85: Status bar showing location of page style
information below the sheet tabs.
Modifying styles
To modify a style, right-click on its name in the Styles and Formatting
window and choose Modify. Make the changes in the Style dialog and
click OK to save the changes.
You can also modify a current cell style by selecting an already
formatted cell and clicking the Update Style button on the top right
hand corner of the Styles and Formatting window.
Style organizer
Right click on the name of a style in the Styles and Formatting window
and click Modify to open a Style dialog similar to the one shown in
Figure 86.
Chapter 4 Using Styles and Templates in Calc 111
The Style dialog has several tabs. The Organizer tab, shown in Figure
86 for cell styles, is found in all components of OOo. It provides basic
information about the style. The Organizer tab for page styles is similar
to the one shown for cell styles.
Figure 86: Organizer tab of Cell Style dialog
Name
This 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 with
This option is only available for cell styles; page styles cannot be
linked. If you link cell styles, then when you change the base style
(for example, by changing the font from Times to Helvetica), all the
linked styles will change as well. Sometimes this is exactly what you
want; other times you do not want the changes to apply to all the
linked styles. It pays to plan ahead.
For example, you can make a new style called red, in which the only
change you want to make is for the cell text to be red. In order to
make sure that the rest of the text characteristics are the same as
the default style, you can link red with default. Then, any changes
you make to default will be automatically applied to red.
Category
In Calc, the only option in this drop-down box is Custom styles.
112 OpenOffice.org 3.x Calc Guide
Cell style options
When editing or creating cell styles, you can set several options, which
are similar to those for directly formatting cells. A more detailed
coverage of cell formatting is given in Chapter 2 (Entering, Editing,
and Formatting Data). A brief summary is provided here.
Numbers
On the Numbers tab, you can control the behavior of the data in a cell
with this style. This includes specifying the type of data, the number of
decimal places, and the language.
Font
Use the Font tab to choose the font for the cell’s contents..
Font effects
The Font Effects tab offers more font options including underlining,
strikethrough, and color.
Alignment
Use the Alignment tab to set the horizontal and vertical alignment for
the data in the cells, and rotate the text.
Borders
Use the Borders tab to set the borders for the cells, along with a
shadow.
Background
Use the Background tab to choose the background color for a cell.
Cell protection
Use the Cell Protection options to protect cells against certain types of
editing.
Page style options
Several of the page style options are described in more detail in
Chapter 6 (Printing, Exporting, and E-mailing), because manually
formatting a sheet at print time (using Format > Page) actually
modifies the page style.
Chapter 4 Using Styles and Templates in Calc 113
Page
Use 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: Page tab
Paper format
Here you can set a generic paper type to be used. Letter or A4 are
most common, but you can also use legal, tabloid, envelope sizes, or
user-defined paper types. You can also define the orientation of the
page and which print tray for the paper to come from (if your printer
has more than one tray).
Margins
Here you can set the margins for the page.
Layout settings: Page layout
Here you can specify whether to apply the formatting to right (odd)
pages only, left (even) pages only, or both right and left pages that
use the current page style.
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.
114 OpenOffice.org 3.x Calc Guide
Layout settings: Format
This area specifies the page numbering style for this page style.
Layout settings: Table alignment
This option specifies the alignment options for the cells on a printed
page, either horizontal or vertical.
Borders
The Border and Background tabs for pages duplicate the tabs of the
same name on cell styles, and are over-ridden by the cell style or
manual settings. You may choose to ignore the Border and Background
tabs altogether in page styles. Both tabs are illustrated with helpful
diagrams.
Similar to formatting a cell style, use the Borders tab to choose
whether the page should have borders, how large the borders should
be, and how far the text will be from the borders.
Background
Use this tab to specify the background for this page style. You can
apply either a solid color or a picture as a background.
Header
Use this tab to design and apply the header for this page style. For
more detailed instructions on how to format the header, see Chapter 6
(Printing, Exporting, and E-mailing).
Footer
Use this tab to design and apply the footer for this page style. For more
detailed instructions on how to format the footer, see Chapter 6.
Sheet
By far the most important settings for Calc page styles are on the
Sheet tab. Although the Sheet tab includes an option that sets the first
page option, most of its settings involve exactly how your spreadsheet
will print. See Chapter 6.
Chapter 4 Using Styles and Templates in Calc 115
Creating new (custom) styles
You may want to add some new styles. You can do this in two ways:
Creating a new style using the Style dialog
Creating a new style from a selection
Note
New styles apply only to this document; they are not be saved
in the template. To save new styles in a template, see
“Copying and moving styles“ on page 117 and “Creating a
template” on page 120.
Creating a new style using the Style dialog
To create a new style using the Style dialog, right-click in the Styles
and Formatting window and choose New from the pop-up menu.
(Cell styles only) If you want your new style to be linked with an
existing style, first select that style and then right-click and choose
New.
If you link styles, then when you change the base style (for example, by
changing the font from Times to Helvetica), all the linked styles will
change as well. Sometimes this is exactly what you want; other times
you do not want the changes to apply to all the linked styles. It pays to
plan ahead.
The dialogs and choices are the same for defining new styles and for
modifying existing styles.
Creating a new style from a selection
You can create a new cell style by copying an existing manual format.
1) Open the Styles and Formatting window and choose the type of
style you want to create.
2) In the document, select the formatted cell that you want to save
as a style.
3) In the Styles and Formatting window, click on the New Style
from Selection icon .
4) In the Create Style dialog, type a name for the new style. The list
shows the names of existing custom styles of the selected type.
Click OK to save the new style.
116 OpenOffice.org 3.x Calc Guide
Figure 88: Naming a new style created
from a selection.
Creating a new style by dragging and dropping
Select a cell and drag it to the Styles and Formatting window.
Copying and moving styles
Occasionally you may want to copy a style from one spreadsheet to
another, or between a spreadsheet and a template, instead of
recreating it in the second spreadsheet. You can do this using the
Template Management dialog.
1) Click File > Templates > Organize.
2) In the Template Management dialog (Figure 89), set the lists at
the bottom to either Templates or Documents, as needed. The
default is Templates on the left and Documents on the right.
Tip
To copy styles from a file that is not open, click the File button.
When you return to this dialog, both lists show the selected file
as well as all the currently open documents.
3) Open the folders and find the templates from and to which you
want to copy. Double-click on the name of the template or
document, and then double-click the Styles icon to show the list of
individual styles (Figure 90).
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 receiving template
or document has many styles, you may not see any change unless
you scroll down in the list. When you are finished, click Close.
Chapter 4 Using Styles and Templates in Calc 117
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 Guide
Deleting styles
You cannot remove (delete) any of Calc’s predefined styles, even if you
are not using them.
You can remove any user-defined (custom) styles; but before you do,
you should make sure the styles are not in use. If an unwanted style is
in use, you will want to replace it with a substitute style.
Replacing styles (and then deleting the unwanted ones) can be very
useful if you are dealing with a spreadsheet that has been worked on
by several people.
To delete unwanted styles, right-click on them (one at a time) in the
Styles and Formatting window and click Delete on the pop-up menu.
Choose Yes in the confirmation pop-up.
Creating a spreadsheet from a template
To create a spreadsheet from a template:
1) From the main menu, choose File > New > Templates and
Documents. The Templates and Documents dialog opens. (See
Figure 91 for the Windows version of this dialog.)
2) In the box on the left, click the Templates icon if it is not already
selected. A list of template folders appears in the center box.
3) Double-click the folder that contains the template that you want
to use. A list of all the templates contained in that folder appears
in the center box.
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 the Preview icon. A preview of
the template appears in the box on the right.
To view the template’s properties, click the Document
Properties icon. The template’s properties appear in the box
on the right.
5) Click Open. The Templates and Documents dialog closes and a
new document based on the selected template opens in Calc. You
can then edit and save the new document just as you would any
other document.
Chapter 4 Using Styles and Templates in Calc 119
Figure 91: Templates and Documents dialog.
Creating a template
You can create a template from a document:
1) Open a new or existing document of the type you want to make
into a template (text document, spreadsheet, drawing,
presentation).
2) Add the content and styles that you want.
3) From the main menu, choose File > Templates > Save. The
Templates dialog opens (see Figure 92).
4) In the New template field, type a name for the new template.
5) In the Categories list, click the category to which you want to
assign the template. The category you choose has no effect on the
template itself; it is simply the folder in which you save the
template. Choosing an appropriate folder (category) makes it
easier to find the template when you want to use it. You may wish
to create a folder for Calc templates.
To learn more about template folders, see “Organizing templates
on page 126.
6) Click OK to save the new template.
120 OpenOffice.org 3.x Calc Guide
Figure 92: Saving a new template
Any settings that can be added to or modified in a document can be
saved in a template. For example, below are some of the settings
(although not a full list) that can be included in a Calc document and
then saved as a template for later use:
Printer settings: which printer, single sided / double sided, and
paper size, and so on
Cell and page styles to be used
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 might contain your company’s name, address and logo.
You can also save menu and toolbar customizations in templates; see
Chapter 14 (Setting up and Customizing Calc) for more information.
Editing a template
You can edit a template’s styles and content, and then, if you wish, you
can reapply the template’s styles to documents that were created from
that template. (Note that you can only reapply styles. You cannot
reapply content.)
To edit a template:
1) From the main menu, choose File > Templates > Organize. The
Template Management dialog opens.
Chapter 4 Using Styles and Templates in Calc 121
Figure 93: Template management dialog
2) In the box on the left, double-click the folder that contains the
template that you want to edit. A list of all the templates
contained in that folder appears underneath the folder name.
3) Select the template that you want to edit.
4) Click the Commands button and choose Edit from the drop-
down menu.
5) Edit the template just as you would any other document. To save
your changes, choose File > Save from the main menu.
Updating a spreadsheet from a changed template
The next time you open a spreadsheet that was created from the
changed template, the following message appears.
Figure 94: Update styles message
Click Update Styles to apply the template’s changed styles to the
spreadsheet. Click Keep Old Styles if you do not want to apply the
template’s changed styles to the spreadsheet (but see Caution notice
below).
122 OpenOffice.org 3.x Calc Guide
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 after changing the template it is based on.
You will not get another chance to update the styles from the
template, although you can use the macro given in the Note
below to re-enable this feature.
Note To re-enable updating from a template:
1) Use Tools > Macros > Organize Macros >
OpenOffice.org Basic. Select the document from the list,
click the +, and select Standard. If Standard has a + beside
it, click that and select a module.
2) Name the macro. For example, you could call it
FixDocument. If the Edit button is active, click it. If the Edit
button is not active, click New, type a module name in the
pop-up dialog, and click OK.
3) In the Basic window, enter the following:
Sub FixDocV3
' set UpdateFromTemplate
oDocSettings = ThisComponent.createInstance( _
"com.sun.star.document.Settings" )
oDocSettings.UpdateFromTemplate = True
End Sub 'FixDocV3
4) Click the Run BASIC icon, then close the Basic window.
5) Save the document.
Next time when you open this document you will have the
update from template feature back.
Adding templates using the Extension
Manager
The Extension Manager provides an easy way to install collections of
templates, graphics, macros, or other add-ins that have been
“packaged” into files with a .OXT extension. See Chapter 14 (Setting
Up and Customizing Calc) for more about the Extension Manager.
This Web page lists many of the available extensions:
http://extensions.services.openoffice.org/.
To install an extension, follow these steps:
1) Download an extension package and save it anywhere on your
computer.
Chapter 4 Using Styles and Templates in Calc 123
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 select the package of
templates you want to install and click Open. The package begins
installing. You may be asked to accept a license agreement.
4) When the package installation is complete, the templates are
available for use through File > New > Templates and
Documents and the extension is listed in the Extension Manager.
Figure 95: Newly-added package of templates.
Setting a default template
If you create a document by choosing File > New > Text Document
(or Spreadsheet, Presentation, or Drawing) from the main menu,
OOo creates the document from the Default template for that type of
document. You can, however, set a custom template to be the default.
You can reset the default later if you choose.
Setting a custom template as the default
You can set any template to be the default, as long as it is in one of the
folders displayed in the Template Management dialog.
124 OpenOffice.org 3.x Calc Guide
To set a custom template as the default:
1) From the main menu, choose File > Templates > Organize. The
Template Management dialog opens.
2) In the box on the left, select the folder containing the template
that you want to set as the default, then select the template.
3) Click the Commands button and choose Set As Default
Template from the drop-down menu.
The next time that you create a document by choosing File > New, the
document will be created from this template.
Although many important settings can be changed in the Options
dialog (see Chapter 14), for example default fonts and page size, more
advanced settings (such as page margins) can only be changed by
replacing the default template with a new one.
Resetting the default template
To re-enable OOo’s default template for a document type as the
default:
1) In the Template Management dialog, click any folder in the box on
the left.
2) Click the Commands button and choose Reset Default
Template > Spreadsheet from the drop-down menu.
The next time that you create a spreadsheet by choosing File > New,
it will be created from OOo’s default template for spreadsheets.
Associating a spreadsheet with a different
template
At times you might want to associate a spreadsheet with a different
template, or perhaps you’re working with a spreadsheet that did not
start from a template but you now want it associated with a template.
One of the major advantages of using templates is the ease of updating
styles in more than one document, as described on page 122. If you
update styles by copying them from a different template (as described
on page 117), the document has no association with the template from
which the styles were loaded—so you cannot use this method. What
you need to do is associate the document with the different template.
For best results, the names of styles should be the same in the existing
document and the new template. If they are not, you will need to use
Search and Replace to replace the old styles with new ones. See
Chapter 4 Using Styles and Templates in Calc 125
Chapter 2 (Entering, Editing, and Formatting Data) for more about
replacing styles using Find and Replace.
1) Use File > New > Templates and Documents. Choose the
template you want. A new file, based on the template, opens. If
the template has unwanted text or graphics in it, delete them.
2) Open the spreadsheet you want to change. (It opens in a new
window.) Press Control+A to select everything in the spreadsheet.
3) Switch to the window containing the blank spreadsheet created in
step 1, and paste the content into that spreadsheet.
4) Save the file under a new name.
Organizing templates
OOo can only use templates that are in OOo template folders. You can
create new OOo template folders and use them to organize your
templates, and import templates into those folders. For example, you
might have one template folder for report templates and another for
letter templates. You can also export templates.
To begin, choose File > Templates > Organize from the main menu.
The Template Management dialog opens.
Note
All the actions made by the Commands button in the Template
Management dialog can be made as well by right-clicking on
the templates or the folders.
Creating a template folder
To create a template folder:
1) In the Template Management dialog, click any folder.
2) Click the Commands button and choose New from the drop-
down menu. A new folder called Untitled appears.
3) Type a name for the new folder, and then press Enter. OOo saves
the folder with the name that you entered.
Deleting a template folder
You cannot delete template folders supplied with OOo or installed
using the Extension Manager; you can only delete template folders that
you have created.
126 OpenOffice.org 3.x Calc Guide
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 Delete from the drop-
down menu. A message box appears and asks you to confirm the
deletion. Click Yes.
Moving a template
To move a template from one template folder to another template
folder:
1) In the Template Management dialog, double-click the folder that
contains the template you want to move. A list of the templates
contained in that folder appears underneath the folder name.
2) Click the template that you want to move and drag it to the
desired folder. If you do not have the authority to delete templates
from the source folder, this action copies the template instead of
moving it.
Deleting a template
You cannot delete templates supplied with OOo or installed using the
Extension Manager; you can only delete templates that you have
created or imported.
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 templates
contained in that folder appears underneath the folder name.
2) Click the template that you want to delete.
3) Click the Commands button and choose Delete from the drop-
down menu. A message box appears and asks you to confirm the
deletion. Click Yes.
Importing a template
If the template that you want to use is in a different location, you must
import it into an OOo template folder.
To import a template into a template folder:
1) In the Template Management dialog, select the folder into which
you want to import the template.
Chapter 4 Using Styles and Templates in Calc 127
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 that you want to import and click
Open. The file browser window closes and the template appears
in the selected folder.
4) If you want, type a new name for the template, and then press
Enter.
Exporting a template
To export a template from a template folder to another location:
1) In the Template Management dialog, double-click the folder that
contains the template you want to export. A list of the templates
contained in that folder appears underneath the folder name.
2) Click the template that you want to export.
3) Click the Commands button and choose Export Template from
the drop-down menu. The Save As window opens.
4) Find the folder into which you want to export the template and
click Save.
128 OpenOffice.org 3.x Calc Guide
Chapter 5
Using Graphics in Calc
Graphics in Calc
Calc is often used to present data and make forecasts and predictions.
Graphics can turn an average document into a memorable one. Calc
can import various vector (line drawing) and raster (bitmap) file
formats. The most commonly used graphic formats are GIF, JPG, PNG,
and BMP. See the Help for a full list of the formats OOo can import.
Graphics in Calc are of three basic types:
Image files, such as photos, drawings, and scanned images
Diagrams created using OOo’s drawing tools
Charts and graphs created using OOo’s Chart facility
This chapter covers images and diagrams. Charts are described in
Chapter 3 (Creating Charts and Graphs).
Although using graphics in Calc is very similar to using graphics in any
other component of OOo, this chapter explains some of the differences
in their use. It also covers some of the more advanced graphics
functions and how they can further enhance your spreadsheet.
Note
The term graphics refers to both pictures and drawing objects.
Often the word images is used when referring to pictures and
other graphics that are not drawing objects.
Adding graphics (images)
Images (also called pictures in OOo), such as corporate logos and
photographs of people and products, are probably the most common
types of graphics added to a Calc document. They may be downloaded
from the Internet, scanned, or created with a graphics program; or
they may be photos taken with a digital camera.
Images can be inserted in four ways:
Using the Insert File dialog
By dragging and dropping a supported file
From the gallery
From the clipboard by copying and pasting
Inserting an image file
Perhaps the most common way to insert graphics is to use an existing
file.
130 OpenOffice.org 3.x Calc Guide
To insert an image from a file, use either of the following methods:
Insert Picture dialog
Drag and drop
Insert Picture dialog
1) Click in the location in the Calc document where you want the
image to appear. Do not worry too much about the exact
placement of the image at this stage; placement can be changed
easily as described in “Positioning graphics” on page 148.
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 dialog, navigate to the file to be inserted,
select it, and click Open.
Note
The picture is inserted into Calc floating above the cells and
anchored to the cell in which the cursor was placed. See
“Positioning graphics” on page 148 for more about positioning
and anchoring graphics.
Figure 96: Inserting a picture from a file
At the bottom of the dialog are two options, Preview and Link. Select
Preview to view a thumbnail of the selected image on the right, so you
can verify that you have the correct file. The Link option is discussed
on page 132. When the Link option is not selected, the picture is
embedded in the Calc document.
Chapter 5 Using Graphics in Calc 131
Note
Your Insert Picture dialog may look quite different from the one
shown here, depending on your operating system and your
choice in Tools > Options > OpenOffice.org > General of
whether to use the OOo Open and Save dialogs.
Drag and drop
1) Open a file browser window and locate the image you want to
insert.
2) Drag the image into the Calc document and drop it where you
want it to appear. A faint vertical line marks where the image will
be dropped. The picture will be anchored to the cell where it was
dropped.
This method always embeds (saves a copy of) the image file in the Calc
document.
Linking an image file
To create a link to the file containing the image instead of saving a
copy of the image in the Calc document, use the Insert picture dialog
and select the Link option. The image is then displayed in the
document, but when the document is saved, it contains only a
reference to the image file—not the image itself. The document and the
image remain as two separate files, which are merged temporarily only
when you open the document again.
Linking an image has two advantages and one disadvantage:
Advantage – Linking can reduce the size of the document when it
is saved, because the image file itself is not included. The file size
is usually not a problem on a modern computer with a reasonable
amount of memory, unless the document includes many large
graphics files. Calc can handle quite large files.
Advantage – You can modify the image file separately without
changing the document because the link to the file remains valid,
and the modified image will appear when you next open the
document. This can be a big advantage if you (or someone else,
perhaps, a graphic artist) is updating images.
Disadvantage – If you send the document to someone else, or
move it to a different computer, you must also send the image
files, or the receiver will not be able to see the linked images. You
need to keep track of the location of the images and make sure
the recipient knows where to put them on another machine, so
the Calc document can find them. For example, you might keep
132 OpenOffice.org 3.x Calc Guide
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 subfolder with the same name (under the folder
containing the Writer document).
Note
When inserting the same image several times in the document,
it might appear beneficial to create links. However, this is not
necessary, as OOo embeds in the document only one copy of
the image file. (Deleting one or more of the copies does not
affect the others.)
Embedding linked images
If you originally linked the images, you can easily embed (save in the
Calc document) one or more of them later if you wish. To do so:
1) Open the document in Calc.
2) Choose Edit > Links from the menu bar.
The Edit Links dialog shows all the linked files. In the Source file
list, select the files you want to change from linked to embedded.
3) Click the Break Link button.
4) Save the Calc document.
Figure 97: The Edit Links dialog
Note
Going the other way, from embedded to linked, is not so easy—
you must delete and reinsert each image, one at a time,
selecting the Link option when you do so.
Chapter 5 Using Graphics in Calc 133
Inserting an image from the clipboard
Using the clipboard, you can copy images into a Calc document from
another Calc document, from another component of OOo (Writer, Draw,
and so on), and from other programs.
To do this:
1) Open both the source document and the Calc document into
which you want to copy the image.
2) In the source document, select the image to be copied.
3) Press Control+C to copy the image to the clipboard.
4) Switch to the Calc window.
5) Click to place the cursor where the graphic is to be inserted.
6) Press Control+V to insert the image.
Caution If the application from which the graphic was copied is closed
before the graphic is pasted into Calc, the image stored on the
clipboard could be lost from the clipboard.
Inserting an image from the Gallery
The Gallery provides a convenient way to group reusable objects such
as graphics and sounds that you can insert into your documents.
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 containing more graphics. The Gallery is explained in more
detail in Chapter 11 (Graphics, the Gallery, and Fontwork) in the
Getting Started guide. For more about extensions, see Chapter 14
(Setting Up and Customizing Calc) in this book.
This section explains the basics of inserting a Gallery image into a Calc
document.
1) To open the Gallery (Figure 98), click on the Gallery icon
(located in the right side of the Standard toolbar) or choose Tools
> Gallery from the menu bar.
2) Navigate through the Gallery to find the desired picture.
3) To insert the picture, either right-click on the picture and choose
Insert > Copy or click and drag the picture from the Gallery into
the Calc document.
134 OpenOffice.org 3.x Calc Guide
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 the workspace. When the pointer changes to parallel lines with
arrows, click and drag downward. The workspace resizes in response.
Figure 98: Gallery in Calc
To expand the Gallery without affecting the workspace, undock it so it
floats over the workspace. To do so, hold down the Control key and
double-click on the upper part of the Gallery next to the View icons.
Double-click in the same area while holding down the Control key to
dock it again (restore it to its position over the workspace).
When the Gallery is docked, to hide it and view the full Calc
workspace, click the Hide/Show button in the middle of the thin bar
separating the Gallery from the workspace.
To close the Gallery, choose Tools > Gallery to uncheck the Gallery
entry, or click on the Gallery icon again.
Chapter 5 Using Graphics in Calc 135
Modifying images
When you insert a new image, you may need to modify it to suit the
document. This section describes the use of the Picture toolbar,
resizing, cropping, and a workaround for rotating a picture. Changes
made in Calc do not affect the original picture, whether it is embedded
or linked.
Calc provides many tools for working with images. These tools are
sufficient for most people’s everyday requirements. However, for
professional results it is generally better to use an image manipulation
program such as GIMP to modify images (for example, to crop, resize,
rotate, and change color values) and then insert the result into Calc.
GIMP is an open-source graphics program that can be downloaded
from http://www.gimp.org/downloads/.
Using the Picture toolbar
When you insert an image or select one already present in the
document, the Picture toolbar appears. You can set it to always be
present (View > Toolbars > Picture). Picture control buttons from
the Picture toolbar can also be added to the Standard Toolbar. See
Chapter 14 (Setting Up and Customizing Calc) for more information.
This toolbar can be either floating or docked. Figure 99 shows the
Picture toolbar when it is floating. A brief explanation of the tools is
given in Table 3. See the Draw Guide for a more detailed explanation.
Two other toolbars can be opened from this one: the Graphic Filter
toolbar, which can be torn off and placed elsewhere on the window,
and the Color toolbar, which opens as a separate floating toolbar.
From these three toolbars, you can apply small corrections to the
graphic or obtain special effects.
Figure 99: The Picture Toolbar
136 OpenOffice.org 3.x Calc Guide
Table 3: Picture toolbar functions (from left to right)
Icon Name Behavior
From File Use of this icon is described in
“Inserting an image file” on page 130.
Filter Displays the Graphic Filter toolbar. See
page 138.
Graphics Mode Provides several color modes in the
drop-down list. See page 138.
Color Opens the Color toolbar, described on
page 139.
Transparency Sets the transparency of the selected
image. See page 139.
Line Adjusts the border style of the selected
image.
Area Fills an area with the selected color or
pattern.
Shadow Adds a drop shadow to the edges of the
picture.
Crop Opens the Crop dialog, where you can
remove a selected part of the picture.
See page 140.
Anchor Toggles between anchoring the image
to the cell or to the page. See page 150.
Bring to Front Brings the selected image to the front
of the stack. See page 149.
Send to Back Pushes the selected image to the rear of
the stack. See page 149.
To Foreground /
Background
Allows image to float in the foreground
or makes it part of the background
(behind the cells). See page 149.
Alignment If two or more pictures are selected,
adjusts the horizontal and vertical
alignment of the pictures in relation to
each other. See page 151.
Chapter 5 Using Graphics in Calc 137
Choosing a graphics mode
You can change color images to grayscale by selecting the image and
then selecting Grayscale from the Graphics mode list.
Table 4: Graphics modes
Graphics mode Behavior
Default Keeps the picture the same as it was inserted.
Grayscale Shows the picture in gradual shades of gray.
Black / White Converts the picture into a monochromatic black and
white image.
Watermark Makes the picture into a watermark that blends into
the background.
Using graphic filters
Click the Filter icon to display the Graphic Filter
toolbar, which provides options for applying basic
photographic and effect filters to images from within
Calc. To “tear off” this toolbar and place it anywhere on
the screen, click on the three parallel lines and drag it
away.
Table 5: Graphic filters and their effects
Icon Name Behavior
Invert Inverts the colors in the picture like a negative.
Smooth Applies a Gaussian Blur to the image which softens
edges.
Sharpen Sharpens the image.
Remove
Noise
Applies crude noise reduction.
Solarization Reverses a portion of the tones, then produces
pronounced outlines of the highlights.
Aging Applies a Sepia filter.
Posterize Opens a dialog to determine the number of poster
colors. This effect is based on the reduction of the
number of colors. It makes photos look like
paintings.
138 OpenOffice.org 3.x Calc Guide
Icon Name Behavior
Pop Art Applies a Pop Art style to the image.
Charcoal
Sketch
Applies a Charcoal Sketch look to the image.
Relief Displays a dialog for creating reliefs. The position of
the imaginary light source that determines the type
of shadow can be chosen.
Mosaic Joins small groups of pixels into rectangular areas of
the same color. The larger the individual rectangles
are, the fewer details the graphic image has.
Caution
Applying OOo picture filters to any image consecutively will
progressively degrade the quality of the image. The picture
filters used in Calc utilize what is known as a Destructive
Editing algorithm, whereby each filter is applied to the image
immediately, changing the original data of the image.
Successive transformations result in less and less original data
remaining, thus compromising the quality of the inserted
picture. While this might be acceptable for use in simple
documents, it is still recommended that dedicated photo or
image editing software be used to perform anything but the
simplest of manipulations.
Adjusting colors
Use the Color toolbar to adjust an image’s red, green,
and blue channels independently, as well as its
brightness, contrast and gamma.
Setting transparency
Modify the percentage value in the Transparency box
on the Picture toolbar to make the image more
transparent. This is particularly useful when creating a
watermark or when wrapping an image in the
background.
Customizing lines, areas, and shadows
The Line, Area, and Shadow icons open dialogs where you can
customize these elements. Details are in the Draw Guide.
Chapter 5 Using Graphics in Calc 139
Cropping pictures
When 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. The user
interface in Calc for cropping an image is not very friendly, so it may
be a better choice to use a graphics package.
Click the Crop icon to open a dialog where you can select which
portion of the image you want to remove.
It is not possible to use the mouse to select the area to be cropped, as
you can in Draw. Instead, in the Crop dialog, specify how far from the
top, bottom, left and right borders of the image the crop should be, as
illustrated in Figure 100. On the thumbnail in the figure, notice that
the cropped selection is highlighted with an inner rectangle.
Figure 100: The Crop dialog
On the Crop dialog, you can control the following parameters:
Keep scale / Keep image size
When Keep scale is selected (default), cropping the image does not
change the scale of the picture.
When Keep image size is selected, cropping enlarges (for positive
cropping values), shrinks (for negative cropping values), or distorts
of the image so that the image size remains constant.
140 OpenOffice.org 3.x Calc Guide
Left, Right, Top, and Bottom
The 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 left side
of the picture.
When Keep scale is selected, the size of the image also changes,
so in this example the width will be reduced by 3 cm.
When Keep image size is selected, the remaining part of the
image is enlarged (when you enter positive values for cropping)
or shrunk (when you enter negative values for cropping) so that
the width and height of the image is not changed.
Width and Height
The Width and Height fields under either Scale or Image size
change as you enter values in the Left, Right, Top, and Bottom
fields. Use the thumbnail next to these fields to determine the
correct amount by which to crop.
The cropped shape is always a rectangle; more complex cropped
shapes are not possible in Calc. Instead, use a dedicated photo or
image editing software for the job, then import the image into Calc.
Note
If you crop an image in Calc, the picture itself is not changed.
If you export the document to HTML, the original image is
exported, not the cropped image.
Resizing an image
To resize an image.
1) Click the picture, if necessary, to show the green resizing handles.
2) Position the pointer over one of the green resizing handles. The
pointer changes shape giving a graphical representation of the
direction of the resizing.
3) Click and drag to resize the picture.
4) Release the mouse button when satisfied with the new size.
The corner handles resize both the width and the height of the graphic
object simultaneously, while the other four handles only resize one
dimension at a time.
Tip
To retain the original proportions of the graphic, Shift+click
one of the corner handles, then drag. Be sure to release the
mouse button before releasing the Shift key.
Chapter 5 Using Graphics in Calc 141
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 graphics package to size your picture correctly before inserting it
into your document, if possible.
For more accurate resizing, use the Position and Size dialog, described
on page 143.
Rotating a picture
Calc does not provide a tool for rotating a picture, but you can use this
workaround:
1) Open a new Draw or Impress document.
2) Insert the image you want to rotate.
3) Select the image, then in the Drawing toolbar (shown by default
at the bottom of the window in Impress and Draw), select the
Rotate icon .
4) Rotate the image as desired. Use the red handles at the corners of
the picture and move the mouse in the direction you wish to
rotate. By default the picture rotates around its center (indicated
by a black crosshair), but you can change the pivot point by
moving the black crosshair to the desired rotation center.
Tip To restrict the rotation angle to multiples of 15 degrees keep
the Shift key pressed while rotating the image.
5) Select the rotated picture by pressing Ctrl+A, then copy the
image to the clipboard with Ctrl+C.
6) Finish by going back to the Calc document, place the cursor
where the image is to be inserted, and press Ctrl+V.
Using the picture context menu
Many of the options accessible from the Picture toolbar can also be
reached by right-clicking on an image to pop up a context menu. Some
additional options are only available from the context menu; these are
described in this section.
Text
Opens 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,
and then press Enter. There should be a cursor inside the graphic. Any
142 OpenOffice.org 3.x Calc Guide
text entered is part of the graphic, so if the graphic is moved the text
will move with it.
Position and Size
Opens the dialog shown below where you can change the size, location,
rotation, slant and corner radius of the image.
Figure 101: Position and Size Dialog
Original Size
Resets the dimensions of the image to the values when it was originally
inserted into the document.
Description
You can add metadata in the form of a title and description to the
image. This information is used by accessibility tools (such as screen
reader software) and as ALT (alternative) attributes if you export the
document to HTML.
Chapter 5 Using Graphics in Calc 143
Name
You can add a custom name to be assigned to the image, to make it
easier to find in the Navigator.
Tip
When collaborating with a team on a large, multi-page
publication, it may be beneficial to give graphics, figures, and
other objects meaningful names and descriptions to aid in clear
communication.
Flip
Flips the image either horizontally or vertically.
Assign Macro
Adds programmable functionality to the image. Calc provides rich
macro functionality. Macros are introduced in Chapter 12 (Calc
Macros).
Group
To group images:
1) Select one image, then hold down the Shift key and click in turn
on each of the others that you want to include in the group. The
invisible “bounding box” (with the 8 green handles) expands to
include all the selected images.
2) With the images selected, choose Format > Group > Group
from the menu bar.
Or hover the mouse pointer over one of the images. When the
pointer changes shape from an arrow to a hand, right-click and
choose Group > Group from the pop-up menu.
Note You cannot include drawing objects in a group with pictures.
After images are grouped, the context menu provides other choices
(Ungroup and Edit Group) and the Format > Group menu includes
Ungroup and Enter Group. For more information about grouping, see
the Draw Guide.
144 OpenOffice.org 3.x Calc Guide
Using Calc’s drawing tools
Calc, like the other components of OOo, has a range of tools to create
custom drawings. This chapter covers the default options in Calc. For a
more detailed explanation of the drawing tools and their uses, see the
Draw Guide.
In general, if you need to create complex drawings, it is recommended
to use OpenOffice.org Draw or another drawing program.
To begin using the drawing tools, choose View > Toolbars >
Drawing. The Drawing toolbar appears at the bottom of the screen.
You can tear off this toolbar and move it to a convenient place on the
window.
Figure 102: The Drawing toolbar showing default icons
Table 6: Drawing toolbar functions (from left to right)
Icon Name Behavior
Select Selects objects.
Line Draws a line.
Rectangle Draws a rectangle. To draw a square, hold
down Shift while you drag.
Ellipse Draws an ellipse. To draw a circle, hold down
Shift while you drag.
Freeform Line Draws a freeform line.
Text Draws a text box with no border.
Callouts Draws a line that ends in a rectangular callout.
Basic Shapes Opens the Basic Shapes toolbar.
Symbol Shapes Opens the Symbol Shapes toolbar.
Block Arrows Opens the Block Arrows toolbar of shapes.
Chapter 5 Using Graphics in Calc 145
Icon Name Behavior
Flowcharts Opens the Flowchart toolbar of shapes.
Callouts Opens the Callouts toolbar of shapes.
Stars Opens the Stars toolbar of shapes.
Points Allows editing of the points of a selected
polygon.
Fontwork
Gallery Opens the Fontwork Gallery.
From File Inserts a picture using the Insert Picture
dialog.
Extrusion On/Off Opens the 3-D Setting toolbar and converts the
selected shape (if any) to 3-D.
To display other icons, click the down-arrow at the right-hand end of
the toolbar, select Visible Buttons, and then choose the tools you
want to appear on the toolbar.
Icon Name Behavior
Polygon
Draws a line composed of a series of straight
line segments. Hold down the Shift key to
position new points at 45 degree angles.
Curve Draws a smooth Bézier curve.
Arc Draws an arc.
Ellipse Pie
Draws a filled shape that is defined by the arc
of an oval and two radius lines in the current
document. To draw a circle pie, hold down
Shift while you drag.
Circle Segment
Draws a filled shape that is defined by the arc
of a circle and a diameter line in the current
document. To draw an ellipse segment, hold
down Shift while you drag.
Text Animation Inserts animated text.
146 OpenOffice.org 3.x Calc Guide
If support for Asian languages has been enabled (in Tools > Options
> Language Settings > Languages), two more tools can be added to
the Drawing toolbar: Vertical Text and Vertical Callouts.
To use a drawing tool:
1) Click in the document where you want the drawing to be
anchored. You can change the anchor later, if necessary.
2) Select the tool from the Drawing toolbar (Figure 102). The mouse
pointer changes to a cross-hair pointer.
3) Move the cross-hair pointer to the place in the document where
you want the graphic to appear and then click-and-drag to create
the drawing object. Release the mouse button. (Some tools have
other requirements; see the Help or the Draw Guide for details.)
The selected drawing function remains active, so you can draw
another object of the same type.
4) To cancel the selected drawing function, press the Esc key or
click on the Select icon (the arrow) on the Drawing toolbar.
5) You can now change the properties (fill color, line type and
weight, anchoring, and others) of the drawing object using either
the Drawing Object Properties toolbar (Figure 103) or the choices
and dialog boxes reached by right-clicking on the drawing object.
Set or change properties for drawing objects
To set the properties for a drawing object before you draw it:
1) On the Drawing toolbar (Figure 102), click the Select tool.
2) On the Drawing Object Properties toolbar (Figure 103), click on
the icon for each property and select the value you want for that
property.
3) For more control, or to define new attributes, you can click on the
Area or Line icons on the toolbar to display detailed dialogs.
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 apply to any other document. The defaults apply to all the
drawing objects except text objects.
To change the properties for an existing drawing object, select the
object and continue as described above.
Other tools and methods for modifying and positioning graphics are
described in “Positioning graphics” below.
Chapter 5 Using Graphics in Calc 147
1Line 5Line Color 9Change Anchor 13 Alignment
2Arrow Style 6Area 10 Bring to Front
3Line Style 7Area Style / Filling 11 Send to Back
4Line Width 8Rotate 12 To Foreground / To Background
Figure 103. Drawing Object Properties toolbar
Resizing a drawing object
Select the object, click on one of the eight handles around it and drag
it to its new size. For a scaled resizing, select one of the corner handles
and keep the Shift key pressed while dragging the handle.
For more control of the size of the object, use the Position and Size
dialog (see “Position and Size” on page 143) to set the width and
height independently. If the Keep ratio option is selected, then the
two dimensions change so that the proportion is maintained, allowing
for a scaled resizing.
Grouping drawing objects
To group drawing objects:
1) Select one object, then hold down the Shift key and select the
others you want to include in the group. The bounding box
expands to include all the selected objects.
2) With the objects selected, hover the mouse pointer over one of
the objects and choose Format > Group > Group from the menu
bar or right-click and choose Group > Group from the pop-up
menu.
Note You cannot include an embedded or linked graphic in a group
with drawing objects.
Positioning graphics
Graphics can be positioned in OOo Calc to work together and build
more complex features.
148 OpenOffice.org 3.x Calc Guide
Arranging graphics
Graphics in a Calc document are maintained in a similar manner to a
deck of cards. As you add more images to the document, each image
occupies a new layer at the top of the stack. To arrange graphics, you
tell Calc to change the order of layers in the stack.
Figure 104: Layering effect
Calc provides four basic options to re-arrange the order of images.
These options can be accessed from both the Picture toolbar and the
picture context menu:
Bring to Front
Places the image on top of any other graphics or text.
Bring Forward
Brings the image one level up in the stack (z-axis). Depending on the
number of overlapping objects, you may need to apply this option
several times to obtain the desired result.
Send Backward
The opposite of Bring Forward; sends the selected image one level
down in the object stack.
Send to Back
Sends the selected graphic to the bottom of the stack, so that other
graphics and text cover it.
Chapter 5 Using Graphics in Calc 149
To Background and To Foreground
An image or a drawing object can be sent to the background as well.
This is not the same as Bring Forward and Send Backward, which
set the order of a number of overlapping graphics. This feature
pushes a graphic behind the cells, allowing cells to be edited without
affecting the graphic.
A graphic in the background will have To Foreground as a menu
item, instead of To Background.
Anchoring graphics
Anchors tell a graphic where to stay in relation to other items.
Anchor to page
Anchoring a graphic to the page allows it to be positioned in a
specific place on the page. The graphic does not move when cells
are added or deleted. This is equivalent to an absolute reference.
The graphic will always stay by cell B10 if that is where it is placed.
Anchor to cell
Anchoring a graphic to a cell ensures that the graphic always stays
with the content it is originally anchored to. If a graphic is anchored
to cell B10, and a new row is inserted, the graphic will then be
anchored to cell B11. This is equivalent to a relative reference.
For example, in Figure 105 the normal Otto and Tux picture is
anchored To Cell B10 (XXX shows where the picture is anchored). The
inverse Otto and Tux picture is anchored to the page.
Figure 105: Anchoring 1
If two rows are inserted above the pictures, the normal picture
(anchored to cell) will shift down two rows and the anchor will change.
The inverse picture (anchored to page) will not move. This is illustrated
in Figure 106. Note that the anchor symbol and the XXX have moved
down to cell B12.
150 OpenOffice.org 3.x Calc Guide
.
Figure 106: Anchoring 2
Aligning graphics
You 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
turn). The graphics will be surrounded by an invisible bounding
box with 8 green handles.
2) On the Picture toolbar, click on the Alignment icon and select one
of the six options.
Or, position the mouse pointer over any of the graphics. When the
pointer changes shape from an arrow to a hand, right-click and
choose Alignment, then select from the six options.
The six options include three for aligning the graphics horizontally
(left, center, right) and three for aligning the graphics vertically (top,
center, bottom).
Creating an image map
An image map defines areas of an image (called hotspots) with
hyperlinks to web addresses, other files on the computer, or parts of
the same document. Hotspots are the graphic equivalent of text
hyperlinks. Clicking on a hotspot causes Calc to open the linked page
in the appropriate program (for example, the default browser for an
HTML page; OOo Calc for a .ODS file; a PDF viewer for a PDF file). You
can create hotspots of various shapes, and include several hotspots in
the same image.
To use the image map editor:
1) In your OOo document, select the picture where you want to
define the hotspots.
2) Choose Edit > ImageMap from the menu bar. The ImageMap
Editor (Figure 107) opens.
Chapter 5 Using Graphics in Calc 151
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 the settings.
5) When done, click the Save icon to save the imagemap to a file,
then click the X in the upper right corner to close the dialog.
Figure 107: The dialog to create or edit an image map
The main part of the dialog shows the image on which the hotspots are
defined. A hotspot is identified by a line indicating its shape.
The toolbar at the top of the dialog contains the following tools:
Apply button: click this button to apply the changes.
Load, Save, and Select icons.
Tools for drawing a hotspot shape: these tools work in exactly the
same way as the corresponding tools in the Drawing toolbar.
Edit, Move, Insert, Delete Points: advanced editing tools to
manipulate the shape of a polygon hotspot. Select the Edit Points
tool to activate the other tools.
Active icon: toggles the status of a selected hotspot between
active and inactive.
Macro: associates a macro with the hotspot instead of just
associating an hyperlink.
152 OpenOffice.org 3.x Calc Guide
Properties: sets the hyperlink properties and adds the Name
attribute to the hyperlink.
Below the toolbar, specify for the selected hotspot:
Address: the address pointed by the hyperlink. You can also point
to an anchor in a document; to do this, write the address in this
format: file:///<path>/document_name#anchor_name
Text: type the text that you want to be displayed when the mouse
pointer is moved over the hotspot.
Frame: where the target of the hyperlink will open: pick among
_blank (opens in a new browser window), _self (opens in the
active browser window), _top or _parent.
Tip
The value _self for the target frame will work just fine in the vast
majority of occasions. It is therefore not recommended to use
the other choices unless absolutely necessary.
Chapter 5 Using Graphics in Calc 153
Chapter 6
Printing, Exporting, and
E-mailing
Quick printing
Click the Print File Directly icon to send the entire document to
the default printer defined for your computer.
Note
You can change the action of the Print File Directly icon to
send the document to the printer defined for the document
instead of the default printer for the computer. Choose Tools >
Options > Load/Save > General and the Load printer
settings with the document option.
Controlling printing
For more control over printing, use File > Print.
Figure 108. The Print dialog
On the Print dialog, you can choose:
Which printer to use (if more than one are installed on your
system) and the properties of the printer—for example, page
orientation (portrait or landscape), which paper tray to use, and
Chapter 6 Printing, Exporting, and E-mailing 155
what paper size to print on. The properties available depend on
the selected printer; consult the printer’s documentation for
details.
What sheets and pages to print, how many copies to print, and in
what order to print them. Use dashes to specify page ranges and
commas or semicolons to separate ranges; for example: 1, 5, 11–
14, 34–40. Selection refers to the highlighted part of a page or
pages.
Whether to print to a printer or to a file.
Printer options
You can set printer options for the current document only or for all
spreadsheets.
To select options for the current document, click the Options
button on the bottom left of the Print dialog (Figure 108).
To set print options permanently, go to Tools > Options >
OpenOffice.org Calc > Print. The dialogs for both are very
similar.
Figure 109. Printer Options dialog
Selecting sheets to print
You can select one or more sheets for printing. This can be useful if
you have a large spreadsheet with multiple sheets and only want to
print certain sheets. For example, an accountant might record costs
over time with one sheet for each month. To print the November and
December sheets, follow this procedure.
1) Go to the November sheet. Hold down the Control key and click
on the tab of the December sheet.
2) If you wish to print all of the sheets, choose File > Print and
click the Options button.
156 OpenOffice.org 3.x Calc Guide
3) Deselect Print only selected sheets. This affects the print
preview, export, and printing of your spreadsheet. Click OK.
Note
The Options button is different from the Properties button.
Properties deals with the settings of the printer, whereas
Options deals with OOo’s settings.
Selecting the page order, details, and scale
To select the page order, details, and scale to be printed:
1) Choose Format > Page from the main menu. The Page Style
dialog opens; any changes made here are saved in the page style
for this spreadsheet.
2) Choose the Sheet tab (Figure 110).
3) After making your selections (see next page), click OK.
Figure 110. The Sheet tab of the Page Style dialog
Page Order
You can set the order in which pages print. This is especially useful in a
large document; for example, controlling the print order can save time
if you have to collate the document a certain way.
Chapter 6 Printing, Exporting, and E-mailing 157
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 column and so on, or by row as shown in the graphic on the top
right of the dialog in Figure 110.
Print
You can specify which details to print. These details include:
Row and column headers
Sheet grid—prints the borders of the cells as a grid
Comments—prints the comments defined in your spreadsheet on
a separate page, along with the corresponding cell reference
Objects and graphics
Charts
Drawing objects
Formulas—prints the formulas contained in the cells, instead of
the results
Zero Values—prints cells with a zero value
Note
Remember that since the print detail options are a part of the
page’s properties, they are also a part of the page style’s
properties. Therefore, different page styles can be set up to
quickly change the print properties of the sheets in the
spreadsheet.
Scale
Use the scale features to control the number of pages the data will
print on. This can be useful if a large amount of data needs to be
printed compactly or, if the reader has poor eyesight, text can be
enlarged when it prints.
Reduce/Enlarge printout—scales the data in the printout either
larger or smaller. For example, if a sheet would normally print out
as four pages (two high and two wide), a scaling of 50% would
print as one page (both width and height are halved).
Fit print range(s) on number of pages—defines exactly how many
pages the printout will take up. This option will only reduce a
printout, it will not enlarge it. To enlarge a printout, the
reduce/enlarge option must be used.
Fit print range(s) to width/height—defines how high and wide the
printout will be, in pages.
158 OpenOffice.org 3.x Calc Guide
Using print ranges
Print ranges have several uses, including printing only a specific part
of the data or printing selected rows or columns on every page.
Defining a print range
To define a new print range or modify an existing print range:
1) Highlight the range of cells that comprise the print range.
2) Choose Format > Print Ranges > Define.
The page break lines display on the screen.
Tip You can check the print range by using File > Page Preview.
OOo will only display the cells in the print range.
Adding to the print range
After defining a print range, you can add more cells to it. This allows
multiple, separate areas of the same sheet to be printed, while not
printing the whole sheet. After you have defined a print range:
1) Highlight the range of cells to be added to the print range.
2) Choose Format > Print Ranges > Add. This adds the extra cells
to the print range.
The page break lines no longer display on the screen.
Note The additional print range will print as a separate page, even if
both ranges are on the same sheet.
Removing a print range
It may become necessary to remove a defined print range, for example
if the whole sheet needs to be printed later.
Choose Format > Print Ranges > Remove. This removes all defined
print ranges on the sheet. After the print range is removed, the default
page break lines will appear on the screen.
Editing a print range
At any time, you can directly edit the print range, for example to
remove or resize part of the print range. Choose Format > Print
Chapter 6 Printing, Exporting, and E-mailing 159
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 range
In this example, three rectangles are selected, each separated by a
semicolon. The first is bounded by cell A3 ($A$3) in the top left and
cell C9 ($C$9) in the bottom right corners. Clicking anywhere in the
text entry box shows the currently selected print range on the screen,
with each rectangle in a different color, as in Figure 112.
After making any changes, click the Shrink icon next to the text
entry box to redisplay the rectangles with their new values.
To re-expand the Edit Print Ranges dialog, click the Shrink icon again.
Figure 112: Print range marked by colored boxes
Printing rows or columns on every page
If a sheet is printed on multiple pages, you can set up certain rows or
columns to repeat on each printed page.
160 OpenOffice.org 3.x Calc Guide
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 > Edit. On the Edit Print
Ranges dialog, type the rows in the text entry box under Rows to
repeat. For example, to repeat rows 1 and 2, type $1:$2. This
automatically changes Rows to repeat from - none - to
- user defined -.
Figure 113: Specifying repeating rows
2) To repeat columns, type the columns in the text entry box under
Columns to repeat. For example, to repeat column A, type $A.
This automatically changes Columns to repeat from - none - to
- user defined -.
3) Click OK.
Note You do not need to select the entire range of the rows to be
repeated; simply select one cell in each row.
Defining a custom print range
In addition to highlighting a print range for each print job, you can
define a range of cells to be used repeatedly. This may be useful if
there are different areas of a large spreadsheet that need to be printed
for different reports. Several different print ranges can be defined to
meet this need.
1) To define a print range, use the same procedure as labeling an
area of the sheet. Highlight the cells you want to define as a print
range and select Insert > Names > Define. (The cells can be
highlighted after opening the Define Names dialog as well.)
2) On the Define Names dialog (Figure 114), type a name for the
range in the text box with the blinking cursor. The name of the
range cannot contain any spaces.
Chapter 6 Printing, Exporting, and E-mailing 161
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 selection, type in
the additional ranges. For example, to select the rectangle with
A3 as the top left cell and F20 as the bottom right cell, enter
;$A$3:$F$20 or ;A3:F20 (both work and are equivalent) after
the initial selection. Make sure that each group of cells is
separated with a semicolon.
5) Click OK.
Figure 114: Define Names dialog
To print this range:
1) Choose Format > Print Ranges > Edit (Figure 111). The
previously defined area now appears in the drop-down box under
Print range.
2) Select the defined print range and click OK.
This method can be useful to quickly change the print range without
highlighting a large area of cells every time.
Note
If the cell range name refers to more than one group of cells, it
will not appear in the drop-down list. You will need to type it in
or highlight and select it.
162 OpenOffice.org 3.x Calc Guide
Page breaks
While 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 use
a manual break. A manual break helps to ensure that your data prints
properly. You can insert a horizontal page break above, or a vertical
page break to the left of, the active cell.
Inserting a page break
To insert a page break:
1) Navigate to the cell where the page break will begin.
2) Select Insert > Manual Break.
3) Select Row Break or Column Break depending on your need.
The break is now set.
Row break
Selecting Row Break creates a page break above the selected cell. For
example, if the active cell is H15, then the break is created between
rows 14 and 15.
Column break
Selecting Column Break creates a page break to the left of the selected
cell. For example, if the active cell is H15, then the break is created
between columns G and H.
Tip
To see page break lines more easily on screen, you can change
their color. Choose Tools > Options > OpenOffice.org >
Appearance and scroll down to the Spreadsheet section.
Deleting a page break
To remove a page break:
1) Navigate to a cell that is next to the break you want to remove.
2) Select Edit > Delete Manual Break.
3) Select Row Break or Column Break depending on your need.
The break is now removed.
Chapter 6 Printing, Exporting, and E-mailing 163
Note
Multiple 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 be confusing at times, because
although there may be a column break set on the page, when
you go to Edit > Manual Break, the Column break choice
may not be available (grayed out).
In order to remove the break, you have to be in the cell next to
the break. For example, if you set the column break while you
are in H15, you can not remove it if you are in cell D15.
However, you can remove it from any cell in column H.
Headers and footers
Headers and footers are predefined pieces of text that are printed at
the top or bottom of a sheet outside of the sheet area. Headers are set
the same way as footers.
Headers and footers are assigned to a page style. You can define more
than one page style for a spreadsheet and assign different page styles
to different sheets. For more about page styles, see Chapter 4.
Setting a header or a footer
To set a header or footer:
1) Navigate to the sheet that you want to set the header or footer
for. Select Format > Page.
2) Select the Header (or Footer) tab. See Figure 115.
3) Select the Header on option.
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 the height of the header or footer.
Margin
Changing the size of the left or right margin adjusts how far the
header or footer is from that side of the page.
Spacing
Spacing affects how far above or below the sheet the header or
footer will print. So, if spacing is set to 1.00", then there will be 1
inch between the header or footer and the sheet.
Height
Height affects how big the header or footer will be.
164 OpenOffice.org 3.x Calc Guide
Figure 115: Header dialog
Header or footer appearance
To change the appearance of the header or footer, click the More
button in the dialog. This opens the Border/Background dialog.
Figure 116: Header/Footer Border/Background dialog
From this dialog you can set the background and border style of the
header or footer. See Chapter 4 (Using Styles and Templates) for more
information.
Chapter 6 Printing, Exporting, and E-mailing 165
Setting the contents of the header or footer
The header or footer of a Calc spreadsheet has three columns for text.
Each column can have different contents.
To set the contents of the header or footer, click the Edit button in the
header or footer dialog shown in Figure 115 to display the dialog
shown in Figure 117.
Figure 117: Edit contents of header or footer
Areas
Each area in the header or footer is independent and can have
different information in it.
Header
You can select from several preset choices in the Header drop-down
list, or specify a custom header using the buttons below the area
boxes. (To format a footer, the choices are the same.)
Custom header
Click in the area (Left, Center, Right) that you want to customize,
then use the buttons to add elements or change text attributes.
Opens the Text Attributes dialog.
Inserts the total number of pages.
Inserts the File Name field.
Inserts the Date field.
Inserts the Sheet Name field.
166 OpenOffice.org 3.x Calc Guide
Inserts the Time field.
Inserts the current page number.
Exporting to PDF
Calc can export documents to PDF (Portable Document Format). This
industry-standard file format is ideal for sending the file to someone
else to view using Adobe Reader or other PDF viewers.
Quick export to PDF
Click the Export Directly as PDF icon to export the entire
document using your default PDF settings. You are asked to enter the
file name and location for the PDF file, but you do not get to choose a
page range, the image compression, or other options.
Controlling PDF content and quality
For more control over the content and quality of the resulting PDF, use
File > Export as PDF. The PDF Options dialog opens. This dialog has
five pages (General, Initial View, User Interface, Links, and Security).
Make your selections and click Export. Enter the location and file
name of the PDF to be created. Click Save to export the file.
General page of PDF Options dialog
On the General page, you can choose which pages to include in the
PDF, the type of compression to use for images (which affects the
quality of images in the PDF), and other options.
Range section
All: Exports the entire document.
Pages: To export a range of pages, use the format 3-6 (pages 3 to
6). To export single pages, use the format 7;9;11 (pages 7, 9, and
11). You can also export a combination of page ranges and single
pages, by using a format like 3-6;8;10;12.
Selection: Exports whatever content is selected.
Chapter 6 Printing, Exporting, and E-mailing 167
Figure 118: General page of PDF Options dialog
Images section
Lossless compression: Images are stored without any loss of
quality. Tends to make large files when used with photographs.
Recommended for other kinds of images or graphics.
JPEG compression: Allows for varying degrees of quality. A
setting of 90% works well with photographs (small file size, little
perceptible loss).
Reduce image resolution: Lower-DPI (dots per inch) images
have lower quality. For viewing on a computer screen a resolution
of 72dpi (for Windows) or 96dpi (GNU/Linux) is sufficient, while
for printing it is generally preferable to use at least 300 or 600
dpi, depending on the capability of the printer. Higher dpi
settings greatly increase the size of the exported file.
168 OpenOffice.org 3.x Calc Guide
Note
EPS images with embedded previews are exported only as
previews. EPS images without embedded previews are
exported as empty placeholders.
General section
PDF/A-1: PDF/A is an ISO standard established in 2005 for long-
term preservation of documents, by embedding all the pieces
necessary for faithful reproduction (such as fonts) while
forbidding other elements (including forms, security, encryption,
and tagged PDF). If you select PDF/A-1, the forbidden elements
are greyed-out (not available).
Tagged PDF: Exports special tags into the corresponding PDF
tags. Some tags that are exported are table of contents,
hyperlinks, and controls. This option can increase the file size
significantly.
Create PDF form - Submit format: Choose the format for
submitting forms from within the PDF file. This setting overrides
the control’s URL property that you set in the document. There is
only one common setting valid for the whole PDF document: PDF
(sends the whole document), FDF (sends the control contents),
HTML, and XML. Most often you will choose the PDF format.
Export bookmarks: Exports sheet names in Calc documents as
“bookmarks” (a table of contents list displayed by some PDF
readers, including Adobe Reader).
Export comments: Exports comments in Calc documents as PDF
notes. You may not want this!
Export automatically inserted blank pages: Not available in
Calc.
Initial View page of PDF Options dialog
On the Initial View page (Figure 119), you can choose how the PDF
opens by default in a PDF viewer. The selections are self-explanatory.
User Interface page of PDF Options dialog
On the User Interface page (Figure 120), you can choose more settings
to control how a PDF viewer displays the file. Some of these choices
are particularly useful when you are creating a PDF to be used as a
presentation or a kiosk-type display.
Chapter 6 Printing, Exporting, and E-mailing 169
Figure 119: Initial View page of PDF Options dialog
Figure 120: User Interface page of PDF Options dialog
Window options section
Resize window to initial page: Causes the PDF viewer window
to resize to fit the first page of the PDF.
170 OpenOffice.org 3.x Calc Guide
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 open full-
screen instead of in a smaller window.
Display document title: Causes the PDF viewer to display the
document’s title in the title bar.
User interface options section
Hide menubar: Causes the PDF viewer to hide the menu bar.
Hide toolbar: Causes the PDF viewer to hide the toolbar.
Hide window controls: Causes the PDF viewer to hide other
window controls.
Transitions
Not available in Calc.
Bookmarks
Select how many heading levels are displayed as bookmarks, if
Export bookmarks is selected on the General page.
Links page of PDF Options dialog
On this page you can choose how links are exported to PDF.
Figure 121: Links page of PDF Options dialog
Export bookmarks as named destinations
If you have defined Writer bookmarks, Impress or Draw slide names,
or Calc sheet names, this option exports them as “named
destinations” to which Web pages and PDF documents can link.
Convert document references to PDF targets
If you have defined links to other documents with OpenDocument
extensions (such as .ODT, .ODS, and .ODP), this option converts the
file names to .PDF in the exported PDF document.
Chapter 6 Printing, Exporting, and E-mailing 171
Export URLs relative to file system
If you have defined relative links in a document, this option exports
those links to the PDF.
Cross-document links
Defines the behavior of links clicked in PDF files.
Security page of PDF Options dialog
PDF export includes options to encrypt the PDF (so it cannot be
opened without a password) and apply some digital rights management
(DRM) features.
With an open password set, the PDF can only be opened with the
password. Once opened, there are no restrictions on what the
user can do with the document (for example, print, copy, or
change it).
With a permissions password set, the PDF can be opened by
anyone, but its permissions can be restricted. See Figure 122.
Figure 122: Security page of PDF Options dialog.
172 OpenOffice.org 3.x Calc Guide
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.
Note Permissions settings are effective only if the user’s PDF viewer
respects the settings.
Figure 123 shows the dialog displayed when you click the Set open
password button on the Security page of the PDF Options dialog.
After you set a password for permissions, the other choices on the
Security page (shown in Figure 122) become available. These
selections are self-explanatory.
Figure 123: Setting a password to encrypt a PDF
Exporting to XHTML
Calc can export spreadsheets to XHTML. Choose File > Export. On
the Export dialog, specify a file name for the exported document, then
select the XHTML in the File format list and click the Export button.
Chapter 6 Printing, Exporting, and E-mailing 173
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 the file contains more than one sheet, the additional sheets will
follow one another in the HTML file. Links to each sheet will be placed
at the top of the document. Calc also allows the insertion of links
directly into the spreadsheet using the Hyperlink dialog.
E-mailing spreadsheets
OOo provides several quick and easy ways to send spreadsheets as an
e-mail attachment in one of three formats: OpenDocument Spreadsheet
(OOo’s default format), Microsoft Excel, or PDF.
To send the current document in OpenDocument format:
1) Choose File > Send > Document as E-mail. OpenOffice.org
opens your default e-mail program with the spreadsheet (*.ODS)
document attached.
2) In your e-mail program, enter the recipient, subject, and any text
you want to add, then send the e-mail.
File > Send > E-mail as OpenDocument Spreadsheet has the
same effect.
If you choose E-mail as Microsoft Excel, OOo first creates a file in
Excel format and then opens your e-mail program with the *.XLS file
attached.
Similarly, if you choose E-mail as PDF, OOo first creates a PDF using
your default PDF settings (as when using the Export Directly as PDF
toolbar button) and then opens your email program with the *.PDF file
attached.
E-mailing a spreadsheet to several recipients
To e-mail a document to several recipients, you can use the features in
your e-mail program or you can use OOo Writer’s mail merge facilities
to extract email addresses from an address book.
For details, see Chapter 10 (Printing, Exporting, and E-mailing) in the
Getting Started guide.
174 OpenOffice.org 3.x Calc Guide
Digital signing of documents
To sign a document digitally, you need a personal key, the certificate. A
personal key is stored on your computer as a combination of a private
key, which 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, which may be a private company or a
governmental institution.
When you apply a digital signature to a document, a checksum is
computed from the document’s content plus your personal key. The
checksum and your public key are stored together with the document.
When someone later opens the document on any computer with a
recent version of OpenOffice.org, the program will compute the
checksum again and compare it with the stored checksum. If both are
the same, the program will signal that you see the original, unchanged
document. In addition, the program can show you the public key
information from the certificate. You can compare this key with the
public key that is published on the web site of the certificate authority.
Whenever someone changes something in the document, this change
breaks the digital signature.
On Windows operating systems, the Windows features of validating a
signature are used. On Solaris and Linux systems, files that are
supplied by Thunderbird, Mozilla or Firefox are used. For a more
detailed description of how to get and manage a certificate, and
signature validation, see “Using Digital Signatures” in the OOo Help.
To sign a document:
1) Choose File > Digital Signatures.
2) If you have not saved the document since the last change, a
message box appears. Click Yes to save the file.
3) After saving, you see the Digital Signatures dialog. Click Add to
add a public key to the document.
4) In the Select Certificate dialog, select your certificate and click
OK.
5) You see again the Digital Signatures dialog, where you can add
more certificates if you want. Click OK to add the public key to
the saved file.
A signed document shows an icon in the status bar. You can double-
click the icon to view the certificate.
Chapter 6 Printing, Exporting, and E-mailing 175
Removing personal data
You may wish to ensure that personal data, versions, notes, hidden
information, or recorded changes are removed from files before you
send them to other people or create PDFs from them.
In Tools > Options > OpenOffice.org > Security > Options, you
can set Calc to remind (warn) you when files contain certain
information and remove personal information automatically on saving.
To remove personal and some other data from a file, go to File >
Properties. On the General tab, uncheck Apply user data and then
click the Reset button. This removes any names in the created and
modified fields, deletes the modification and printing dates, and resets
the editing time to zero, the creation date to the current date and time,
and the version number to 1.
To remove version information, either go to File > Versions, select the
versions from the list and click Delete, or use Save As and save the
file with a different name.
176 OpenOffice.org 3.x Calc Guide
Chapter 7
Using Formulas and
Functions
Introduction
In previous chapters, we have been entering one of two basic types of
data into each cell: numbers and text. However, we will not always
know what the contents should be. Often the contents of one cell
depends on the contents of other cells. To handle this situation, we use
a third type of data: the formula. Formulas are equations using
numbers and variables to get a result. In a spreadsheet, the variables
are cell locations that hold the data needed for the equation to be
completed.
A function is a predefined calculation entered in a cell to help you
analyze or manipulate data in a spreadsheet. All you have to do is add
the arguments, and the calculation is automatically made for you.
Functions help you create the formulas needed to get the results that
you are looking for.
Setting up a spreadsheet
If you are setting up more than a simple one-worksheet system in Calc,
it is worth planning ahead a little. Avoid the following traps:
Typing fixed values into formulas
Not including notes and comments describing what the system
does, including what input is required and where the formulas
come from (if not created from scratch)
Not incorporating a system of checking to verify that the formulas
do what is intended
The trap of fixed values
Many users set up long and complex formulas with fixed values typed
directly into the formula.
For example, conversion from one currency to another requires
knowledge of the current conversion rate. If you input a formula in cell
C1 of =0.75*B1 (for example to calculate the value in Euros of the USD
dollar amount in cell B1), you will have to edit the formula when the
exchange rate changes from 0.75 to some other value. It is much
easier to set up an input cell with the exchange rate and reference that
cell in any formula needing the exchange rate. What-if type
calculations also are simplified: what if the exchange rate varies from
0.75 to 0.70 or 0.80? No formula editing is needed and it is clear what
rate is used in the calculations. Breaking complex formulas down into
178 OpenOffice.org 3.x Calc Guide
more manageable parts, described below, also helps to minimise errors
and aid troubleshooting.
Lack of documentation
Lack of documentation is a very common failing. Many users prepare a
simple worksheet which then develops into something much more
complicated over time. Without documentation, the original purpose
and methodology is often unclear and difficult to decipher. In this case
it is usually easier to start again from the beginning, wasting the work
done previously. If you insert comments in cells, and use labels and
headings, a spreadsheet can be later modified by you or others and
much time and effort will be saved.
Error-checking formulas
Adding up columns of data or selections of cells from a worksheet often
results in errors due to omitting cells, wrongly specifying a range, or
double-counting cells. It is useful to institute checks in your
spreadsheets. For example, set up a spreadsheet to calculate columns
of figures, and use SUM to calculate the individual column totals. You
can check the result by including (in a non-printing column) a set of
row totals and adding these together. The two figures—row total and
column total—must agree. If they do not, you have an error
somewhere.
Figure 124: Error checking of formulas
Chapter 7 Using Formulas and Functions 179
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 124).
Creating formulas
You can enter formulas in two ways, either directly into the cell itself,
or at the input line. Either way, you need to start a formula with one of
the following symbols: =, + or –. Starting with anything else causes the
formula to be treated as if it were text.
Operators in formulas
Each cell on the worksheet can be used as a data holder or a place for
data calculations. Entering data is accomplished simply by typing in
the cell and moving to the next cell or pressing Enter. With formulas,
the equals sign indicates that the cell will be used for a calculation. A
mathematical calculation like 15 + 46 can be accomplished as shown
in Figure 125.
While the calculation on the left was accomplished in only one cell, the
real power is shown on the right where the data is placed in cells and
the calculation is performed using references back to the cells. In this
case, cells B3 and B4 were the data holders, with B5 the cell where the
calculation was performed. Notice that the 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 cell holding
the formula. All formulas build upon this concept. Other ways of
entering formulas are shown in Table 7.
These cell references allow formulas to use data from anywhere in the
worksheet being worked on or from any other worksheet in the
workbook that is opened. If the data needed was in different
worksheets, they would be referenced by referring to the name of the
worksheet, for example =SUM(Sheet2.B12+Sheet3.A11).
Note
To enter the = symbol for a purpose other than creating a
formula as described in this chapter, type an apostrophe or
single quotation mark before the =. For example, in the entry '=
means different things to different people, Calc treats everything
after the single quotation mark—including the = sign—as text.
180 OpenOffice.org 3.x Calc Guide
Simple Calculation in 1 Cell Calculation by Reference
Figure 125: A simple calculation
Table 7: Common ways to enter formulas
Formula Description
=A1+10 Displays the contents of cell A1 plus 10.
=A1*16% Displays 16% of the contents of A1.
=A1*A2 Displays the result of the multiplication of
A1 and A2.
=ROUND(A1;1) Displays the contents of cell A1 rounded to
one decimal place.
=EFFECTIVE(5%;12) Calculates the effective interest for 5%
annual nominal interest with 12 payments a
year.
Chapter 7 Using Formulas and Functions 181
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 and
adds the value to B8.
=SUM(B1:B65536) Sums all numbers in column B.
=AVERAGE(BloodSugar) Displays the average of a named range
defined under the name BloodSugar.
=IF(C31>140; "HIGH"; "OK") Displays the results of a conditional
analysis of data from two sources. If the
contents of C31 is greater than 140, then
HIGH is displayed, otherwise OK is
displayed.
Note
Users of Lotus 1-2-3®, Quattro Pro® and other spreadsheet
software may be familiar with formulas that begin with +, -, =,
(, @, ., $, or #. A mathematical formula would look like
+D2+C2 or +2*3. Functions begin with the @ symbol such as
@SUM(D2..D7), @COS(@DEGTORAD(30)) and
@IRR(GUESS;CASHFLOWS). Ranges are identified such as
A1..D3.
Functions can be identified in Table 7 with a word, for example
ROUND, followed by parentheses enclosing references or numbers.
It is also possible to establish ranges for inclusion by naming them
using Insert > Names, for example BloodSugar representing a range
such as B3:B10. Logical functions can also be performed as
represented by the IF statement which results in a conditional
response based upon the data in the identified cell, for example
=IF(A2>=0;"Positive";"Negative")
A value of 3 in cell A2 would return the result Positive, –9 the result
Negative.
Operator types
You can use the following operators in OpenOffice.org Calc: arithmetic,
comparative, descriptive, text, and reference.
Arithmetic operators
The addition, s