Calc Guide 7.5

Chapter 2
Entering and Editing Data

CopyrightThis document is Copyright © 2023 by the LibreOffice Documentation Team. Contributors arelisted below. You may distribute it and/or modify it under the terms of either the GNU General
Public License (https://www.gnu.org/licenses/gpl.html), version 3 or later, or the Creative
Commons Attribution License (https://creativecommons.org/licenses/by/4.0/), version 4.0 or later.
All trademarks within this guide belong to their legitimate owners.

Contributors
To this edition
Olivier Hallot

To previous editions
Skip Masonsmith
Barbara Duprey
Shelagh MantonPeter Kupfer
Iain Roberts
Jared Kobos
Rafael Lima

Kees Kriek
Christian Chenal
John A Smith
Andy Brown
Hazel Russman
Dave Barton

Jean Hollis Weber
Peter Schofield
Stephen Buck
Barbara M. Tobias
Kees Kriek

Claire Wood
Annie Nguyen
Steve Fanning
Felipe Viggiano
Leo Moons

Feedback
Please direct any comments or suggestions about this document to the Documentation Team’s
forum at https://community.documentfoundation.org/c/documentation/loguides/ (registration isrequired) or send an email to: loguides@community.documentfoundation.org.

Note
Everything you post to a forum, including your email address and any otherpersonal information that is written in the message, is publicly archived andcannot be deleted. E-mails sent to the forum are moderated.

Publication date and software version
Published February 2023. Based on LibreOffice 7.5 Community.
Other versions of LibreOffice may differ in appearance and functionality.

Using LibreOffice on macOS
Some keystrokes and menu items are different on macOS from those used in Windows andLinux. The table below gives some common substitutions for the instructions in this document.
For a more detailed list, see the Help and Appendix A (Keyboard Shortcuts) to this guide.
Windows or LinuxmacOS equivalent

EffectTools > Options
menu selection

LibreOffice > Preferences

Access setup options

Right-click

Control+click and/or right-click
Open a context menu
depending on computer setup

Ctrl (Control)⌘ (Command)

Used with other keys

F11

⌘+T

Open the Styles deck in Sidebar

Documentation for LibreOffice is available at https://documentation.libreoffice.org/en/Contents
Copyright.......................................................................................................................... 2
Contributors................................................................................................................................2
Feedback....................................................................................................................................2
Publication date and software version........................................................................................2
Using LibreOffice on macOS......................................................................................................2

Introduction...................................................................................................................... 5Entering data....................................................................................................................5
Numbers.....................................................................................................................................5
Negative numbers..................................................................................................................5
Leading zeroes......................................................................................................................5
Numbers as text.....................................................................................................................5
Text.............................................................................................................................................7
Date and time.............................................................................................................................7
Special characters......................................................................................................................7
AutoCorrect options....................................................................................................................9
Inserting dashes.........................................................................................................................9

Speeding up data entry................................................................................................. 10
AutoInput tool............................................................................................................................10
Fill tool......................................................................................................................................10
Using a fill series..................................................................................................................11
Defining a fill series..............................................................................................................12
Selection lists............................................................................................................................13

Merging and splitting cells............................................................................................14
Merging.....................................................................................................................................14
Splitting.....................................................................................................................................14

Sharing content between sheets..................................................................................14
Validating cell contents.................................................................................................15
Defining validation....................................................................................................................16
Criteria options.....................................................................................................................16
Input Help options................................................................................................................17
Error Alert options................................................................................................................18
Calc Detective...........................................................................................................................18

Editing data.................................................................................................................... 19
Deleting data.............................................................................................................................19
Deleting cell data only..........................................................................................................19
Deleting cells.......................................................................................................................19
Deleting data and formatting................................................................................................20
Replacing data..........................................................................................................................20
Editing data...............................................................................................................................20
Using the keyboard..............................................................................................................20
Using the mouse..................................................................................................................21
Paste Special function..............................................................................................................21
Paste Special dialog............................................................................................................21
Paste Special options..........................................................................................................22
Chapter 2 Entering, Editing, and Formatting Data | 3

Paste Only options...............................................................................................................23
Insert cell fields.........................................................................................................................23

Group and outline..........................................................................................................23
Grouping...................................................................................................................................23
Hiding details............................................................................................................................24
Showing details.........................................................................................................................24
Ungrouping...............................................................................................................................24
AutoOutline...............................................................................................................................25
Removing..................................................................................................................................25

Filtering...........................................................................................................................25
Applying a standard filter..........................................................................................................25
Applying an AutoFilter...............................................................................................................27
Filtering by color..................................................................................................................28
Filtering by condition............................................................................................................28
Applying an advanced filter......................................................................................................28

Sorting records.............................................................................................................. 29
Sort dialog.................................................................................................................................29
Sort options...............................................................................................................................30
Quick sort..................................................................................................................................31

Find and replace............................................................................................................ 31
Find toolbar...............................................................................................................................32
Find and Replace dialog...........................................................................................................32
Finding.................................................................................................................................32
Replacing.............................................................................................................................32
Find and Replace options....................................................................................................33
Search Results dialog..........................................................................................................34

Searching and filtering with regular expressions.......................................................35

4| Chapter 2 Entering, Editing, and Formatting DataIntroduction
You can enter data into Calc in several ways: using the keyboard, the Fill tool, and selection lists,
as well as dragging and dropping. Calc also provides the ability to enter data into multiple sheetsof the same spreadsheet at the same time.

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

Numbers
Click in the cell and type the number using the number keys on either the main keyboard or thenumeric keypad.

Negative numbers
To enter a negative number, either type a minus sign in front of the number or enclose thenumber in parentheses, for example (1234). The result for both methods of entry is the same; forexample, –1234.

Leading zeroes
By default, if a number is entered with leading zeroes, for example 01481, Calc will drop theleading zeroes. To retain both the number format and a minimum number of characters in a cellwhen entering numbers, for example 1234 and 0012, use one of these methods to add leading
zeroes.
Method 1
1) With the cell selected, go to Format > Cells on the Menu bar, or right-click on the cell andselect Format Cells in the context menu, or use the keyboard shortcut Ctrl+1, to open
the Format Cells dialog (Figure 1).
2) Make sure the Numbers tab is selected, then select Number in the Category list.
3) In the Leading zeroes field within the Options area, enter the minimum number ofcharacters required. For example, for four characters, enter 4. Any number less than fourcharacters will then have leading zeroes added, for example 12 becomes 0012.
4) Click OK. The number entered retains its number format and any formula used in the
spreadsheet will treat the entry as a number in formula functions.
Method 2
1) Select the cell.
2) On the Sidebar, go to the Properties deck.
3) In the Number Format panel (Figure 2), select Number in the drop-down list, and enter 4
in the Leading zeroes field. Formatting is applied immediately.

Numbers as text
Numbers can also be converted into text using one of the following methods.
Method 1
1) With the cell selected, open the Format Cells dialog (Figure 1).
2) Make sure the Numbers tab is selected, then select Text from the Category list.
3) Click OK. The number is converted to text and, by default, is left-aligned. You can change
the formatting and alignment of any text numbers just as you would with normal text.
Entering data | 5

