Calc Guide

1

ChapterIntroducing Calc
Using Spreadsheets in OpenOffice.org

This PDF is designed to be read onscreen, two pages at atime. If you want to print a copy, your PDF viewer shouldhave an option for printing two pages on one sheet ofpaper, but you may need to start with page 2 to get it toprint facing pages correctly. (Print this cover page
separately.)CopyrightThis 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 orlater, or the Creative Commons Attribution License, version 3.0 or
later.
All trademarks within this guide belong to their legitimate owners.

Authors
Rick Barnes
Peter Kupfer
Jean Hollis Weber
Krishna Aradhi
Andy Brown
Alexandre Martins
Anthony Petrillo
Gary Schnabl
Barbara M Tobias

Feedback
Please direct any comments or suggestions about this document to:authors@documentation.openoffice.org

Publication date and software version
Published 16 September 2010. Based on OpenOffice.org 3.2.

You can download
an editable version of this document fromhttp://oooauthors.org/english/userguide3/published/

Contents
Copyright............................................................................................... 2
What is Calc?......................................................................................... 5
Spreadsheets, sheets, and cells.............................................................5
Parts of the main Calc window...............................................................6
Title bar.............................................................................................. 6
Menu bar............................................................................................ 6
Toolbars.............................................................................................. 7
Formula Bar........................................................................................ 8
Individual cells.................................................................................... 9
Sheet tabs........................................................................................... 9
Status bar...........................................................................................9Starting new spreadsheets...................................................................11
Opening existing spreadsheets............................................................12
Opening CSV files................................................................................13
Saving spreadsheets............................................................................14
Password protection.........................................................................15
Saving a document automatically.....................................................15
Saving as a Microsoft Excel document.............................................15
Saving as a CSV file..........................................................................17
Saving in other formats....................................................................18
Navigating within spreadsheets...........................................................18
Going to a particular cell..................................................................18
Moving from cell to cell....................................................................18
Moving from sheet to sheet..............................................................21
Selecting items in a sheet or spreadsheet............................................22
Selecting cells................................................................................... 22
Selecting columns and rows.............................................................24
Selecting sheets................................................................................ 24
Working with columns and rows..........................................................25
Inserting columns and rows..............................................................25
Deleting columns and rows...............................................................26
Introducing Calc

3

Working with sheets............................................................................. 27
Inserting new sheets......................................................................... 27
Deleting sheets.................................................................................28
Renaming sheets............................................................................... 28
Viewing Calc........................................................................................ 28
Using zoom....................................................................................... 28
Freezing rows and columns..............................................................29
Splitting the screen........................................................................... 31
Using the Navigator............................................................................. 33
Moving quickly through a document.................................................35
Choosing a drag mode......................................................................35

4

Introducing Calc

What is Calc?
Calc is the spreadsheet component of OpenOffice.org (OOo). You canenter 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 andFilled Net Charts—have been introduced in OOo 3.2• Macros, for recording and executing repetitive tasks; scripting
languages supported include OpenOffice.org Basic, Python,
BeanShell, and JavaScript• Ability to open, edit, and save Microsoft Excel spreadsheets• Import and export of spreadsheets in multiple formats, including
HTML, CSV, PDF, and PostScript
Note

If you want to use macros written in Microsoft Excel using the
VBA macro code in OOo, you must first edit the code in the
OOo Basic IDE editor.

Spreadsheets, sheets, and cells
Calc works with elements called spreadsheets. Spreadsheets consist of
a number of individual sheets, each sheet containing cells arranged inrows and columns. A particular cell is identified by its row number andcolumn 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.

Spreadsheets, sheets, and cells

5Parts of the main Calc window
When Calc is started, the main window looks similar to Figure 1.

Figure 1: Parts of the Calc window

Note

If any part of the Calc window in Figure 1 is not shown, you candisplay 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 ofthem, 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 themenus, a submenu appears with other options. You can modify the
Menu bar, as discussed in Chapter 14 (Setting Up and CustomizingCalc).

6

Introducing Calc

• File contains commands that apply to the entire document suchas 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 userinterface 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, andMerge Cells.
• Tools contains functions such as Spelling, Share Document,

Cell Contents, Gallery, and Macros.

• Data contains commands for manipulating data in yourspreadsheet 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.

ToolbarsThree toolbars are located under the Menu bar by default: theStandard 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 turnedon 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 thecurrent 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.
Parts of the main Calc window

7

Figure 2: Apply Style, Font Name and Font Size lists

