Getting Started Guide

5

Chapter
Getting Started with
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
Richard Barnes
Richard Detwiler
John KanePeter Kupfer
Jean Hollis Weber
Linda Worthington
Michele Zarri

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

Acknowledgments
Thanks to Robert Scott for proofreading an earlier version of thisdocument.

Publication date and software version
Published 23 January 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?......................................................................................... 6
Spreadsheets, sheets and cells..............................................................6
Parts of the main Calc window...............................................................7
Title bar.............................................................................................. 7
Menu bar............................................................................................ 7
Toolbars.............................................................................................. 7
Formula bar........................................................................................ 8
Individual cells.................................................................................... 9
Sheet tabs........................................................................................... 9
Status bar........................................................................................... 9Starting new spreadsheets................................................................... 10
Opening existing spreadsheets............................................................11
Opening CSV files................................................................................11
Saving spreadsheets............................................................................12
Saving as a CSV file..........................................................................13
Navigating within spreadsheets...........................................................14
Going to a particular cell..................................................................14
Moving from cell to cell....................................................................15
Moving from sheet to sheet..............................................................17
Selecting items in a sheet or spreadsheet............................................18
Selecting cells................................................................................... 18
Selecting columns and rows.............................................................19
Selecting sheets................................................................................ 20
Working with columns and rows..........................................................21
Inserting columns and rows..............................................................21
Deleting columns and rows...............................................................21
Working with sheets............................................................................. 22
Inserting new sheets......................................................................... 22
Deleting sheets.................................................................................23
Renaming sheets............................................................................... 23
Getting Started with Calc

3

Viewing Calc........................................................................................ 24
Using zoom....................................................................................... 24
Freezing rows and columns..............................................................24
Splitting the screen........................................................................... 25
Entering data using the keyboard........................................................28Entering numbers.............................................................................28Entering text..................................................................................... 28
Entering numbers as text.................................................................. 28
Entering dates and times..................................................................29
Deactivating automatic changes.......................................................29
Speeding up data entry........................................................................29
Using the Fill tool on cells................................................................30
Using selection lists..........................................................................32
Sharing content between sheets..........................................................33
Validating cell contents........................................................................33
Editing data.......................................................................................... 33
Removing data from a cell................................................................34
Replacing all the data in a cell..........................................................34
Changing part of the data in a cell....................................................34
Formatting data...................................................................................35
Formatting multiple lines of text.......................................................35
Shrinking text to fit the cell..............................................................37
Formatting numbers.........................................................................37
Formatting the font........................................................................... 38
Formatting the cell borders..............................................................39
Formatting the cell background........................................................39
Autoformatting cells and sheets...........................................................39
Defining a new AutoFormat..............................................................40
Formatting spreadsheets using themes...............................................41
Using conditional formatting...............................................................41
Hiding and showing data.....................................................................42
Outline group controls......................................................................42
4

Getting Started with Calc

Filtering which cells are visible........................................................43
Sorting records.................................................................................... 44
Printing................................................................................................ 45
Selecting sheets to print...................................................................46
Selecting the page order, details, and scale......................................46
Using print ranges............................................................................48
Printing rows or columns on every page...........................................49
Page breaks....................................................................................... 50
Headers and footers.......................................................................... 51

Getting Started with Calc

5

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.
Other features provided by Calc include:• Functions, which can be used to create formulas to perform
•
•
•
•
•

complex calculations on data
Database functions, to arrange, store, and filter data
Dynamic charts: a wide range of 2D and 3D charts
Macros, for recording and executing repetitive tasks
Ability to open, edit, and save Microsoft Excel spreadsheets
Import and export of spreadsheets in multiple formats, including
HTML, CSV, PDF, and PostScript

Note

If you want to use macros written in Microsoft Excel using the
VBA macro code in OOo, you must first edit the code in the
OOo Basic IDE editor. See Chapter 13 (Getting Started with
Macros) in this book and Chapter 12 in the Calc Guide.

Spreadsheets, sheets and cells
Calc works with documents called spreadsheets. Spreadsheets consistof 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.

6

Getting Started with Calc

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

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 (Customizing OpenOffice.org).

ToolbarsThree toolbars are located under the Menu bar by default: theStandard toolbar, the Formatting toolbar, and the Formula Bar.
The icons on these toolbars provide a wide range of common
commands and functions. You can also modify these toolbars, as
discussed in Chapter 14 (Customizing OpenOffice.org).

Parts of the main Calc window7

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

Figure 2. Apply Style, Font Name and Font Size boxes

Formula bar
On the left hand side of the Formula bar (see Figure 3) 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, 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 includes much more thanmathematical functions. See Chapter 7 in the Calc Guide.
The Sum button inserts a formula into the current cell that totals thenumbers 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

Getting Started with Calc

