Calc Guide

Chapter 1
Introduction
Using spreadsheets in LibreOfficeCopyrightThis document is Copyright © 2021 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 (http://www.gnu.org/licenses/gpl.html), version 3 or later, or the Creative
Commons Attribution License (http://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
Felipe Viggiano

Kees Kriek

Jean Hollis Weber

Gabriel Godoy
Christian Chenal
Pierre-Yves Samyn
Cathy Crumbley
Leo MoonsJean Hollis Weber
Laurent Balland-Poirier
Shelagh Manton
Kees Kriek
Gordon Bates

To previous editions
Barbara Duprey
John A Smith
Philippe Clément
Peter Schofield
Steve Fanning
Felipe Viggiano

Feedback
Please direct any comments or suggestions about this document to the Documentation Team’s
mailing list: documentation@global.libreoffice.org.

Note
Everything you send to a mailing list, including your email address and any otherpersonal information that is written in the message, is publicly archived and cannot
be deleted.

Publication date and software version
Published May 2021. Based on LibreOffice 7.1 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 book. For a
more detailed list, see the application 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
depending on computer setup

Open a context menu

Ctrl (Control)⌘ (Command)

Used with other keys

Ctrl+Q⌘+Q

Exit / quit LibreOffice

F11

⌘+T

Open the Sidebar’s Styles deck

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

What is Calc?.....................................................................................................................5
Spreadsheets, sheets, and cells......................................................................................5
Calc main window.............................................................................................................5
Title bar..................................................................................................................................... 6
Menu bar................................................................................................................................... 6
Toolbars.................................................................................................................................... 7
Formula Bar......................................................................................................................... 7
Status Bar............................................................................................................................ 8
Sidebar..................................................................................................................................... 9
Spreadsheet layout................................................................................................................. 11
Individual cells.................................................................................................................... 11
Sheet tabs.......................................................................................................................... 11Creating, opening, and saving spreadsheets...............................................................12
Creating and opening.............................................................................................................. 12
Using templates...................................................................................................................... 12
Opening a CSV file.................................................................................................................. 12
Saving spreadsheets............................................................................................................... 14
Saving in other formats........................................................................................................... 14

Password protection.......................................................................................................16
Navigating within spreadsheets....................................................................................16
Cell navigation........................................................................................................................ 16
Sheet navigation..................................................................................................................... 17
Keyboard navigation............................................................................................................... 18
Customizing the Enter key..................................................................................................20

Selecting items in a spreadsheet...................................................................................20
Selecting cells......................................................................................................................... 20
Single cell........................................................................................................................... 20
Range of contiguous cells..................................................................................................20
Range of non-contiguous cells...........................................................................................21
Selecting columns and rows....................................................................................................21
Single column or row.......................................................................................................... 21
Multiple columns or rows....................................................................................................21
Entire sheet........................................................................................................................ 22
Selecting sheets...................................................................................................................... 22
Single sheet....................................................................................................................... 22
Multiple contiguous sheets.................................................................................................22
Multiple non-contiguous sheets..........................................................................................22
All sheets............................................................................................................................ 22

Working with columns and rows...................................................................................23
Chapter 1 Introduction | 3

Inserting columns and rows.....................................................................................................23
Single column or row.......................................................................................................... 23
Multiple columns or rows....................................................................................................23
Deleting columns and rows.....................................................................................................23
Single column or row.......................................................................................................... 23
Multiple columns or rows....................................................................................................24
Deleting cells........................................................................................................................... 24

Working with sheets........................................................................................................24
Inserting new sheets...............................................................................................................24
Moving and copying sheets................................................................................................25
Dragging and dropping.......................................................................................................25
Using Move/Copy Sheet dialog..........................................................................................26
Deleting sheets....................................................................................................................... 26
Hiding and showing sheets.....................................................................................................27
Renaming sheets.................................................................................................................... 27

Viewing a spreadsheet....................................................................................................27
Changing document view........................................................................................................27
Freezing rows and columns....................................................................................................27
Unfreezing............................................................................................................................... 28
Splitting the screen.................................................................................................................. 28
Splitting horizontally or vertically.........................................................................................28
Splitting both horizontally and vertically..............................................................................29
Removing split views...............................................................................................................29

Using the Navigator.........................................................................................................29
Using document properties............................................................................................31
General................................................................................................................................... 32
Description.............................................................................................................................. 32
Custom Properties..................................................................................................................32
CMIS Properties...................................................................................................................... 33
Security................................................................................................................................... 33
Font......................................................................................................................................... 33
Statistics.................................................................................................................................. 33

Regular expressions.......................................................................................................33

4| Chapter 1 Introduction

What is Calc?
Calc is the spreadsheet component of LibreOffice. You can enter data (usually numerical) in a
spreadsheet and then manipulate this data to produce certain results.
Alternatively, you can enter data and then use Calc in a ‘What if...’ manner by changing some ofthe data and observing the results without having to retype the entire spreadsheet or sheet.
Other features provided by Calc include:•

Functions, which can be used to create formulas to perform complex calculations on
data.

•

Database functions to arrange, store, and filter data.

•

Data statistics tools, to perform complex data analysis.

•

Dynamic charts, including a wide range of 2D and 3D charts.

•

Macros for recording and executing repetitive tasks; scripting languages supported
include LibreOffice Basic, Python, BeanShell, and JavaScript.

•

Ability to open, edit, and save Microsoft Excel spreadsheets.

•

Import and export of spreadsheets in multiple formats, including HTML, CSV, PDF, andData Interchange Format.

Note
If you want to use macros written in Microsoft Excel using the VBA macro code in
LibreOffice, you must first edit the code in the LibreOffice Basic IDE editor. For more
information, see Chapter 12, Macros, in this guide or Chapter 13, Getting Started
with Macros, in the Getting Started Guide.

Spreadsheets, sheets, and cells
Calc works with documents called spreadsheets. Spreadsheets consist of a number of individual
sheets, each sheet containing cells arranged in rows and columns. A particular cell is identified
by its row number and column letter.
Cells hold the individual elements – text, numbers, formulas, and so on – that make up the data
to display and manipulate.
Each spreadsheet can have up to 10,000 sheets and each sheet can have a maximum of
1,048,576 rows and 1,024 columns.

Calc main window
When Calc is started, the main window opens (Figure 1). The various parts of this display are
explained below.

Note
If any part of the Calc window in Figure 1 is not shown, you can display it using theView menu. For example, View > Status Bar on the Menu bar will toggle (show orhide) the Status Bar. It is not always necessary to display all of the parts shown; youcan show or hide any of them as desired.

Calc main window | 5

Figure 1: Calc main window

Title bar
The Title bar, located at the top, shows the name of the current spreadsheet. When the
spreadsheet is newly created, its name is Untitled X, where X is a number. When you save a
spreadsheet for the first time, you are prompted to enter a name of your choice.

Menu bar
Under the Title bar is the Menu bar. When you choose one of the menus, a list of options
appears. You can also modify the Menu bar, as explained in Chapter 14, Setting up andCustomizing.
•

File – contains commands that apply to the entire document, such as Open, Save,
Wizards, Export as PDF, Print, Digital Signatures, Templates.

•

Edit – contains commands for editing the document, such as Undo, Copy, Find andReplace, Track Changes.

•

View – contains commands for modifying how the Calc user interface looks, such as
Toolbars, View Headers, Full Screen, Zoom.

•

Insert – contains commands for inserting elements into a spreadsheet, such as Image,
Chart, Text Box, Headers and Footers.

•

Format – contains commands for modifying the layout of a spreadsheet, such as Cells,
Page, AutoFormat Styles, Align.

•

Styles – contains options for applying and managing styles, such as Heading 1,
Footnote, Manage Styles.

•

Sheet – contains commands for inserting and deleting elements and modifying the entire
sheet, such as Delete Rows, Insert Sheet, Rename Sheet, Navigate.

•

Data – contains commands for manipulating data in your spreadsheet, such as Define
Range, Sort, AutoFilter, Consolidate, Statistics.

6 | Chapter 1 Introduction•Tools – contains functions to help check and customize a spreadsheet, for example
Spelling, Share Spreadsheet, Macros, Options.

•Window – contains two commands; New Window and Close Window. Also shows all
open windows in other LibreOffice applications.

•

Help – contains links to LibreOffice Help (included with the software), User Guides, andother miscellaneous functions; for example Restart in Safe Mode, License
Information, Check for Updates, About LibreOffice.

Toolbars
The default setting when Calc opens is for the Standard and Formatting toolbars to be docked at
the top of the workspace (Figure 1).
Calc toolbars can be either docked (fixed in place), or floating, allowing you to move a toolbar toa more convenient location on your workspace. Docked toolbars can be undocked and moved toa different docked location or become floating toolbars. Likewise, floating toolbars can bedocked.
You can choose the single-toolbar alternative to the default double toolbar arrangement. It
contains the most-used commands. To activate it, enable View > User Interface > Single
Toolbar. Other variations are also available through View > User Interface.
For additional information on toolbars, see Chapter 1, Introducing LibreOffice, of the Getting
Started Guide.
The default set of icons (sometimes called buttons) on toolbars provides a wide range ofcommon commands and functions. You can also remove or add icons to toolbars. See Chapter
14, Setting up and Customizing, for more information.
Placing the mouse cursor over an icon, text box, or menu command displays a small box called atooltip that shows the name of the item’s function. To close a tooltip, move away from theunderlying component or press the Esc key.
To see a more detailed explanation of an icon, text box, or menu command, do one of thefollowing to open extended tips:•

To activate extended tips just once: press Shift+F1.

•

To activate extended tips from the Menu bar: go to Help > What’s This? and hover themouse pointer over an icon.

•

To turn extended tips on or off: go to Tools > Options > LibreOffice > General on the
Menu bar and toggle the Extended tips checkbox.

Formula Bar
The Formula Bar is located at the top of the Calc workspace. It is permanently docked in thisposition and cannot be used as a floating toolbar. However, it can be hidden or made visible bygoing to View > Formula Bar on the Menu bar.

Figure 2: Formula Bar
From left to right in Figure 2, the Formula Bar consists of the following:•

Name Box – gives the current active cell reference using a combination of a letter andnumber, for example A1. The letter indicates the column and the number indicates therow of the selected cell. If you have selected a range of cells that is also a named range,
the name of the range is shown in this box. You can also type a cell reference in theName Box to jump to the referenced cell. If you type the name of a named range andpress the Enter key, the named range is selected and displayed.
Calc main window | 7

•Function Wizard – opens a dialog from which you can search through lists of available
functions and formulas. This can be very useful because it also shows how the functions
are formatted.

•

Select Function – performs a calculation on the numbers in the cells above the selected
cell and then places the result in it. If there are no numbers above the selected cell, thenthe calculation operates on the cells to the left. The calculation to be performed is
selected from a drop-down menu containing options for Sum, Average, Min, Max, andCount. The Alt+= keyboard shortcut is equivalent to clicking the Select Function icon
and selecting the Sum option.

•

Formula – inserts an equals (=) sign in the selected cell and the Input line, allowing aformula to be entered.

•

Input line – displays the contents of the selected cell (data, formula, or function) andallows you to edit the cell contents. To turn the Input line into a multiline input area forvery long formulas, click the Expand Formula Bar icon on the right. To edit inside theInput line area, click in the area, then type your changes.

You can also directly edit inside the cell by double-clicking on the cell. When you enter new data
into a cell, the Select Function and Formula icons change to Cancel and Accept icons.

Note
In a spreadsheet, the term “function” covers much more than just mathematical
functions. See Chapter 7, Using Formulas and Functions, for more information.

Status Bar
The Status Bar at the bottom of the workspace (Figure 3) provides information about the
spreadsheet and convenient ways to quickly change some of its features. Most of the fields are
similar to those in other components of LibreOffice. See Chapter 1, Introducing LibreOffice, in theGetting Started Guide for more information.

Figure 3: Status Bar
The fields on the Status Bar, from left to right, are as follows.
Sheet number
Shows the sequence number of the current sheet and the total number of sheets in the
spreadsheet. The sequence number may not be the same as the name on the sheet tab if
sheets have been moved. Double-click on this zone to open the Navigator dialog.
Cell selection information
Shows how many rows and how many columns are selected. Blank if the selected cells do
not form a rectangular block.
Page styleShows the page style of the current sheet. To edit the page style, double-click on this field
and the Page Style dialog opens.
LanguageShows current default language as selected at Tools > Options > Language Settings >Languages.
8 | Chapter 1 Introduction

Insert / overwrite mode
When you are interacting with a cell in edit mode, this field distinguishes between the insert
and overwrite modes. If the cell being edited is in overwrite mode, the field shows the text
Overwrite. In other cases, the field is blank. You can press the Insert key on the keyboard,or click on this field, to switch between insert and overwrite modes.
Selection mode
Right-click to open a context menu and select Standard selection, Extending selection,
or Adding selection. You can also double-click the icon to switch to the next selection
mode in sequence. The icon does not change when different selection modes are selected.
See Chapter 1, Introducing LibreOffice, in the Getting Started Guide for more information.
Unsaved changes
The appearance of this icon indicates whether the spreadsheet contains unsaved changes.
When there are unsaved changes, click this icon, use File > Save, click the Save icon on
the Standard toolbar, or press Ctrl+S to save the spreadsheet.
Digital signature
If the document has been digitally signed, an icon shows here. You can click the icon toview the certificate. See Chapter 6, Printing, Exporting, E-mailing, and Signing for more
information about digital signatures.
Cell or object information
Displays information related to the position of the cursor or the selected element of the
spreadsheet. When a group of cells is selected, the sum of the contents is displayed bydefault. Right-click in this field to select other functions, such as the average value,
maximum value, minimum value, or count (number of items selected).
Zoom slider
Drag the zoom slider, click on the slider’s rail, or click the + and – symbols to change theview magnification.
Zoom percentage
Indicates the magnification level of the document. Right-click on the percentage to open alist of magnification values from which to choose. Click on the percentage to open theZoom & View Layout dialog.

Sidebar
The Sidebar (Figure 4) is a mixture of toolbar and dialog. When opened (View > Sidebar or
Ctrl+F5), it appears on the right side of the window and consists of five decks: Properties, Styles,
Gallery, Navigator, and Functions. Each deck has a corresponding icon on the Tab panel to theright of the Sidebar, allowing you to switch between them. The decks are described below.

Calc main window | 9

Figure 4: Sidebar
Properties
The Properties deck includes five content panels. Each panel has a More Options button
that open a dialog with additional options. These dialogs lock the document for editing until
they are closed.
Style: Options for applying, updating, or creating cell and page styles.
Character: Options for formatting the text, such as font name, size, and color. Some
controls, such as superscript, only become active when the text cursor is active in the Input
line of the Formula Bar or the cell.
Number Format: Options for formatting numbers, including decimals, currency, dates, or
numeric text.
Alignment: Options for arranging the text in various ways, including horizontal and verticalalignment, wrapping, indenting, merging, text orientation, and vertical stacking.
Cell Appearance: Options include background color and border formats, including line
color and style.
StylesThe Styles deck enables you to apply existing styles, modify existing styles, or create newstyles. This deck can also be opened by selecting Styles > Manage Styles on the Menubar, selecting View > Styles on the Menu bar, or pressing F11.

10 | Chapter 1 Introduction

Gallery
The Gallery deck enables you to add images, diagrams, or other objects that are available
in the Gallery themes. This deck can also be opened by selecting View > Gallery on the
Menu bar.
Navigator
The Navigator deck can also be opened by selecting View > Navigator from the Menu bar
or pressing F5. See “Using the Navigator” on page 28.
Functions
The Functions deck contains a list of Calc’s many functions organized by category. It is a
simpler version of the Function Wizard, which is opened by selecting Insert > Function on
the Menu bar, clicking the Function Wizard icon on the Formula Bar, or pressing Ctrl+F2.
To the right side of the title bar of each open deck is a Close Sidebar Deck button, which closes
the deck to leave only the Tab bar of the Sidebar open. Click any button in the Tab bar to toggle
on / off the display of the associated deck.
To hide the Sidebar, or reveal it if already hidden, click on the left edge Hide/Show button. To
adjust the deck width, drag on the left edge of the Sidebar.

Spreadsheet layout
Individual cells
The main section of the screen displays the cells in the form of a grid, with each cell located at
the intersection of a column and a row.
At the tops of the columns and the left ends of the rows are a series of header boxes containing
letters and numbers. The column headers use alpha characters that start with A and increase to
the right. The row headers use numerical characters that start at 1 and increase down.
These column and row headers form the cell references that appear in the Name Box on the
Formula Bar (Figure 2). If the headers are not visible on the spreadsheet, go to View > Headerson the Menu bar.

Sheet tabs
A spreadsheet file can contain many individual sheets. At the bottom of the grid of cells in a
spreadsheet are sheet tabs (Figure 1). Each tab represents a sheet in a spreadsheet. You cancreate a new sheet by clicking on the plus sign to the left of the sheet tabs or by clicking in theblank space to the right of the sheet tabs.
Clicking on a tab makes an individual sheet active. When a sheet is active, the tab is highlighted.
To select multiple sheets, hold down the Ctrl key while clicking on the sheet tabs.
To change the default name for a sheet (Sheet1, Sheet2, and so on):1) Right-click on the sheet tab and select Rename Sheet in the context menu. In the dialog
that opens, type in a new name for the sheet.
2) Click OK when finished to close the dialog.
To change the color of a sheet tab:
1) Right-click on the sheet tab and select Tab Color in the context menu to open the Tab
Color dialog (Figure 5).
2) Select a color and click OK when finished to apply the color and close the dialog.