Figure 1: Format Cells dialog – Numbers tab
Method 2
1) Select the cell.
2) On the Sidebar, go to the Properties deck.
3) In the Number Format panel (Figure 2), select Text in the drop-down list. Formatting isapplied to the cell immediately.

Figure 2: Set leading zeroes in Sidebar

Tip
If numerical characters do not need to be treated as numbers in calculations (forexample when entering zip codes), you can type an apostrophe (') before thenumber, for example '01481. When you move the cell focus, the apostrophe isremoved, the leading zeroes are retained, and the number is converted to leftaligned text.

6 | Chapter 2 Entering, Editing, and Formatting Data

Text
Click in a cell and type the text. The text is left-aligned by default. Cells can contain several linesof text. If you want to use paragraphs, press Ctrl+Enter to create another paragraph.
On the Formula Bar, you can extend the Input line if you are entering several lines of text. Clickon the Expand / Collapse Formula Bar icon located on the right of the Formula Bar and theInput line becomes multi-line, as shown in Figure 3. You can drag the bottom of the Input line up
and down to control its exact height. Click the Expand / Collapse Formula Bar icon again to
return the Input line to its default single line height.

Figure 3: Expanded Input line on Formula Bar

Date and time
Select the cell and type the date or time. You can separate the date elements with a slash (/) or ahyphen (–) or use text, for example 10 Oct 2020. The date format automatically changes to theselected format used by Calc.

Note
Tools > Options > Language Settings > Languages > Formats > Dateacceptance patterns defines the date patterns that will be recognized by Calc. In
addition, every locale accepts input in an ISO 8601 YYYY-MM-DD pattern (forexample, 2020-07-26).
When you enter a time, separate time elements with colons, for example 10:43:45. The time
format automatically changes to the selected format used by Calc.
To change the date or time format used by Calc:
1) With the cell selected, open the Format Cells dialog (Figure 1).
2) Make sure the Numbers tab is selected, then select Date or Time in the Category list.
3) Select the date or time format you want to use from the Format list.
4) Click OK to save the changes and close the dialog.

Note
The date format will be influenced by the system or document language settings.

Special characters
A special character is a character not normally found on a standard keyboard; for example, © ¾
æ ç ñ ö ø ¢. To insert a special character:1) Select a cell and place the cursor in the cell or in the Input line, at the point where youwant the character to appear.
Entering data | 72) Go to Insert > Special Character on the Menu bar to open the Special Characters dialog(Figure 4).

Figure 4: Special Characters dialog
3) From the grid of characters, select the required character. The last character selected isshown on the right of the Special Characters dialog along with its numerical code.
4) Any recently inserted characters are shown below the grid of characters and can beselected in the same way as any other character in the dialog.
5) At the bottom of the dialog there is provision for building a small collection of Favorite
Characters. To add a new character to the collection, select the required character andclick the Add to Favorites button. To remove an existing character from the collection,
select the character and click the Remove from Favorites button.
6) Double-click a special character to insert it into the cell, without closing the dialog. Click
Insert to insert a selected special character into the cell and close the dialog.

Tip
You can quickly insert one of your recent or favorite special characters by clicking
the Insert Special Characters icon on the Standard toolbar and selecting therequired character from the drop-down. Click More Characters on this drop-down
to open the Special Characters dialog (Figure 4).

Note
Different fonts include different special characters. If you do not find a particularspecial character you want, try changing the Font and Subset selections.

8 | Chapter 2 Entering, Editing, and Formatting Data

AutoCorrect options
Calc automatically applies many changes during data input using AutoCorrect, unless you havedeactivated any AutoCorrect changes. You can undo any AutoCorrect changes by selecting Edit
> Undo on the Menu bar, pressing the keyboard shortcut Ctrl+Z, or manually going back to thechange and replacing the autocorrection with what you actually want to see.
To change the AutoCorrect options, go to Tools > AutoCorrect Options on the Menu bar toopen the AutoCorrect dialog (Figure 5).

Figure 5: AutoCorrect dialog•

Replace – edit the replacement table for automatically correcting or replacing words orabbreviations.

•

Exceptions – specify the abbreviations or letter combinations that you do not wantcorrected automatically.

•

Options – select the options for automatically correcting errors as you type.

•

Localized Options – specify the AutoCorrect options for quotation marks and for options
that are specific to the language of the text.

•

Reset – reset modified values back to their previous values.

Inserting dashes
Calc provides text shortcuts so that you can quickly insert dashes into a cell and these shortcuts
are shown in Table 1.
Table 1: Inserting dashes
Text that you type

ResultA - B (A, space, hyphen, space, B)

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

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

A—B (A, em-dash, B)
Entering data | 9

A-B (A, hyphen, B)

A-B (unchanged)

A -B (A, space, hyphen, B)

A -B (unchanged)

A --B (A, space, hyphen, hyphen, B)

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

Speeding up data entry
Entering data into a spreadsheet can be very labor-intensive, but Calc provides tools forremoving some of the drudgery from input. These tools include drag and drop (to move or copy
contents of one location to another using a mouse), an AutoInput tool, the Fill tool, and selection
lists.
The Fill tool and selection lists automate input of repetitive material into cells. Calc also has theability to input information into multiple sheets of the same spreadsheet at the same time.

AutoInput tool
The AutoInput function in Calc automatically completes entries, based on other entries in thesame column.
When text is highlighted in a cell, AutoInput can be used as follows:•

Press Enter to accept the completion and move to the next cell. Press F2 to accept thecompletion and move the cursor to the end of the text inside the cell. Clicking outside thecell will accept the completion and select the clicked cell.

•

When multiple matches continue with the same letters they will appear in the cell after
what has already been typed. Press → to accept the partial completion and move thecursor to the end of the text inside the cell.

•

To view more completions that start with the same letters, use the key combinations
Ctrl+Tab to scroll forward, or Ctrl+Shift+Tab to scroll backward.

•

To see a list of all available AutoInput text items for the current column, use the keyboard
combination Alt+↓. See “Selection lists” on page 13 for more information.

When typing formulas using characters that match function names, a Help tip will appear listing
the available functions that start with matching characters.
AutoInput ignores the case sensitivity of any data you enter. If, for example, you have written“Total” in a cell, you cannot then use AutoInput to enter “total” in another cell of the same column
without first deactivating AutoInput or entering “total” manually.
By default, AutoInput is activated in Calc. To turn it off, go to Tools on the Menu bar and deselectAutoInput.

Fill tool
You can use the Fill tool in Calc to duplicate existing content or create a series in a range of cells
in the spreadsheet as shown by the examples in Figure 6.
1) Select the cell containing the contents you want to copy or start the series from.
2) Drag the mouse pointer in any direction or hold down the Shift key and click in the lastcell you want to fill.
3) Go to Sheet > Fill Cells on the Menu bar and select the direction in which you want tocopy or create data (Down, Right, Up, Left, Sheets, Series, or Random Number). A
menu option will be grayed out if it is not available.

10 | Chapter 2 Entering, Editing, and Formatting Data

Figure 6: Examples of using the Fill tool