The Function button inserts an equals (=) sign into the selected celland the Input Line, thereby setting the cell ready 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) aredisplayed in the Input line, which forms the remainder of the FormulaBar. You can either edit the cell contents of the current cell here, 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 individual cells in the formof a grid, with each cell being at the intersection of a column and arow.
At the top of the columns and at the left-hand end of the rows are aseries of gray boxes containing letters and numbers. These are thecolumn 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 Sheet Area box on the Formula Bar (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, which enable
access to each individual sheet. The visible (active) sheet has a whitetab.
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
At the very bottom of the Calc window is the status bar, which provides
information about the spreadsheet and convenient ways to quickly
change some of its features. Most of the fields are similar to those inother components of OOo; see Chapter 1 (Introducing OpenOffice.org)
in this book and Chapter 1 (Introducing Calc) in the Calc Guide.

Parts of the main Calc window

9Figure 4: Left end of Calc status bar

Figure 5: Right end of Calc status bar

Starting new spreadsheets
A new spreadsheet can be opened from any component of OOo, forexample from Writer or Draw.
From the Start Center
Click the Spreadsheet icon.

From the Menu bar
Choose File > New > Spreadsheet.
From the toolbar
If a document is open in any component of OOo (for example,
Writer), you can use the New Document button
at the lefthand end of 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 theabove procedures, but instead of choosing Spreadsheet, choose
Templates and Documents. On the Templates and Documents
window, navigate to the appropriate folder and double-click on therequired template. A new spreadsheet, based on the selected
template, opens.
10

Getting Started with Calc

Opening existing spreadsheets
You can open an existing spreadsheet from the Start Center or fromany component of OOo.
From the Start Center
Click the Open a document icon.

From the Menu bar
Choose File > Open.
From the toolbar
Click the Open buttonon 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 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 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 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 6), select the Separator
options to divide the text in the file into columns.
Opening CSV files

116) 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.
7) 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.
8) Click OK to open the file.
CautionIf you do not select Text CSV as the file type when opening thefile, the document opens in Writer, not Calc.

Figure 6: 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).

12

Getting Started with Calc

From the toolbar
Click the Save buttonon the Function bar. If the file has beensaved and no subsequent changes have been made, this button isgrayed-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. Here you can specify the
spreadsheet name and the location in which to save it.

Note

If the spreadsheet has been previously saved, then saving will
overwrite the existing copy without opening the Save As
dialog. If you want to save the spreadsheet in a different
location or with a different name, then select File > Save As.

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 and click Save.
You may see the message box shown below. Click Keep Current
Format.

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

Saving spreadsheets13

Figure 7: Choosing options when exporting to Text CSVNavigating 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.
Or just click into the Name box, backspace over the existing cellreference and type in the cell reference you want.
Using the Navigator
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.
Type the cell reference into the top two fields, labeled Column andRow, and press Enter. In Figure 8 the Navigator would select cell
A7.
You can dock the Navigator to either side of the main Calc windowor leave it floating. (To dock or float the Navigator, hold down theControl key and double-click in an empty area near the icons at the
top.)

14

Getting Started with Calc

Figure 8: The Navigator in Calc
The Navigator displays lists of all the objects in a spreadsheet
document, grouped into categories. If an indicator (plus sign orarrow) appears next to a category, at least one object of this kindexists. To open a category and see the list of items, click on theindicator.
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.

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 9). If a group ofcells is selected, they have a highlight color (usually gray), with thefocus cell having a dark border.
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 changes the focus to the new cell. This method is mostuseful when the two cells are a large distance apart.

Navigating within spreadsheets

15

Figure 9. (Left) One selected cell and (right) a group of
selected cells
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 right or 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 andPage Up moves the display up one complete screen.
• Combinations of Control and Alt with Home, End, Page Down,
Page Up, and the cursor keys move the focus of the current cell inother ways.
Tip

Holding down Alt+Cursor key resizes a cell.

Customizing 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 10. It can move the focus down, right, up, or

16

Getting Started with Calc

left. Depending on the file being used or on the type of data being
entered, different directions can be useful.
The Enter key can also be used to switch into and out of editing mode.
Use the options under Input settings in Figure 10 to change the Enter
key settings.

Figure 10: Customizing the effect of the Enter key

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+PgDn moves one sheet to the right and pressing
Control+PgUp 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 11 shows how to do this.
Notice that the sheets here are not numbered in order. Sheet
numbering is arbitrary—you can name a sheet as you wish.
Note

The sheet tab arrows that appear in Figure 11 only appear ifyou have some sheet tabs that can not be seen. Otherwise,
they will appear faded as in Error: Reference source not found.

Navigating within spreadsheets

17Move to the first sheet
Move left one sheet
Move right one sheet
Move to the last sheet
Sheet tabs

Figure 11. Sheet tab arrows

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 9.
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.
3) Hold down the Shift key and click.
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.

18

Getting Started with Calc

The result of any of these methods looks like the right side of Figure 9.

Tip

You can also directly select a range of cells using the Namebox. Click into the Name Box as described in “Using a cellreference” on page 14. 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 non-contiguous 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 select a
range.
4) Repeat as necessary.

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 (see Figure 12).

Selecting items in a sheet or spreadsheet

19

Select All

Figure 12. 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 13).
Multiple contiguous sheets
To select multiple contiguous sheets:
1) Click on the sheet tab for the first sheet.
2) Move the mouse pointer over the last sheet tab.
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 non contiguous sheets
To select multiple non contiguous sheets:
1) Click on the sheet tab for the first sheet.
2) Move the mouse pointer over the second sheet tab.
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.

20

