OpenOffice 3.2 Calc Guide
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
If you want to use macros written in Microsoft Excel using the
Note
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
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
Note
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
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-
Note
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.
You can also use the Recent Documents list to open a
Tip
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
If the spreadsheet has been previously saved, then saving it
using the Save (or Save All) command will overwrite an
Note
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
From this point on, all changes you make to the spreadsheet
Caution
Chapter 1 Introducing Calc 21
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.
To have Calc save documents by default in a Microsoft Excel
file format, go to Tools > Options > Load/Save > General.
Tip
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
Use one of the four Alt+Arrow key combinations to resize the
Tip
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
→
←
↑
↓
Control+→
Control+←
Control+↑
Control+↓
Control+Home To Cell A1
Control+End To lower right-hand corner of the rectangular area
Right one cell
Left one cell
Up one cell
Down one cell
To the next column to the right containing data in
that row or to Column AMJ
To the next column to the left containing data in that
row or to Column A
To the next row above containing data in that
column or to Row 1
To the next row below containing data in that
column or to Row 65536
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
Move to the first sheet
Move left one sheet
Move right one sheet
Move to the last sheet
Sheet tabs
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.
The sheet tab arrows that appear in Figure 12 only appear if
Note
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
3) Hold down the Shift key and click.
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,
Tip
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.
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
Tip
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.
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
Tip
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.
Select All
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
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.
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.
Note
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
Loading...