Getting Started Guide
 

Chapter 5  
Getting Started with Calc

Using Spreadsheets in LibreOffice

Copyright

This document is Copyright © 2010–2016 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

Jean Hollis Weber

Peter Schofield

David Michel

Hazel Russman

Ron Faile Jr.

Martin Saffron

John A Smith

Olivier Hallot

 

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.

Acknowledgments

This chapter is based on Chapter 5 of Getting Started with OpenOffice.org 3.3. The contributors to that chapter are:

Richard BarnesRichard DetwilerJohn Kane
Peter KupferJoe SellmanJean Hollis Weber
Linda WorthingtonMichele Zarri

Publication date and software version

Published 30 June 2016. Based on LibreOffice 5.1.

Note for Mac users

Some keystrokes and menu items are different on a Mac from those used in Windows and Linux. The table below gives some common substitutions for the instructions in this chapter. For a more detailed list, see the application Help.

Windows or Linux

Mac 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

F5

Shift+⌘+F5

Open the Navigator

F11

⌘+T

Open the Styles and Formatting window

 

Contents

Copyright

Contributors

Feedback

Acknowledgments

Publication date and software version

Note for Mac users

What is Calc?

Spreadsheets, sheets, and cells

Calc main window

Title bar

Menu bar

Toolbars

Formula bar

Spreadsheet layout

Individual cells

Sheet tabs

Status bar

Sidebar

Opening a CSV file

Saving spreadsheets

Saving in other spreadsheet formats

Navigating within spreadsheets

Cell navigation

Sheet navigation

Keyboard navigation

Customizing the Enter key

Selecting items in a spreadsheet

Selecting cells

Single cell

Range of contiguous cells

Range of non-contiguous cells

Selecting columns and rows

Single column or row

Multiple columns or rows

Entire sheet

Selecting sheets

Single sheet

Multiple contiguous sheets

Multiple non-contiguous sheets

All sheets

Working with columns and rows

Inserting columns and rows

Single column or row

Multiple columns or rows

Deleting columns and rows

Single column or row

Multiple columns or rows

Working with sheets

Inserting new sheets

Moving and copying sheets

Dragging and dropping

Using a dialog

Deleting sheets

Renaming sheets

Viewing Calc

Changing document view

Freezing rows and columns

Freezing rows or columns

Freezing rows and columns

Unfreezing

Splitting the screen

Splitting horizontally or vertically

Splitting horizontally and vertically

Removing split views

Using the keyboard

Numbers

Minus numbers

Leading zeroes

Numbers as text

Text

Date and time

Autocorrection options

Replace

Exceptions

Options

Localized options

Reset

Deactivating automatic changes

Speeding up data entry

Using the Fill tool

Using a fill series

Defining a fill series

Defining a fill series from a range in a sheet

Using selection lists

Sharing content between sheets

Validating cell contents

Editing data

Deleting data

Deleting data only

Deleting data and formatting

Replacing data

Editing data

Using the keyboard

Using the mouse

Formatting data

Multiple lines of text

Automatic wrapping

Manual line breaks

Shrinking text to fit the cell

Formatting numbers

Formatting a font

Formatting cell borders

Formatting cell background

AutoFormatting of cells

Using AutoFormat

Defining a new AutoFormat

Using themes

Using conditional formatting

Hiding and showing data

Hiding and protecting data

Showing data

Sorting records

Using formulas and functions

Analyzing data

Printing

Print ranges

Defining a print range

Adding to a print range

Removing a print range

Editing a print range

Printing options

Repeat printing of rows or columns

Page breaks

Inserting a break

Deleting a page break

Headers and footers

Setting a header or footer

 

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:

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. See Chapter 13 Getting Started with Macros and Calc Guide Chapter 12 Calc Macros.

Spreadsheets, sheets, and cells

Calc works with elements 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 several sheets, and each sheet can have many individual cells. In Calc, each sheet can have a maximum of 1,048,576 rows (65,536 rows in Calc 3.2 and earlier) and a maximum of 1024 columns. LibreOffice Calc can hold up to 32,000 sheets.

Calc main window

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

Title bar

The Title bar, located at the top, shows the name of the current spreadsheet. When a spreadsheet is newly created from a template or a blank document, 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

When you select an item on the Menu bar, a sub-menu drops down to show commands. You can also customize the Menu bar; see Chapter 14 Customizing LibreOffice for more information.

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 and fixed in place, or floating; you can move a toolbar into a more convenient position on the workspace. Docked toolbars can be undocked and either moved to different docked position on the workspace, or left as a floating toolbar. Toolbars that are floating when opened can be docked into a fixed position on the workspace.

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 Customizing LibreOffice for more information.