Getting Started with Calc

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 column or rows where you want the new column or row
inserted.
2) Select either Insert > Columns or Insert > Rows.
Using the mouse:
1) Select the column or rows where you want the new column or row
inserted.
2) Right-click the header.
3) Select 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 only be deleted by using the mouse:
1) Select the column or row to be deleted.
2) Right-click on the column or row header.
3) Select Delete Columns or Delete Rows from the pop-up menu.
Working with columns and rows

21

Multiple columns or rows
Multiple columns or rows can be deleted at once rather than deletingthem 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 deleting a single column or row above.

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.
• Chosse Insert > Sheet from the menu bar.
• Right-click on the sheet tab and select Insert Sheet.
• Click in an empty space at the end of the line of sheet tabs (seeFigure 13).
Click here to insert
a new sheet

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

22

Getting Started with Calc

Figure 14. Insert Sheet dialog

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 selectDelete Sheet from the pop-up menu, or chose Edit > Sheet >Delete from the menu bar.
Multiple sheets
To delete multiple sheets, select them as described earlier, theneither right-click over one of the tabs and select Delete Sheet fromthe pop-up menu, or choose Edit > Sheet > Delete from the menubar.

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 better one.
• (New in OOo3.1) Double-click on a sheet tab to pop up the

Rename Sheet dialog.

Working with sheets

23

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 underline 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. For more about zoom, see Chapter 1 (Introducing
OpenOfice.org).

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.
Figure 15 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 are frozen into place.

Figure 15. Frozen rows and columns

24

Getting Started with Calc

You can set the freeze point at one row, one column, or both a row anda column as in Figure 15.
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.

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 either horizontally or
vertically or both. This allows you to have up to four portions of the
spreadsheet in view at any one time.
Why would you want to do this? Imagine you have a large spreadsheet
and one of the cells has a number in it which is used by three formulas
in other cells. Using the split screen technique, you can position thecell 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.

Viewing Calc

25

Figure 16. Split screen example

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 17. Split screen bar on vertical scroll bar2) Immediately above this button you will see a thick black line
(Figure 17). Move the mouse pointer over this line and it turns
into a line with two arrows (Figure 18).

Figure 18. 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.
26

Getting Started with Calc

Notice in Figure 16, 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 affects 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 19: Split bar on horizontal scroll bar2) Immediately to the right of this button is a thick black line (Figure19). Move the mouse pointer over this line and it turns into a linewith two arrows.
3) Hold down the left mouse button and a gray line appears, runningup 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.

Viewing Calc

27

Entering data using the keyboard
Most data entry in Calc can be accomplished using the keyboard.

Entering numbers
Click in the cell and type in the number using the number keys oneither the main keyboard or the numeric keypad.
To enter a negative number, either type a minus (–) sign in front of it or
enclose it in parentheses (brackets), like this: (1234).
By default, numbers are right-aligned and negative numbers have aleading minus symbol.

Entering text
Click in the cell and type the text. Text is left-aligned by default.

Entering numbers as text
If a number is entered in the format 01481, Calc will drop the leading
0. (Exception: see Tip below.) To preserve the leading zero, for example
for telephone area codes, type an apostrophe before the number, like
this: '01481.
The data is now regarded as text by Calc. Formulas and functions willtreat the entry like any other text entry, which typically results in it
being a zero in a formula, and being ignored in a function.

28

Tip

Numbers can have leading zeros and be regarded as numbers(as opposed to text) if the cell is formated appropriately. Rightclick on the cell and chose Format Cells > Numbers. Adjust
the leading zeros setting to add leading zeros to numbers.

Note

When using an apostrophe to allow a leading 0 to be displayed,
the apostrophe is not visible in the cell after the Enter key ispressed—if the apostrophe is a plain apostrophe (not a “smart
quote” apostrophe). If “smart quotes” are selected for
apostrophes, the apostrophe remains visible in the cell.
To choose the type of apostrophe, use Tools > AutoCorrect >Custom Quotes. The selection of the apostrophe type affects
both Calc and Writer.

Getting Started with CalcCaution

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

Entering dates and times
Select the cell and type the date or time. You can separate the dateelements with a slant (/) or a hyphen (–) or use text such as 10 Oct 03.
Calc recognizes a variety of date formats. You can separate timeelements with colons such as 10:43:45.

Deactivating automatic changes
Calc automatically applies many changes during data input, unless you
deactivate those changes. You can also immediately undo any
automatic changes with Ctrl+Z.
AutoCorrect changes
Automatic correction of typing errors, replacement of straightquotation marks by curly (custom) quotes, and starting cell content
with an uppercase (capital letter) are controlled by Tools >AutoCorrect Options. Go to the Custom Quotes, Options, orReplace tabs to deactivate any of the features that you do not want.
On the Replace tab, you can also delete unwanted word pairs andadd new ones as required.
AutoInput
When you are typing in a cell, Calc automatically suggests matching
input found in the same column. To turn the AutoInput on and off,
set or remove the check mark in front of Tools > Cell Contents >AutoInput.
Automatic date conversion
Calc automatically converts certain entries to dates. To ensure thatan entry that looks like a date is interpreted as text, type anapostrophe at the beginning of the entry. The apostrophe is not
displayed in the cell.