Note

If any of the icons (buttons) in Figure 2 is not shown, you candisplay it by clicking the small triangle at the right end of theFormatting toolbar, selecting Visible Buttons in the dropdown 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 theName 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 Function Wizard, Sum, andFunction 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 justmathematical functions. See Chapter 7 (Using Formulas andFunctions) 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.
8

Introducing Calc

Clicking the Function button inserts an equals (=) sign into theselected cell and the Input line, thereby enabling the cell to accept aformula.
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) aredisplayed 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, clickin the area, then type your changes. To edit within the current cell, justdouble-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 ofgray boxes containing letters and numbers. These are the column androw headers. The columns start at A and go on to the right, and therows 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 theseheaders 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 awhite 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 andconvenient ways to quickly change some of its features.

Figure 4: Left end of Calc status bar
Parts of the main Calc window

9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 notcorrespond 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 notin 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 22 for more information.
Unsaved changes ( )An asterisk (*) appears here if changes to the spreadsheet have notbeen saved.
Digital signature ()
If the document has not been digitally signed, double-clicking in thisarea 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 thisarea. 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 canright-click on this field and select other functions, such as the10

Introducing Calc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, theinformation 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, fromwithin Calc, or from any other component of OOo such as from Writeror 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 othercomponent of OOo (for example, Writer), click the down-arrow andchoose 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 theabove procedures, but instead of choosing Spreadsheet, choose theTemplates 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 anddouble-click on the required template. A new spreadsheet, based on
the selected template, opens.
Starting new spreadsheets11

A new OpenOffice.org installation does not contain many templates,
but you can add more by downloading them fromhttp://extensions.services.openoffice.org/ and installing them as
described in Chapter 14 (Setting Up and 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 fromany component of OOo. Calc can open spreadsheets in a wide range offile formats, including Microsoft Excel (*.xls and *.xlsx).
From the Start Center
Click the Open a document

icon.

From the Menu bar
Choose File > Open.

12

Introducing Calc

From a toolbar
Click the Open iconon the Standard toolbar.

From the keyboard
Press the key combination Control+O.
Each of these options displays the Open dialog, where you can locate
the spreadsheet that you want to open.

Tip

You can also use the Recent Documents list to open a
spreadsheet. This list is located on the File menu, directly
below Open. The list displays the last 10 files that wereopened 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 toseparate 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 thefile, 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.
CautionIf you do not select Text CSV (*csv;*txt;*xls) as the file typewhen opening the file, the document opens in Writer, not Calc.

Opening CSV files

13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 buttonon 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 theseactions will open the Save As dialog. There you can specify the
spreadsheet name and the location in which to save it.

14

Introducing Calc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 adifferent location or with a different name by selecting File >Save As.

Password protection
To protect an entire document from being viewable without apassword, 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, andthen 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 thepasswords 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 thepassword.

Saving a document automatically
You can choose to have Calc save your spreadsheet automatically at
regular intervals. Automatic saving, like manual saving, overwrites thelast 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. Enterthe value you want by typing it or by pressing the up or downarrow 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.

Saving spreadsheets15

Some users of Microsoft Excel may be unwilling or unable to receive*.ods files. (Perhaps their employer does not allow them to install theplug-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 havemade since the last time you saved it will only appear in theMicrosoft 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 astype) drop-down menu, select the type of Excel format you need.
Click Save.

Figure 8. Saving a spreadsheet in Microsoft Excel format

Caution16

From this point on, all changes you make to the spreadsheet
will occur only in the Microsoft Excel document. You haveactually changed the name of your document. If you want to go
back to working with the *.ods version of your spreadsheet, youmust open it again.

Introducing Calc

Tip

To have Calc save documents by default in a Microsoft Excel
file format, go to Tools > Options > Load/Save > General.
In the section named Default file format and ODF settings,
under Document type, select Spreadsheet, then under Always
save as, select your preferred file format.

Saving as a CSV file
To save a spreadsheet as a comma separate value (CSV) file:
1) Choose File > Save As.
2) In the File name box, type a name for the file.
3) In the File type list, select Text CSV (*.csv;*.txt;*.xls) and click
Save.
You may see the message box shown below. Click Keep Current
Format.

4) In the Export of text files dialog (Figure 9), select the options you
want and then click OK.

Figure 9: Choosing options when exporting to Text CSV

Saving spreadsheets

