This document is Copyright © 2024 by the LibreOffice Documentation Team. Contributors are listed 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.
To this edition
To previous editions
Jean Hollis Weber
Barbara M. Tobias
B. Antonio Fernández
Please direct any comments or suggestions about this document to the Documentation Team’s forum at https://community.documentfoundation.org/c/documentation/loguides/ (registration is required) or send an email to: loguides@community.documentfoundation.org.
Everything you send to a forum, including your email address and any other personal information that is written in the message, is publicly archived and cannot be deleted. Emails sent to the forum are moderated.
Published March 2024. Based on LibreOffice 24.2 Community.Other versions of LibreOffice may differ in appearance and functionality.
Some keystrokes and menu items are different on macOS from those used in Windows and Linux. The table below gives some common substitutions for the instructions in this document. For a detailed list, see the application Help and Appendix A (Keyboard Shortcuts) to this guide.
Windows or Linux
Tools > Options on Menu bar
LibreOffice > Preferences on Menu bar
Access to setup options
Ctrl +click and/or right-click depending on computer setup
Opens a context menu
Ctrl or Control
⌘ and/or Cmd or Command , depending on keyboard
Used with other keys
⌥ and/or Alt or Option depending on keyboard
Used with other keys
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 sheets of the same spreadsheet at the same time.
Most data entry in Calc can be done using the keyboard.
Click in the cell and type the number using the number keys on either the main keyboard or the numeric keypad.
To enter a negative number, either type a minus sign in front of the number or enclose the number in parentheses, for example (1234). The result for both methods of entry is the same; for example, –1234.
By default, if a number is entered with leading zeroes, for example 01481, Calc will drop the leading zeroes. To retain both the number format and a minimum number of characters in a cell when entering numbers, for example 1234 and 0012, use one of these methods to add leading zeroes.
1) With the cell selected, go to Format > Cells on the Menu bar, or right-click on the cell and select 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 of characters required. For example, for four characters, enter 4. Any number less than four characters 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.
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 can also be converted into text using one of the following methods.
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.
Figure 1: Format Cells dialog – Numbers tab
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 is applied to the cell immediately.
Figure 2: Set leading zeroes in Sidebar
If numerical characters do not need to be treated as numbers in calculations (for example when entering zip codes), you can type an apostrophe (') before the number, for example '01481. When you move the cell focus, the apostrophe is removed, the leading zeroes are retained, and the number is converted to left-aligned text.
Click in a cell and type the text. The text is left-aligned by default. Cells can contain several lines of 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. Click on the Expand / Collapse Formula Bar icon located on the right of the Formula Bar and the Input 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
Select the cell and type the date or time. You can separate the date elements with a slash (/) or a hyphen (–) or use text, for example 10 Oct 2020. The date format automatically changes to the selected format used by Calc.
Tools > Options > Languages and Locales > General > Formats > Date acceptance 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 (for example, 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.
The date format will be influenced by the system or document language settings.
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 you want the character to appear.
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 is shown 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 be selected 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 and click 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.
Like several other Windows applications, Calc allows the user to input arbitrary Unicode characters by typing a hexadecimal code point and then pressing Alt + X . For example, typing A9 and then pressing Alt + X inserts a copyright symbol (@). This facility is available on all platforms and toggles between the character before the cursor and its Unicode notation.
Several other Windows applications also provide a way to use Alt + NumPad to input Unicode characters. NumPad represents a decimal code point entered using the digits on the numeric keypad. For example, pressing Alt , then typing 9 , 5 , 5 on the numeric keypad, and then releasing Alt , produces "λ" (Greek Small Letter Lambda, code U+03BB, decimal 955). This feature is available only on Windows and does not work on other platforms.
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 the required character from the drop-down. The area above the More Characters button on this drop-down shows the name of the highlighted character and this can be helpful distinguishing characters which look similar. In addition, the name of the character under the mouse pointer is shown in a tooltip. Click More Characters on this drop-down to open the Special Characters dialog (Figure 4).
Different fonts include different special characters. If you do not find a particular special character you want, try changing the Font and Subset selections.
Calc automatically applies many changes during data input using AutoCorrect, unless you have deactivated 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 the change 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 to open the AutoCorrect dialog (Figure 5).
Figure 5: AutoCorrect dialog
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
A - 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, space, hyphen, B)
A --B (A, space, hyphen, hyphen, B)
A –B (A, space, en-dash, B)
Entering data into a spreadsheet can be very labor-intensive, but Calc provides tools for removing 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, selection lists, and the Data Entry Form tool.
The Fill tool and selection lists automate input of repetitive material into cells. Calc also has the ability to input information into multiple sheets of the same spreadsheet at the same time.
The AutoInput function in Calc automatically completes entries, based on other entries in the same column.
When text is highlighted in a cell, AutoInput can be used as follows:
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 deselect AutoInput .
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 last cell you want to fill.
3) Go to Sheet > Fill Cells on the Menu bar and select the direction in which you want to copy or create data ( Down , Right , Up , Left , Sheets , Series , or Random Number ). A menu option will be grayed out if it is not available.
Figure 6: Examples of using the Fill tool
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 the original 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 1), a series will be created. To duplicate the number or text instead, hold Ctrl while dragging.
When 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 the Fill tool, any data contained in selected cells is overwritten.
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.
Figure 7: Fill Series dialog
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 and the 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).
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
Populate a cell range with automatically generated pseudo-random numbers using the selected distribution function and its specified parameters.
When you select a random number fill from Sheet > Fill Cells > Fill Random Number on the Menu bar, the Random Number Generator dialog (Figure 10) opens. Here you can select the type of random numbers you want.
Figure 10: Random Number Generator dialog
Define the range of cells to fill with random numbers. If you have previously selected a range, it will be displayed here.
Select the distribution function for the random number generator. Available distributions are Uniform, Uniform Integer, Normal, Cauchy, Bernoulli, Binomial, Chi Squared, Geometric, Negative Binomial, and Poisson.
Fields directly below Distribution enable entry of parameters related to the selected distribution, as follows:
Enable custom seed
Set a value to initiate the random number generator algorithm. It is used to initialize (seed) the random number generator in order to reproduce the same sequence of pseudo-random numbers. Specify a positive integer to produce a specific sequence.
Round the generated numbers to the given number of decimal places.
Selection lists are available only for text and are limited to using only text that has already been entered 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 at least 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.
The Data Entry Form tool makes table data entry easier in spreadsheets, accelerating intensive manual input. Using the tool you can enter, edit, and delete data records (or rows) and avoid horizontal scrolling when the table has many columns or when some columns are very wide.
To be effective, the data table should have a header row, where the content of each cell is the title of the column. The content of each header cell becomes the label for each data field in the form.
To use the Data Entry Form tool:
1) Select a header or data cell within the table of data.
2) Go to Data > Form on the Menu bar.
3) Calc displays the Data Form dialog (Figure 11), showing the data for the first entry in the data table.
4) Add, edit, or remove entries from the data table as required.
5) Click the Close button to close the dialog.
Figure 11: Using the Data Entry Form tool
The Data Form dialog provides a text entry field for displaying, editing, or entering the value of each cell within one row of the data table. In addition, the dialog provides the following options:
Use the Tab and Shift+Tab keys to jump forward and backward between text boxes of the Data Form dialog. In addition, you can use the form’s scroll bar to move between records.
Move to the last record before entering a new record, otherwise the current record will be edited.
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 Menu bar, or click on the Merge and Center Cells icon on the Formatting toolbar, or right-click on the selected cells and select Merge Cells in the context menu. Using Merge and Center Cells will center align any contents in the cells.
3) If the cells contain any data, the Merge Cells dialog (Figure 12) opens, showing choices for moving or hiding data in the hidden cells.
4) Make your selection and click OK .
Figure 12: Merge choices for non-empty cells
Merging cells can lead to calculation errors in formulas used in the spreadsheet.
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 and Center Cells icon on the Formatting toolbar, or right-click and select Split Cells in the context 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 the correct cell.
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 of individuals or organizations. Instead of entering information on each sheet individually, you can enter it in several sheets at the same time.
Figure 13: Select Sheets dialog
1) Go to Edit > Select > Select Sheets on the Menu bar to open the Select Sheets dialog (Figure 13).
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 want repeated in the sheets.
You can select sheets with the mouse, as described in the “Selecting sheets” section of Chapter 1, Introduction.
This technique automatically overwrites, without any warning, any information that is already in the cells on the selected sheets. Make sure you deselect the additional sheets when you have finished entering the information to be repeated before continuing to enter data into the spreadsheet.
When creating spreadsheets for other people to use, you may want to make 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 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 be entered, 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.
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 14).
Figure 14: Validity dialog – Criteria tab
2) Define the type of contents that can be entered in that cell using the options given on the tabbed pages for Criteria , Input Help , and Error Alert . The options are explained below.
Specify the validation rules for the selected cells using the Criteria tab of the Validity dialog as shown in Figure 14. 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.
Enter the message to be displayed when the cell or cell range is selected in the spreadsheet (Figure 15).
Figure 15: Validity dialog – Input Help tab
Define the error message that is displayed when invalid data is entered in a cell (Figure 16).
Figure 16: Validity dialog – Error Alert tab
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 were previously marked as containing invalid data have the invalid data mark removed.
A validity rule is considered part of the format for a cell. If you select Delete all on the Delete Contents dialog (Figure 18), then it is removed. If you want to copy a validity rule with the rest of the cell, use Edit > Paste Special > Paste Special to open the Paste Special dialog (Figure 19), then select Paste all or Formats and click OK .
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.
This option completely deletes selected cells, columns, or rows. The cells below or to the right of the 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 and choose Delete in the context menu, or press the Ctrl + – keys.
Figure 17: Delete Cells dialog
3) The Delete Cells dialog (Figure 17) provides four options to specify how sheets are displayed after deleting cells:
4) To confirm the selection, click OK .
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.
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 and choose Clear Contents from the context menu, or press the Backspace key.
3) In the Delete Contents dialog (Figure 18), choose any of the options or choose Delete All . Click OK .
Figure 18: Delete Contents dialog
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 original formatting used in the cell.
Alternatively, click in the Input line on the Formula Bar, then double-click on the data to highlight it completely and type the new data.
Sometimes it is necessary to edit the contents of a cell without removing all of the data from the cell. For example, changing the phrase “Sales in Qtr. 2” to “Sales rose in Qtr” can be done as follows.
To edit data with 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 new data.
4) Alternatively, use the keyboard arrow keys to reposition the cursor where you want to start entering the new data in the cell, then press the Delete key or Backspace key to delete any unwanted data before typing the new data.
5) When you have finished editing, press the Enter key to save the changes.
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.
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.
You can use the Paste Special function to paste into another cell selected parts of the data in the original cell or cell range, for example its format or the result of its formula.
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, or right-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 select Paste Special > Paste Special in the context menu, or use the keyboard shortcut Ctrl+Shift+V , to open the Paste Special dialog (Figure 19).
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 19: Paste Special dialog
Instead of steps and above, you can choose one of the four preset options at the left of the dialog – Values Only , Values & Formats , Formats Only , or Transpose All . With the Run immediately checkbox ticked, clicking one of these shortcut buttons actions the requested paste operation and closes the dialog.
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 Formula options in the Edit > Paste Special menu on the Menu bar.
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 the context menu.
3) Alternatively use the similar options in the Insert > Field menu on the Menu bar.
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.
The fields are refreshed when the spreadsheet is saved or recalculated when using the Ctrl+Shift+F9 shortcut.
You can create an outline of your data and group rows or columns together so that you can collapse 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 group indicator 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.
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.
3) On the Group dialog that opens, select either Rows or Columns and click OK . A group indicator appears to the left of any rows grouped or above any columns grouped. Figure 20 shows a group indicator on the left of the first two rows of the spreadsheet showing that they have been grouped.
Figure 20: Group indicator
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 the group indicator.
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 and Outline > 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.
To ungroup any groups of rows or columns:
1) Make sure the grouped rows or columns are displayed and click on a cell within the group.
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 .
Any hidden groups of rows or columns must be displayed. If they are hidden, then the grouped rows or columns are deleted from the spreadsheet.
If there are nested groups, only the last group of rows or columns created is ungrouped.
If a selected cell range contains formulas or references, Calc can automatically outline the selection. For example, in Figure 21 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 are grouped 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 the cells as necessary.
Figure 21: Example of AutoOutline
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.
A filter is a list of conditions that each entry has to meet to be displayed. Calc provides three types of filters:
A standard filter is more complex than the AutoFilter. You can set as many as eight conditions as a 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 22).
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 the filter criteria and options that you specified are shown.
Figure 22: 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 name of a field, a logical condition, and a value.
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 of lexical 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 the same 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 in the 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 23).
Figure 23: 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.
You can filter data by the background or font colors used in the cells of a column of interest.
It is not possible to filter by a combination of different colors.
Figure 24: Filtering by font and background colors
The AutoFilter drop-down provides a Filter by Condition menu containing five entries:
Figure 25: Filtering by condition
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 26).
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 the filter criteria and options that you specified are shown.
The options for advanced filtering are the same as those used for standard filtering, see “ Applying a standard filter” on page 1 for more information.
Figure 26: Advanced Filter dialog
For an example of an advanced filter, see the Help page entitled “Filter: Applying Advanced Filters”.
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 are searching 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 is long, 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.
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, to open the Sort dialog.
3) On the Options tab (Figure 27), choose options including whether to sort on rows or columns. See “Sort options” on page 1 for details.
4) On the Sort Criteria tab (Figure 28), 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 – select Top to bottom (sort rows) to sort rows by the values in the active columns of the selected range or select Left to right (sort columns) to sort columns by the values in the active rows of the selected range.
8) Click OK and the sort is carried out on the spreadsheet.
If any of the cells that you select for sorting are protected and the sheet is protected, 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 labels that are protected, since these are not modified by the sort.
Figure 27: Sort dialog – Options tab
Figure 28: Sort dialog – Sort Criteria tab
On the Options tab of the Sort dialog (Figure 27), you can set these options:
For Asian languages, select Case sensitive to apply multi-level collation. With multi-level collation, entries are first compared in their primitive forms with their cases and diacritics ignored. If they evaluate as the same, their diacritics are taken into account for the second-level comparison. If they still evaluate as the same, their cases, character widths, and Japanese Kana difference are considered for the third-level comparison.
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.
If your data table has an AutoFilter applied, then you can quickly sort using the Sort Ascending , Sort Descending , and the Sort by Color menu options on the AutoFilter drop-down (Figure 29). Using the Sort by Color menu, you can sort data by the background or font colors used in the cells of a column of interest. The color selected in this menu appears at the top of the sorted list. The options provided in the Sort by Color menu are similar to those in the Filter by Color menu of the AutoFilter drop-down (see Figure 24) and it is not possible to sort by a combination of different colors.
Figure 29: AutoFilter sort by color options
Calc has two ways to find text within a document: the Find toolbar and the Find and Replace dialog. The toolbar is quick and easy to use, but its functions are more limited than using the dialog.
Figure 30: 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 30). 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 the search 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.
To open the Find and Replace dialog (Figure 31), go to Edit > Find and Replace on the Menu bar, or click the Find and Replace icon on the Standard toolbar or the Find toolbar, or use the keyboard shortcut Ctrl+H .
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 as needed.
6) Or, click Find All to locate all cells containing the search criteria. These cells will be highlighted in the spreadsheet. A Search Results dialog will pop up, listing the cell locations.
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) 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 the Replace 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.
Figure 31: Find and Replace dialog
To replace the first instance of the search criteria you can simply press Replace without pressing Find Next first.
Use 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.
The options available to refine your find and replace are as follows:
Other options may appear depending on your language settings on the Tools > Options > Languages and Locales > General page (for example, Match character width , Sounds like (Japanese) , Kashida-sensitive ). See the Help system for more information about these options.
The Wildcards , Regular expressions , and Similarity search options are mutually exclusive; only one can be selected.
After you select Find All on the Find toolbar, or Find All or Replace All on the Find and Replace dialog, Calc’s initial default behavior is to display the Search Results dialog (Figure 32) after it has completed the operation. This dialog summarizes the results. In addition, affected cells are highlighted 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.
Figure 32: Search Results dialog
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 for locating 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 in the 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 of using 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 the International Components for Unicode (ICU), see https://unicode-org.github.io/icu/userguide/strings/regexp.html for further details, including a full definition of the syntax for ICU Regular Expressions.
Regular expressions appear in three areas of Calc’s functionality, as follows:
To illustrate the use of regular expressions we can use a sales data spreadsheet, the first few rows of which are shown in Figure 33.
Figure 33: 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 the entire 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 Replace dialog (Figure 34).
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.
Figure 34: Using a regular expression on the Find and Replace dialog
Suppose we want to filter the same spreadsheet (Figure 33) to show only data relating to the Tennis 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 35).
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.
Figure 35: Using regular expressions on the Standard Filter dialog
The data shown in Figure 36 can be used to show a simple example of a function call that incorporates a regular expression. Suppose we wanted to calculate the revenue from the sale of pencils, 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 the name of any product that starts with the characters “pen”. Alternatively we could calculate the revenue 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 the characters “book”.
Figure 36: 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 the formula =REGEX("123456ABCDEF";"[126]";"";"g") returns "345ABCDEF", where any occurrence of "1", "2" or "6" is replaced by the empty string and is thus deleted.
The online help describes many more regular expressions and their uses.
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 be exported to Excel format without loss of data. See Chapter 8, Using Formulas and Functions, 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.