Calc main window | 11

Figure 5: Tab Color dialog
To add new colors to this color palette, see “Adding custom colors” in Chapter 14, Setting up andCustomizing.

Creating, opening, and saving spreadsheets
Creating and opening
Creating and opening spreadsheets is identical to creating and opening documents in the other
LibreOffice modules. For more information on creating and opening spreadsheets, see Chapter
1, Introducing LibreOffice, in the Getting Started Guide.

Using templates
Calc documents can also be created from templates. For information on how to create and use
templates, see Chapter 4, Using Styles and Templates, in this guide.

Opening a CSV file
Comma-separated values (CSV) files are spreadsheet files in a text format where cell contents
are separated by a character such as a comma or semi-colon. Each line in a CSV text file
represents a row in a spreadsheet. Text is entered between quotation marks; numbers areentered without quotation marks.
To open a CSV file in Calc:
1) Choose File > Open on the Menu bar, click the Open icon on the Standard toolbar, orpress Ctrl+O and locate the CSV file that you want to open.
2) Select the file and click Open. By default, a CSV file has the extension .csv. However,some CSV files may have a .txt extension.
3) The Text Import dialog (Figure 6) opens. Here you can select options for importing a CSV
file into a Calc spreadsheet.
12 | Chapter 1 Introduction4) Click OK to open and import the file.