17

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 tocell 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 theName 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 thesame. Or just click into the Name Box, backspace over the existingcell 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 34, the Navigator would select cell A7. For more about using
the Navigator, see page 33.

Moving from cell to cellIn 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 cellhaving a dark border.

18

Introducing Calc

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 pointerto the cell where you want the focus to be and click the left mousebutton. This action changes the focus to the new cell. This method ismost 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 theleft, 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 andPage 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), andthe arrow keys move the focus of the current cell in other ways.
Table 1 describes the keyboard shortcuts for moving about aspreadsheet.

Navigating within spreadsheets

19

Tip

Use one of the four Alt+Arrow key combinations to resize theheight or width of a cell. (For example: Alt+↓ increases theheight 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 inthat row or to Column AMJ

Control+←

To the next column to the left containing data in thatrow or to Column A

Control+↑

To the next row above containing data in thatcolumn or to Row 1

Control+↓

To the next row below containing data in thatcolumn 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 rightShift+Tab

To the next cell on the left

Enter

Down one cell (unless changed by user)

Shift+EnterUp one cell (unless changed by user)

20

Introducing CalcCustomizing the effects of the Enter key
You can customize the direction in which the Enter key moves thefocus, by selecting Tools > Options > OpenOffice.org Calc >General.
The four choices for the direction of the Enter key are shown on theright hand side of Figure 11. It can move the focus down, right, up, orleft. 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 tochange 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 arethree ways to navigate between different sheets in a spreadsheet.
Using the keyboard
Pressing Control+Page Down moves one sheet to the right andpressing 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.

Navigating within spreadsheets

21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.
Note

The sheet tab arrows that appear in Figure 12 only appear ifyou 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.
22

Introducing Calc

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 instep 3 above. If you use this method, be sure to change EXTback 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 selectthe rest of the range.
The result of any of these methods looks like the right side of Figure
10.

Tip

You can also directly select a range of cells using the Name
Box. Click into the Name Box as described in “Using a cellreference” on page 18. To select a range of cells, enter the cellreference for the upper left-hand cell, followed by a colon (:),
and then the lower right-hand cell reference. For example, toselect 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 singlecell.
3) Hold down the Control key and click or click-and-drag to selectanother range of cells to add to the first range.
4) Repeat as necessary.

Tip

You can also select a noncontiguous range of cells by firstclicking 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 therange 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 items in a sheet or spreadsheet

23

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 (seeFigure 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 toselect multiple sheets at times when you want to make changes tomany 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).

24

Introducing Calc

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 thatyou 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 Sheetsfrom the pop-up menu.

Working with columns and rows
Inserting columns and rows
Columns and rows can be inserted individually or in groups.

Note

When you insert a single new column, it is inserted to the leftof the highlighted column. When you insert a single new row, it
is inserted above the highlighted row.
Cells in the new columns or rows are formatted like thecorresponding cells in the column or row before (or to the leftof) 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.
Working with columns and rows252) Choose either Insert > Columns or Insert > Rows.
Using the mouse:
1) Select the cell, column, or row where you want the new column or
row inserted.
2) Right-click the header of the column or row.
3) Choose Insert Rows or Insert Columns.
Multiple columns or rows
Multiple columns or rows can be inserted at once rather than insertingthem one at a time.
1) Highlight the required number of columns or rows by holding
down the left mouse button on the first one and then dragging
across the required number of identifiers.
2) Proceed as for inserting a single column or row above.

Deleting columns and rows
Columns and rows can be deleted individually or in groups.
Single column or row
A single column or row can be deleted by using the mouse:
1) Select the column or row to be deleted.
2) Choose Edit > Delete Cells from the menu bar.
Or,1) Right-click on the column or row header.
2) Choose Delete Columns or Delete Rows from the pop-up menu.
Multiple columns or rows
Multiple columns or rows can be deleted at once rather than deletingthem one at a time.
1) Highlight the required columns or rows by holding down the left
mouse button on the first one and then dragging across therequired number of identifiers.
2) Proceed as for deleting a single column or row above.

Tip

26

Instead of deleting a row or column, you may wish to delete
the contents of the cells but keep the empty row or column.
See Chapter 2 (Entering, Editing, and Formatting Data) for
instructions.

Introducing Calc

Working with sheets
Like any other Calc element, sheets can be inserted, deleted, andrenamed.

Inserting new sheets
There are several ways to insert a new sheet. The first step for all of
the methods is to select the sheets that the new sheet will be inserted
next to. Then any of the following options can be used.
• Choose Insert > Sheet from the menu bar.
• Right-click on the sheet tab and choose Insert Sheet.
• Click in an empty space at the end of the line of sheet tabs.
Click here to insert
a new sheet

