Image6

Calc Guide 7.1

Chapter 1
Introduction

Using spreadsheets in LibreOffice

Copyright

This document is Copyright © 2021 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 (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

To previous editions

Barbara Duprey

Gabriel Godoy

Jean Hollis Weber

John A Smith

Christian Chenal

Laurent Balland-Poirier

Philippe Clément

Pierre-Yves Samyn

Shelagh Manton

Peter Schofield

Cathy Crumbley

Kees Kriek

Steve Fanning

Leo Moons

Gordon Bates

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 other personal 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 and Linux. 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 Linux

macOS equivalent

Effect

Tools > 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

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 of the data and observing the results without having to retype the entire spreadsheet or sheet.

Other features provided by Calc include:

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 the View menu. For example, View > Status Bar on the Menu bar will toggle (show or hide) the Status Bar. It is not always necessary to display all of the parts shown; you can show or hide any of them as desired.

Figure 1: Calc main window

Image4

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 and Customizing.

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 to a more convenient location on your workspace. Docked toolbars can be undocked and moved to a different docked location or become floating toolbars. Likewise, floating toolbars can be docked.

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 of common 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 a tooltip that shows the name of the item’s function. To close a tooltip, move away from the underlying component or press the Esc key.

To see a more detailed explanation of an icon, text box, or menu command, do one of the following to open extended tips:

Formula Bar

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

Figure 2: Formula Bar

Image2

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

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 the Getting Started Guide for more information.

Figure 3: Status Bar

Image278

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 style

Shows the page style of the current sheet. To edit the page style, double-click on this field and the Page Style dialog opens.

Language

Shows current default language as selected at Tools > Options > Language Settings > Languages.

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 to view 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 by default. 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 the view magnification.

Zoom percentage

Indicates the magnification level of the document. Right-click on the percentage to open a list of magnification values from which to choose. Click on the percentage to open the Zoom & 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 the right of the Sidebar, allowing you to switch between them. The decks are described below.

Figure 4: Sidebar

Image9

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 vertical alignment, wrapping, indenting, merging, text orientation, and vertical stacking.

Cell Appearance: Options include background color and border formats, including line color and style.

Styles

The Styles deck enables you to apply existing styles, modify existing styles, or create new styles. This deck can also be opened by selecting Styles > Manage Styles on the Menu bar, selecting View > Styles on the Menu bar, or pressing F11.

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

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 > Headers on 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 can create a new sheet by clicking on the plus sign to the left of the sheet tabs or by clicking in the blank 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.

Figure 5: Tab Color dialog

LibreOfficeLogo

To add new colors to this color palette, see “Adding custom colors” in Chapter 14, Setting up and Customizing.

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 are entered 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, or press 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.

4)  Click OK to open and import the file.

Figure 6: Text Import dialog

Image8

The options for importing CSV files into a Calc spreadsheet are as follows:

Import

Separator Options

Other options

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.

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 and also 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 in another format.

1)  Select File > Save As on the Menu bar, click the down arrow at the right of the Save icon on 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.

Figure 7: The Save As dialog

Image19

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 Warn when 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

Image21

If you select Text CSV format (*.csv), the Export Text File dialog (Figure 9) opens. Here you can select 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 have changed 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.

Figure 9: Export Text File dialog

Image24

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 the available 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 group of selected cells depends on the operating system being used and how you have set up LibreOffice.

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.

If there are many sheets in the spreadsheet, some of the sheet tabs may be hidden. If this is the case, 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.

Figure 10: Navigating sheet tabs

Image11

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 and key combinations you can use for spreadsheet navigation in Calc.

Table 1. Keyboard cell navigation

Keyboard shortcut

Cell navigation

→/←

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 the first cell on the right that contains data. If there is no cell on the right containing data, it moves focus along the current row to the last cell at the right of the sheet.

If focus is on a blank cell, Ctrl+← moves focus along the current row to the first 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 A of the sheet.

If focus is on a cell containing data, Ctrl+→ normally moves focus along the current 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 cell at the left edge of the next data region to the right. In this case, if there is no data region to the right, focus is moved along the current row to the last cell at the right of the sheet.

If focus is on a cell containing data, Ctrl+← normally moves focus along the current 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 cell at the right edge of the next data region to the left. In this case, if there is no data region to the left, focus is moved along the current row to the cell in column A of the sheet.