Speeding up data entry
Entering data into a spreadsheet can be very labor-intensive, but Calcprovides several tools for removing some of the drudgery from input.

Speeding up data entry

29

The most basic ability is to drop and drag the contents of one cell toanother with a mouse. However, Calc also includes several other tools
for automating input, especially of repetitive material. They include theFill tool, selection lists, and the ability to input information into
multiple sheets of the same document.

Using the Fill tool on cells
At its simplest, the Fill tool is a way to duplicate existing content. Start
by selecting the cell to copy, then drag the mouse in any direction (orhold down the Shift key and click in the last cell you want to fill), andthen choose Edit > Fill and the direction in which you want to copy:
Up, Down, Left or Right.
CautionTip

Choices that are not available are grayed out, but you can stillchoose the opposite direction from what you intend, which
could cause you to overwrite cells accidentally.
A shortcut way to fill cells is to grab the “handle” in the lower
right-hand corner of the cell and drag it in the direction you
want to fill. If the cell contains a number, the number will fill inseries. If the cell contains text, the same text will fill in the
direction you chose.

Figure 20: Using the Fill tool

Using a fill series
A more complex use of the Fill tool is to use a fill series. The default
lists are for the full and abbreviated days of the week and the monthsof the year, but you can create your own lists as well.
To add a fill series to a spreadsheet, select the cells to fill, choose Edit
> Fill > Series. In the Fill Series dialog, select AutoFill as the Series
30

Getting Started with Calc

type, and enter as the Start value an item from any defined series. The
selected cells then fill in the other items on the list sequentially,
repeating from the top of the list when they reach the end of the list.

Figure 21: Specifying the start of a fill series (result is in Figure 20)

Figure 22: Result of fill series
selection shown in Figure 21
You can also use Edit > Fill > Series to create a one-time fill series
for numbers by entering the start and end values and the increment.
For example, if you entered start and end values of 1 and 7 with anincrement of 2, you would get the sequence of 1, 3, 5, 7.
In all these cases, the Fill tool creates only a momentary connection
between the cells. Once they are filled, the cells have no further
connection with one another.

Speeding up data entry

31

Defining a fill series
To define a fill series, go to Tools > Options > OpenOffice.org Calc
> Sort Lists. This dialog shows the previously-defined series in the
Lists box on the left, and the contents of the highlighted list in the
Entries box.

Figure 23: Predefined fill series
Click New. The Entries box is cleared. Type the series for the new listin the Entries box (one entry per line), and then click Add.

Figure 24: Defining a new fill series

Using selection lists
Selection lists are available only for text, and arelimited to using only text that has already been enteredin the same column.
To use a selection list, select a blank cell and press
Ctrl+D. A drop-down list appears of any cell in the same
column that either has at least one text character orwhose format is defined as Text. Click on the entry you
require.

32

Getting Started with Calc

Sharing content between sheets
You might want to enter the same information in the same cell on
multiple sheets, for example to set up standard listings for a group ofindividuals or organizations. Instead of entering the list on each sheet
individually, you can enter it in all the sheets at once. To do this, selectall the sheets (Edit > Sheet > Select), then enter the information in
the current one.
CautionThis technique overwrites any information that is already inthe cells on the other sheets—without any warning. For thisreason, when you are finished, be sure to deselect all the
sheets except the one you want to edit. (Ctrl+click on a sheet
tab to select or deselect the sheet.)

Validating cell contents
When creating spreadsheets for other people to use, you may want tomake sure they enter data that is valid or appropriate for the cell. You
can also use validity in your own work as a guide to entering data thatis either complex or rarely used.
Fill series and selection lists can handle some types of data, but they
are limited to predefined information. For a more general case, you canselect a cell and use Data > Validity to define the type of contents
that can be entered in that cell. For example, a cell might require adate or a whole number, with no alphabetic characters or decimal
points; or a cell may not be blank.
Depending on how validity is set up, the tool can also define the rangeof contents that can be entered and provide help messages that explain
the content rules you have set up for the cell and what users should dowhen they enter invalid content. You can also set the cell to refuse
invalid content, accept it with a warning, or start a macro when anerror is entered.
See Chapter 2 in the Calc Guide for more information.

Editing data
Editing data is done is in much the same way as it is entered. The firststep is selecting the cell containing the data to be edited.

Editing data33

Removing data from a cell
Data can be removed (deleted) from a cell in several ways.
Removing data only
The data alone can be removed from a cell without removing any of
the formatting of the cell. Click in the cell to select it, and then press
the Backspace key.
Removing data and formatting
The data and the formatting can be removed from a cell at the sametime. Press the Delete key (or right-click and choose Delete
Contents, or use Edit > Delete Contents) to open the Delete
Contents dialog (Figure 25). From this dialog, the different aspectsof the cell can be deleted. To delete everything in a cell (contents
and format), check Delete all.

Figure 25: Delete Contents dialog

Replacing all the data in a cell
To remove data and insert new data, simply type over the old data. The
new data will retain the original formatting.

Changing part of the data in a cell
Sometimes it is necessary to change the contents of cell without
removing all of the contents, for example if the phrase “See Dick run”
is in a cell and it needs to be changed to “See Dick run fast.” It is often
useful to do this without deleting the old cell contents first.