Tip
You can use the keyboard shortcut Ctrl+D as an alternative to selecting Sheet >Fill Cells > Fill Down on the Menu bar.
Alternatively, you can use a shortcut to fill cells:
1) Select the cell containing the contents you want to copy or start the series from.
2) Move the pointer over the small selection handle in the bottom right corner of the selected
cell. The pointer will change shape.
3) Click and drag in the direction you want the cells to be filled, vertical or horizontal. If theoriginal cell contained text, then the text will automatically be copied. If the original cell
contained a number or text from a defined list (see “Defining a fill series” on page 12), a
series will be created. To duplicate the number or text instead, hold Ctrl while dragging.

CautionWhen you are selecting cells so you can use the Fill tool, make sure that none of
the cells contain data, except for the cell data you want to use. When you use theFill tool, any data contained in selected cells is overwritten.

Using a fill series
When you select a series fill from Sheet > Fill Cells > Fill Series on the Menu bar, the Fill Series
dialog (Figure 7) opens. Here you can select the type of series you want or create your own list.
•

Direction – determines the direction of series creation.
– Down – creates a downward series in the selected cell range for the column using thedefined increment to the end value.
– Right – creates a series running from left to right within the selected cell range using
the defined increment to the end value.
– Up – creates an upward series in the selected cell range of the column using thedefined increment to the end value.
– Left – creates a series running from right to left within the selected cell range using
the defined increment to the end value.

Speeding up data entry | 11

Figure 7: Fill Series dialog•

Series Type – defines the series type. These are:– Linear – creates a linear number series using the defined increment and end value.
– Growth – creates a growth series using the defined increment and end value.
– Date – creates a date series using the defined increment and end date.
– AutoFill – forms a series directly in the sheet. The AutoFill function takes account ofcustomized lists. For example, by entering January in the first cell, the series iscompleted using the list defined in Tools > Options > LibreOffice Calc > Sort Lists.
AutoFill tries to complete a value series by using a defined pattern. For example, anumerical series using 1,3,5 is automatically completed with 7,9,11,13; a date andtime series using 01.01.99 and 15.01.99, is completed using an interval of fourteen
days.

•

Time Unit – in this area you specify the desired unit of time. This area is only active if theDate option has been selected in Series Type. The options are:– Day – creates a series using seven days.
– Weekday – creates a series of five day sets.
– Month – creates a series from the names or abbreviations of the months.
– Year – creates a series of years.

•

Start value – determines the start value for the series. Use numbers, dates, or times.

•

End value – determines the end value for the series. Use numbers, dates, or times.

•

Increment – determines the value by which the series of the selected type increases by
each step. Entries can only be made if the linear, growth, or date series types have been
selected.

Defining a fill series
To define your own fill series:
1) Go to Tools > Options > LibreOffice Calc > Sort Lists to open the Sort Lists dialog(Figure 8). This dialog shows any previously defined series in the Lists box on the left andthe contents of the highlighted list in the Entries box.
2) Click New and the Entries box is cleared.
3) Type the series for the new list in the Entries box, with one entry per line (Figure 9).
12 | Chapter 2 Entering, Editing, and Formatting Data

4) Click Add and the new list will now appear in the Lists box.
5) Click OK to save the new list and close the dialog.

Figure 8: Sort Lists dialog

Figure 9: Creating a new sort list

Selection lists
Selection lists are available only for text and are limited to using only text that has already beenentered in the same column.
1) Select a blank cell in a column that contains cells with text entries.
2) Right-click and select Selection List in the context menu, or use the keyboard shortcut
Alt+↓. A drop-down list appears listing any cell in the same column that either has atleast one text character or whose format is defined as text.
3) Click on the text entry you require and it is entered into the selected cell.

Speeding up data entry | 13

Merging and splitting cells
Merging
You can select contiguous cells and merge them into one as follows:
1) Select the range of contiguous cells you want to merge.
2) Go to Format > Merge Cells > Merge Cells or Merge and Center Cells on the Menubar, or click on the Merge and Center Cells icon on the Formatting toolbar, or right-clickon the selected cells and select Merge Cells in the context menu. Using Merge andCenter Cells will center align any contents in the cells.
3) If the cells contain any data, the Merge Cells dialog (Figure 10) opens, showing choices
for moving or hiding data in the hidden cells.
4) Make your selection and click OK.

Figure 10: Merge choices for non-empty cells

CautionMerging cells can lead to calculation errors in formulas used in the spreadsheet.

Splitting
You can reverse a merge operation by splitting a cell that was previously created by merging
several cells.
1) Select a merged cell.
2) Go to Format > Merge Cells > Split Cells on the Menu bar, or click on the Merge andCenter Cells icon on the Formatting toolbar, or right-click and select Split Cells in thecontext menu.
3) Any data in the cell will remain in the first cell. If the hidden cells did have any contents
before the cells were merged, then you may have to manually move the contents to thecorrect cell.

Sharing content between sheets
You might want to enter the same information in the same cell on multiple sheets, for example toset up standard listings for a group of individuals or organizations. Instead of entering information
on each sheet individually, you can enter it in several sheets at the same time.

14 | Chapter 2 Entering, Editing, and Formatting Data

Figure 11: Select Sheets dialog
1) Go to Edit > Select > Select Sheets on the Menu bar to open the Select Sheets dialog(Figure 11).
2) Select the individual sheets where you want the information to be repeated.
3) Click OK to select the sheets and the sheet tabs will be highlighted.
4) Enter the information in the cells on the sheet where you want the information to first
appear and the information will be repeated in the selected sheets.
5) Deselect the sheets when you have finished entering the information that you wantrepeated in the sheets.

Tip
You can select sheets with the mouse, as described in the “Selecting sheets”
section of Chapter 1, Introduction.

CautionThis technique automatically overwrites, without any warning, any information thatis already in the cells on the selected sheets. Make sure you deselect theadditional sheets when you have finished entering the information to be repeated
before continuing to enter data into the spreadsheet.

Validating cell contents
When creating spreadsheets for other people to use, you may want to make sure they enter datathat is valid or appropriate for the cell. You can also use validation in your own work as a guide toentering data that is either complex or rarely used.
Fill series and selection lists can handle some types of data, but are limited to predefined
information. For example, a cell may 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, it can also define the range of contents that can beentered, and provide help messages explaining the content rules set up for the cell and what
users should do when they enter invalid content. You can also set the cell to refuse invalid
content, accept it with a warning, or start a macro when an error is entered.
Validating cell contents | 15

Defining validation
To validate any new data entered into a cell:
1) Select a cell and go to Data > Validity on the Menu bar to open the Validity dialog (Figure
12).

Figure 12: Validity dialog – Criteria tab2) Define the type of contents that can be entered in that cell using the options given on thetabbed pages for Criteria, Input Help, and Error Alert. The options are explained below.

Criteria options
Specify the validation rules for the selected cells using the Criteria tab of the Validity dialog asshown in Figure 12. For example, you can define criteria such as numbers between 1 and 10, or
texts that are no more than 20 characters.
The options available on the Criteria tab will depend on what has been selected in the Allow
drop-down list.
•