Figure 6: Text Import dialog
The options for importing CSV files into a Calc spreadsheet are as follows:
Import• Character set – specifies the character set to be used in the imported file.
•

Language – determines how the number strings are imported. If Language is set to
Default, Calc will use the language associated with the locale selected at Tools >Options > Language Settings > Languages > Formats. If another language isselected, that language will determine how numbers are treated.

•

From row – specifies which row the import starts with. The initial rows are visible in thepreview window at the bottom of the dialog.

Separator Options• Fixed width – separates data into columns by a set number of characters. Click on theruler that appears in the preview window to set the width.
•

Separated by – separates data into columns based on the separator defined here. SelectOther to specify another character used to separate data into columns. This custom
separator must also be contained in the data.

•

Merge delimiters – combines consecutive delimiters and removes blank data fields.

•

Trim spaces – removes starting and trailing spaces from within fields.

•

String delimiter – select a character to delimit text data.

Creating, opening, and saving spreadsheets | 13

Other options• Format quoted field as text – when this option is enabled, fields or cells whose values are
entirely within quotes are imported as text.
•

Detect special numbers – when this option is enabled, Calc will automatically detect all
number formats, including special number formats such as dates, time, and scientificnotation. The selected language influences how such special numbers are detected,
since different languages and regions many have different conventions for such special
numbers.
When this option is disabled, Calc will detect and convert only decimal numbers. The
rest, including numbers formatted in scientific notation, will be imported as text. A decimal
number string can have digits 0–9, thousands separators, and a decimal separator.
Thousands separators and decimal separators may vary with the selected language andregion.

