Calc Guide

2

ChapterEntering, Editing, andFormatting Data
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
Peter Kupfer
Andy Brown
Stephen Buck
Iain Roberts
Hazel Russman
Barbara M. Tobias
Jean Hollis Weber
Jared Kobos

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

Acknowledgments
The sections on speeding up data entry, hiding and showing data, andsorting records are revised from articles written by Bruce Byfield andfirst published on the Linux Journal website; used with permission.

Publication date and software version
Published 20 June 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
Introduction........................................................................................... 5Entering data using the keyboard..........................................................5
Entering numbers...............................................................................5
Entering text....................................................................................... 5Entering numbers as text.................................................................... 5Entering dates and times....................................................................6
Entering special characters................................................................6
Inserting dashes..............................................................................7
Deactivating automatic changes.........................................................7
Speeding up data entry.......................................................................... 8
Using the Fill tool on cells..................................................................8
Using a fill series.............................................................................9Defining a fill series.......................................................................10
Using selection lists..........................................................................11
Sharing content between sheets..........................................................11
Validating cell contents........................................................................12
Editing data.......................................................................................... 14
Removing data from a cell................................................................14
Replacing all the data in a cell..........................................................15
Changing part of the data in a cell....................................................15
Formatting data...................................................................................16
Formatting multiple lines of text.......................................................16
Using automatic wrapping.............................................................16
Using manual line breaks..............................................................17
Shrinking text to fit the cell..............................................................17
Formatting numbers.........................................................................18
Formatting the font...........................................................................19
Choosing font effects........................................................................19
Setting cell alignment and orientation..............................................20
Formatting the cell borders..............................................................21Entering, Editing, and Formating Data

3

Formatting the cell background........................................................22
Autoformatting cells and sheets...........................................................22
Defining a new AutoFormat..............................................................23
Formatting spreadsheets using themes...............................................23
Using conditional formatting...............................................................24
Hiding and showing data.....................................................................26
Outline group controls......................................................................26
Filtering which cells are visible........................................................27
Sorting records.................................................................................... 28
Finding and replacing in Calc..............................................................30
Finding and replacing formulas or values.........................................31
Finding and replacing text................................................................32
Finding and replacing cell styles......................................................32
Using wildcards (regular expressions)..............................................32

4Entering, Editing, and Formating DataIntroduction
You can enter data into Calc in several ways: using the keyboard, themouse (dragging and dropping), the Fill tool, and selection lists. Calc
also provides the ability to enter information into multiple sheets of thesame document at the same time.
After entering data, you can format and display it in various ways.

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

Entering numbers
Click in the cell and type in the number using the number keys 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 treated as text and displayed exactly as entered.
Typically, formulas will treat the entry as a zero and functions will
ignore it.

Tip

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

Entering data using the keyboard

5

Note

CautionWhen a plain apostrophe is used to allow a leading 0 to be
displayed, it is not visible in the cell after the Enter key ispressed. If “smart quotes” are used for apostrophes, theapostrophe remains visible in the cell.
To choose the type of apostrophe, use Tools > AutoCorrect
Options > Custom Quotes. The selection of the apostrophe
type affects both Calc and Writer.
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 slash (/) 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.

Entering special characters
A “special” character is one not found on a standard English keyboard.
For example, © ¾ æ ç ñ ö ø ¢ are all special characters. To insert a
special character:
1) Place the cursor in your document where you want the characterto appear.
2) Click Insert > Special Character to open the Special
Characters dialog (Figure 1).
3) Select the characters (from any font or mixture of fonts) you wish
to insert, in order; then click OK. The selected characters areshown in the bottom left of the dialog. As you select eachcharacter, it is shown alone at the bottom right, along with thenumerical code for that character.
Note

6Different fonts include different special characters. If you do
not find a particular special character you want, try changing
the Font selection.

Entering, Editing, and Formating Data

Figure 1: The Special Characters dialog

Inserting dashes
To enter en and em dashes, you can use the Replace dashes option
under Tools > AutoCorrect Options. This option replaces two
hyphens, under certain conditions, with the corresponding dash.
In the following table, the A and B represent text consisting of letters A
to z or digits 0 to 9.
Text that you type:A - B (A, space, minus, space, B)

ResultA – B (A, space, en-dash, space, B)

A -- B (A, space, minus, minus, space, B) A – B (A, space, en-dash, space, B)
A--B (A, minus, minus, B)

A—B (A, em-dash, B)

A-B (A, minus, B)

A-B (unchanged)

A -B (A, space, minus, B)

A -B (unchanged)