Formula bar

The Formula Bar is located at the top of the sheet in the Calc workspace. The Formula Bar is permanently docked in this position and cannot be used as a floating toolbar. If the Formula Bar is not visible, go to View on the Menu bar and select Formula Bar.

 

Figure 2: Formula bar

 

Going from left to right and referring to Figure 2, the Formula Bar consists of the following:

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

Spreadsheet layout

Individual cells

The main section of the workspace in Calc displays the cells in the form of a grid. Each cell is formed by the intersection of one column and one row in the spreadsheet.

At the top of the columns and the left end of the rows are a series of header boxes containing letters and numbers. The column headers use an alpha character starting at A and go on to the right. The row headers use a numerical character starting at 1 and go 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 on the Menu bar and select Column & Row Headers.

Sheet tabs

In Calc, you can have more than one sheet in a spreadsheet. At the bottom of the grid of cells in a spreadsheet are sheet tabs indicating how many sheets there are in the spreadsheet. Clicking on a tab enables access to each individual sheet and displays that sheet. An active sheet is indicated with a white tab (default Calc setup). You can also select multiple sheets by holding down the Ctrl key while clicking on the sheet tabs.

To change the default name for a sheet (Sheet1, Sheet2, and so on), right-click on a sheet tab and select Rename Sheet from the context menu. A dialog opens, in which you can type a new name for the sheet. Click OK when finished to close the dialog.

To change the color of a sheet tab, right-click on the tab and select Tab Color from the context menu to open the Tab Color dialog (Figure 3). Select a color and click OK when finished to close the dialog. To add new colors to this color palette, see Chapter 14 Customizing LibreOffice.

 

Figure 3: Tab color dialog

 

Status bar

The Calc status bar (Figure 4) provides information about the spreadsheet as well as quick and convenient ways to change some of its features. Most of the fields are similar to those in other components of LibreOffice; see Chapter 1 Introducing LibreOffice in this guide and the Calc Guide Chapter 1 Introducing Calc for more information.

LibreOffice/5.2.1.2$Linux_X86_64 LibreOffice_project/31dd62db80d4e60af04904455ec9c9219178d62000169992595true$(inst)/share/palette%3B$(user)/config/standard.sob0$(inst)/share/palette%3B$(user)/config/standard.soc$(inst)/share/palette%3B$(user)/config/standard.sod1250false$(inst)/share/palette%3B$(user)/config/standard.sog$(inst)/share/palette%3B$(user)/config/standard.sohfalsefalsetruetruefalsefalsetruefalsefalsefalse$(inst)/share/palette%3B$(user)/config/standard.soefalse34false0low-resolutionfalse11true

 

 

 

 

 

 

 

 

 

 

 

 

 
 

Figure 4: Calc status bar

 

Sidebar

The Calc Sidebar (View > Sidebar) is located on the right side of the window. It is a mixture of toolbar and dialog. It is similar to the sidebar in Writer (shown in Chapter 1 and Chapter 4 of this book) and consists of five decks: Properties, Styles and Formatting, 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.

Each of these panels has a More Options button, which opens a dialog giving a greater number of options. These dialogs lock the document for editing until they are closed.

To the right side of the title bar of each open deck is a Close button (X), which closes the deck to leave only the Tab bar open. Clicking on any Tab button reopens the deck.

To hide the Sidebar, or reveal it if already hidden, click on the edge Hide/Show button. To adjust the deck width, drag on the left edge of the sidebar.

Opening a CSV file

Comma-separated-values (CSV) files are spreadsheet files in a text format where cell contents are separated by a character, for example a comma or semicolon. 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.

Most CSV files come from databases tables, queries, or reports, where further calculations and charting are required. On Microsoft Windows, CSV files often have the XLS file name extension to look like an Excel file, but they are still CSV files internally.

To open a CSV file in Calc:

  1. 1)Choose File > Open on the Menu bar and locate the CSV file that you want to open. 

  2. 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. 3)The Text Import dialog (Figure 5) opens. Here you can select the various options available when importing a CSV file into a Calc spreadsheet. 

  4. 4)Click OK to open and import the file. 

 

Figure 5: Text Import dialog

 

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

Saving spreadsheets

To save a spreadsheet, see Chapter 1 Introducing LibreOffice for more details on how to save files manually or automatically. Calc can also save spreadsheets in a range of formats and also export spreadsheets to PDF, HTML, and XHTML file formats; see the Calc Guide Chapter 6 Printing, Exporting, and E-mailing for more information.

Saving in other spreadsheet formats