Fields – shows how your data will look when it is separated into columns.
Column type – select a column in the preview window and select the data type to be
applied to the imported data.
– Standard – Calc determines the type of data.
– Text – imported data are treated as text.
– Date – imported data are treated as dates in the selected format – “DMY”, “MDY”, or“YMD”.
– US English – numbers formatted in US English are searched for and included
regardless of the system language. A number format is not applied. If there are no USEnglish entries, the Standard format is applied.
– Hide – the data in the column are not imported.

Saving spreadsheets
For information on how to save files manually or automatically, see Chapter 1, Introducing
LibreOffice, in the Getting Started Guide. Calc can save spreadsheets in a range of formats andalso export spreadsheets to PDF and XHTML file formats; see Chapter 6, Printing, Exporting,
Emailing, and Signing, for more information.

Saving in other formats
If you need to send files to users who are unable to receive spreadsheet files in Open Document
Format (ODF) (*.ods), which Calc uses as its default format, you can save a spreadsheet inanother format.
1) Select File > Save As on the Menu bar, click the down arrow at the right of the Save iconon the Standard toolbar and select Save As from the drop-down menu, or press
Ctrl+Shift+S to open the Save As dialog (Figure 7).
2) In File name, if you wish, enter a new file name for the spreadsheet and select the folder
where you want to save the file.
3) In the Save as type field, select from the drop-down menu the type of spreadsheet format
you want to use. If Automatic file name extension is selected, the correct file extension
for the spreadsheet format you have selected will be added to the file name.
4) Click Save.

14 | Chapter 1 Introduction

Figure 7: The Save As dialog
Each time a file is saved in a format other than ODF format, the Confirm File Format dialog
opens (Figure 8). Click Use [xxx] Format to continue saving in the selected spreadsheet format
or click Use ODF Format to save the spreadsheet in Calc’s default format. If you disable Warnwhen not saving in ODF or default format on Tools > Options > Load/Save > General on the
Menu bar, the Confirm File Format dialog will no longer appear. You can also clear the checkbox
Ask when not saving in ODF or default format on the dialog to stop the dialog appearing.

Figure 8: Confirm File Format dialog
If you select Text CSV format (*.csv), the Export Text File dialog (Figure 9) opens. Here you canselect the character set, field delimiter, string delimiter, and so on to be used for the CSV file.

Note
Once you have saved a spreadsheet in another format, all changes you make to the
spreadsheet will now occur only in the format you are using because you havechanged the name and file type of your document. If you want to go back to working
with an *.ods version, you must save the file as an *.ods file.

Creating, opening, and saving spreadsheets | 15

Figure 9: Export Text File dialog

Tip
To have Calc save documents by default in a file format other than the default ODF
format, go to Tools > Options > Load/Save > General. In the section named
Default File Format and ODF Settings, next to Document type, select Spreadsheet,then next to Always save as, select your preferred file format, for example one of theavailable Microsoft Excel options.

Password protection
To protect a spreadsheet and restrict who can open, read, and make changes to it, you have to
use password protection. Password protection is common to all LibreOffice modules; for more
information, see Chapter 1, Introducing LibreOffice, in the Getting Started Guide.

Navigating within spreadsheets
Calc provides many ways to navigate within a spreadsheet from cell to cell and sheet to sheet.
You can generally use the method you prefer.

Cell navigation
When a cell is selected or in focus, the cell borders are emphasized. When a group of cells is
selected, the cell area is colored. The color of the cell border emphasis and the color of a groupof selected cells depends on the operating system being used and how you have set up
LibreOffice.
•Using the mouse – place the mouse pointer over the cell and click the left mouse button.
To move the focus to another cell using the mouse, move the mouse pointer to the celland click the left mouse button.

•

Using a cell reference – select or delete the existing cell reference in the Name Box on
the Formula Bar (Figure 2 on page 7). Type the reference of the cell you want to move toand press the Enter key. Cell references are case insensitive. Thus, typing a3 or A3 will
move the focus to cell A3.

•

Using the Navigator – to open the Navigator (Figure 19), go to View > Navigator on the
Menu bar, or press F5, or click the Navigator button on the Sidebar. Type the cellreferences into the Column and Row fields and press the Enter key.

16 | Chapter 1 Introduction•Using the Enter key – pressing Enter moves the cell focus down one cell (by default). You
can change the direction of this focus movement as described in the “Customizing theEnter key” section on page 19.

•

Pressing Shift+Enter moves the focus one cell in the opposite direction to that associatedwith the Enter key.

•

Using the Tab key – pressing Tab moves the cell focus one cell to the right. Pressing
Shift+Tab moves the focus one cell to the left.

•

Using the arrow keys – pressing the arrow keys on the keyboard moves the cell focus in
the direction of the arrow pressed.

•

Using Home, End, Page Up, and Page Down
– Home moves the cell focus to the start of a row. Ctrl+Home moves the cell focus to thefirst cell in the sheet, A1.
– The result of pressing End or Ctrl+End depends on the data contained in the sheet. To
explain these key presses, it is helpful to define Rmax as the highest numbered row inthe sheet that contains any data and Cmax as the rightmost column in the sheet that
contains any data. Press End to move the cell focus along the current row to the cellin column Cmax. Press Ctrl+End to move the cell focus to the cell at the intersection of
row Rmax and column Cmax. Note that in either case, the newly focused cell may notcontain any data.
– Page Down moves the cell focus down one complete screen display.
– Page Up moves the cell focus up one complete screen display.

Sheet navigation
Each sheet in a spreadsheet is independent of the other sheets, though references can be linked
from one sheet to another. There are three ways to navigate between different sheets in a
spreadsheet.
•

Using the Navigator – when the Navigator is open (Figure 19), double-clicking on any ofthe listed sheets selects the sheet.

•

Using the keyboard – using key combinations Ctrl+Page Down moves one sheet to theright and Ctrl+Page Up moves one sheet to the left.

•

Using the mouse – clicking on one of the sheet tabs at the bottom of the spreadsheet
selects that sheet.