Figure 14. Creating a new sheet
Each method will open the Insert Sheet dialog. Here you can selectwhether the new sheet is to go before or after the selected sheet andhow many sheets you want to insert. If you are inserting only one
sheet, there is the opportunity to give the sheet a name.

Figure 15: Insert Sheet dialog

Working with sheets

27

Deleting sheets
Sheets can be deleted individually or in groups.
Single sheet
Right-click on the tab of the sheet you want to delete and choose
Delete Sheet from the pop-up menu, or choose Edit > Sheet >Delete from the Menu bar. Either way, an alert will ask if you want
to delete the sheet permanently. Click Yes.
Multiple sheets
To delete multiple sheets, select them as described earlier, theneither right-click over one of the tabs and choose Delete Sheetfrom the pop-up menu, or choose Edit > Sheet > Delete from the
Menu bar.

Renaming sheets
The default name for the a new sheet is SheetX, where X is a number.
While this works for a small spreadsheet with only a few sheets, it
becomes awkward when there are many sheets.
To give a sheet a more meaningful name, you can:
• Enter the name in the Name box when you create the sheet, or• Right-click on a sheet tab and choose Rename Sheet from the
pop-up menu; replace the existing name with a different one.
• (New in OOo3.1) Double-click on a sheet tab to pop up the
Rename Sheet dialog.

Note

Sheet names must start with either a letter or a number; othercharacters including spaces are not allowed. Apart from thefirst character of the sheet name, allowed characters areletters, numbers, spaces, and the underscore character.
Attempting to rename a sheet with an invalid name willproduce an error message.

Viewing Calc
Using zoom
Use the zoom function to change the view to show more or fewer cells
in the window.

28

Introducing Calc

In addition to using the Zoom slider (new in OOo 3.1) on the Status bar(see page 11), you can open the Zoom dialog and make a selection on
the left-hand side.
• Choose View > Zoom from the Menu bar, or• Double-click on the percentage figure in the Status bar at thebottom of the window.

Figure 16. Zoom dialog
OptimalResizes the display to fit the width of the selected cells. To use thisoption, you must first highlight a range of cells.
Fit Width and Height
Displays the entire page on your screen.
Fit Width
Displays the complete width of the document page. The top andbottom edges of the page may not be visible.
100%
Displays the document at its actual size.
VariableEnter a zoom percentage of your choice.

Freezing rows and columns
Freezing locks a number of rows at the top of a spreadsheet or anumber of columns on the left of a spreadsheet or both. Then whenscrolling around within the sheet, any frozen columns and rows remain
in view.
Viewing Calc

29

Figure 17 shows some frozen rows and columns. The heavier horizontal
line between rows 3 and 14 and the heavier vertical line between
columns C and H denote the frozen areas. Rows 4 through 13 andcolumns D through G have been scrolled off the page. The first threerows and columns remained because they are frozen into place.

Figure 17. Frozen rows and columns
You can set the freeze point at one row, one column, or both a row anda column as in Figure 17.
Freezing single rows or columns
1) Click on the header for the row below where you want the freezeor for the column to the right of where you want the freeze.
2) Choose Window > Freeze.
A dark line appears, indicating where the freeze is put.
Freezing a row and a column
1) Click into the cell that is immediately below the row you wantfrozen and immediately to the right of the column you wantfrozen.
2) Choose Window > Freeze.
Two lines appear on the screen, a horizontal line above this celland a vertical line to the left of this cell. Now as you scroll aroundthe screen, everything above and to the left of these lines willremain in view.
Unfreezing
To unfreeze rows or columns, choose Window > Freeze. The check
mark by Freeze will vanish.
30

Introducing Calc

Splitting the screen
Another way to change the view is by splitting the window, also known
as splitting the screen. The screen can be split horizontally, vertically,or both. You can therefore have up to four portions of the spreadsheet
in view at any one time.

Figure 18. Split screen example
Why would you want to do this? Imagine you have a large spreadsheet
and one of the cells has a number in it that is used by three formulas inother cells. Using the split-screen technique, you can position the cell
containing the number in one section and each of the cells withformulas in the other sections. Then you can change the number in thecell and watch how it affects each of the formulas.

Splitting the screen horizontally
To split the screen horizontally:
1) Move the mouse pointer into the vertical scroll bar, on the righthand side of the screen, and place it over the small button at the
top with the black triangle.

