OpenOffice 3.3 Calc Guide
OpenOffice.org 3.3
Calc Guide
Using Spreadsheets in OpenOffice.org 3.3
Copyright
This document is Copyright © 2005–2011 by its contributors as listed below. You may
distribute it and/or modify it under the terms of either the GNU General Public
License (http://www.gnu.org/licenses/gpl.html), version 3 or later, or the Creative
Commons Attribution License (http://creativecommons.org/licenses/by/3.0/), 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.
Contributors
Rick Barnes Peter Kupfer Martin Fox
James Andrew Krishna Aradhi Andy Brown
Stephen Buck Bruce Byfield Nicole Cairns
T. J. Frazier Stigant Fyrwitful Ingrid Halama
Spencer E. Harpe Regina Henschel Peter Hillier-Brook
John Kane Kirk Abbott Emma Kirsopp
Jared Kobos Sigrid Kronenberger Shelagh Manton
Alexandre Martins Kashmira Patel Anthony Petrillo
Andrew Pitonyak Iain Roberts Hazel Russman
Gary Schnabl Rob Scott Jacob Starr
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 18 April 2011. Based on OpenOffice.org 3.3.
You can download
an editable version of this document from
http://wiki.services.openoffice.org/wiki/Documentation/
Contents
Copyright................................................................................................................... 2
Note for Mac users.................................................................................................... 8
Chapter 1
Introducing Calc.......................................................................................................... 9
What is Calc?........................................................................................................... 10
Spreadsheets, sheets, and cells...............................................................................10
Parts of the main Calc window................................................................................10
Starting new spreadsheets...................................................................................... 18
Opening existing spreadsheets................................................................................20
Opening CSV files.................................................................................................... 21
Saving spreadsheets................................................................................................ 22
Password protection.................................................................................................24
Navigating within spreadsheets.............................................................................. 26
Selecting items in a sheet or spreadsheet...............................................................29
Working with columns and rows.............................................................................. 32
Working with sheets.................................................................................................33
Viewing Calc............................................................................................................ 34
Using the Navigator................................................................................................. 38
Using document properties......................................................................................40
Chapter 2
Entering, Editing, and Formatting Data....................................................................43
Introduction............................................................................................................. 44
Entering data using the keyboard...........................................................................44
Speeding up data entry............................................................................................46
Sharing content between sheets.............................................................................. 49
Validating cell contents............................................................................................ 50
Editing data.............................................................................................................. 52
Formatting data....................................................................................................... 53
Autoformatting cells and sheets..............................................................................59
Formatting spreadsheets using themes...................................................................60
Using conditional formatting...................................................................................60
Hiding and showing data.........................................................................................61
Sorting records........................................................................................................ 63
Finding and replacing in Calc.................................................................................. 65
Chapter 3
Creating Charts and Graphs......................................................................................69
Introduction............................................................................................................. 70
Creating a chart....................................................................................................... 70
Editing charts...........................................................................................................74
Formatting charts.................................................................................................... 79
Formatting 3D charts............................................................................................... 82
Formatting the chart elements................................................................................85
OpenOffice.org 3.3 Calc Guide 3
Adding drawing objects to charts............................................................................87
Resizing and moving the chart................................................................................ 88
Gallery of chart types............................................................................................... 89
Chapter 4
Using Styles and Templates in Calc...........................................................................98
What is a template?................................................................................................. 99
What are styles?....................................................................................................... 99
Types of styles in Calc..............................................................................................99
Accessing styles..................................................................................................... 100
Applying cell styles................................................................................................ 101
Applying page styles.............................................................................................. 102
Modifying styles..................................................................................................... 103
Creating new (custom) styles.................................................................................106
Copying and moving styles....................................................................................107
Deleting styles....................................................................................................... 109
Creating a spreadsheet from a template...............................................................109
Creating a template............................................................................................... 110
Editing a template.................................................................................................. 111
Adding templates using the Extension Manager...................................................113
Setting a default template..................................................................................... 113
Associating a spreadsheet with a different template.............................................114
Organizing templates.............................................................................................115
Chapter 5
Using Graphics in Calc.............................................................................................117
Graphics in Calc..................................................................................................... 118
Adding graphics (images)...................................................................................... 118
Modifying images...................................................................................................123
Using the picture context menu.............................................................................128
Using Calc’s drawing tools.................................................................................... 130
Positioning graphics...............................................................................................133
Creating an image map.......................................................................................... 136
Chapter 6
Printing, Exporting, and E-mailing..........................................................................138
Quick printing........................................................................................................ 139
Controlling printing............................................................................................... 139
Using print ranges................................................................................................. 142
Page breaks............................................................................................................ 145
Printing options setup in page styles..................................................................... 146
Headers and footers............................................................................................... 148
Exporting to PDF................................................................................................... 151
Exporting to XHTML.............................................................................................. 157
Saving as Web pages (HTML)................................................................................157
E-mailing spreadsheets.......................................................................................... 157
Digital signing of documents.................................................................................157
4 OpenOffice.org 3.3 Calc Guide
Removing personal data........................................................................................ 158
Chapter 7
Using Formulas and Functions................................................................................159
Introduction........................................................................................................... 160
Setting up a spreadsheet....................................................................................... 160
Creating formulas.................................................................................................. 161
Understanding functions........................................................................................175
Strategies for creating formulas and functions.....................................................180
Finding and fixing errors.......................................................................................182
Examples of functions............................................................................................187
Using regular expressions in functions..................................................................191
Advanced functions................................................................................................ 192
Chapter 8
Using the DataPilot.................................................................................................. 193
Introduction........................................................................................................... 194
Examples with step by step descriptions...............................................................194
DataPilot functions in detail...................................................................................214
Function GETPIVOTDATA...................................................................................... 236
Chapter 9
Data Analysis........................................................................................................... 240
Introduction........................................................................................................... 241
Consolidating data................................................................................................. 241
Creating subtotals.................................................................................................. 243
Using “what if” scenarios...................................................................................... 245
Using other “what if” tools.................................................................................... 249
Working backwards using Goal Seek.....................................................................254
Using the Solver.....................................................................................................256
Chapter 10
Linking Calc Data...................................................................................................... 259
Why use multiple sheets?.......................................................................................260
Setting up multiple sheets.....................................................................................260
Referencing other sheets in the spreadsheet........................................................ 263
Referencing other documents: links to sheets in other spreadsheets...................266
Hyperlinks and URLs............................................................................................. 267
Linking to external data.........................................................................................270
Linking to registered data sources........................................................................274
Embedding spreadsheets....................................................................................... 278
Chapter 11
Sharing and Reviewing Documents..........................................................................283
Introduction........................................................................................................... 284
Sharing documents (collaboration)........................................................................ 284
Recording changes.................................................................................................286
Adding comments to changes................................................................................288
Adding other comments......................................................................................... 290
OpenOffice.org 3.3 Calc Guide 5
Reviewing changes................................................................................................ 291
Merging documents............................................................................................... 294
Comparing documents........................................................................................... 295
Saving versions...................................................................................................... 295
Chapter 12
Calc Macros............................................................................................................ 298
Introduction........................................................................................................... 299
Using the macro recorder...................................................................................... 299
Write your own functions.......................................................................................303
Accessing cells directly.......................................................................................... 310
Sorting................................................................................................................... 311
Conclusion..............................................................................................................312
Chapter 13
Calc as a Simple Database.......................................................................................313
Introduction........................................................................................................... 314
Associating a range with a name........................................................................... 315
Sorting................................................................................................................... 320
Filters..................................................................................................................... 322
Calc functions similar to database functions.........................................................330
Database-specific functions................................................................................... 339
Conclusion..............................................................................................................340
Chapter 14
Setting up and Customizing Calc.............................................................................341
Introduction........................................................................................................... 342
Choosing options that affect all of OOo.................................................................342
Choosing options for loading and saving documents.............................................347
Choosing options for Calc...................................................................................... 350
Controlling Calc’s AutoCorrect functions.............................................................. 358
Customizing the user interface.............................................................................. 358
Adding functionality with extensions..................................................................... 366
Appendix A
Keyboard Shortcuts..................................................................................................369
Introduction........................................................................................................... 370
Navigation and selection shortcuts........................................................................370
Function and arrow key shortcuts.........................................................................371
Cell formatting shortcuts.......................................................................................373
DataPilot shortcuts................................................................................................ 374
Appendix B
Description of Functions..........................................................................................375
Functions available in Calc.................................................................................... 376
Mathematical functions......................................................................................... 376
Financial analysis functions................................................................................... 381
Statistical analysis functions..................................................................................393
Date and time functions......................................................................................... 401
6 OpenOffice.org 3.3 Calc Guide
Logical functions.................................................................................................... 404
Informational functions.......................................................................................... 405
Database functions................................................................................................ 407
Array functions...................................................................................................... 408
Spreadsheet functions...........................................................................................410
Text functions......................................................................................................... 414
Add-in functions..................................................................................................... 417
Appendix C
Calc Error Codes...................................................................................................... 421
Introduction to Calc error codes............................................................................ 422
Error codes displayed within cells.........................................................................423
General error codes...............................................................................................424
Index.......................................................................................................................... 427
OpenOffice.org 3.3 Calc Guide 7
Note for Mac users
Some keystrokes and menu items are different on a Mac from those used in Windows
and Linux. The table below gives some common substitutions for the instructions in
this chapter. For a more detailed list, see the application Help.
Windows/Linux Mac equivalent Effect
Tools > Options
menu selection
OpenOffice.org >
Preferences
Access setup options
Right-click Control+click Open context menu
Ctrl (Control) z (Command) Used with other keys
F5 Shift+z+F5 Open the Navigator
F11 z+T Open Styles & Formatting window
8 OpenOffice.org 3.3 Calc Guide
Chapter 1
Introducing Calc
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; a wide range of 2D and 3D charts
• 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. See Chapter 12 (Calc Macros).
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.3, each sheet can have a maximum of 1,048,576 (65,536 rows in Calc
3.2 and earlier) and a maximum of 1024 columns
Parts of the main Calc window
When Calc is started, the main window looks similar to Figure 1.
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.
10 OpenOffice.org 3.3 Calc Guide
Figure 1: Parts of the Calc window
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).
• 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.
Chapter 1 Introducing Calc 11
• 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
Calc has several types of toolbars: docked (fixed in place), floating, and tear-off.
Docked toolbars can be moved to different locations or made to float, and floating
toolbars can be docked.
Four toolbars are located under the Menu bar by default: the Standard toolbar, the
Find 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.
Displaying or hiding toolbars
To display or hide toolbars, choose View > Toolbars, then click on the name of a
toolbar in the list. An active toolbar shows a check mark beside its name. Tear-off
toolbars are not listed in the View menu.
Palettes and tear-off toolbars
Toolbar icons with a small triangle to the right will display palettes, tear-off toolbars,
and other ways of selecting things, depending on the icon.
An example of a palette is shown in Figure 2. It is displayed by clicking the small
triangle to the right of the Borders toolbar icon.
An example of a tear-off toolbar is shown in Figure 3. Tear-off toolbars can be floating
or docked along an edge of the screen or in one of the existing toolbar areas. To move
a floating tear-off toolbar, drag it by the title bar.
12 OpenOffice.org 3.3 Calc Guide
Figure 2: Toolbar palette
Figure 3: Example of a tear-off toolbar
Moving toolbars
To move a docked toolbar, place the mouse pointer over the toolbar handle, hold
down the left mouse button, drag the toolbar to the new location, and then release
the mouse button.
To move a floating toolbar, click on its title bar and drag it to a new location, as
shown in Figure 3.
Docking/floating windows and toolbars
Toolbars and some windows, such as the Navigator and the Styles and Formatting
window, are dockable. You can move, resize, or dock them to an edge.
To dock a window or toolbar, hold down the Control key and double-click on the
frame of the floating window (or in a vacant area near the icons at the top of the
floating window) to dock it in its last position.
To undock a window, hold down the Control key and double-click on the frame (or a
vacant area near the icons at the top) of the docked window.
Chapter 1 Introducing Calc 13
Figure 4: Moving a docked toolbar
Figure 5: Control+double-click to dock or undock
Customizing toolbars
You can customize toolbars in several ways, including choosing which icons are
visible and locking the position of a docked toolbar.
To access a toolbar’s customization options, use the down-arrow at the end of the
toolbar or on its title bar (Figure 6).
To show or hide icons defined for the selected toolbar, choose Visible Buttons from
the drop-down menu. Visible icons are indicated by a border around the icon (Figure
7). Click on icons to hide or show them on the toolbar.
You can also add icons and create new toolbars, as described in Chapter 16.
Formatting toolbar
In the Formatting toolbar, the three boxes on the left are the Apply Style, Font
Name, and Font Size lists (see Figure 8). 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.
14 OpenOffice.org 3.3 Calc Guide
Figure 6: Customizing toolbars
Figure 7: Selection of visible toolbar icons
Figure 8: Apply Style, Font Name and Font Size lists
Note
If any of the icons (buttons) in Figure 8 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.
To the right of the Name Box are 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 (Using Formulas and Functions) 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.
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
Chapter 1 Introducing Calc 15
Figure 9: Formula Bar
the Input line area, click in the area, then type your changes. To edit within the
current cell, just double-click the cell.
Right-click (context) menus
Right-click on a cell, graphic, or other object to open a context menu. Often the
context menu is the fastest and easiest way to reach a function. If you’re not sure
where in the menus or toolbars a function is located, you may be able to find it by
right-clicking.
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 9). 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.
From Calc 3.3, you can choose colors for the different sheet tabs. Right-click on a tab
and choose Tab Color from the pop-up menu to open a palette of colors. To add new
colors to the palette, see “Color options” in Chapter 14 (Setting up and Customizing
Calc).
16 OpenOffice.org 3.3 Calc Guide
Figure 10: Choose tab color
Status bar
The Calc status bar provides information about the spreadsheet and convenient ways
to quickly change some of its features.
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 29 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.
Chapter 1 Introducing Calc 17
Figure 11: Left end of Calc status bar
Figure 12: Right end of Calc status bar
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).
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 ( )
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 start a new, blank document in Calc in several ways.
• From the operating system menu, in the same way that you start other
programs. When OOo was installed on your computer, in most cases a menu
entry for each component was added to your system menu. If you are using a
Mac, you should see the OpenOffice.org icon in the Applications folder. When
you double-click this icon, OOo opens at the Start Center (Figure 14).
• From the Quickstarter, which is found in Windows, some Linux distributions,
and (in a slightly different form) in Mac OS X. The Quickstarter is an icon that
is placed in the system tray or the dock during system startup. It indicates that
OpenOffice.org has been loaded and is ready to use.
Right-click the Quickstarter icon (Figure 13) in the system tray to open a popup menu from which you can open a new document, open the Templates and
Documents dialog box, or choose an existing document to open. You can also
double-click the Quickstarter icon to display the Templates and Documents
dialog box.
See Chapter 1 (Introducing OpenOffice.org) in the Getting Started guide for
more information about using the Quickstarter.
18 OpenOffice.org 3.3 Calc Guide
Figure 13: Quickstarter pop-up menu on Windows XP
• From the Start Center. When OOo is open but no document is open (for
example, if you close all the open documents but leave the program running),
the Start Center is shown. Click one of the icons to open a new document of
that type, or click the Templates icon to start a new document using a
template. If a document is already open in OOo, the new document opens in a
new window.
When OOo is open, you can also start a new document in one of the following ways.
• Press the Control+N keys.
• Use File > New > Spreadsheet.
• Click the New button on the main toolbar.
Starting a new document 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 (Figure 15), 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 http://extensions.services.openoffice.org/ and
installing them as described in Chapter 14 (Setting Up and Customizing Calc).
Chapter 1 Introducing Calc 19
Figure 14: OpenOffice.org Start Center
Figure 15: Starting a new spreadsheet from a template
Opening existing spreadsheets
When no document is open, the Start Center (Figure 14) provides an icon for opening
an existing document or choosing from a list of recently-edited documents.
You can also open an existing document in one of the following ways. If a document is
already open in OOo, the second document opens in a new window.
• Choose File > Open....
• Click the Open button on the main toolbar.
• Press Control+O on the keyboard.
• Use File > Recent Documents to display the last 10 files that were opened in
any of the OOo components.
• Use the Open Document or Recent Documents selections on the
Quickstarter.
In each case, the Open dialog box appears. Select the file you want, and then click
Open. If a document is already open in OOo, the second document opens in a new
window.
If you have associated Microsoft Office file formats with OpenOffice.org, you can also
open these files by double-clicking on them.
20 OpenOffice.org 3.3 Calc Guide
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 entered in
quotation marks; numbers are entered 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 16), 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.
Chapter 1 Introducing Calc 21
Figure 16: Text Import dialog, with Comma (,) selected as the separator and double
quotation mark (“) as the text delimiter.
6) In OOo 3.3, two new options are available when importing CSV files that
contain data separated by specific characters.
These options determine whether quoted data will always be imported as text,
and whether Calc will automatically detect all number formats, including
special number formats such as dates, time, and scientific notation. The
detection depends on the language settings.
7) 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.
Saving spreadsheets
Spreadsheets can be saved in three ways.
• Press Control+S.
• Choose File > Save (or Save All or Save As).
• Click the Save button on the main toolbar.
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.
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.
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 and set the time 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, available from
Softpedia, http://www.softpedia.com/get/Office-tools/Other-Office-Tools/Sun-ODF-
Plugin-for-Microsoft-Office.shtml.
22 OpenOffice.org 3.3 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 17), in the File type (or Save as type) dropdown menu, select the type of Excel format you need. Click Save.
Caution
From this point on, all changes you make to the spreadsheet will occur
only in the Microsoft Excel document. You have changed the name and
file type of your document. If you want to go back to working with the
*.ods version of your spreadsheet, you must open it again.
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.
Chapter 1 Introducing Calc 23
Figure 17. Saving a spreadsheet in Microsoft Excel format
Saving as a CSV file
To save a spreadsheet as a comma separated value (CSV) file:
1) Choose File > Save As.
2) In the File name box, type a name for 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, select the options you want and then click
OK.
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.
Password protection
Calc provides two levels of document protection: read-protect (file cannot be viewed
without a password) and write-protect (file can be viewed in read-only mode but
cannot be changed without a password). Thus you can make the content available for
reading by a selected group of people and for reading and editing by a different
group. This behavior is compatible with Microsoft Excel file protection.
24 OpenOffice.org 3.3 Calc Guide
Figure 18: Choosing options when exporting to Text CSV
1) Use File > Save As when saving the document. (You can also use File > Save
the first time you save a new document.)
2) On the Save As dialog, type the file name, select the Save with password
option, and then click Save.
3) The Set Password dialog opens.
Here you have several choices:
• To read-protect the document, type a password in the two fields at the top
of the dialog box.
• To write-protect the document, click the More Options button and select
the Open file read-only checkbox.
Chapter 1 Introducing Calc 25
Figure 19: Two levels of password protection
• To write-protect the document but allow selected people to edit it, select
the Open file read-only checkbox and type a password in the two boxes at
the bottom of the dialog box.
4) Click OK to save the file. If either pair of passwords do not match, you receive
an error message. Close the message box to return to the Set Password dialog
box and enter the password again.
Caution
OOo uses a very strong encryption mechanism that makes it almost
impossible to recover the contents of a document if you lose the
password.
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
9). 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 32 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 20). 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.
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.
26 OpenOffice.org 3.3 Calc Guide
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.
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
Chapter 1 Introducing Calc 27
Figure 20. (left) One selected cell and (right) a group of selected cells
Key Combination Movement
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)
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 21. 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.
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 21 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.
28 OpenOffice.org 3.3 Calc Guide
Figure 21: Customizing the effect of the Enter key
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 22 shows
how to do this.
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 22 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 20. 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.
Chapter 1 Introducing Calc 29
Move to the first sheet
Move left one sheet
Move right one sheet
Move to the last sheet
Sheet tabs
Figure 22. Sheet tab arrows
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.
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 20.
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 26.
To select a range of cells, enter the cell reference for the upper lefthand 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.
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.
30 OpenOffice.org 3.3 Calc Guide
Loading...