If there are many sheets in the spreadsheet, some of the sheet tabs may be hidden. If this is thecase, use the four buttons to the left of the sheet tabs to move the tabs into view (Figure 10).

Note
The sheet tab arrows that appear on the left in Figure 10 are only active if there are
more sheet tabs than can be displayed.

Navigating within spreadsheets | 17

Figure 10: Navigating sheet tabs

Note
When you insert a new sheet into a spreadsheet, Calc automatically uses the next
number in the numeric sequence as a name. Depending on which sheet is open
when you insert a new sheet, your new sheet may not be in numerical order. It is
recommended to rename sheets in a spreadsheet to make them more recognizable.

Keyboard navigation
You can navigate a spreadsheet using the keyboard, by pressing a key or a combination of keys
at the same time. For example, Ctrl+Home moves the focus to cell A1. Table 1 lists the keys andkey combinations you can use for spreadsheet navigation in Calc.
Table 1. Keyboard cell navigation
Keyboard
Cell navigation
shortcut→/←Moves cell focus right/left one cell.

↑/↓

Moves cell focus up/down one cell.

Ctrl+→ /Ctrl+←

If focus is on a blank cell, Ctrl+→ moves focus along the current row to thefirst cell on the right that contains data. If there is no cell on the rightcontaining data, it moves focus along the current row to the last cell at theright of the sheet.
If focus is on a blank cell, Ctrl+← moves focus along the current row to thefirst cell on the left that contains data. If there is no cell on the left
containing data, it moves focus along the current row to the cell in column Aof the sheet.
If focus is on a cell containing data, Ctrl+→ normally moves focus along thecurrent row to the cell at the right edge of the same data region. However, if
there is a blank cell to the right of the original cell, focus is moved to the cellat the left edge of the next data region to the right. In this case, if there is nodata region to the right, focus is moved along the current row to the last cellat the right of the sheet.
If focus is on a cell containing data, Ctrl+← normally moves focus along thecurrent row to the cell at the left edge of the same data region. However, if
there is a blank cell to the left of the original cell, focus is moved to the cellat the right edge of the next data region to the left. In this case, if there is nodata region to the left, focus is moved along the current row to the cell incolumn A of the sheet.

18 | Chapter 1 Introduction

Keyboard
shortcut

Cell navigation

Ctrl+↑ /Ctrl+↓

If focus is on a blank cell, Ctrl+↑ moves focus up the current column to thefirst cell that contains data. If there is no cell above containing data, it
moves focus up the current column to the cell in row 1 of the sheet.
If focus is on a blank cell, Ctrl+↓ moves focus down the current column tothe first cell that contains data. If there is no cell below containing data, it
moves focus down the current column to the last cell at the bottom of the
sheet.
If focus is on a cell containing data, Ctrl+↑ normally moves focus up thecurrent column to the cell at the top edge of the same data region.
However, if there is a blank cell above the original cell, focus is moved tothe cell at the bottom edge of the next data region above. In this case, if
there is no data region above, focus is moved up the current column to thecell in row 1 of the sheet.
If focus is on a cell containing data, Ctrl+↓ normally moves focus down thecurrent column to the cell at the bottom edge of the same data region.
However, if there is a blank cell below the original cell, focus is moved tothe cell at the top edge of the next data region below. In this case, if there isno data region below, focus is moved down the current column to thebottom of the sheet.

Ctrl+Home /Ctrl+EndA detailed description of these shortcuts is given on Page 17.

Alt+Page Down /Alt+Page Up

Moves focus one screen to the right/left (if possible).

Ctrl+Page Down /Ctrl+Page Up

Moves focus to the next sheet to the right/left in sheet tabs if there are more
sheets in that direction.

Tab /Shift+Tab

Moves focus to the next cell on the right/left.

Enter /
Shift+Enter

Moves focus down/up one cell (unless you have changed this action, as
described in the following subsection).

Customizing the Enter key
You can choose the direction in which the Enter key moves the cell focus by going to Tools >Options > LibreOffice Calc > General. Use the first three options under Input Settings (Figure11) to change the Enter key settings. Select the direction cell focus moves from the drop-downlist. Depending on the file being used or the type of data being entered, setting a different
direction can be useful. The Enter key can also be used to switch into and out of editing mode. In
Calc, when the content of a cell is copied to the clipboard, you can paste the information inanother cell by pressing the Enter key; here you can disable this feature.

Navigating within spreadsheets | 19

Figure 11: Customizing the Enter key

Selecting items in a spreadsheet
Selecting cells
Single cell
Click in the cell. You can verify the selection by looking in the Name Box on the Formula Bar(Figure 2).

Range of contiguous cells
A range of cells can be selected using the keyboard or the mouse.
To select a range of cells by dragging the mouse cursor:
1) Click in a cell.
2) Press and hold down the left mouse button.
3) Move the mouse to highlight the desired block of cells, then release the left mousebutton.
To select a range of cells without dragging the mouse:
1) Click in the cell which is to be one corner of the range of cells.
2) Move the mouse to the opposite corner of the range of cells.
3) Hold down the Shift key and click.
To select a range of cells using Extending selection mode:
1) Click in the cell which is to be one corner of the range of cells.
2) Right-click in the Selection mode field on the Status Bar (Figure 3 on page 8) and selectExtending selection.
3) Click in the cell in the opposite corner of the range of cells.

20 | Chapter 1 Introduction

Tip
Make sure to change back to Standard selection mode or you may find yourself
extending a cell selection unintentionally.
To select a range of cells without using the mouse:
1) Select the cell that will be one of the corners in the range of cells.
2) While holding down the Shift key, use the cursor arrows to select the rest of the range.
To select a range of cells using the Name Box:
1) Click in the Name Box on the Formula Bar (Figure 2 on page 7).
2) Enter the cell reference for the upper left-hand cell, followed by a colon (:), and then thelower right-hand cell reference, then press the Enter key. For example, to select the rangethat would go from A3 to C6, enter A3:C6.

Range of non-contiguous cells
To select a range of non-contiguous cells using the mouse:
1) Select the first cell or range of cells using one of the methods above.
2) Move the mouse pointer to the start of the next range or single cell.
3) Hold down the Ctrl key and click or click-and-drag to select another range of cells to add
to the first range.
4) Repeat as necessary.
To select a range of cells using Adding selection mode:
1) Right-click in the Selection mode field on the Status Bar (Figure 3 on page 8) and selectAdding selection.
2) Click or click-and-drag to select ranges of cells to add to the selection.

Selecting columns and rows
Single column or row
To select a single column, click on the column header (Figure 1 on page 6). To select a single
row, click on the row header.