A --B (A, space, minus, minus, B)

A –B (A, space, en-dash, B)

Deactivating automatic changes
Calc automatically applies many changes during data input, unless you
deactivate those changes. You can also immediately undo any
automatic changes with Ctrl+Z.
AutoCorrect changes
Automatic correction of typing errors, replacement of 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.
Entering data using the keyboard

7

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.
The most basic ability is to drop and drag the contents of one cell toanother with a mouse. Many people also find AutoInput helpful. Calc
also includes several other tools for automating input, especially ofrepetitive material. They include the Fill tool, selection lists, and theability 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

8

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.

Entering, Editing, and Formating Data

Figure 2: 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
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 3: Specifying the start of a fill series (result is in Figure 4)

Speeding up data entry

9Figure 4: Result of fill series
selection shown in Figure 3
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.

Defining a fill series
To define your own fill series, go to Tools > Options >OpenOffice.org Calc > Sort Lists. This dialog shows the previouslydefined series in the Lists box on the left, and the contents of thehighlighted list in the Entries box.

Figure 5: 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.

10Entering, Editing, and Formating Data

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

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

Sharing content between sheets

11

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 validation in your own work as a guide to entering datathat is either complex or rarely used.
Fill series and selection lists can handle some types of data, but they
are limited to predefined information. To validate new data entered bya user, select a cell and use Data > Validity to define the type ofcontents that can be entered in that cell. For example, a cell might
require a date or a whole number, with no alphabetic characters or
decimal points; or a cell may not be left empty.
Depending on how validation is set up, the tool can also define therange of values that can be entered and provide help messages that
explain the content rules you have set up for the cell and what users
should do when they enter invalid content. You can also set the cell torefuse invalid content, accept it with a warning, or—if you areespecially well-organized—start a macro when an error is entered.
Validation is most useful for cells containing functions. If cells are set
to accept invalid content with a warning, rather than refusing it, you
can use Tools > Detective > Mark Invalid Data to find the cells withinvalid data. The Detective marks with a circle any cells containing
invalid data.
Note that a validity rule is considered part of a cell’s format. If you
select Format or Delete All from the Delete Contents window, then it
is removed. (Repeating the Detective’s Mark Invalid Data commandthen removes the invalid data circle, because the data is no longer
invalid.) If you want to copy a validity rule with the rest of the cell, use
Edit > Paste Special > Paste Formats or Paste All.
Figure 7 shows the choices for a typical validity test. Note the Allow
blank cells option under the Allow list.

12Entering, Editing, and Formating Data

Figure 7: Typical validity test choices.
The validity test options vary with the type of data selected from theAllow list. For example, Figure 8 shows the choices when a cell must
contain a cell range.

Figure 8: Validity choices for a cell range.
To provide input help for a cell, use the Input Help page of the Validity
dialog (Figure 9). To show an error message when an invalid value isentered, use the Error Alert page (Figure 10). Be sure to write
something helpful, explaining what a valid entry should contain—notjust “Invalid data—try again” or something similar.

Validating cell contents

13

Figure 9: Defining input help for a cell

Figure 10: Defining an error message for a cell withinvalid data

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

Removing data from a cell
Data can be removed (deleted) from a cell in several ways.
Removing data only
The data alone can be removed from a cell without removing any of
the formatting of the cell. Click in the cell to select it, and then press
the Backspace key.
Removing data and formatting
The data and the formatting can be removed from a cell at the sametime. Press the Delete key (or right-click and choose Delete
Contents, or use Edit > Delete Contents) to open the Delete
14Entering, Editing, and Formating Data

Contents dialog (Figure 11). From this dialog, different aspects ofthe cell can be deleted. To delete everything in a cell (contents andformat), check Delete all.

Figure 11: Delete Contents dialog

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

Changing part of the data in a cell
Sometimes it is necessary to change the contents of cell without
removing all of the contents, for example if the phrase “See Dick run”
is in a cell and it needs to be changed to “See Dick run fast.” It is often
useful to do this without deleting the old cell contents first.
The process is the similar to the one described above, but you need 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.
Editing data

15

Formatting data
The data in Calc can be formatted 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 range, right-click on it, and select Format
Cells. All of the format options are discussed below.
Note

All the settings discussed in this section can also be set as apart of the cell style. See Chapter 4 (Using Styles andTemplates) 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 12), under Properties,
select Wrap text automatically. The results are shown in Figure 13.

Figure 12: Format Cells > Alignment dialog
16Entering, Editing, and Formating Data

Figure 13: 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 14 shows the results of using two manual line breaks after the
first line of text.