Ctrl+↑ /

Ctrl+↓

If focus is on a blank cell, Ctrl+↑ moves focus up the current column to the first 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 to the 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 the current 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 to the 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 the cell in row 1 of the sheet.

If focus is on a cell containing data, Ctrl+↓ normally moves focus down the current 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 to the cell at the top edge of the next data region below. In this case, if there is no data region below, focus is moved down the current column to the bottom of the sheet.

Ctrl+Home / Ctrl+End

A detailed description of these shortcuts is given on Page 1.

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 (Figure 11) to change the Enter key settings. Select the direction cell focus moves from the drop-down list. 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 in another cell by pressing the Enter key; here you can disable this feature.

Figure 11: Customizing the Enter key

Image5

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 mouse button.

To select a range of cells without dragging the mouse:

1)  Click in the cell which is to be one corner of the range of cells.

2)  Move the mouse to the opposite corner of the range of cells.

3)  Hold down the Shift key and click.

To select a range of cells 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 1) and select Extending selection.

3)  Click in the cell in the opposite corner of the range of cells.

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

2)  Enter the cell reference for the upper left-hand cell, followed by a colon (:), and then the lower right-hand cell reference, then press the Enter key. For example, to select the range that 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 1) and select Adding 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 1). 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.

Figure 12: Select All box

graphics145

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 the column 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.

2)  Go to Sheet on the Menu bar. For columns, select Sheet > Insert Columns and then select Columns Before or Columns After. For rows, select Sheet > Insert Rows and then 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 button on the first one and then dragging across the required number of identifiers.

2)  Proceed as for inserting a single column or row above. 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:

Figure 13: Delete Cells dialog

Image13

Multiple columns or rows

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

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 and select 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.

Figure 14: Insert Sheet dialog

Image12

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.

Dragging and dropping

To move a sheet to a different position within the same spreadsheet, click on the sheet tab and drag 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 the same 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

Image14

1)  In the current document, right-click on the sheet tab you wish to move or copy and select Move 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 already open, 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 so on).

6)  Click OK to confirm the move or copy and close the dialog.

Caution

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

Deleting sheets

To delete a single sheet, right-click on the sheet tab you want to delete and select Delete Sheet in the context menu, or go to Sheet > Delete Sheet on the Menu bar. Click Yes to confirm the deletion.

To delete multiple sheets, select the sheets (see “Selecting sheets” on page 1), 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 editing or 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 the context 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:

Note

Sheet names can contain almost any character. Some naming restrictions apply, the following 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 character of 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.

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 and 23 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 and columns, select the cell (not a row or column) that is below the row and to the right of the column 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

Image7

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 the window). 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 is used by three formulas in other cells. Using the split-screen technique, the cell containing the number 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 each of the formulas.

Figure 17: Split screen example

graphics12

Splitting horizontally or vertically

There are two ways to split a screen horizontally or vertically:

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

graphics16

Splitting both horizontally and vertically

Method One:

Position both the black horizontal and the black vertical lines as described above and as shown in 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 the screen vertically.

2)  Go to View on the Menu bar and select Split Window. Thick lines appear between the rows and columns indicating where the splits have been placed.

Removing split views

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

Using the Navigator

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

Figure 19: Navigator dialog in Calc

Image10

The Navigator categorizes and groups spreadsheet objects which you can click on to move quickly 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 list of 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:

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

Tip

Ranges, scenarios, pictures, and other objects are much easier to find if you have given 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. These default 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

Image3

General

Contains basic information about the current file.

Description

Contains optional editable descriptive information about the spreadsheet.

Custom Properties

Use this page to assign custom information fields to the spreadsheet. In a new spreadsheet, this page 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 to delete a custom property.

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.

Font

When Embed fonts in the document is selected, any fonts used in the spreadsheet will be embedded 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 not embed 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 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 utilises the open source Regular Expressions package from the International 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.

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

Figure 21: Spreadsheet data for example regular expressions

Image26

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

Figure 22: Using a regular expression on the Find and Replace dialog

Image17

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

Figure 23: Using regular expressions on the Standard Filter dialog

Image27

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 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 24: Stationery sales and revenue data

Image28

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

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.

Contents