Multiple columns or rows
To select multiple columns or rows that are contiguous:
1) Click on the first column or row in the group.
2) Hold down the Shift key.
3) Click the last column or row in the group.
To select multiple columns or rows that are not contiguous:
1) Click on the first column or row in the group.
2) Hold down the Ctrl key.
3) Click on all of the subsequent columns or rows while holding down the Ctrl key.

Entire sheet
To select the entire sheet, click on the small box between the column headers and the row
headers (Figure 12), use the key combination Ctrl+A, press Ctrl+Shift+Space, or go to Edit on the
Menu bar and select Select All.

Selecting items in a spreadsheet | 21

Figure 12: Select All box

Selecting sheets
You can select either one or multiple sheets in Calc. It can be advantageous to select multiple
sheets, especially when you want to make changes to many sheets at once.

Single sheet
Click on the sheet tab for the sheet you want to select. The tab for the selected sheet becomes
highlighted.

Multiple contiguous sheets
To select multiple contiguous sheets:
1) Click on the sheet tab for the first desired sheet.
2) While holding down the Shift key, click on the sheet tab for the last desired sheet.
3) All tabs between these two selections will be highlighted. Any actions that you perform will
now affect all highlighted sheets.

Multiple non-contiguous sheets
To select multiple non-contiguous sheets:
1) Click on the sheet tab for the first desired sheet.
2) While holding down the Ctrl key, click on the sheet tabs for other desired sheets.
3) The selected tabs will be highlighted. Any actions that you perform will now affect all
highlighted sheets.

All sheets
Right-click a sheet tab and choose Select All Sheets in the context menu, or select Edit >Select > Select All Sheets on the Menu bar.

Tip
You can also select sheets using the Select Sheets dialog, accessed by selecting
Edit > Select > Select Sheets on the Menu bar.

Working with columns and rows
Inserting columns and rows
When you insert columns or rows, the cells take the formatting of the corresponding cells in thecolumn to the left or the row above.

Single column or row
Using the Sheet menu:
1) Select a cell, column, or row where you want the new column or row inserted.

22 | Chapter 1 Introduction2) Go to Sheet on the Menu bar. For columns, select Sheet > Insert Columns and thenselect Columns Before or Columns After. For rows, select Sheet > Insert Rows andthen select Rows Above or Rows Below.
Using the context menu:
1) Select a column or row where you want the new column or row inserted.
2) Right-click the column or row header.
3) Select Insert Columns Before / After or Insert Rows Above / Below in the context
menu.

Multiple columns or rows
Multiple columns or rows can be inserted at once rather than inserting them one at a time.
1) Highlight the required number of columns or rows by holding down the left mouse buttonon the first one and then dragging across the required number of identifiers.
2) Proceed as for inserting a single column or row above. The number of columns or rows
highlighted will be inserted.

Deleting columns and rows
Single column or row
To delete a single column or row, do one of the following:•

Select a cell in the column or row you want to delete, right-click and select Delete in thecontext menu, select Sheet > Delete Cells on the Menu bar, or press Ctrl+- to open the
Delete Cells dialog (Figure 13). Select Delete entire column(s) or Delete entire row(s)and click OK.

•

Select a cell in the column or row you want to delete and select Sheet > Delete
Columns or Sheet > Delete Rows.

•

Right-click the header of the column or row that you want to delete and select Delete
Columns or Delete Rows in the context menu.

Figure 13: Delete Cells dialog

Multiple columns or rows
To delete multiple columns or rows, do one of the following:•

Select a range of cells across the columns or rows you want to delete, right-click andselect Delete in the context menu, select Sheet > Delete Cells on the Menu bar, orpress Ctrl+- to open the Delete Cells dialog. Select Delete entire column(s) or Delete
entire row(s) and click OK.

•

Select a range of cells across the columns or rows you want to delete and select Sheet >Delete Columns or Sheet > Delete Rows.

Working with columns and rows | 23

•Highlight the required columns or rows by holding down the left mouse button on theheader of the first one and then dragging across the required number of headers. Thenright-click on one of the selected column or row headers and select Delete Columns orDelete Rows in the context menu.

Deleting cells
1) Select the cell or cells you want to delete.
2) Select Sheet > Delete Cells, press Ctrl+-, or right-click on one of the selected cells andselect Delete in the context menu.
3) Select the option you require from the Delete Cells dialog and click OK.

Working with sheets
Inserting new sheets
Click on the + symbol next to the sheet tabs to insert a new sheet after the last sheet in the
spreadsheet without opening the Insert Sheet dialog. The following methods open the Insert
Sheet dialog (Figure 14) where you can position the new sheet, create more than one sheet,name the new sheet, or select a sheet from a file.
•

Select the sheet where you want to insert a new sheet, then select Sheet > Insert Sheeton the Menu bar.

•

Right-click on the sheet tab where you want to insert a new sheet and select Insert
Sheet in the context menu.

•

Click in the empty space at the end of the sheet tabs.

•

Right-click in the empty space at the end of the sheet tabs and select Insert Sheet fromthe context menu.

Figure 14: Insert Sheet dialog

Moving and copying sheets
You can move or copy sheets within the same spreadsheet by dragging and dropping or using
the Move/Copy Sheet dialog (Figure 15). To move or copy a sheet into a different spreadsheet,
use the Move/Copy Sheet dialog.
24 | Chapter 1 Introduction

Dragging and dropping
To move a sheet to a different position within the same spreadsheet, click on the sheet tab anddrag it to its new position before releasing the mouse button.
To copy a sheet within the same spreadsheet, hold down the Ctrl key then click on the sheet tab
and drag it to its new position before releasing the mouse button. The mouse pointer may change
to include a plus sign depending on the setup of your operating system.

Using Move/Copy Sheet dialog
The Move/Copy Sheet dialog allows you to specify exactly whether you want the sheet in thesame or a different spreadsheet, its position within the spreadsheet, and the sheet name when it
is moved or copied.

Figure 15: Move/Copy Sheet dialog
1) In the current document, right-click on the sheet tab you wish to move or copy and selectMove or Copy Sheet in the context menu, or go to Sheet > Move or Copy Sheet on the
Menu bar.
2) Select Move to move the sheet or Copy to copy the sheet.
3) Select the spreadsheet where you want the sheet to be placed from the To document
drop-down list. This can be the same spreadsheet, another spreadsheet that is alreadyopen, or you can create a new spreadsheet.
4) Select the position in Insert before where you want to place the sheet.
5) Type a name in the New name text box if you want to rename the sheet when it is moved
or copied. When copying, Calc suggests a default name (Sheet1_2, Sheet2_2, and soon).
6) Click OK to confirm the move or copy and close the dialog.

CautionWhen you move or copy to another spreadsheet or to a new one, a conflict may
occur if formulas are linked to sheets in the previous location.

Working with sheets | 25