If you need to exchange files with users who are unable to receive spreadsheet files in Open Document Format (ODF) (*.ods), which Calc uses as default format, you can save a spreadsheet in another format.

  1. 1)Save the spreadsheet in Calc spreadsheet file format (*.ods). 

  2. 2)Select File > Save As on the Menu bar to open the Save As dialog (Figure 6). 

 

Figure 6: Save As dialog

 
  1. 3)In File name, you can enter a new file name for the spreadsheet. 

  2. 4)In File type drop-down menu, select the type of spreadsheet format you want to use. 

  3. 5)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. 6)Click Save. 

  5. 7)Each time you click Save, the Confirm File Format dialog opens (Figure 7). Click Use [xxx] Format to continue saving in your selected spreadsheet format or click Use ODF Format to save the spreadsheet in Calc ODS format. 

  6. 8)If you select Text CSV format (*.csv) for your spreadsheet, the Export Text File dialog (Figure 8) opens. Here you can select the character set, field delimiter, text delimiter, and so on to be used for the CSV file. 

 

Figure 7: Confirm File Format dialog

 
 

Figure 8: Export Text File dialog for CSV files

 

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 Default file format and ODF settings > Document type, select Spreadsheet, then in Always save as, select your preferred file format.

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.

 

Figure 9: Navigator dialog in Calc

 

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 sheets in a spreadsheet:

If your spreadsheet contains a lot of sheets, then some of the sheet tabs may be hidden behind the horizontal scroll bar at the bottom of the screen. If this is the case:

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, and the method you use to insert a new sheet, the new sheet may not be in numerical order. It is recommended to rename sheets in a spreadsheet to make them more recognizable.

 
 

Keyboard navigation

To navigate a spreadsheet using the keyboard, pressing a key or a combination of keys. For a key combination, press more than one key at the same time. 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 one cell.

Moves cell focus left one cell.

Moves cell focus up one cell..

Moves cell focus down one cell

Ctrl+→

Moves cell focus to the first column on the right containing data in that row if cell focus is on a blank cell.

Moves cell focus to the last column on the right in the same range of occupied cells in that row if cell focus is on a cell containing data.

Moves cell focus to the last column on the right in the spreadsheet if there are no more cells containing data.

Ctrl+←

Moves cell focus to the last column on the left containing data in that row if cell focus is on a blank cell.

Moves cell focus to the first column on the left in the same range of occupied cells in that row if cell focus is on a cell containing data.

Moves cell focus to the first column in that row if there are no more cells containing data.

Ctrl+↑

Moves cell focus from a blank cell to the first cell above containing data in the same column.

Moves cell focus to the first row in the same range of occupied cells if cell focus is on a cell containing data.

Moves cell focus from the last cell containing data to the cell in the same column in the last row of the spreadsheet.

Ctrl+↓

Moves cell focus from a blank cell to the first cell below containing data in the same column.

Moves cell focus to the last row in the same range of occupied cells in that column if cell focus is on a cell containing data.

Moves cell focus from the last cell containing data to the cell in the same column in the last row of the spreadsheet.

Ctrl+Home

Moves cell focus from anywhere on the spreadsheet to Cell A1 on the same sheet.

Ctrl+End

Moves cell focus from anywhere on the spreadsheet to the last cell in the lower right-hand corner of the rectangular area of cells containing data on the same sheet.

Alt+Page Down

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

Alt+Page Up

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

Ctrl+Page Down

Moves cell focus to the same cell on the next sheet to the right in sheet tabs if the spreadsheet has more than on sheet.

Ctrl+Page Up

Moves cell focus to the same cell on the next sheet to the left in sheet tabs if the spreadsheet has more than on sheet.

Tab

Moves cell focus to the next cell on the right.

Shift+Tab

Moves cell focus to the next cell on the left.

Enter

Moves cell focus down one cell (unless changed by user).

Shift+Enter

Moves cell focus up one cell (unless changed by user).

Customizing the Enter key

You can customize the direction in which the Enter key moves the cell focus by going to Tools > Options > LibreOffice Calc > General. 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. Use the first two options under Input settings in Figure 12 to change the Enter key settings.

 

Selecting items in a spreadsheet

Selecting cells

Single cell

Left-click in the cell. You can verify your selection by looking in the Name Box on the Formula Bar (Figure 2 on page 8).

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. 1)Click in a cell. 

  2. 2)Press and hold down the left mouse button. 

  3. 3)Move the mouse around the screen. 

  4. 4)Once the desired block of cells is highlighted, release the left mouse button. 

To select a range of cells without dragging the mouse:

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

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

  3. 3)Hold down the Shift key and click. 

You can also select a contiguous range of cells by first clicking in the Selection mode field on the Status Bar (Figure 4 on page 10) and selecting Extending selection before clicking in the opposite corner of the range of cells. Make sure to change back to Standard selection or you may find yourself extending a cell selection unintentionally.