Figure 14: 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 option in the Format Cells
dialog (Figure 12). Figure 15 shows the results.

Figure 15: Shrinking font size to fit cells

Formatting data17

Formatting numbers
Several different number formats can be applied to cells by using icons
on the Formatting toolbar. Select the cell, then click the relevant icon.
Some icons may not be visible in a default setup; click the down-arrowat the end of the Formatting bar and select other icons to display.

Figure 16: 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 17) of the Format Cells dialog.
• Apply any of the data types in the Category list to the data.
• Control the number of decimal places and leading zeros.
• Enter a custom format code.
The Language setting controls the local settings for the different
formats such as the date order and the currency marker.

Figure 17: Format Cells > Numbers

18Entering, Editing, and Formating Data

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

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 14 formore information.)To specify the language of the cell (useful because it allows different
languages to exist in the same document and be spell checked
correctly), use the Font tab of the Format Cells dialog. See Chapter 4
for more information.

Choosing font effects
The Font Effects tab (Figure 18) of the Format Cells dialog offers more
font options.
Overlining and underlining
You can choose from a variety of overlining and underlining options(solid lines, dots, short and long dashes, in various combinations)and the color of the line.
StrikethroughThe strikethrough options include lines, slashes, and Xs.
Relief
The relief options are embossed (raised text), engraved (sunken
text), outline, and shadow.

Formatting data

19

Figure 18: Format Cells > Font Effects

Setting cell alignment and orientation
Some of the cell alignment and orientation icons are not shown by
default on the Formatting toolbar. To show them, click on the small
arrow at the right-hand end of the toolbar and select them from the listof icons.

Figure 19: Cell alignment and orientation
Some of the alignment and orientation icons are available only if you
have Asian or CTL (Complex Text Layout) languages enabled (in Tools> Options > Language Settings > Languages). If you choose an
unavailable icon from the list, it does not appear on the toolbar.

20Entering, Editing, and Formating Data

For more control and other choices, use the Alignment tab (Figure 12)of the Format Cells dialog to set the horizontal and vertical alignmentand rotate the text. If you have Asian languages enabled, then the Text
orientation section shows an extra option (labeled Asian layoutmode) under the Vertically stacked option, as shown in Figure 20.

Figure 20: Asian layout mode option
The difference in results between having Asian layout mode on or off
is shown in Figure 21.

Figure 21: Types of vertical stacking

Formatting the cell borders
To quickly choose a line style and color for the borders of a cell, click
the small arrows next to the Line Style and Line Color icons on theFormatting toolbar. If the Line Style and Line Color icons are notdisplayed in the formatting toolbar, select the down arrow on the rightside of the bar, then Visible Buttons. In each case, a palette of
choices is displayed.
For more control, including the spacing between the cell borders andthe text, use the Borders tab of the Format Cells dialog. There you canalso define a shadow. See Chapter 4 for details.

Formatting data

21

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 14 for more information.)
You can also use the Background tab of the Format Cells dialog. See
Chapter 4 for details.

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

Figure 22: Choosing an AutoFormat

22Entering, Editing, and Formating DataNote

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

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

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

Formatting spreadsheets using themes
Calc comes with a predefined set of formatting themes that you 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.
Formatting spreadsheets using themes23

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 thisspecific spreadsheet document.

Using conditional formatting
You can set up cell formats to change depending on conditions that you
specify. For example, in a table of numbers, you can show all the values
above the average in green and all those below the average in red.
Note

To apply conditional formatting, AutoCalculate must be
enabled. Choose Tools > Cell Contents > AutoCalculate.

Conditional formatting depends upon the use of styles. If you are notfamiliar with styles, please refer to Chapter 4. An easy way to set up
the required styles is to format a cell the way you want it and click the
New Style from Selection icon in the Styles and Formatting window.
After the styles are set up, here is how to use them.
1) In your spreadsheet, select the cells to which you want to apply
conditional formatting.
2) Choose Format > Conditional Formatting from the menu bar.
3) On the Conditional Formatting dialog (Figure 23), enter theconditions. Click OK to save. The selected cells are now
formatted in the relevant style.
Cell value is / Formula isSpecifies whether conditional formatting is dependent on a cellvalue or on a formula. If you select cell value is, the Cell Value
Condition box is displayed, as shown in the example. Here you canchoose from conditions including less than, greater than, between,
and others.
Parameter field
Enter a reference, value, or formula in the parameter field, or inboth parameter fields if you have selected a condition that requires
two parameters. You can also enter formulas containing relativereferences.

24Entering, Editing, and Formating Data