34

Getting Started with Calc

The process is the similar to the one described above, but you need toplace the cursor inside the cell. You can do this in two ways.
Using the keyboard
After selecting the appropriate cell, press the F2 key and the cursor
is placed at the end of the cell. Then use the keyboard arrow keys tomove the cursor through the text in the cell.
Using the mouse
Using the mouse, either double-click on the appropriate cell (toselect it and place the cursor in it for editing), or single-click to
select the cell and then move the mouse pointer up to the input line
and click into it to place the cursor for editing.

Formatting data
The data in Calc can be formatting in several ways. It can either beedited as part of a cell style so that it is automatically applied, or it canbe applied manually to the cell. Some manual formatting can beapplied using toolbar icons. For more control and extra options, selectthe appropriate cell or cells, right-click on it, and select Format Cells.
All of the format options are discussed below.
Note

All the settings discussed in this section can also be set as apart of the cell style. See Chapter 4 (Using Styles andTemplates) in the Calc Guide for more information.

Formatting multiple lines of text
Multiple lines of text can be entered into a single cell using automatic
wrapping or manual line breaks. Each method is useful for different
situations.

Using automatic wrapping
To set text to wrap at the end of the cell, right-click on the cell andselect Format Cells (or choose Format > Cells from the menu bar, orpress Ctrl+1). On the Alignment tab (Figure 26), under Properties,
select Wrap text automatically. The results are shown in Figure 27.

Formatting data

35

Figure 26: Format Cells > Alignment dialog

Figure 27: Automatic text wrap

Using manual line breaks
To insert a manual line break while typing in a cell, press Ctrl+Enter.
This method does not work with the cursor in the input line. Whenediting text, first double-click the cell, then single-click at the position
where you want the line break.
When a manual line break is entered, the cell width does not change.
Figure 28 shows the results of using two manual line breaks after the
first line of text.

36

Getting Started with Calc

Figure 28: Cell with manual line breaks

Shrinking text to fit the cell
The font size of the data in a cell can automatically adjust to fit in acell. To do this, select the Shrink to fit cell size option in the Format
Cells dialog (Figure 26). Figure Figure 29 shows the results.

Figure 29: Shrinking font size to fit cells

Formatting numbers
Several number formats can be applied to cells by using icons on theFormatting toolbar. Select the cell, then click the relevant icon.

Figure 30: Number format icons. Left to right: currency, percentage,date, exponential, standard, add decimal place, delete decimal place.
For more control or to select other number formats, use the Numberstab (Figure 31) of the Format Cells dialog.
• Apply any of the data types in the Category list to the data.
• Control the number of decimal places and leading zeros.
• Enter a custom format code.

The Language setting controls the local settings for the different
formats such as the date order and the currency marker.

Formatting data

37

Figure 31: Format Cells > Numbers

Formatting the font
To quickly choose the font used in a cell, select the cell, then click thearrow next to the Font Name box on the Formatting toolbar and choose
a font from the list.

Tip

To choose whether to show the font names in their font or inplain text, go to Tools > Options > OpenOffice.org > View
and select or deselect the Show preview of fonts option in the
Font Lists section. For more information, see Chapter 14(Setting Up and Customizing Calc) of the Calc Guide.

To choose the size of the font, click the arrow next
to the Font Size box on the Formatting toolbar. For
other formatting, you can use the Bold, Italic, orUnderline icons.
To choose a font color, click the arrow next to the
Font Color icon to display a color palette. Click on
the required color.
(To define custom colors, use Tools > Options >OpenOffice.org > Colors. See Chapter 2.)

38

Getting Started with Calc

To specify the language of the cell (useful because it allows different
languages to exist in the same document and be spell checked
correctly), use the Font tab of the Format Cells dialog. Use the Font
Effects tab to set other font characteristics. See Chapter 4 of the Calc
Guide for more information.

Formatting the cell borders
To quickly choose a line style and color for the borders of a cell, click
the small arrows next to the Line Style and Line Color icons on theFormatting toolbar. In each case, a palette of choices is displayed.
For more control, including the spacing between the cell borders andthe text, use the Borders tab of the Format Cells dialog. There you canalso define a shadow. See Chapter 4 of the Calc Guide for details.

Note

The cell border properties apply to a cell, and can only bechanged if you are editing that cell. For example, if cell C3 has
a top border (which would be equivalent visually to a bottom
border on C2), that border can only be removed by selecting
C3. It cannot be removed in C2.

Formatting the cell backgroundTo quickly choose a background color for a cell, click the small arrownext to the Background Color icon on the Formatting toolbar. A paletteof color choices, similar to the Font Color palette, is displayed.
(To define custom colors, use Tools > Options > OpenOffice.org >Colors. See Chapter 2 for more information.)
You can also use the Background tab of the Format Cells dialog. See
Chapter 4 of the Calc Guide for details.

Autoformatting cells and sheets
You can use the AutoFormat feature to quickly apply a set of cell
formats to a sheet or a selected cell range.
1) Select the cells, including the column and row headers, that you
want to format.
2) Choose Format > AutoFormat.
Note

If the selected cell range does not have column and row
headers, AutoFormat is not available.

Autoformatting cells and sheets