To select a range of cells without using the mouse:

  1. 1)Select the cell that will be one of the corners in the range of cells. 

  2. 2)While holding down the Shift key, use the cursor arrows to select the rest of the range. 

  • Tip 

You can also directly select a range of cells using the Name Box. Click into the Name Box on the Formula Bar (Figure 2 on page 8). To select a range of cells, enter the cell reference for the upper left-hand cell, followed by a colon (:), and then the lower right-hand cell reference. For example, to select the range that would go from A3 to C6, you would enter A3:C6.

Range of non-contiguous cells

  1. 1)Select the cell or range of cells using one of the methods above. 

  2. 2)Move the mouse pointer to the start of the next range or single cell. 

  3. 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. 4)Repeat as necessary. 

Selecting columns and rows

Single column or row

To select a single column, click on the column header (Figure 1 on page 7).

To select a single row, click on the row header.

Multiple columns or rows

To select multiple columns or rows that are contiguous:

  1. 1)Click on the first column or row in the group. 

  2. 2)Hold down the Shift key. 

  3. 3)Click the last column or row in the group. 

To select multiple columns or rows that are not contiguous:

  1. 1)Click on the first column or row in the group. 

  2. 2)Hold down the Ctrl key. 

  3. 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 13), or use the key combination Ctrl+A to select the entire sheet, or go to Edit on the Menu bar and select Select All.

 

Selecting sheets

You can select 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 white (default Calc setup).

Multiple contiguous sheets

To select multiple contiguous sheets:

  1. 1)Click on the sheet tab for the first desired sheet. 

  2. 2)Move the mouse pointer over the sheet tab for the last desired sheet. 

  3. 3)Hold down the Shift key and click on the sheet tab. 

  4. 4)All tabs between these two selections will turn white (default Calc setup). Any actions that you perform will now affect all highlighted sheets. 

Multiple non-contiguous sheets

To select multiple non-contiguous sheets:

  1. 1)Click on the sheet tab for the first desired sheet. 

  2. 2)Move the mouse pointer over the sheet tab for the second desired sheet. 

  3. 3)Hold down the Ctrl key and click on the sheet tab. 

  4. 4)Repeat as necessary. 

  5. 5)The selected tabs will turn white (default Calc setup). Any actions that you perform will now affect all highlighted sheets. 

All sheets

Right-click a sheet tab and choose Select All Sheets from the context menu.

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 next column to left or the row above.

Single column or row

Using the Sheet menu:

  1. 1)Select a cell, column, or row where you want the new column or row inserted. 

  2. 2)Go to Sheet on the Menu bar and select either Insert > Columns > Columns Left or > Columns Right or Insert > Rows > Rows Above or > Rows Below. 

Using the mouse:

  1. 1)Select a column or row where you want the new column or row inserted. 

  2. 2)Right-click the column or row header. 

  3. 3)Select Insert Columns Left, Insert Columns Right, Insert Rows Above, or Insert Rows Below from 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. 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. 2)Proceed as for inserting a single column or row above. 

Deleting columns and rows

Single column or row

To delete a single column or row:

  1. 1)Select a cell in the column or row you want to delete. 

  2. 2)Go to Sheet on the Menu bar and select Delete Cells or right-click and select Delete from the context menu. 

  3. 3)Select the option you require from the Delete Cells dialog (Figure 14). 

 

Alternatively:

  1. 1)Click in the column or header to select the column or row. 

  2. 2)Go to Sheet on the Menu bar and select Delete Cells or right-click and select Delete Columns or Delete Rows from the context menu. 

Multiple columns or rows

To delete multiple columns or rows:

  1. 1)Select the columns or rows, see “Multiple columns or rows” on page 19 for more information. 

  2. 2)Go to Sheet on the Menu bar and select Delete Cells, or right-click and select Delete Columns or Delete Rows from the context menu. 

Working with sheets

Inserting new sheets

  1. 1)Select the sheet where you want to insert a new sheet, then go to Sheet > Insert Sheet... on the Menu bar. 

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

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

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

 

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. To move or copy a sheet into a different spreadsheet, you have to use the Move/Copy Sheet dialog.

Dragging and dropping

To move a sheet to a different position within the same spreadsheet, click and hold 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 (Option key on Mac) 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 a dialog

Use the Move/Copy Sheet dialog (Figure 16) 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 you move or copy the sheet.

  1. 1)In the current document, right-click on the sheet tab you wish to move or copy and select Move/Copy Sheet from the context menu or go to Sheet > Move or Copy Sheet... on the Menu bar. 

  2. 2)Select Move to move the sheet or