Allow – select a validation option for the selected cells from the drop-down list.
– All values – no limitation.
– Whole Numbers – only whole numbers allowed.
– Decimal – all numbers correspond to decimal format.
– Date – all numbers correspond to date format. The entered values are formatted thenext time the dialog is called up.
– Time – all numbers correspond to time format. The entered values are formatted thenext time the dialog is called up.
– Cell range – allow only values that are given in a cell range. The cell range can be
specified explicitly, or as a named database range, or as a named range. The range
may consist of one column or one row of cells. If you specify a range of columns androws, only the first column is used.
– List – allow only values or strings specified in a list. Strings and values can be mixed.
Numbers evaluate to their value, so if you enter the number 1 in the list, the entry
100% is also valid.
– Text length – allow entries whose length matches the condition on the number ofcharacters that has been set.

16 | Chapter 2 Entering, Editing, and Formatting Data

– Custom – allow entries that correspond to a formula entered in the Formula box.
•

Allow empty cells – in conjunction with Tools > Detective > Mark Invalid Data, thisdefines that blank cells are shown as invalid data (disabled) or not shown (enabled).

•

Show selection list – shows a list of all valid strings or values to select from. The list canbe opened either by clicking the down arrow at the right of the cell, or by selecting thecell and pressing Alt+↓.

•

Sort entries ascending – sorts the selection list in ascending order and filters duplicatesfrom the list. If not checked, the order from the data source is taken.

•

Source – enter the cell range that contains the valid values or text.

•

Entries – enter the entries that will be valid values or text strings.

•

Data – select the comparative operator that you want to use from the drop-down list. The
available operators depend on what you have selected in the Data drop-down list. For
example, if you select valid range, the Minimum and Maximum input boxes replace theValue box.

•

Value – enter the value for the data validation option that you selected in the Data dropdown list.

•

Minimum – enter the minimum value for the data validation option that you selected in
the Data drop-down list.

•

Maximum – enter the maximum value for the data validation option that you selected in
the Data drop-down list.

•

Formula – enter a formula that can be interpreted as true (non-zero) or false (zero) to
provide a custom validation. For example, assuming cell A4 was selected before opening
the dialog, you could enter ISEVEN(A4) to indicate that only even values should beentered in cell A4.

Input Help options
Enter the message to be displayed when the cell or cell range is selected in the spreadsheet(Figure 13).

Figure 13: Validity dialog – Input Help tab•

Show input help when cell is selected – displays the message that you enter in the
Title and Input help boxes when the cell or cell range is selected in the sheet. If you entertext in the Title and Input help boxes and then deselect this option, the text will not be
displayed.
Validating cell contents | 17•

Title – enter the title to be displayed when the cell or cell range is selected.

•

Input help – enter the message to be displayed when the cell or cell range is selected.

Error Alert options
Define the error message that is displayed when invalid data is entered in a cell (Figure 14).
•Show error message when invalid values are entered – when selected, displays theerror message that you enter in the Contents area when invalid data is entered in a cell.

•

Action – select the action that you want to occur when invalid data is entered in a cell.
– Stop – rejects the invalid entry and displays a dialog that you have to close byclicking OK.
– Warning and Information – displays a dialog that can be closed by clicking OK orCancel. The invalid entry is only rejected when you click Cancel.
– Macro – activates the Browse button to open the Macro Selector dialog where you
can select a macro that is executed when invalid data is entered in a cell. The macrois executed after the error message is displayed.

•

Title – enter the title of the macro or the error message that you want to display wheninvalid data is entered in a cell.

•

Error message – enter the message that you want to display when invalid data isentered in a cell.

Figure 14: Validity dialog – Error Alert tab

Calc Detective
The Detective is a tool within Calc that you can use to locate any cells in a spreadsheet that
contain invalid data if the cells are set to accept invalid data with a warning.
1) Go to Tools > Detective > Mark Invalid Data on the Menu bar to locate any cells
containing invalid data. The Detective function marks any cells containing invalid data.
2) Correct the data so that it becomes valid.
3) Go to Tools > Detective > Remove All Traces on the Menu bar and any cells that werepreviously marked as containing invalid data have the invalid data mark removed.

18 | Chapter 2 Entering, Editing, and Formatting Data

Note
A validity rule is considered part of the format for a cell. If you select Delete all on
the Delete Contents dialog (Figure 16), then it is removed. If you want to copy avalidity rule with the rest of the cell, use Edit > Paste Special > Paste Special toopen the Paste Special dialog (Figure 17), then select Paste all or Formats andclick OK.

Editing data
Deleting data
Deleting cell data only
Data can be deleted from a cell without deleting any of the cell formatting. Select a cell or a range
of cells and then press the Delete key.

Deleting cells
This option completely deletes selected cells, columns, or rows. The cells below or to the right ofthe deleted cells will fill the space.
1) Select a cell or a range of cells.
2) Select Sheet > Delete Cells on the Menu bar, or right-click inside the selected cells andchoose Delete in the context menu, or press the Ctrl+– keys.

Figure 15: Delete Cells dialog
3) The Delete Cells dialog (Figure 15) provides four options to specify how sheets are
displayed after deleting cells:– Shift cells up. Fills the resulting space with data from the cells underneath.
– Shift cells left. Fills the resulting space with data from the cells to the right of thedeleted cells.
– Delete entire row(s). After selecting at least one cell, deletes the entire row from the
sheet.
– Delete entire column(s). After selecting at least one cell, deletes the entire column
from the sheet.
4) To confirm the selection, click OK.

Editing data | 19

Note
The selected delete option is stored and reloaded when the dialog is next
opened, until LibreOffice is closed. After opening LibreOffice again the delete
option contains the default setting.

Deleting data and formatting
Data and cell formatting can be deleted from a cell at the same time. To do this:1) Select a cell or a range of cells.
2) Select Sheet > Clear Cells on the Menu bar, or right-click inside the selected cells andchoose Clear Contents from the context menu, or press the Backspace key.
3) In the Delete Contents dialog (Figure 16), choose any of the options or choose Delete
All. Click OK.

Figure 16: Delete Contents dialog

Replacing data
To completely replace data in a cell and insert new data, select the cell and type in the new data.
The new data will replace the data already contained in the cell and will retain the originalformatting used in the cell.
Alternatively, click in the Input line on the Formula Bar, then double-click on the data to highlight itcompletely and type the new data.

Editing data
Sometimes it is necessary to edit the contents of a cell without removing all of the data from thecell. For example, changing the phrase “Sales in Qtr. 2” to “Sales rose in Qtr” can be done asfollows.

Using the keyboard
1) Click in the cell to select it.
2) Press the F2 key and the cursor is placed at the end of the cell.
3) Press the Backspace key to delete any data up to the point where you want to enter newdata.
4) Alternatively, use the keyboard arrow keys to reposition the cursor where you want tostart entering the new data in the cell, then press the Delete key or Backspace key todelete any unwanted data before typing the new data.
5) When you have finished editing, press the Enter key to save the changes.

20 | Chapter 2 Entering, Editing, and Formatting Data

Tip
Each time you select a cell, the contents are displayed in the Input line on the
Formula Bar. Using the Input line may be easier when editing data.

Using the mouse
1) Double-click on the cell to select it and place the cursor in the cell for editing.
2) Reposition the cursor to where you want to start editing the data in the cell.
3) Alternatively, single-click to select the cell, then move the cursor to the Input line on the
Formula Bar and click at the position where you want to start editing the data in the cell.
4) When you have finished, click away from the cell to deselect it and the editing changes
are saved.