Figure 23: Conditional formatting dialog
Cell style
Choose the cell style to be applied if the specified condition
matches. The style must have been defined previously.
See the Help for more information and examples of use.
To apply the same conditional formatting
later to other cells:
1) Select one of the cells that has been
assigned conditional formatting.
2) Copy the cell to the clipboard.
3) Select the cells that are to receive thissame formatting.
4) Choose Edit > Paste Special.
5) On the Paste Special dialog, in the
Selection area, select only the Formats
option. Make sure all other options are
not selected. Click OK.

Using conditional formatting

25

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 24: 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.
26Entering, Editing, and Formating Data

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 > Group andOutline > Hide Details. They are strictly for online use, and do notprint.
The basic outline controls have plus or minus signs at the start of thegroup to show or hide hidden cells. However, if outline groups arenested, the controls have numbered buttons for hiding the different
levels.
If you no longer need a group, place the mouse cursor in any cell in it
and select Data > Group and Outline > Ungroup. To remove allgroups on a sheet, select Data > Group and Outline > Remove.

Figure 25: 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 andalmost as useful with text as with numbers, because the list includes
every unique entry in the selected cells.
In addition to these unique entries, automatic filters include the option
to display all entries, the ten highest numerical values, and all cells
that are empty or not empty, as well as a standard filterthat you cancustomize (see below). However, they are somewhat limited. In
particular, they do not allow regular expressions, so you cannot use
them to display cell contents that are similar but not identical.

Hiding and showing data27

Standard filters are more complex than automatic filters. You can set
as many as three conditions as a filter, combining them with theoperators AND and OR. Standard filters are mostly useful for numbers,
although a few of the conditional operators, such as = and < > canalso be used 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 in order to apply them.

Sorting records
Sorting rearranges the visible cells on the sheet. In Calc, you can sort
by up to three criteria, which are applied one after another. Sorts are
handy when you are searching for a particular item, and become evenmore 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 inserting rows in the proper places.
After you have added the information, you can sort it to update thesheet.
Highlight the cells to be sorted, then select Data > Sort to open the
Sort dialog (Figure 26) or click the Sort Ascending or Sort
Descending toolbar buttons. Using the dialog, you can sortthe selected cells using up to three columns, in either
ascending (A-Z, 1-9) or descending (Z-A, 9-1) order.
Tip

You can define a custom sort order if the suppliedalphanumeric ones do not fit your requirements. See “Defining
a fill series” on page 10 for instructions.

On the Options tab of the Sort dialog (Figure 27), you can choose thefollowing options.

28Entering, Editing, and Formating Data

Figure 26: Choosing the criteria and order of sorting

Figure 27: Options for sorting
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 if
the sort is descending; if the sort is ascending, then the entry withan upper case letter is placed after one with a lower case letter inthe same position.
Sorting records

29

Range contains column labels
Does not include the column heading in the sort.
Include formats
A cell's formatting is moved with its contents. If formatting is used
to distinguish different types of cells, then use this option.
Copy sort results to
Sets a spreadsheet address to which to copy the sort results. If a
range is specified that does not have the necessary number of cells,
then cells are added. If a range contains cells that already havecontent, then the sort fails.
Custom sort order
Select the box, then choose from the drop-down list one of the sortorders defined in Tools > Options > OpenOffice.org Calc >Sort Lists.
Direction
Sets whether rows or columns are sorted. The default is to sort bycolumns unless the selected cells are in a single column.

Finding and replacing in Calc
In spreadsheet documents you can search for text, formulas, andstyles. You can navigate from one occurrence to the next using Find,or you can highlight all matching cells at once using Find All, thenapply another format or replace the cell contents by other content.
Text and numbers in cells may have been entered directly or may be
the result of a calculation. The search method you use depends on thetype of data you are searching for.

Tip

Cell contents can be formatted in different ways. For example,
a number can be formatted as a currency, to be displayed witha currency symbol. You see the currency symbol in the cell, but
you cannot search for it.

By default, Calc searches the current sheet. To search through all
sheets of the document, click More Options, then select Search in all
sheets option.

30

Entering, Editing, and Formating Data

Caution

Use Replace All with caution; otherwise, you may end up withsome highly embarrassing mistakes. A mistake with Replace
All might require a manual, word-by-word search to fix, if it is
not discovered in time to undo it.

Figure 28: Expanded Find & Replace dialog

Finding and replacing formulas or values
You can use the Find & Replace dialog to search in formulas or in thedisplayed values that result from a calculation.
1) To open the Find & Replace dialog, use the keyboard shortcut
Control+F or select Edit > Find & Replace.
2) Click More Options to expand the dialog.