39

Figure 32: Choosing an AutoFormat
3) To select which properties (number format, font, alignment,borders, pattern, autofit width and height) to include in an
AutoFormat, click More. Select or deselect the required options.
4) Click OK.
If you do not see any change in color of the cell contents, choose View
> Value Highlighting from the menu bar.

Defining a new AutoFormat
You can define a new AutoFormat that is available to all spreadsheets.
1) Format a sheet.
2) Choose Edit > Select All.
3) Choose Format > AutoFormat. The Add button is now active.
4) Click Add.
5) In the Name box of the Add AutoFormat dialog, type a meaningful
name for the new format.
6) Click OK to save. The new format is now available in the Format
list in the AutoFormat dialog.

40

Getting Started with Calc

Formatting spreadsheets using themes
Calc comes with a predefined set of formatting themes that you canapply to your spreadsheets.
It is not possible to add themes to Calc, and they cannot be modified.
However, you can modify their styles after you apply them to a
spreadsheet.
To apply a theme to a spreadsheet:
1) Click the Choose Themes icon in the Tools toolbar. If this toolbar
is not visible, you can show it using View > Toolbars > Tools.
The Theme Selection dialog appears. This dialog lists theavailable themes for the whole spreadsheet.

2) In the Theme Selection dialog, select the theme that you want toapply to the spreadsheet.
As soon as you select a theme, some of the properties of thecustom styles are applied to the open spreadsheet and are
immediately visible.
3) Click OK. If you wish, you can now go to the Styles andFormatting window to modify specific styles. These modifications
do not change the theme; they only change the appearance of thisspreadsheet document.

Using conditional formatting
You can set up cell formats to change depending on conditions that you
specify. For example, in a table of numbers, you can show all the values
above the average in green and all those below the average in red.
Conditional formatting depends upon the use of styles, and the
AutoCalculate feature (Tools > Cell Contents > AutoCalculate)must be enabled. See Chapter 2 in the Calc Guide for details.

Using conditional formatting

41

Hiding and showing data
When elements are hidden, they are neither visible nor printed, but
can still be selected for copying if you select the elements aroundthem. For example, if column B is hidden, it is copied when you selectcolumns A and C. When you need a hidden element again, you canreverse the process, and show the element.
To hide or show sheets, rows, and columns, use the options on theFormat menu or the right-click (context) menu. For example, to hide arow, first select the row, and then choose Format > Row > Hide (orright-click and choose Hide).
To hide or show selected cells, choose Format > Cells from the menubar (or right-click and choose Format Cells). On the Format Cells
dialog, go to the Cell Protection tab.

Figure 33: Hiding or showing cells

Outline group controls
If you are continually hiding and showing the same cells, you cansimplify the process by creating outline groups, which add a set ofcontrols for hiding and showing the cells in the group that are quick to
use and always available.
If the contents of cells falls into a regular pattern, such as four cells
followed by a total, then you can use Data > Group and Outline >AutoOutline to have Calc add outline controls based on the pattern.
Otherwise, you can set outline groups manually by selecting the cells
for grouping, then choosing Data > Group and Outline > Group. On
the Group dialog, you can choose whether to group the selected cellsby rows or columns.
42

Getting Started with Calc

When you close the dialog, the outline group controls are visible
between either the row or column headers and the edges of the editing
window. The controls resemble the tree-structure of a file-manager inappearance, and can be hidden by selecting Data > Outline > Hide
Details. They are strictly for on screen use, and do not print.
The basic outline controls have plus or minus signs at the start of thegroup to show or hide hidden cells. However, if one or more outline
group is nested in another, the controls have numbered buttons forhiding different levels of group.
If you no longer need a group, place the mouse cursor in any cell in it
and select Data > Group and Outline > Ungroup. To remove allgroups on a sheet, select Data > Group and Outline > Remove.

Figure 34: Outline group controls

Filtering which cells are visible
A filter is a list of conditions that each entry has to meet in order to be
displayed. You can set three types of filters from the Data > Filter
sub-menu.
Automatic filters add a drop-down list to the top row of a column that
contains commonly used filters. They are quick and convenient, and,because the condition includes every unique entry in the selected cells,
are almost as useful with text as with numbers.
In addition to each unique entry, automatic filters include the option todisplay all entries, the ten highest numerical values, and all cells thatare empty or not-empty, as well as a standard filter. Their drawback isthat they are somewhat limited. In particular, they do not allow regularexpressions, so you cannot display contents that are similar, but not
identical, by using automatic filters.
Standard filters are more complex than automatic filters. You can set
as many as three conditions as a filter, combining them with the
Hiding and showing data

43

operators AND and OR. Standard filters are mostly useful for numbers,
although a few of the conditional operators, such as = and < > canalso be useful for text.
Other conditional operators for standard filters include options todisplay the largest or smallest values, or a percentage of them. Usefulin themselves, standard filters take on added value when used tofurther refine automatic filters.
Advanced filters are structured similarly to standard filters. The
differences are that advanced filters are not limited to three
conditions, and their criteria are not entered in a dialog. Instead,advanced filters are entered in a blank area of a sheet, then referenced
by the advanced filter tool to apply them.