Paste Special function
You can use the Paste Special function to paste into another cell selected parts of the data in theoriginal cell or cell range, for example its format or the result of its formula.

Paste Special dialog
1) Select a cell or a cell range.
2) Go to Edit > Copy on the Menu bar, or click the Copy icon on the Standard toolbar, orright-click and select Copy in the context menu, or press Ctrl+C.
3) Select the target cell or cell range.
4) Go to Edit > Paste Special > Paste Special on the Menu bar, or right-click and selectPaste Special > Paste Special in the context menu, or use the keyboard shortcut
Ctrl+Shift+V, to open the Paste Special dialog (Figure 17).
5) Select the options for Paste, Operations, Options, and Shift Cells. The Paste Special
options are explained below.
6) Click OK to paste the data into the target cell or range of cells and close the dialog.

Figure 17: Paste Special dialog

Editing data | 21

Tip
Instead of steps 5) and 6) above, you can choose one of the four preset options
at the left of the dialog – Values Only, Values & Formats, Formats Only, orTranspose All. With the Run immediately checkbox ticked, clicking one of theseshortcut buttons actions the requested paste operation and closes the dialog.

Paste Special options•

Paste – select a format for the clipboard contents that you want to paste.
– All – pastes all cell contents, comments, formats, and objects into the current
document.
– Text – pastes cells containing text.
– Numbers – pastes cells containing numbers.
– Date & time – pastes cells containing date and time values.
– Formulas – pastes cells containing formulas.
– Comments – pastes comments that are attached to cells. If you want to add thecomments to the existing cell content, select the Add operation.
– Formats – pastes cell format attributes.
– Objects – pastes objects contained within the selected cell range. These can be OLE
objects, chart objects, or drawing objects.

•

Operations – select the operation to apply when you paste cells into the sheet.
– None – does not apply an operation when you insert the cell range from the
clipboard. The contents of the clipboard will replace existing cell contents.
– Add – adds the values in the clipboard cells to the values in the target cells. Also, if
the clipboard only contains comments, adds the comments to the target cells.
– Subtract – subtracts the values in the clipboard cells from the values in the target
cells.
– Multiply – multiplies the values in the clipboard cells with the values in the target cells.
– Divide – divides the values in the target cells by the values in the clipboard cells.

•

Options – sets the paste options for the clipboard contents.
– Skip empty cells – does not replace target cells with empty cells from the clipboard. If
you use this option in conjunction with the Multiply or the Divide operation, theoperation is not applied to the target cell of an empty cell in the clipboard. If you
select a mathematical operation and deselect Skip empty cells, empty cells in the
clipboard are treated as zeroes. For example, if you apply the Multiply operation, thetarget cells are filled with zeroes.
– Transpose – pastes the rows of the range in the clipboard as columns of the output
range, and the columns of the range in the clipboard as rows.
– As Link – inserts the cell range as a link, so that changes made to the cells in thesource file are updated in the target file. To ensure that changes made to empty cells
in the source file are updated in the target file, ensure that All is also selected in the
Paste area. You can also link sheets within the same spreadsheet. When you link toother files, a DDE link is automatically created. A DDE link is inserted as a matrix
formula and can only be modified as a whole.

22 | Chapter 2 Entering, Editing, and Formatting Data•

Shift Cells – set the shift options for the target cells when the clipboard content isinserted.
– Don't shift – replaces target cells with inserted cells.
– Down – shifts target cells downward when you insert cells from the clipboard.
– Right – shifts target cells to the right when you insert cells from the clipboard.

Paste Only options
If you only want to copy text, numbers, or formulas to the target cell or cell range:
1) Select the source cell or cell range and copy the data.
2) Select the target cell or cell range.
3) Right-click on the target cell or cell range and select Paste Special in the context menu,
then select Text, Number, or Formula.
4) Alternatively, use the Paste Only Text, Paste Only Numbers, or Paste Only Formulaoptions in the Edit > Paste Special menu on the Menu bar.

Insert cell fields
You can insert a field linked to the date, sheet name, or document name in a cell.
1) Select a cell and double-click to activate edit mode.
2) Right-click and select Insert Field > Date, Time, Sheet Name or Document Title in thecontext menu.
3) Alternatively use the similar options in the Insert > Field menu on the Menu bar.

Note
The Insert Field > Document Title command inserts the name of the
spreadsheet and not the title defined on the Description tab of the Properties
dialog for the file.

Tip
The fields are refreshed when the spreadsheet is saved or recalculated whenusing the Ctrl+Shift+F9 shortcut.

Group and outline
You can create an outline of your data and group rows or columns together so that you cancollapse a group to hide it or expand a group to show it using a single click.
The basic controls for grouping and outlining show plus (+) or minus (-) signs on the groupindicator to show or hide rows or columns. However, if there are groups nested within each other,the basic controls have numbered buttons so you can hide the different levels of nested groups.

Grouping
To group rows or columns:
1) Select the cells you want to group in the spreadsheet.
2) Go to Data > Group and Outline > Group on the Menu bar, or press the F12 key.

Group and outline | 23

3) On the Group dialog that opens, select either Rows or Columns and click OK. A groupindicator appears to the left of any rows grouped or above any columns grouped. Figure
18 shows a group indicator on the left of the first two rows of the spreadsheet showingthat they have been grouped.

Figure 18: Group indicator

Hiding details
To hide the details of any group of rows or columns:
1) Click on the minus (–) sign on the group indicator.
2) Alternatively, select a cell within the group and go to Data > Group and Outline > Hide
Details on the Menu bar.
3) The rows or columns are hidden and the minus (–) sign becomes a plus (+) sign on thegroup indicator.

Showing details
To show the details of any hidden groups of rows or columns:
1) Click on the plus (+) sign on the group indicator.
2) Alternatively, select a cell on each side of the hidden group and go to Data > Group andOutline > Show Details on the Menu bar.
3) The hidden rows or columns are displayed and the plus (+) sign becomes a minus (–)
sign on the group indicator.

Ungrouping
To ungroup any groups of rows or columns:
1) Make sure the grouped rows or columns are displayed and click on a cell within thegroup.
2) Go to Data > Group and Outline > Ungroup on the Menu bar, or use the keyboard
combination Ctrl+F12.
3) If only rows or only columns are grouped, they are ungrouped. If both rows and columns
are grouped, select either Rows or Columns on the Ungroup dialog and click OK.

CautionAny hidden groups of rows or columns must be displayed. If they are hidden, thenthe grouped rows or columns are deleted from the spreadsheet.

Note
If there are nested groups, only the last group of rows or columns created is
ungrouped.

24 | Chapter 2 Entering, Editing, and Formatting Data

AutoOutline
If a selected cell range contains formulas or references, Calc can automatically outline theselection. For example, in Figure 19 the cells for the 1st and 2nd quarters each contain a sum
formula for the three cells to their left. If you apply the AutoOutline command, the columns aregrouped into two quarters.
To apply the AutoOutline function, go to Data > Group and Outline > AutoOutline on the Menu
bar. Calc will then check for cells that contain formulas or references and automatically group thecells as necessary.