Deleting sheets
To delete a single sheet, right-click on the sheet tab you want to delete and select Delete Sheetin the context menu, or go to Sheet > Delete Sheet on the Menu bar. Click Yes to confirm thedeletion.
To delete multiple sheets, select the sheets (see “Selecting sheets” on page 22), then right-click
one of the sheet tabs and select Delete Sheet in the context menu, or go to Sheet > Delete
Sheet on the Menu bar. Click Yes to confirm the deletion.

Hiding and showing sheets
Sometimes you may want to hide the contents of a sheet to preserve data from accidental editingor because its contents are not important to display.
To hide a sheet or many sheets, select the sheet or sheets as above, right-click to open thecontext menu, and select Hide Sheet.
To show hidden sheets, right-click any sheet tab and select Show Sheet in the context menu. A
dialog will open with all hidden sheets listed. Select the desired sheets and then click OK.

Note
LibreOffice Calc does not let you hide the last visible sheet.

Renaming sheets
By default, the name for each new sheet added is SheetX, where X is the number of the next
sheet to be added. While this works for a spreadsheet with only a few sheets, it can become
difficult to identify sheets when a spreadsheet contains many sheets.
You can rename a sheet using one of the following methods:•

Enter the name in the Name text box when you create the sheet using the Insert Sheetdialog (Figure 14 on page 24).

•

Right-click on a sheet tab and select Rename Sheet in the context menu to open the
Rename Sheet dialog•

Select Sheet > Rename Sheet on the Menu bar to access the Rename Sheet dialog.

•

Double-click on a sheet tab to open the Rename Sheet dialog.

Note
Sheet names can contain almost any character. Some naming restrictions apply, thefollowing characters are not allowed in sheet names: colon (:), back slash (\), forward
slash (/), question mark (?), asterisk (*), left square bracket ([), or right square
bracket (]). In addition a single quote (‘) cannot be used as the first or last characterof the name.

Viewing a spreadsheet
Changing document view
Use the zoom function to show more or fewer cells in the window when you are working on a
spreadsheet. For more about zoom, see Chapter 1, Introducing LibreOffice, in the Getting
Started Guide.

26 | Chapter 1 Introduction

Freezing rows and columns
Freezing is used to lock rows across the top of a spreadsheet or to lock columns on the left of a
spreadsheet. Then, when moving around within a sheet, the cells in frozen rows and columns
always remain in view.
Figure 16 shows some frozen rows and columns. The heavier horizontal line between rows 3 and23 and the heavier vertical line between columns F and Q indicate that rows 1 to 3 and columns
A to F are frozen. The rows between 3 and 23 and the columns between F and Q have been
scrolled off the page. To freeze rows or columns:
1) Click on the row header below the rows you want the freeze or click on the column
header to the right of the columns where you want the freeze. To freeze both rows andcolumns, select the cell (not a row or column) that is below the row and to the right of thecolumn that you want to freeze
2) Go to View on the Menu bar and select Freeze Rows and Columns. A heavier line
appears between the rows or columns indicating where the freeze has been placed.

Figure 16: Frozen rows and columns

Unfreezing
To unfreeze rows or columns, go to View on the Menu bar and click Freeze Rows and Columns
to toggle it off. The heavier lines indicating freezing will disappear.

Splitting the screen
Another way to change the view is by splitting the screen displayed (also known as splitting thewindow). The screen can be split horizontally, vertically, or both, displaying up to four portions of
the spreadsheet at the same time. An example of splitting the screen is shown in Figure 17
where a split is indicated by a gray line.
This could be useful for example, when a large spreadsheet has one cell with a number that isused by three formulas in other cells. Using the split-screen technique, the cell containing thenumber can be positioned in one section of the view and the cells with formulas can be seen in
the other sections. This makes it easy to see how changing the number in one cell affects eachof the formulas.

Figure 17: Split screen example

Splitting horizontally or vertically
There are two ways to split a screen horizontally or vertically:
Viewing a spreadsheet | 27

Method One:
1) Click on the row header below the rows where you want to split the screen horizontally or
click on the column header to the right of the columns where you want to split the screen
vertically.
2) Go to View on the Menu bar and select Split Window or right-click and choose Split
Window in the context menu. A thick line appears between the rows or columns
indicating where the split has been placed. An example of a split line is shown below Row
2 in Figure 17.
Method Two:
For a horizontal split, click on the thick black line at the top of the vertical scroll bar (Figure 18)
and drag the split line below the row where you want the horizontal split positioned.
Similarly, for a vertical split, click on the thick black line at the right of the horizontal scroll bar(Figure 18) and drag the split line to the right of the column where you want the vertical split
positioned.

Figure 18: Split screen bars

Splitting both horizontally and vertically
Method One:
Position both the black horizontal and the black vertical lines as described above and as shownin Figure 18.
Method Two:
1) Click the cell that is immediately below the rows where you want to split the screen
horizontally and immediately to the right of the columns where you want to split thescreen vertically.
2) Go to View on the Menu bar and select Split Window. Thick lines appear between therows and columns indicating where the splits have been placed.

Removing split views
To remove a split view, do any of the following:•

Double-click on each split line in turn.

•Click on and drag the split lines back to their places at the ends of the scroll bars.

•

Go to View on the Menu bar and click Split Window to toggle it off.

•

Right-click on a column or row heading and click Split Window in the context menu totoggle it off.

Using the Navigator
The Navigator (Figure 19) is available in all LibreOffice modules. It provides tools and methods tomove quickly through a spreadsheet and find specific items.

28 | Chapter 1 Introduction

Figure 19: Navigator dialog in Calc
The Navigator categorizes and groups spreadsheet objects which you can click on to movequickly to that object. If an indicator (plus sign or triangle, dependent on computer setup) appears
next to a category, at least one object in this category exists. To open a category and see the listof items, click on the indicator. When a category is showing the list of objects in it, double-click on
an object to jump directly to that object’s location in the spreadsheet.
To open the Navigator, do one of the following:•

Press the F5 key.

•

Select View > Navigator on the Menu bar.

•

Click the Navigator icon on the Tab panel of the Sidebar.

The controls and tools available in the Navigator are as follows:•

Column – type a column letter and press the Enter key to reposition the cell cursor to thespecified column in the same row.

•

Row – type a row number and press the Enter key to reposition the cell cursor to thespecified row in the same column.

•

Data Range – specifies the current data range denoted by the position of the cell cursor.

•

Start – moves the cursor to the cell at the beginning of the current data range, which you
can highlight using the Data Range icon.

•

End – moves the cursor to the cell at the end of the current data range, which you canhighlight using the Data Range icon.

•

Contents – toggles on / off the display of the contents view in the lower part of theNavigator dialog, to temporarily reduce its size. There is no equivalent control needed on
the Navigator deck of the Sidebar.