Sorting records
Sorting arranges the visible cells on the sheet. In Calc, you can sort by
up to three criteria, with each criterion applied one after the other.
Sorts are handy when you are searching for a particular item, andbecome even more powerful after you have filtered data.
In addition, sorting is often useful when you add new information.
When a list is long, it is usually easier to add new information at thebottom of the sheet, rather than adding rows in the proper places.
After you have added information, you can then sort it to update thesheet.
You can sort by highlighting the cells to be sorted, then selecting Data
> Sort. The selected cells can be sorted by the order of information in
up to three columns or rows, in either ascending (A-Z, 1-9) ordescending (Z-A, 9-1) order.
On the Options tab of the Sort dialog, you can choose the following
options:
Case sensitive
If two entries are otherwise identical, one with an upper case letteris placed before one with a lower case letter in the same position.
Range contains column labels
Does not include the column heading in the sort.
Include formats
A cell's formatting is moved with its contents. If formatting is used
to distinguish different types of cells, then use this option.

44

Getting Started with Calc

Copy sort results to
Sets a spreadsheet address to which to copy the sort results. If a
range is specified that does not have the necessary number of cells,
then cells are added. If a range contains cells that already havecontent, then the sort fails.
Custom sort order
Select the box, then choose one of the sort orders defined in Tools >Options > Spreadsheet > Sort Lists from the drop-down list.
Direction
Sets whether rows or columns are sorted. The default is to sort bycolumns unless the selected cells are in a single column.

Printing
Printing from Calc is the same as printing from other OOo components(see Chapter 10), but some details are different, especially regarding
preparation for printing.
The Print dialog (Figure 35), reached from File > Print, has someCalc-specific options: which sheets to print.

Figure 35. Part of the Print dialog
The Printer Options dialog (reached by clicking the Options button on
the bottom left of the Print dialog) has only two choices, as shown in
Figure 36: Suppress output of empty pages and Print only
selected sheets.

Printing

45

Figure 36. Top of the Printer Options dialog

Selecting sheets to print
You can select one or more sheets for printing. This can be useful if
you have a large spreadsheet with multiple sheets and only want toprint certain sheets. For example, an accountant might record costs
over time with one sheet for each month. To print the November andDecember sheets, follow this procedure:
1) Go to the November sheet. Hold down the Control key and clickon the tab of the December sheet.
2) To print all of the sheets, go to File > Print and select Options.
Note

The Options button is different from the Properties button.
Properties deals with the settings of the printer, whereas
Options deals with OOo’s settings.

3) Select Print only selected sheets. This choice affects the print
preview, export, and printing of your spreadsheet. Click OK.
CautionIf you keep the selected sheets selected, when you enter dataon one sheet, you enter data on all sheets at the same time.
This might not be what you want.

Selecting the page order, details, and scale
To select the page order, details, and scale to be printed:
1) Select Format > Page from the main menu.
2) Select the Sheet tab (Figure 37).
3) Make your selections, and then click OK.
Page Order
You can set the order in which pages print. This is especially useful in alarge document; for example, controlling the print order can save time
if you have to collate the document a certain way.
46

Getting Started with Calc

Where a sheet prints on more than one page of paper, it can be printed
either by column, where the first column of pages prints, and then thesecond column and so on, or by row as shown in the graphic on the top
right of the page order dialog in Figure 37.

Figure 37. The Sheet tab of the Page Style dialog
Print
You can specify which details to print. Those details include:• Row and column headers• Sheet grid—prints the borders of the cells as a grid
• Notes—prints the notes defined in your spreadsheet on a separate
page, along with the corresponding cell reference• Objects and graphics• Charts• Drawing objects• Formulas—prints the formulas contained in the cells, instead of
the results• Zero Values—prints cells with a zero value

Printing

47

Note

Remember that since the print detail options are a part of thepage’s properties, they are also a part of the page style’s
properties. Therefore, different page styles can be set up to
quickly change the print properties of the sheets in the
spreadsheet.

Scale
Use the scale features to control the number of pages the data will
print on. This can be useful if a large amount of data needs to beprinted more compactly or, if the reader has poor eyesight, text can beenlarged when it prints.
• Reduce/Enlarge printout—scales the data in the printout either
larger or smaller. For example if a sheet would normally print out
as four pages (two high and two wide), a scaling of 50% would
print as one page (both width and height are halved).
• Fit print range(s) on number of pages—defines exactly how many
pages the printout will take up. This option will only reduce a
printout, it will not enlarge it. To enlarge a printout, thereduce/enlarge option must be used.
• Fit print range(s) to width/height—defines how high and wide theprintout will be, in pages.

Using print ranges
Print ranges have several uses, including printing only a specific partof the data or printing selected rows or columns on every page. For
more about using print ranges, see Chapter 6 (Printing, Exporting, andE-mailing) in the Calc Guide.

Defining a print range
To define a new print range or modify an existing print range:
1) Highlight the range of cells that comprise the print range.
2) Choose Format > Print Ranges > Define.
The page break lines display on the screen.
Tip

48

You can check the print range by using File > Page Preview.
OOo will only display the cells in the print range.

Getting Started with Calc