Figure 19: Example of AutoOutline

Removing
To remove any cell groups of rows or columns, go to Data > Group and Outline > Remove
Outline on the Menu bar and any groups are removed.
For any cell group of rows or columns that are hidden, the grouping is removed from the cells
and the cells are displayed in the spreadsheet.

Filtering
A filter is a list of conditions that each entry has to meet to be displayed. Calc provides three
types of filters:•

Standard – specifies the logical conditions to filter the data.

•

AutoFilter – filters data according to a specific value or string. Automatically filters theselected cell range and creates one-row list boxes where you can choose the items thatyou want to display.

•

Advanced – uses filter criteria from specified cells.

Applying a standard filter
A standard filter is more complex than the AutoFilter. You can set as many as eight conditions asa filter, combining them with the operators AND or OR. Standard filters are mostly useful for
numbers, although a few of the conditional operators can also be used for text.
1) Select a cell range in the spreadsheet.
2) Go to Data > More Filters > Standard Filter on the Menu bar to open the Standard Filter
dialog (Figure 20).
3) Specify the filter criteria and filtering options that you want to use.
4) Click OK to carry out standard filtering and close the dialog. Any records that match thefilter criteria and options that you specified are shown.

Filtering | 25

Figure 20: Standard Filter dialog
Use the Standard Filter dialog to define the filter conditions to be combined to form the overall
filter criteria. Each filter condition is specified by indicating the type of logical operator, the nameof a field, a logical condition, and a value.
•

Operator – for the following arguments, you can choose between the logical operators
AND and OR. No logical operator is specified for the first filter condition in the list.

•

Field name – specifies the field names from the current table to set them in theargument. You will see the column identifiers if no text is available for the field names.

•

Condition – specifies the comparative operators through which the entries in the Field
name and Value fields can be linked.

•

Value – specifies a value to filter the field. The Value list box contains all possible values
for the specified Field name. Select a value to be used in the filter, including Empty andNot Empty entries.

•

Remove – deletes the associated filter criterion from the Filter Criteria area.

•

Case sensitive – distinguishes between uppercase and lowercase letters when filtering
the data.

•

Range contains column labels – includes the column labels in the first row of a cellrange.

•

Copy results to – select the check box and then select the cell range where you want todisplay the filter results. You can also select a named range from the list.

•

Regular expressions – select to use regular expressions in the filter definition. If
selected, you can use regular expressions in the Value field of the Standard Filter dialog
if the Condition field is set to “=” (equal) or “<>” (not equal). For more information about
regular expressions, see the section entitled “Regular expressions” in Chapter 1,
Introduction.

•

No duplications – excludes duplicate rows from the list of filtered data.

26 | Chapter 2 Entering, Editing, and Formatting Data•Keep filter criteria – select Copy results to and then specify the destination range
where you want to display the filtered data. If this box is checked, the destination range
remains linked to the source range. You must have defined the source range under Data
> Define Range as a database range. You can also reapply the defined filter at any timeby clicking into the source range and then going to Data > Refresh Range.

Applying an AutoFilter
An AutoFilter adds a drop-down list to the top row of one or more data columns which lets you
select the rows to be displayed. The list includes every unique entry in the selected cells sorted
into lexical order (see https://www.dictionary.com/browse/lexical-order for an explanation oflexical order). AutoFilter can be used on multiple sheets without first defining a database range.
1) Click in a cell range on the spreadsheet. If you want to apply multiple AutoFilters to thesame sheet, you must first define database ranges, then apply the AutoFilters to the
database ranges.
2) Go to Data > AutoFilter on the Menu bar, click the AutoFilter icon on the Standard
toolbar, or press Ctrl+Shift+L. An arrow button is added to the head of each column inthe database range and the size of these buttons scales according to the sheet’s zoom
level.
3) Click the arrow or small triangle in the column that contains the value or string that you
want to set as the filter criteria (shown in Figure 21).

Figure 21: AutoFilter example
4) Select one or more values or enter a string to be used as the filter criteria in the Search
items box. After clicking OK only the records matching the filter criteria will be visible.
Rows affected by the AutoFilter will have their numbers displayed in blue. Columns where
filtering conditions were created will display the arrow button in blue.

Filtering | 27

Filtering by color
You can filter data by the colors used in the cells. For cell background colors, a list of backgroundcolors used in the range appears. Select the desired color for filtering. The No Fill options isused if the cells don't have a background color set.

Figure 22: Filtering by font andbackground colors
Similarly, select the desired font color to filter the data.
In either case, there are no provisions to filter by a combination of different colors.

Filtering by condition
An additional set of conditions is available for filtering:•

Empty / Not Empty: Cell data to filter either contains data or not.

•Top 10: Filter cells that has numeric values in the top 10 values of the filtered range.

•

Bottom 10: similarly, filter cell data that has numeric values in the bottom 10 values ofthe filtered range.

Applying an advanced filter
An advanced filter has a structure similar to a standard filter. The difference is that the advanced
filter arguments are not entered in a dialog. Instead, filters can be entered in a blank area of a
spreadsheet, then referenced by the filter dialog to apply the filters.
1) Select a cell range in the spreadsheet.
2) Go to Data > More Filters > Advanced Filter on the Menu bar to open the Advanced
Filter dialog (Figure 23).
3) In Read Filter Criteria From, select the named range, or enter the cell range that contains
the filter criteria that you want to use.
4) Click OK to carry out advanced filtering and close the dialog. Any records that match thefilter criteria and options that you specified are shown.

28 | Chapter 2 Entering, Editing, and Formatting Data

Note
The options for advanced filtering are the same as those used for standard
filtering, see “Applying a standard filter” on page 25 for more information.

Figure 23: Advanced Filter dialog
For an example of an advanced filter, see the Help page entitled “Filter: Applying Advanced
Filters”.

Sorting records
Sorting within Calc arranges the cells in a sheet using the sort criteria that you specify. Several
criteria can be used and a sort applies each criteria consecutively. Sorts are useful when you aresearching for a particular item and become even more useful after you have filtered data.
Also, sorting is useful when you add new information to a spreadsheet. When a spreadsheet islong, it is usually easier to add new information at the bottom of the sheet, rather than adding
rows in their correct place. After you have added information, you can then sort the records to
update the spreadsheet.

Sort dialog
To sort cells in a spreadsheet using the Sort dialog:
1) Select the cells, rows, or columns to be sorted.
2) Go to Data > Sort on the Menu bar, or click the Sort icon on the Standard toolbar, toopen the Sort dialog.
3) On the Options tab (Figure 24), choose options including whether to sort on rows orcolumns. See “Sort options” on page 30 for details.
4) On the Sort Criteria tab (Figure 25), select the criteria in the drop-down lists. The
selection lists are populated from the selected cells.
5) Select either Ascending order (A-Z, 0-9) or Descending order (Z-A, 9-0).
6) Headers – omits the first column/row in the selection from the sort.
7) Direction – setting defines the name and function of this check box: if top to bottom, thencolumn labels; if left to right, then row labels.
8) Click OK and the sort is carried out on the spreadsheet.

Sorting records | 29