Finding and replacing in Calc

313) Select Formulas or Values in the Search in drop-down list.
• Formulas finds parts of the formulas.
• Values finds the results of the calculations.
4) Type the text you want to find in the Search for box.
5) To replace the text with different text, type the new text in the
Replace with box.
6) When you have set up your search, click Find. To replace text,
click Replace instead.

Finding and replacing text1) Open the Find & Replace dialog, click More Options to expand
the dialog, and select Values or Notes in the Search in drop-down
list.
2) Type the text you want to find in the Search for box.
3) To replace the text with different text, type the new text in the
Replace with box.
4) Click Find, Find All, Replace, or Replace All.
When you click Find, Calc selects the next cell that contains your text.
You can edit the text, then click Find again to advance to the next
found cell. If you closed the dialog, you can press Ctrl+Shift+F to findthe next cell without opening the dialog.
When you click Find All, Calc selects all cells that contain your entry.
Now you can, for example, apply a cell style to all of them at once.

Finding and replacing cell styles
To quickly change all the paragraphs of one (unwanted) style toanother (preferred) style:1) On the expanded Find & Replace dialog, select Search for
Styles. The Search for and Replace with boxes now contain a listof styles.
2) Select the styles you want to search for and replace.
3) Click Find, Find All, Replace, or Replace All.

Using wildcards (regular expressions)Wildcards (also known as regular expressions) are combinations ofcharacters that instruct OOo how to search for something. Regularexpressions are very powerful but not very intuitive. They can savetime and effort by combining multiple finds into one.
32

Entering, Editing, and Formating Data

To use wildcards and regular expressions when searching andreplacing:1) On the Find & Replace dialog, click More Options to see morechoices. On this expanded dialog, select the Regularexpressions option.
2) Type the search text, including the wildcards, in the Search forbox and the replacement text (if any) in the Replace with box.
3) Click Find, Find All, Replace, or Replace All (notrecommended).
Tip

The online help describes many of the regular expressions andtheir uses.

The following points are interesting to Calc users:• In Calc, regular expressions are applied separately to each cell.

•

•

•

•

So a search for r.d will match red in cell A1 but will not match r
in cell A2 and d (or ed) in cell A3. (The regular expression r.dmeans “try to match r followed by any other character followedby d.”)
When a match is found, the whole cell is shown highlighted, but
only the text found will be replaced. For example, searching forbrown will highlight a cell containing redbrown clay, andreplacing with nothing will leave the cell containing red clay.
If Find is used twice in a row, the second time with Current
selection only activated, then the second search will evaluate
the whole of each selected cell, not just the strings found whichcaused the cells to be selected in the first search. For example,
searching for joh?n, then activating Current selection only andsearching for sm.th will find cells containing Jon Smith andSmythers, Johnathon.
If a cell contains a hard line break (entered by Ctrl+Enter), thismay be found by \n. For example if a cell contains red
hard_line_break clay then searching for d\nc and replacing withnothing leaves the cell containing relay.
The hard line break acts to mark “end of text” as understood by
the regular expression special character $ (in addition of course
to the end of text in the cell). For example, if a cell contains red
hard_line_break clay then a search for d$ replacing with al
leaves the cell with real hard_line_break clay. Note that withthis syntax the hard line break is not replaced—it simply marks
the end of text.

Finding and replacing in Calc

33

• Using \n in the Replace with box will replace with the literalcharacters \n, not a hard line break.
• The Find & Replace dialog has an option to search Formulas,
Values, or Notes. This applies to any search, not just one using
regular expressions. Searching with the Formulas option wouldfind SUM in a cell containing the formula =SUM(A1:A6). If a cell
contains text instead of a function, the text will still be found - sothat the simple text SUMMARY in a cell would also give a match
to SUM using the Formulas option.
• Searching for the regular expression ^$ will not find empty cells.
This is intentional—the rationale being to avoid performanceissues when selecting a huge number of cells. Note that empty
cells will not be found even if you are only searching a selection.
• Find .+ (or similar) and Replace with & effectively re-enters thecontents of cells. This can be used to strip formatting
automatically applied by Calc (often needed to clean data
imported from the clipboard or badly formatted files), forexample, to convert text strings consisting of digits into actual
numbers (the cells must first be correctly formatted numbers).
The leading apostrophes, telling Calc to treat the numbers as text,
are removed.
See Chapter 7 (Using Formulas and Functions) for the use of regularexpressions within formulas.

34

Entering, Editing, and Formating Data