Adding to the print range
After defining a print range, you can add more cells to it. This allows
multiple, separate areas of the same sheet to be printed, while notprinting the whole sheet. After you have defined a print range:
1) Highlight the range of cells to be added to the print range.
2) Choose Format > Print Ranges > Add. This adds the extra cells
to the print range.
The page break lines no longer display on the screen.
Note

The additional print range will print as a separate page, even if
both ranges are on the same sheet.

Removing a print range
It may become necessary to remove a defined print range, for example
if the whole sheet needs to be printed later.
Choose Format > Print Ranges > Remove. This removes all defined
print ranges on the sheet. After the print range is removed, the default
page break lines will appear on the screen.

Editing a print range
At any time, you can directly edit the print range, for example toremove or resize part of the print range. Choose Format > Print
Ranges > Edit. See Chapter 2 of the Calc Guide for details.

Printing rows or columns on every pageIf a sheet is printed on multiple pages, you can set up certain rows orcolumns to repeat on each printed page.
For example, if the top two rows of the sheet as well as column A need
to be printed on all pages, do the following:
1) Choose Format > Print Ranges > Edit. On the Edit Print
Ranges dialog, type the rows in the text entry box under Rows to
repeat. For example, to repeat rows 1 and 2, type $1:$2. In the
Rows to repeat list, - none - changes to - user defined -.

Printing

49

Figure 38: Specifying repeating rows2) Columns can also repeat; type the columns in the text entry box
under Columns to repeat. For example, to repeat column A, type
$A. In the Columns to repeat list, - none - changes to- user defined -.
3) Click OK.
Note

You do not need to select the entire range of the rows to berepeated; selecting one cell in each row works.

Page breaks
While defining a print range can be a powerful tool, it may sometimes
be necessary to manually tweak Calc’s printout. To do this, you can use
a manual break. A manual break helps to ensure that your data prints
properly. You can insert a horizontal page break above, or a verticalpage break to the left of, the active cell.

Inserting a page break
To insert a page break:1) Navigate to the cell where the page break will begin.
2) Select Insert > Manual Break.
3) Select Row Break or Column Break depending on your need.
The break is now set.
Row breakSelecting Row Break creates a page break above the selected cell.
For example, if the active cell is H15, then the break is createdbetween rows 14 and 15.

50

Getting Started with Calc

Column break
Selecting Column Break creates a page break to the left of theselected cell. For example, if the active cell is H15, then the break iscreated between columns G and H.
Tip

To see page break lines more easily on screen, you can change
their color. Choose Tools > Options > OpenOffice.org >Appearance and scroll down to the Spreadsheet section.

Deleting a page breakTo remove a page break:1) Navigate to a cell that is next to the break you want to remove.
2) Select Edit > Delete Manual Break.
3) Select Row Break or Column Break depending on your need.
The break is now removed.

Note

Multiple manual row and column breaks can exist on the same
page. When you want to remove them, you have to remove
each one individually. This may be confusing at times, because
although there may be a column break set on the page, when
you go to Edit > Manual Break, Column break may begrayed out.
In order to remove the break, you have to be in the cell next tothe break. So, for example, if you set the column break while
you are in H15, you can not remove it if you are in cell D15.
However, you can remove it from any cell in column H.

Headers and footers
Headers and footers are predefined pieces of text that are printed at
the top or bottom of a sheet outside of the sheet area. They are set thesame way.
Headers and footers are assigned to a page style. You can define morethan one page style for a spreadsheet and assign different page styles
to different sheets. For more about page styles, see Chapter 4 (Using
Styles and Templates) in the Calc Guide.
To set a header or footer:1) Navigate to the sheet that you want to set the header or footer
for. Select Format > Page.
2) Select the Header (or Footer) tab. See Figure 39.
3) Select the Header on option.
Printing

51

From here you can also set the margins, the spacing, and height for
the header or footer. You can check the AutoFit height box to havethe height of the header or footer automatically adjust.
MarginChanging the size of the left or right margin adjusts how far theheader or footer is from the side of the page.
Spacing
Spacing affects how far above or below the sheet the header orfooter will print. So, if spacing is set to 1.00", then there will be 1
inch between the header or footer and the sheet.
HeightHeight affects how big the header or footer will be.

Figure 39: Header dialog

Header or footer appearance
To change the appearance of the header or footer, click More.
From this dialog (Figure 40) you can set the background and border ofthe header or footer.

52

Getting Started with Calc

Figure 40: Header/Footer Border/BackgroundContents of the header or footer
The header or footer of a Calc spreadsheet has three columns for text.
Each column can have different contents.
To set the contents of the header or footer, click the Edit button in theheader or footer dialog shown in Figure 39 to display the dialog shownin Figure 41.

Figure 41: Edit contents of header or footer
Printing

53

AreasEach area is independent and can have different information in it.
HeaderYou can select from several preset choices in the Header drop-down
list, or specify a custom header using the buttons below. (If you areformatting a footer, the choices are the same.)
Custom header
Click in the area (Left, Center, Right) that you want to customize,then use the buttons to add elements or change text attributes.
Opens the Text Attributes dialog.
Inserts the total number of pages.
Inserts the File Name field.
Inserts the Date field.
Inserts the Sheet Name field.
Inserts the Time field.
Inserts the current page number.

54

Getting Started with Calc