Split screen bar

Figure 19. Split screen bar onvertical scroll bar

Viewing Calc

31

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

Figure 20. Split-screen bar onvertical scroll bar with cursor
3) Hold down the left mouse button. A gray line appears, running
across the page. Drag the mouse downwards and this line follows.
4) Release the mouse button and the screen splits into two views,
each with its own vertical scroll bar. You can scroll the upper andlower parts independently.
Notice in Figure 18, the Beta and the A0 values are in the upper partof the window and other calculations are in the lower part. Thus, you
can make changes to the Beta and A0 values and watch their effects on
the calculations in the lower half of the window.
Tip

You can also split the screen using a menu command. Click in acell immediately below and to the right of where you wish thescreen to be split, and choose Window > Split.

Splitting the screen vertically
To split the screen vertically:
1) Move the mouse pointer into the horizontal scroll bar at thebottom of the screen and place it over the small button on theright with the black triangle.

Split screen bar

Figure 21: Split bar on
horizontal scroll bar2) Immediately to the right of this button is a thick black line (Figure21). Move the mouse pointer over this line and it turns into a linewith two arrows.
32

Introducing Calc

3) Hold down the left mouse button, and a gray line appears,
running up the page. Drag the mouse to the left and this line
follows.
4) Release the mouse button, and the screen is split into two views,
each with its own horizontal scroll bar. You can scroll the left andright parts of the window independently.

Removing split views
To remove a split view, do any of the following:• Double-click on each split line.
• Click on and drag the split lines back to their places at the ends ofthe scroll bars.
• Choose Window > Split to remove all split lines at the same
time.

Using the Navigator
In addition to the cell reference boxes (labeled Column and Row), theNavigator provides several other ways to move quickly through a
spreadsheet and find specific items.
To open the Navigator, click its iconon the Standard toolbar, orpress F5, or choose View > Navigator on the Menu bar, or doubleclick on the Sheet Sequence Numberin the Status Bar. You
can dock the Navigator to either side of the main Calc window or leave
it floating. (To dock or float the Navigator, hold down the Control key
and double-click in an empty area near the icons at the top.)
The Navigator displays lists of all the objects in a spreadsheet
document, grouped into categories. If an indicator (plus sign or arrow)appears next to a category, at least one object of this kind exists. To
open a category and see the list of items, click on the indicator.
To hide the list of categories and show only the icons at the top, click
the Contents icon

. Click this icon again to show the list.

Table 2 summarizes the functions of the icons at the top of theNavigator.

Using the Navigator

33

Figure 22: The Navigator in Calc
Table 2: Function of icons in the Navigator
Icon

ActionData Range. Specifies the current data range denoted by the
position of the cell cursor.
Start/End. Moves to the cell at the beginning or end of thecurrent data range, which you can highlight using the Data
Range button.
Contents. Shows or hides the list of categories.
Toggle. Switches between showing all categories and showing
only the selected category.
Displays all available scenarios. Double-click a name to apply that
scenario. See Chapter 9 (Data Analysis) for more information.
Drag Mode. Choose hyperlink, link, or copy. See “Choosing a dragmode” for details.

34

Introducing Calc

Moving quickly through a document
The Navigator provides several convenient ways to move around a
document and find items in it:• To jump to a specific cell in the current sheet, type its cellreference in the Column and Row boxes at the top of theNavigator and press the Enter key; for example, in Figure 22 thecell reference is A7.
• When a category is showing the list of objects in it, double-clickon an object to jump directly to that object’s location in the
document.
• To see the content in only one category, highlight that category
and click the Toggle icon. Click the icon again to display all thecategories.
• Use the Start and End icons to jump to the first or last cell in theselected data range.

Tip

Ranges, scenarios, pictures, and other objects are much easier
to find if you have given them informative names when creating
them, instead of keeping Calc’s default Graphics 1, Graphics 2,
Object 1, and so on, which may not correspond to the position of
the object in the document.

Choosing a drag mode
Sets the drag and drop options for inserting items into a document
using the Navigator.
Insert as Hyperlink
Creates a hyperlink when you drag and drop an item into thecurrent document.
Insert as Link
Inserts the selected item as a link where you drag and drop an
object into the current document.
Insert as Copy
Inserts a copy of the selected item where you drag and drop in thecurrent document. You cannot drag and drop copies of graphics,
OLE objects, or indexes.

Using the Navigator

35