Note
If any of the cells that you select for sorting are protected and the sheet isprotected, then Calc cannot modify those cells and the sort will not be executed.
An error message will be displayed to indicate that protected cells cannot be
modified. However, it is possible to sort a range containing a row of column labelsthat are protected, since these are not modified by the sort.

Figure 24: Sort dialog – Options tab

Figure 25: Sort dialog – Sort Criteria tab

Sort options
On the Options tab of the Sort dialog (Figure 24), you can set these options:•

Case sensitive – sorts first by uppercase letters and then by lowercase letters. For Asian
languages, special handling applies.

30 | Chapter 2 Entering, Editing, and Formatting Data

Note
For Asian languages, select Case sensitive to apply multi-level collation. With
multi-level collation, entries are first compared in their primitive forms with theircases and diacritics ignored. If they evaluate as the same, their diacritics aretaken into account for the second-level comparison. If they still evaluate as thesame, their cases, character widths, and Japanese Kana difference areconsidered for the third-level comparison.
•

Include formats – preserves the current cell formatting.

•

Enable natural sort – natural sorting is an algorithm that sorts string-prefixed numbers
based on the value of the numerical element in each sorted number, instead of the
traditional way of sorting them as ordinary strings. For instance, assume you have aseries of values such as, A1, A2, A3, A4, A5, A6, ..., A19, A20, A21. When you put thesevalues into a range of cells and run the sort, it will become A1, A11, A12, A13, ..., A19,
A2, A20, A21, A3, A4, A5, ..., A9. With natural sorting selected, values such as these aresorted correctly.

•

Include boundary column(s) containing only comments – keeps these cells
associated with the cells being sorted.

•

Include boundary column(s) containing only images – keeps these cells associatedwith the cells being sorted.

•

Copy sort results to – copies the sorted list to the cell range that you specify. Select a
named cell range where you want to display the sorted list, or enter a cell range in the
input box.

•

Custom sort order – select this option and then select the custom sort order that you
want to apply. The available selections are defined as “fill series” in Tools > Options >LibreOffice Calc > Sort Lists. See “Defining a fill series” on page 12.

•

Language – select the language for the sorting rules.

•

Options – select a sorting option for the language. For many languages this selection isgrayed out but for some languages options are available. For example, for German thedefault choice is Alphanumeric, but you can also select the Phone book option to
include the umlaut special character in the sorting.

Quick sort
If the columns in the spreadsheet have a header with a text format, you can use a quick sort.
1) Select a cell or a cell range to be sorted.
2) Select Data > Sort Ascending or Data > Sort Descending on the Menu bar, or click the
Sort Ascending or Sort Descending icon on the Standard toolbar.

Find and replace
Calc has two ways to find text within a document: the Find toolbar and the Find and Replacedialog. The toolbar is quick and easy to use, but its functions are more limited than using the
dialog.

Find and replace | 31

Find toolbar

Figure 26: Find toolbar
1) Go to View > Toolbars > Find or Edit > Find on the Menu bar, or use the keyboard
shortcut Ctrl+F, to open the Find toolbar (Figure 26). By default this toolbar is docked in
the lower left of the Calc window. You can undock the toolbar and move it.
2) Type a search term in the Find text box.
3) To refine the search, you can select the Match Case option. For example, if Owner is thesearch term, selecting Match Case will find Owner but not owner. You can also select the
Find All or Formatted Display options.
4) Click the Find Next or Find Previous icon. To find other occurrences of the same term,
continue clicking the icon.

Find and Replace dialog
To open the Find and Replace dialog (Figure 27), go to Edit > Find and Replace on the Menubar, or click the Find and Replace icon on the Standard toolbar or the Find toolbar, or use the
keyboard shortcut Ctrl+H.

Finding
1) In the Find and Replace dialog enter the search criteria in the Find box.
2) Select basic options from those located directly under the Find box.
3) If necessary, click on Other options to increase the number of search filters.
4) Click Find Next to locate the first instance of the search criteria.
5) Click Find Next again to locate the next instance of the search criteria. Repeat asneeded.
6) Or, click Find All to locate all cells containing the search criteria. These cells will behighlighted in the spreadsheet. A Search Results dialog will pop up, listing the cell
locations.

Replacing1) In the Find and Replace dialog enter the search criteria in the Find box.
2) Select basic options from those located directly under the Find box.
3) Enter the replacement contents in the Replace box.
4) If necessary, click on Other options to increase the number of search filters.
5) To step through the cells and choose whether to replace the contents:a) Click Find Next to locate the first instance of the search criteria.
b) If necessary, click Replace to replace the search criteria with the contents of theReplace box.
c) Repeat as required.
6) Or, to locate and replace all instances of the search criteria without stopping at each one,click Replace All. A Search Results dialog will pop up, listing the affected cells.
32 | Chapter 2 Entering, Editing, and Formatting Data

Figure 27: Find and Replace dialog

Tip
To replace the first instance of the search criteria you can simply press Replace
without pressing Find Next first.

CautionUse Replace All with caution; otherwise, you may end up with some 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.

Find and Replace options
The options available to refine your find and replace are as follows:•

Find – enter the text that you want to search for, or select a previous search in the list.

•

Match case – distinguishes between uppercase and lowercase characters.

•

Formatted display – searches for cell contents in a particular format. For example, if acell has a currency value, say $123.45 and another has the same contents but default
formatting (123.45), then searching for “$123.45” will find the currency cell but not thedefault-formatted cell.

•

Entire cells – searches for cells with contents that are identical to the search text.

•

All sheets – Searches through all of the sheets in the current spreadsheet.

•

Replace – enter the replacement text, or select a recent replacement text or style fromthe list.

•

Current selection only – searches only the selected text or cells.

•

Replace backwards – search starts at the current cursor position and goes backwards
to the beginning of the spreadsheet.
Find and replace | 33

•Wildcards – select to use wildcards in the search. A wildcard is a special character that
represents one or more unspecified characters. Wildcards make text searches more
powerful, but often less specific. The following wildcards are available:–? (question mark) matches any single character. For example, b?g finds “bag”,“beg”, “big”, “bog”, and “bug”.

–* (asterisk) matches any sequence of characters, including an empty string. For
example, *cast finds “cast”, “forecast”, and “outcast”.

–~ (tilde) escapes the special meaning of a question mark, asterisk, or tilde character
that follows immediately after the tilde character. For example, why~? finds “why?”.

•

Regular expressions – select to use regular expressions in the search. Regularexpressions offer the most powerful method of searching for text strings. For more
information about regular expressions, see the “Regular expressions” section in Chapter
1, Introduction.

•

Similarity search – find terms that are similar to the Find text. Select this option, andthen click the Similarities button to define the similarity options on the Similarity Search
dialog.

•Cell Styles – searches for cell content formatted with the style that you specify. Selectthis option and then select a style from the Find list. To specify a replacement style,select a style from the Replace list.

•

Diacritic-sensitive – include diacritics and accents in the search.

•

Direction – determines the Rows or Columns order for searching the cells.

•

Search in – select either Formulas, Values, or Comments to search for the charactersthat you specify in the results of formulas, in values, or in comments.

Other options may appear depending on your language settings on the Tools > Options >Language Settings > Languages page (for example, Match character width, Sounds like
(Japanese), Kashida-sensitive). See the Help system for more information about these options.