•

Toggle – toggles the contents view. Only the selected category and its objects are
displayed. Click the icon again to restore all elements for viewing.

•

Scenarios – displays all available scenarios. See Chapter 9, Data Analysis, for more
information about scenarios. Double-click a name to apply that scenario and the result isUsing the Navigator | 29

shown in the sheet. If the Navigator displays scenarios, you can access the following
commands when you right-click a scenario entry:– Delete – deletes the selected scenario.
– Properties – opens the Edit Scenario dialog, where you can edit the scenario
properties.
•

Drag Mode – opens a submenu for selecting which action is performed when dragging
and dropping an object from the Navigator into a document. Depending on the mode you
select, the icon indicates whether a hyperlink, a link, or a copy is created.
– Insert as Hyperlink – hyperlinks the entire item.
– Insert as Link – links the copied item to the original item so that when the original item
is changed, that change will be reflected in the current document.
– Insert as Copy – inserts a copy of the selected item.

Tip
Ranges, scenarios, pictures, and other objects are much easier to find if you havegiven them informative names when creating them, instead of keeping the default
Calc names, for example Scenario 1, Image 1, Image 2, Object 1, and so on. Thesedefault names may not correspond to the position of the object in the document.

Using document properties
To open the Properties dialog for a document, go to File > Properties on the Menu bar. The
Properties dialog provides information about the spreadsheet and allows you to set some of its
properties. The dialog is shown in Figure 20 and its tabs are described below.

Figure 20: Properties dialog, General tab
30 | Chapter 1 Introduction

GeneralContains basic information about the current file.
•

The text at the top of the dialog displays the file name.

•

Change Password – opens a dialog to change the password. It is only active if apassword has been set for the file.

•

Type – displays the file type of the current document.

•

Location – displays the path and the name of the directory where the file is stored.

•

Size – displays the size of the current document in bytes.

•

Created – displays the date, time, and author when the file was first opened.

•

Modified – displays the date, time, and author when the file was last saved in a
LibreOffice file format.

•

Template – displays the template that was used to create the file, if applicable.

•

Digitally signed – displays the date and time when the file was last signed as well as thename of the author who signed the document.

•

Digital Signatures – opens the Digital Signatures dialog where you can manage digital
signatures for the current document.

•

Last printed – displays the date, time, and user name when the file was last printed.

•

Total editing time – displays the amount of time that the file has been open for editing
since the file was created. The editing time is updated when you save the file.

•

Revision number – displays the number of times that the file has been saved.

•

Apply user data – saves the full name of the user with the file. You can edit the name bygoing to Tools > Options > LibreOffice > User Data on the Menu bar.

•

Save preview image with this document – saves a thumbnail.png inside the document.
These images may be used by a file manager under certain conditions.

•

Reset Properties – resets the editing time to zero, the creation date to the current date
and time, and the version number to 1. The modification and printing dates are alsodeleted.

Description
Contains optional editable descriptive information about the spreadsheet.
•

Title – enter a title for the spreadsheet.

•

Subject – enter a subject for the spreadsheet. You can use a subject to group
documents with similar content.

•

Keywords – enter the words that you want to use to index the content of the
spreadsheet. Keywords must be separated by commas. A keyword can contain whitespace characters or semicolons.

•Comments – enter comments to help identify the spreadsheet.

Custom Properties
Use this page to assign custom information fields to the spreadsheet. In a new spreadsheet, thispage may be blank. If the new spreadsheet is based on a template, this page may contain fields.
You can change the name, type, and contents of each row. The information in the fields will be
exported as metadata to other file formats.
Click Add Property to add a new custom property. Use the adjacent Remove Property button todelete a custom property.
Using document properties | 31

CMIS Properties
Only relevant for spreadsheets stored on remote servers. See the Help or the Getting Started
Guide for more information.

Security
Enables two password-protected security options.
•

Open file read-only – select to allow this document to be opened only in read-onlymode. This file sharing option protects the document against accidental changes. It is still
possible to edit a copy of the document and save that copy with the same name as theoriginal.

•

Record changes – select to require that all changes be recorded. To protect therecording state with a password, click Protect and enter a password. This is similar to
Edit > Track Changes > Record on the Menu bar. However, while other users of thisdocument can apply their changes, they cannot disable change recording without
knowing the password.

•

Protect or Unprotect – protects the change recording state with a password. If change
recording is protected for the current document, the button is named Unprotect. Click
Unprotect to disable the protection.

Font
When Embed fonts in the document is selected, any fonts used in the spreadsheet will beembedded into the document when it is saved. This may be useful if you are creating a PDF of
the spreadsheet and want to control how it will look on other computer systems.
Only embed fonts that are used in documents – If fonts have been defined for the
spreadsheet (for example, in the template), but have not been used, select this option to notembed them.
Font scripts to embed – You can choose which types of fonts are embedded: Latin, Asian,Complex. See the Getting Started Guide for more information.

Statistics
Displays statistics for the current file: the number of sheets, cells, pages, and formula groups.

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 utilises the open source Regular Expressions package from theInternational Components for Unicode (ICU), see http://userguide.icu-project.org/strings/regexp
for further details, including a full definition of the syntax for ICU Regular Expressions.
32 | Chapter 1 Introduction

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. See Chapter 2, Entering, Editing, and Formatting Data, for more
information.

•

Filtering using the Standard Filter and Advanced Filter dialogs, accessed by selecting
Data > More Filters > Standard Filter and Data > More Filters > Advanced Filter. See
Chapter 2, Entering, Editing, and Formatting Data, for more information.

•

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 checked. 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 7, 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 21.

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

Regular expressions | 33

Figure 22: Using a regular expression on the Find and Replace dialog
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.
Suppose we want to filter the same spreadsheet (Figure 21) 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 23).

34 | Chapter 1 Introduction

Figure 23: Using regular expressions on the Standard Filter dialog
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 Region from the second drop-down menu in the Field name column.
7) Select = from the second drop-down menu in the Condition column.
8) Type North|East in the second text box in the Value column.
9) If necessary, click the icon to expand the Options area of the dialog.
10) Make sure that the Regular expressions checkbox is checked.
11) Click OK and Calc updates the data displayed in accordance with the specified filter
criteria.
The data shown in Figure 24 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 24: Stationery sales andrevenue data

Regular expressions | 35

In Calc there are 26 functions that support the use of regular expressions and these are listed in
Chapter 7, 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.

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 be exported to Excel format without
loss of data. See Chapter 7, 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 and on
The Document Foundation’s wiki starting at
https://wiki.documentfoundation.org/Documentation/HowTo/Calc/Regular_Expressions.

36 | Chapter 1 Introduction