Note
The Wildcards, Regular expressions, and Similarity search options aremutually exclusive; only one can be selected.

Search Results dialog
After you select Find All on the Find toolbar, or Find All or Replace All on the Find and Replacedialog, Calc’s initial default behavior is to display the Search Results dialog (Figure 28) after it
has completed the operation. This dialog summarizes the results. In addition, affected cells arehighlighted in the spreadsheet.
The Search Results dialog lists the affected cells and their current content (after any
replacement). It also states the number of affected cells. If you deselect the Show this dialog
checkbox on the Search Results dialog before pressing the Close button, then the dialog will not
be displayed after future uses of Find All or Replace All. The display of the Search Results
dialog can also be controlled by enabling or disabling the Tools > Options > LibreOffice Calc >View > Window > Summary on search option.

34 | Chapter 2 Entering, Editing, and Formatting Data

Figure 28: Search Results dialog

Searching and filtering with regular expressions
Support for regular expressions (often known as regex or regexp) is a hidden gem within Calc.
Regular expressions are very powerful and enable users to define complex search patterns forlocating data of interest within a spreadsheet. Some users may find the syntax used to define a
regular expression daunting and inexperienced users may make mistakes. However a small
investment in time to learn the basic concepts and some of the syntax will pay rich dividends inthe future. We recommend that you do not try to memorize the full syntax but just concentrate on
discovering a few aspects that will help you in your everyday work. As you see the benefits ofusing regular expressions, you will certainly be motivated to learn more.
A regular expression is a string of characters defining a pattern of text that is to be matched.
More detailed, general background information can be found on Wikipedia at
https://en.wikipedia.org/wiki/Regular_expression.
Regular expressions are widely used in many domains and there are multiple regular expression
processors available. Calc utilizes the open source Regular Expressions package from theInternational Components for Unicode (ICU), see
https://unicode-org.github.io/icu/userguide/strings/regexp.html for further details, including a fulldefinition of the syntax for ICU Regular Expressions.
Regular expressions appear in three areas of Calc’s functionality, as follows:•

Searching with the Find and Replace dialog, accessed by selecting Edit > Find andReplace on the Menu bar, by clicking the Find and Replace icon on the Find toolbar, orby pressing Ctrl+H.

•

Filtering using the Standard Filter and Advanced Filter dialogs, accessed by selecting
Data > More Filters > Standard Filter and Data > More Filters > Advanced Filter.

Searching and filtering with regular expressions | 35

•Functions. Many of Calc’s functions can use regular expressions in search criteria.
However these only operate correctly if the Tools > Options > LibreOffice Calc >Calculate > Formulas wildcards > Enable regular expressions in formulas option
has been selected. We also recommend that you enable the Tools > Options >LibreOffice Calc > Calculate > General Calculations > Search criteria = and <> must
apply to whole cells option so that search criteria in formulas must match the whole ofthe cell contents. See Chapter 8, Using Formulas and Functions, for more information.

To illustrate the use of regular expressions we can use a sales data spreadsheet, the first few
rows of which are shown in Figure 29.

Figure 29: Spreadsheet data for example regular expressions
Unfortunately the data entry clerks do not know the sales personnel well and so the spreadsheet
contains a number of misspellings of Brigitte’s name. On inspecting the data, we notice that if we
could find all cells containing a string that begins with the characters “Bri” and then replace theentire contents of each such cell with the string “Brigitte”, then the data in the spreadsheet would
be corrected. This can be achieved easily with a simple regular expression, as follows:
1) Select Edit > Find and Replace on the Menu bar. Calc opens the Find and Replacedialog (Figure 30).
2) Type ^Bri.* into the Find field. The character “^” means match at the beginning; “.”means match any character; and “*” means match 0 or more times.
3) Type Brigitte into the Replace field.
4) If necessary, click the icon to expand the Other options area of the dialog.
5) Make sure that the Regular expressions checkbox is checked.
6) Click Replace All. Calc updates the spreadsheet data, replacing all occurrences of“Bridget” and “Brigid” with “Brigitte”.
7) Click Close to close the Find and Replace dialog.

36 | Chapter 2 Entering, Editing, and Formatting Data

Figure 30: Using a regular expression on the Find and Replace dialog
Suppose we want to filter the same spreadsheet (Figure 29) to show only data relating to theTennis and Golf categories, from the North and East regions. This is easily achieved using
regular expressions, using the following steps:
1) Click a cell within the sales data.
2) Select Data > More Filters > Standard Filter on the Menu bar. Calc opens the Standard
Filter dialog (Figure 31).
3) Select Category from the first drop-down menu in the Field name column.
4) Select = from the first drop-down menu in the Condition column.
5) Type Tennis|Golf in the first text box in the Value column.
6) Select AND from the first drop-down menu in the Operator column.
7) Select Region from the second drop-down menu in the Field name column.
8) Select = from the second drop-down menu in the Condition column.
9) Type North|East in the second text box in the Value column.
10) If necessary, click the icon to expand the Options area of the dialog.
11) Make sure that the Regular expressions checkbox is checked.
12) Click OK and Calc updates the data displayed in accordance with the specified filter
criteria.

Searching and filtering with regular expressions | 37

Figure 31: Using regular expressions on the Standard Filter dialog
The data shown in Figure 32 can be used to show a simple example of a function call thatincorporates a regular expression. Suppose we wanted to calculate the revenue from the sale ofpencils, pencil cases, and pens. This can be achieved by entering the following formula into an
empty cell =SUMIFS(C2:C6; A2:A6; "^pen.*"), where the regular expression matches thename of any product that starts with the characters “pen”. Alternatively we could calculate therevenue from the sale of books and notebooks using the formula =SUMIFS(C2:C6; A2:A6;".*book$"), where the regular expression matches the name of any product that ends with thecharacters “book”.

Figure 32: Stationery sales and revenue data
In Calc there are 26 functions that support the use of regular expressions and these are listed in
Chapter 8, Using Formulas and Functions. The REGEX function is particularly powerful,matching and extracting, or optionally replacing, text using regular expressions. For example theformula =REGEX("123456ABCDEF";"[126]";"";"g") returns "345ABCDEF", where any
occurrence of "1", "2" or "6" is replaced by the empty string and is thus deleted.

Tip
The online help describes many more regular expressions and their uses.

38 | Chapter 2 Entering, Editing, and Formatting Data

Note
If interoperability with Microsoft Excel is important for your spreadsheet, then you
may not be able to fully utilize Calc’s regular expression facilities because Excel
does not provide equivalent facilities. Hence, when you export a Calc
spreadsheet to Excel format, information relating to regular expressions will not
be usable within Excel. In this case you can use the less powerful wildcards
facility provided by Calc because spreadsheets that utilize wildcards can beexported to Excel format without loss of data. See Chapter 8, Using Formulas andFunctions, for more information about wildcards.
There are numerous websites that include examples of regular expressions and these provide an
endless source of inspiration and ideas to help improve your data analysis skills.
Additional information about regular expressions in Calc can be found in the Help system and on
The Document Foundation’s wiki starting at
https://wiki.documentfoundation.org/Documentation/HowTo/Calc/Regular_Expressions.

Searching and filtering with regular expressions | 39