Calc Guide 7.4

Chapter 14
Setting up and Customizing

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

Contributors
To this edition
Skip Masonsmith

Kees Kriek

To previous editions
Barbara Duprey
Mark Morin
Leo MoonsJean Hollis Weber
Kees Kriek
Felipe Viggiano

Simon Brydon
Steve Fanning

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

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

Publication date and software version
Published August 2022. Based on LibreOffice 7.4 Community.
Other versions of LibreOffice may differ in appearance and functionality.

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

EffectTools > Options
menu selection

LibreOffice > Preferences

Access setup options

Right-click

Ctrl (Control)

Control+click and/or rightclick depending on computer
setup⌘ (Command)

F11

⌘+T

Open a context menu
Used with other keys
Open the Styles deck in the
SidebarDocumentation for LibreOffice is available at https://documentation.libreoffice.org/en/Contents
Copyright..........................................................................................................................2
Contributors................................................................................................................................2
Feedback....................................................................................................................................2
Publication date and software version........................................................................................2
Using LibreOffice on macOS......................................................................................................2

Introduction......................................................................................................................5
LibreOffice options..........................................................................................................5
User data....................................................................................................................................6
View............................................................................................................................................6
Print............................................................................................................................................6
Paths...........................................................................................................................................6
Security.......................................................................................................................................6
Security Options and Warnings.............................................................................................7
Application colors........................................................................................................................8
Advanced options.......................................................................................................................9

Options for loading and saving documents..................................................................9
General.......................................................................................................................................9VBA Properties.........................................................................................................................10
Microsoft Office.........................................................................................................................11
HTML Compatibility...................................................................................................................11

Calc-specific options.....................................................................................................12
General.....................................................................................................................................12
Defaults.....................................................................................................................................14
View..........................................................................................................................................15
Calculate...................................................................................................................................17
Formula.....................................................................................................................................20
Sort lists....................................................................................................................................21
Changes...................................................................................................................................22
Compatibility.............................................................................................................................22
Grid options..............................................................................................................................23
Print options..............................................................................................................................23

Default colors for charts............................................................................................... 24
Customizing the user interface.................................................................................... 24
Menu content............................................................................................................................24
Modifying an existing menu.................................................................................................25
Creating a new menu...........................................................................................................26
Creating an accelerator.......................................................................................................27
Toolbars....................................................................................................................................27
Creating a new toolbar.........................................................................................................29
Choosing icons for toolbar commands................................................................................29
Tabbed interface.......................................................................................................................30
Keyboard shortcuts...................................................................................................................31
Chapter 14 Setting up and Customizing | 3

Saving changes to a file.......................................................................................................32
Loading a saved keyboard configuration.............................................................................32
Resetting the shortcut keys.................................................................................................32
Running macros from key combinations..............................................................................32

Assigning macros to events.........................................................................................32
Adding functionality with extensions..........................................................................33
Installing extensions.................................................................................................................33
Updating extensions.................................................................................................................33
Removing and disabling extensions.........................................................................................33

Adding custom colors...................................................................................................34

4| Chapter 14 Setting up and Customizing

Introduction
This chapter presents some of the setup options found under Tools > Options on the Menu bar(LibreOffice > Preferences on macOS). Additional options, and more details about those given
here, are covered in the Help and in Chapters 2 (Setting up LibreOffice) and 14 (Customizing
LibreOffice) in the Getting Started Guide.
This chapter also describes some common customizations to menus, toolbars, and keyboard
shortcuts. Other customizations are made easy by extensions that you can install from the
LibreOffice website or from other providers.

LibreOffice options
This section covers some of the settings that apply to all the components of LibreOffice and areof particular interest to users of Calc.
1) Choose Tools > Options to open the Options dialog. The list on the left-hand side varies
depending on which LibreOffice component is open. The illustrations in this chapter show
the list as it appears when Calc is open.
2) Click the expansion symbol (+ or triangle) next to LibreOffice. A list of subsections drops
down.

Figure 1: LibreOffice options

Note
The Reset button, located in the lower right of the full Options dialog, has thesame effect on all pages of the dialog. It resets the options to the values that werein place when you opened the dialog.

LibreOffice options | 5

User data
User data is used by templates and wizards in LibreOffice. For example, Calc uses the first andlast name stored here to fill in the Created and Modified fields in the document properties. These
fields may also be used in the footer of a printed spreadsheet or as the name associated withcomments. If you want your name to appear, then fill in the form on the LibreOffice – User Data
page.

View
The options on the LibreOffice – View page affect how the document window looks and behaves,including the size and style of toolbar and Sidebar icons. Set these options to suit your personal
preferences.

Print
On the LibreOffice – Print page, you can set the print options to suit your default printer and yourmost common printing method. You can change these settings at any time, either through this
dialog or during the printing process (through the Print dialog).
Calc-specific print options are described in “Print options” on page 23.
See Chapter 6, Printing, Exporting, Emailing, and Signing, for more about these options.

Paths
On the LibreOffice – Paths page, you can change the location of files associated with, or used by,
LibreOffice to suit your needs. For example, you might want to store spreadsheets by default ona shared server. Some items can have at least two paths listed: one to a shared folder (whichmight be on a network) and one to a user-specific folder (normally on the user’s personal
computer).

Security
Use the LibreOffice – Security page to choose security options for saving documents and for
opening documents that contain macros.

Figure 2: Choosing security options for opening and saving documents

6 | Chapter 14 Setting up and Customizing

Security Options and Warnings
If you record changes, save multiple versions, or include hidden information or notes inyour documents, and you do not want some of the recipients to see that information, you
can set warnings to remind you to remove this information, or you can have LibreOfficeremove some information automatically. Note that (unless removed) much of thisinformation is retained in a file whether the file is in LibreOffice’s default OpenDocument
Format, or has been saved to other formats, including PDF.
Click the Options button to open a separate dialog with specific choices (see Figure 3).
Passwords for Web Connections
You can enter a master password to enable easy access to websites that require a username and password. If you select the Persistently save passwords for web
connections option, the Set Master Password dialog opens. LibreOffice will securely
store all passwords that you use to access files from web servers. You can retrieve thepasswords from the list after you enter the master password.
Macro Security
Click the Macro Security button to open the Macro Security dialog, where you can adjust
the security level for executing macros and specify trusted sources.
Certificate Path
Users can digitally sign documents using LibreOffice. A digital signature requires apersonal signing certificate. Most operating systems can generate a self-signed
certificate. However, a personal certificate issued by an outside agency (after verifying an
individual’s identity) has a higher degree of trust associated with it than a self-signed
certificate. LibreOffice does not provide a secure method of storing these certificates, but
it can access certificates that have been saved using other programs. Click theCertificate button and select which certificate store to use.

Note
The Certificate Path option appears only on Linux and macOS systems. On
Windows, LibreOffice uses the default Windows location for storing and retrieving
certificates.
TSAs –Time Stamping Authorities
Allows you to optionally select a Time Stamping Authority (TSA) URL for PDF documents
created by LibreOffice. Adding a trusted timestamp to an electronic signature on a PDF
provides a digital seal of data integrity and a trusted date and time of when the file wassigned. Recipients of PDF documents with a trusted timestamp can verify when the
document was digitally or electronically signed, as well as verify that the document was
not altered after the date the timestamp vouches for.

Security Options and Warnings
The following options are on the Security Options and Warnings dialog (Figure 3).
Remove personal information on saving
Select this option to always remove user data from the file properties when saving the file.
To manually remove personal information from specific documents, deselect this option
and then use the Reset Properties button under File > Properties > General on the
Menu bar.
Ctrl-click required to open hyperlinks
The default behavior in most components of LibreOffice is to Ctrl+click on a hyperlink to
open the linked document, but you can choose to open hyperlinks using a single click.
The other options on this dialog should be self-explanatory.

LibreOffice options | 7

Figure 3: Security Options and Warnings dialog

Application colors
On the LibreOffice – Application Colors page (Figure 4), you can specify which user interface
elements are visible and the colors used to display them. Scroll down the list until you find thesection headed Spreadsheet. To change the default color for Grid lines or other on-screen
indicators, click the down-arrow by the color and select a new color in the drop-down list.

Figure 4: Changing the color of elements in Calc
If you wish to save your color changes as a color scheme, click Save, type a name in the Nameof color scheme box in the pop-up dialog, and then click OK.

8 | Chapter 14 Setting up and CustomizingLibreOffice is supplied with a default color scheme (entitled “LibreOffice”) and an alternative
preset color scheme (entitled “LibreOffice Dark”). The latter may be preferable in some working
environments where a reduction in the amount of bright white light on the screen could be
beneficial. Use the Scheme drop-down menu to select one of these schemes, or somealternative scheme that you have already created and saved. As you make selections in thismenu, the Custom Colors area on the dialog is updated to show the settings for the chosen
scheme and the underlying Calc window is also updated accordingly – if you subsequently press
the Cancel button, the previously selected color scheme is re-applied.

Advanced options
Two items of interest on this page are found under Optional Features:
Enable experimental features (may be unstable)
Selecting this option enables features that are not yet complete or contain known bugs.
The list of these features is different version by version.
Enable macro recording (may be limited)
This option enables macro recording, with some limitations. For more about macro
recording, see Chapter 13, Getting Started with Macros, in the Getting Started Guide, andChapter 12, Macros, in this book.

Options for loading and saving documents
You can set the Load/Save options to suit the way you work. This chapter describes only a few ofthe options, those most relevant to working with Calc. See Chapter 2, Setting up LibreOffice, in
the Getting Started Guide for a description of the other options.
If the Options dialog is not already open, click Tools > Options on the Menu bar. Click the
expansion symbol (+ or triangle) by Load/Save on the left-hand side to display the list ofload/save options pages.

GeneralMany of the choices on the Load/Save – General page (Figure 5) are familiar to users of otheroffice suites. Those of most interest to Calc users are in the Default File Format and ODF
Settings section.
Default File Format and ODF Settings
ODF format version: LibreOffice by default saves documents in OpenDocument Format(ODF) version 1.3 Extended. You will rarely need to change this for compatibility when
exchanging files with other people.
Document type: If you routinely share documents with users of Microsoft Excel, you
might want to change the Always save as option to one of the Excel formats, including
XLSX. However, you can choose an Excel format when you save any individual file.
We recommend that you always save a working copy in ODS format and only create an
Excel version if necessary for sharing. Also, note that recent versions of Microsoft Excel
can open ODS files, so this may no longer be needed.

Options for loading and saving documents | 9

Figure 5: Choosing Load and Save general options

VBA Properties
On the Load/Save – VBA Properties page (Figure 6), you can choose whether to keep any VBA(Visual Basic for Applications) macros in Microsoft Office documents that are opened in
LibreOffice.

Figure 6: Choosing Load/Save VBA Properties
If you choose Load Basic code, you can edit the macros in LibreOffice. The changed code issaved in an ODF document but is not retained if you save into a Microsoft Office format.
If you choose Save original Basic code, the macros will not work in LibreOffice but are retained
unchanged if you save the file into Microsoft Office format.
If you are importing a Microsoft Word or Excel file containing VBA code, you can select the option
Executable code. Whereas normally the code is preserved but rendered inactive (if you inspect
it with the LibreOffice Basic IDE you will notice that it is all commented), with this option the codeis ready to be executed.
Save original Basic code takes precedence over Load Basic code. If both options are selected
and you edit the disabled code in LibreOffice, the original Microsoft Basic code will be saved
when saving in a Microsoft Office format.
To remove any possible macro viruses from the Microsoft Office document, deselect Saveoriginal Basic code. The document will be saved without the Microsoft Basic code.
10 | Chapter 14 Setting up and Customizing

Microsoft Office
On the Load/Save – Microsoft Office page (Figure 7), you can choose what to do when importingor exporting Microsoft Office OLE objects (linked or embedded objects or documents such as
spreadsheets or equations): convert them into or from the corresponding LibreOffice OLE object
or load and save them in their original format.
Select the [L] options to convert Microsoft OLE objects into the corresponding LibreOffice OLE
objects when a Microsoft document is loaded into LibreOffice.
Select the [S] options to convert LibreOffice OLE objects into the corresponding Microsoft OLE
objects when a document is saved in a Microsoft format.

Figure 7: Choosing Load/Save Microsoft Office options
The Character Highlighting options enable you to select between the two text highlighting(character background) attributes that Microsoft Office provides, Highlighting or Shading.
Shading is the initial default.
The Create MSO lock file option improves interoperability with Microsoft Office for sharedspreadsheets. When enabled, Calc writes two lock files when opening a Microsoft Office format
file, one LibreOffice lock file and one Microsoft Office lock file. This enables users of both suites
to be made aware that the file is in use. The option is initially disabled by default.

HTML Compatibility
Choices made on the Load/Save – HTML Compatibility page (Figure 8) affect how LibreOfficeimports or exports HTML pages. See Chapter 2, Setting up LibreOffice, in the Getting Started
Guide, and “HTML documents importing/exporting” in the Help, for more information.
The main items of interest for Calc users are in the Export section.
LibreOffice Basic
Select this option to include LibreOffice Basic macros (scripts) when exporting to HTML
format. You must activate this option before you create the LibreOffice Basic macro;otherwise the script will not be inserted. LibreOffice Basic macros must be located in theheader of the HTML document. Once you have created the macro in the LibreOffice Basic
IDE, it appears in the source text of the HTML document in the header.
If you want the macro to run automatically when the HTML document is opened, choose
Tools > Customize > Events. See Chapter 12, Macros, for more information.
Display warning
When the LibreOffice Basic option (see above) is not selected, the Display warning
option becomes available. If the Display warning option is selected, then when exporting
to HTML a warning is shown that LibreOffice Basic macros will be lost.
Options for loading and saving documents | 11

Print layout
Select this option to export the print layout of the current document as well. The HTMLfilter supports CSS2 (Cascading Style Sheets Level 2) for printing documents. These
capabilities are only effective if print layout export is activated.
Copy local images to Internet
Select this option to automatically upload the embedded pictures to an Internet serverwhen uploading using FTP.

Figure 8: Choosing HTML compatibility options

Calc-specific options
Calc must be open in order for the LibreOffice Calc options to be displayed in the Options dialog.
Click the expansion symbol (+ or triangle) by LibreOffice Calc. A list of subsections drops down
(Figure 9).

GeneralThe choices on the LibreOffice Calc – General page (Figure 10) affect the units used for rulers
and other measurements, the default tab stop positions, the updating of links and fields, andvarious input settings.
Metrics section
Choose the unit of measurement used in spreadsheets and the default tab stops
distance.
Update links when opening section
Choose whether to update links when opening a document: Always, On request, or
Never. Spreadsheets that include many or large charts or graphics may load slowly if thisoption is set to Always.

12 | Chapter 14 Setting up and Customizing

Figure 9: Calc options

Figure 10: Selecting general options for Calc
Input Settings
Press Enter to move selection
Specifies that pressing Enter moves the cursor to another cell. You can also choose thedirection the cursor moves: Up, Down, Left, or Right. If this option is not selected,pressing Enter places the data into the cell but does not move the cursor.
Press Enter to switch to edit mode
Specifies that pressing Enter puts the selected cell into edit mode. To end edit mode andmove the cursor in the desired direction, press Enter again.
Press Enter to paste and clear clipboard
When the content of a cell is copied to the clipboard, you can paste the information inanother cell by pressing the Enter key. After pasting occurs, the clipboard is cleared.
Here you can enable/disable this feature.

Calc-specific options | 13

Expand formattingSpecifies whether to automatically apply the formatting attributes of the selected cell toempty adjacent cells. If, for example, a selected cell has the bold attribute, this attribute
will also apply to empty adjacent cells. However, cells that already have a special format
will not be modified by this function. To see the affected range, press Ctrl+*(multiplication sign on the number pad). The format will also apply to all new values
inserted within this range. This feature works only while you are inserting data in adjacentcells with the previous formatting style; if you insert some data in a non-adjacent cell,
Expand formatting will no longer work. In order to get it to work again, you will need toformat a cell again (it could be the same one if you like) and insert data into its adjacentcells.
Expand references when new columns/rows are inserted
Specifies whether to expand references when inserting columns or rows adjacent to thereference range. This is only possible if the reference range, where the column or row isinserted, originally spanned at least two cells in the desired direction.
Example: If the range A1:B1 is referenced in a formula and you insert a new column after
column B, the reference is expanded to A1:C1. If the range A1:B1 is referenced and a
new row is inserted under row 1, the reference is not expanded, since there is only asingle cell in the vertical direction.
If you insert rows or columns in the middle of a reference area, the reference is always
expanded.
Update references when sorting range of cells
When selected, references to cells are updated when a range of cells is sorted. If notselected, the references are unchanged.
Highlight selection in column/row headers
Specifies whether to highlight column and row headers in the selected columns or rows.
Use printer metrics for text formatting
Select this option to use the properties of the default printer to format the display of a
spreadsheet. Those properties can be found by choosing File > Printer Settings andthen selecting the Properties button for the default printer. If the printer has options forpage setup, those settings will be applied to Calc documents. If this option is not selected,
pages will be configured for a generic printer.
Show overwrite warning when pasting data
Pasting contents of the clipboard into a cell will replace the contents of that cell with what
is in the clipboard. Select this option to be alerted to a possible loss of data when pasting.
Position cell reference with selection
With this option set, expanding a selection (with Ctrl+Shift+Down/Up) jumps to theend of the range in the column that was added as last to the initial selection. When theoption is not set, expanding a selection (with Ctrl+Shift+Down/Up) jumps to the endof the range in the column where selecting the cell range was started. The same applies
when extending a selection on rows, with Ctrl+Shift+Left/Right.

Defaults
On the LibreOffice Calc – Defaults page you can enter the number of sheets to be opened bydefault when you start a new spreadsheet, and either accept the default sheet prefix or type a
new one. After a spreadsheet has been created, you can add new sheets and change the label of
any tab; see Chapter 1, Introduction, for more information.

14 | Chapter 14 Setting up and Customizing

View
The options on the LibreOffice Calc – View page (Figure 11) affect the way the document window
looks and behaves when viewed onscreen.
Display section
Select various options for the display.
Formulas
Display the content of a cell either as a formula or as the result of that formula.
Zero values
Display a zero in a cell when the cell has a value of zero, or display an empty cell.
Comment indicator
Select this option to show a small symbol in the top right corner of a cell that contains acomment.
To always display a comment, right-click on the cell and select Show comment in thecontext menu.

Figure 11: Selecting view options for Calc
Value highlighting
Select this option to highlight all values, or results of formulas, in the sheet. Text is
highlighted in black; numbers, dates and logical values in blue, since they are all handled
as numbers by Calc; formulas are displayed in green. When this option is active, anycolors assigned in the document are not displayed.
Anchor
If an inserted object, such as a picture, is anchored to a cell, an image of an anchor willappear in the cell to which the object is anchored.
Text overflow
If a cell contains text that is wider than the width of the cell, the text is displayed overempty neighboring cells in the same row. If there is no empty neighboring cell, a small
triangle at the cell border indicates that the text continues. If this option is selected, text
will wrap inside the cell and the height of the row will expand to accommodate the hight ofthe expanded cell.

Calc-specific options | 15

Show references in color
Select this option to provide a visual aid to assist the editing of a formula. Each referenceis then highlighted in color in the formula, and the referenced cell range is enclosed by acolored border as soon as the cell containing the reference is selected for editing.

Figure 12: Formula references – visual aids
Window section
The following elements of the spreadsheet window can be made visible or invisible:
column and row headers, horizontal and vertical scroll bars, sheet tabs, and, if an outline
has been defined, outline symbols.
If the Sheet tabs option is not selected, you can only switch between the sheets by using
the Navigator.
The Summary on search option controls the display of the Search Results dialog, which
is described in detail in Chapter 2, Entering, Editing, and Formatting Data.
Visual Aids section
Grid lines
Grid lines are the borders around the cells of a spreadsheet when viewed onscreen. The
options are: Show, Show on colored cells, or Hide. If grid lines are hidden, tables will
be displayed on a solid background with no grid around cells. The color choice overrides
the selection made in Tools > Options > LibreOffice > Application Colors >Spreadsheet > Grid lines.

Note
To print grid lines, choose Format > Page Style > Sheet > Print > Grid.
Color
Specifies the color for the gridlines. A color can be chosen from different pallettes or canbe set to Automatic for the default gray.
Pointer
When the mouse pointer lies over the grid of cells on a Calc spreadsheet, the default
pointer (“System”) is normally shown, typically as an arrow pointer. However, analternative pointer (“Themed”) is available to allow you to switch to using the pointershape defined in the icon theme (typically a “fat” cross).
Page breaks
Specifies whether to view the page breaks within a defined print area.
Helplines while moving
Specifies whether to view helplines when moving drawings, frames, graphics, and otherobjects. These lines help you align objects.
In Figure 13, the graph with the dark background represents the image in its original
location. The lighter area represents the position of the image as it is being moved.
Helplines are the horizontal and vertical dotted lines at the corners of the lighter object.
Objects section
Choose whether to show or hide graphics, charts, and drawing objects.
Zoom section
Select the Synchronize sheets option to apply any selected zoom (magnification) factor
to all sheets in the spreadsheet. If this option is not selected, separate zoom factors canbe applied to individual sheets.
16 | Chapter 14 Setting up and Customizing

Figure 13: Helplines visible when moving a graphic

Calculate
Use the LibreOffice Calc – Calculate page (Figure 14) to define the calculation settings for
spreadsheets.

Figure 14: Calc calculation options
Formulas Wildcards section
Enable wildcards in formulas
Specifies that wildcards are enabled when searching and also for character string
comparisons. Select this option for spreadsheets that need to be interoperable with
Microsoft Excel.

Calc-specific options | 17

Enable regular expressions in formulas
Specifies that regular expressions instead of simple wildcards are enabled whensearching and also for character string comparisons. Do not enable regular expressions
in formulas for spreadsheets that need to be interoperable with Microsoft Excel.
No wildcards or regular expressions in formulas
Specifies that only literal strings are used when searching and also for character string
comparisons. Do not disable wildcards in formulas for spreadsheets that need to beinteroperable with Microsoft Excel.
The options listed above are mutually exclusive. For more information about them, see Chapter
7, Using Formulas and Functions.
Date section
Select the start date for the internal conversion from days to numbers.
12/30/1899 (default) – Sets December 30, 1899 as day zero.
01/01/1900 (StarCalc 1.0) – Sets January 1, 1900 as day zero. Use this setting for
StarCalc 1.0 spreadsheets containing date entries.
01/01/1904 – Sets January 1, 1904 as day zero. Use this setting for spreadsheets thatare imported from foreign formats.
General Calculations section
Specify a variety of options relevant to spreadsheet calculation.
Case sensitive
Specifies whether to distinguish between upper and lower case in texts when comparing
cell contents. For example: type 'Test' in cell A1; and 'test' in B1. Then type the formula=A1=B1 in cell C1. If the Case sensitive option is checked, FALSE will appear in the cell; if
the analysis was case insensitive, TRUE will appear in the cell. This option is not applied
to the EXACT text function, which is always case-sensitive.
Precision as shown
Specifies whether to make calculations using the rounded values displayed in the sheet.
Charts will be shown with the displayed values. If the Precision as shown option is notmarked, the displayed numbers are rounded, but they are calculated internally using the
non-rounded number.
Search criteria = and <> must apply to whole cells
The default search method in Calc is to search for any instance of the target regardless ofthe position of the target within a word. For example, when searching for day, Calc will
match Friday, Sunday, and days. Select this option to search an exact occurrence of thetarget. Wildcard symbols can be used to expand the search to match the default
condition.
Automatically find column and row labels
Calc will name the range of data, under a column heading or to the right of a row heading,using the text in those headings.
Example: Cell E5 contains the text Europe. Below, in cell E6, is the value 100 and in cellE7 the value 200. If the Automatically find column and row labels option is selected,
you can write the following formula in cell A1: =SUM(Europe).
Limit decimals for general number format
Limits the number of decimals to be displayed for numbers with the general number
format. This overrides the setting applied in the General option of the Format section
inside the Number Category in Format > Cells > Numbers. This setting refers to how
numbers are displayed. When decimal places are limited, calculations are not affected
because of rounding.

18 | Chapter 14 Setting up and Customizing

Iterative References section
Iterative references are formulas that are continuously repeated until the problem issolved. In this section you can choose the number of approximation steps carried out
during iterative calculations and the degree of precision of the answer.
Iterations
Select this option to enable iterations. If this option is not selected, an iterative reference
causes an error message.
Steps
Sets the maximum number of iteration steps.
Minimum change
Specifies the difference between two consecutive iteration step results. If the result of theiteration is lower than the minimum change value, then the iteration will stop.
CPU Threading Settings section
Enable multi-threaded calculation
Controls the use of multi-threading / parallelism, which can be used to speed up
LibreOffice Calc calculations on computers with multiple processing cores. See Chapter
7, Using Formulas and Functions for more information.

CautionWhen calculations involve a range that includes an empty cell, take note of thesetting for Zero values in the LibreOffice Calc > View options. If this option is
not selected, a value of zero will display as an empty cell. Empty cells and thevalue zero are treated differently by many functions. For example, COUNT will notinclude an empty cell as an item to be counted. The average of eight cells, one ofwhich contains the value zero, is not the same as the average of eight cells, oneof which is empty (an empty cell is not included in determining the average).

FormulaUse the LibreOffice Calc – Formula page (Figure 15) to specify the following formula options.
Formula syntax
Syntax is the format in which formulas are written. Choose the syntax most appropriate
for your work. If you regularly open or save to Microsoft Excel formats, you may wish touse one of the Excel syntaxes.
To illustrate the differences between syntaxes, assume a spreadsheet contains two
sheets.
The cell C4 can be referenced in three ways:
Calc A1 =$Sheet2.C4 – the default syntax for Calc.
Excel A1 =Sheet2!C4 – the default of Microsoft Excel.
Excel R1C1 =Sheet2!R[3]C[2] – the relative row and column addressing used in Excel.

Calc-specific options | 19

Figure 15: Formula options
Use English function names
The default names for functions in Calc are those for the user's locale. For English users,
the localized names are the same as the English names. When the language set by
locale is not English, you may wish to express functions in English; if so, select this option
to change formula input and display, Function Wizard, and formula tips.
Separators
Separators are used in functions to distinguish points in the range used in the function:
the symbols used between elements. Enter the separator character to be used forfunctions, row arrays, and column arrays. For example, a comma can be used as aseparator in functions and instead of writing =SUM(A1;B1;C1) the function would be
expressed as =SUM(A1,B1,C1).
Detailed Calculation Settings
Most users will not need to change the default setting. For custom settings, selectCustom and click Details to choose reference syntax and whether to treat empty strings
as zero.
Recalculation on File Load
Recalculating formulas can take significant time while loading very large files.
Excel 2007 and newer. Loading a large spreadsheet file can take a long time. If you do
not need to update your large spreadsheet data immediately, you can postpone therecalculation to a better time. Calc allows you to defer recalculation of Excel 2007 (andmore recent) spreadsheets to speed up loading time.
ODF spreadsheet (not saved by LibreOffice). Recent versions of Calc cache spreadsheet
formula results in its ODF file. This feature helps Calc to recalculate a large ODF
spreadsheet saved by Calc faster. For ODF spreadsheets saved by other programs,
where such cached formula results may not exist, recalculation can be deferred to speed
up file loading as with Excel 2007 files.
For each of these two options, the following choices are possible: Never recalculate (noformulas will be recalculated on loading the file), Always recalculate (all formulas will berecalculated on file load), and Prompt user (prompt user for action).
Calc saved ODF spreadsheets will honor Never recalculate and Always recalculate
options.
20 | Chapter 14 Setting up and Customizing

Sort lists
View, define, delete, and edit sort lists on the LibreOffice Calc – Sort Lists page. Sort lists areused for more than sorting, for example filling a series of cells during data entry. They are lists
that are sorted in a particular order but not alphabetically or numerically; for example, days of theweek. In addition to the supplied lists, you can define and edit your own lists, as described in
Chapter 2, Entering, Editing, and Formatting Data.

Figure 16: Defining sort lists in Calc

ChangesOn the LibreOffice Calc – Changes page you can assign specific colors for insertions, deletions,and other changes, or you can let Calc assign colors based on the author of the change; in thelatter case, one color will apply to all changes made by that author. The selections made here areapplied when change recording is enabled by Edit > Track Changes > Record on the Menu bar.

Figure 17: Calc options for highlighting changes

Calc-specific options | 21

Compatibility
The LibreOffice Calc – Compatibility page provides a quick way to switch between LibreOffice’s
default key bindings and OpenOffice.org’s legacy default key bindings for Calc. Most of thedefaults are identical, but there are some differences.
The actions associated with the two key binding choices are shown in the table below.
Key Binding

Default

OpenOffice.org legacy

Backspace

Opens Delete Contents
dialogImmediately deletes contents

DeleteImmediately deletes contents

Opens Delete Contents
dialogControl+D(Command+D on macOS)

Fills cell content down within
selection

Opens the Selection List
dialogAlt+DownArrow(Option+DownArrow on

Select header of range;no action in empty cell

Increases row height

macOS)

Grid options
The LibreOffice Calc – Grid page defines the grid settings for spreadsheets. Using a grid helps
you determine the exact position of any charts or other objects you may add to a spreadsheet.
You can also set this grid in line with the snap grid.
If you have activated the snap grid but wish to move or create individual objects without snap
positions, you can press the Ctrl key to deactivate the snap grid for as long as needed.
Grid section
Snap to grid activates the snap function.
Visible grid displays grid points on the screen. These points are not printed.
Resolution and Subdivision sections
Here you can set the unit of distance for the spacing between horizontal and vertical grid
points and subdivisions (intermediate points) of the grid.
Synchronize axes changes the current grid settings symmetrically.

Figure 18: Calc grid options

22 | Chapter 14 Setting up and Customizing

Print options
Use the LibreOffice Calc – Print page to choose defaults for printing spreadsheets. You canoverride these for individual print jobs.

Figure 19: Calc Print options

Default colors for charts
Use Tools > Options > Charts > Default Colors to change the default colors used for charts or
to add new data series to the list provided.
To add new colors to the choices, see “Adding custom colors” on page 33.
To change the default color for a data series, select it in the left-hand column and then click on
the required color in the color table.
To add another data series, click Add, then select the new series and click on the required color.
Click Default to restore the color settings that were defined when the program was installed.

Figure 20: Defining chart colors

Customizing the user interface
You can customize menus, toolbars, keyboard shortcuts, and the tabbed interface; add newmenus and toolbars; and assign macros to events.

Customizing the user interface | 23

Tip
Changes to menus and toolbars can be saved in a template. First, save them in adocument; then, save that document as a template, as described in Chapter 4,
Using Styles and Templates.

Menu content
You can add and rearrange menus on the Menu bar or context (right-click) menus; add,
rearrange, and remove commands on menus; and make other changes.
To customize menus, choose Tools > Customize on the Menu bar. On the Customize dialog, go
to the Menus tab (Figure 21) or the Context Menus tab. The following examples focus on the
Menus tab but the Context Menus tab is similar in appearance and behavior.

Figure 21: The Menus tab of the Customize dialog

Modifying an existing menu
1) In the Scope drop-down list in the upper right of the Customize dialog, choose whether tosave this changed menu for Calc or for a selected document.
2) In the Target drop-down list, select the menu that you want to customize. The list includes
all the sub-menus as well as the main menus. The commands on the selected menu areshown in the Assigned Commands list below.

24 | Chapter 14 Setting up and Customizing3) To add a command to the selected menu, click on a command in the Available
Commands list and then click the right arrow. You can narrow the search by using the
Search box on the top left or selecting the Category in the drop-down list. Use the up anddown arrows on the far right-hand side to move the command into the place where youwant it in the Assigned Commands list.
4) To remove a command from the selected menu, click on it in the Assigned Commands list
and then click the left arrow.
5) To insert a separator or submenu, use the commands in the Insert drop-down under
Customize at the bottom right.

Figure 22: Customize dialog, Menus tab, Insert drop-down
6) To rename a menu item, select it in the Assigned Commands list and choose Rename in
the Modify drop-down below.

Figure 23: Customize dialog, Menus tab, Modify drop-down
7) Use the Reset button to reset Calc’s menu configuration to the default settings.
8) When you have finished making all your changes, click OK to save them.

Creating a new menu
1) On the Menus tab of the Customize dialog, click the button next to the Target field (Figure24) and select Add in the drop-down list, to display the New Menu dialog (Figure 25).

Figure 24: Location of command to add a new menu
2) In the New Menu dialog, type a name for the new menu in the Menu name box.
3) Use the up and down arrow buttons to move the new menu into the required position on
the Menu bar. Click OK to save and return to the Customize dialog.

Customizing the user interface | 25

The new menu now appears on the list of menus in the Customize dialog. It will appear on the
Menu bar itself after you save your customizations.
After creating a new menu, you need to add some commands to it, as described above formodifying a menu.

Figure 25: Adding a new menu

Creating an accelerator
You can allocate a letter in a custom menu’s name to be used as a keyboard shortcut. (That letter
will become underlined.) When you press Alt+that letter, that menu or command will be
invoked. Existing submenus can also be edited to change the letter which is used to select themusing the keyboard when the menu is open.
To create an accelerator:
1) On the Customize dialog, select a menu item in the Assigned Commands list.
2) Click the Modify button and select Rename.
3) Add a tilde (~) in front of the letter to use as an accelerator.
4) Click OK to save the change.
For example. if you enter “~Art”, the “A” in the menu item Art is now underlined. Press Alt+A andthe Art menu will open.

CautionWhen assigning an accelerator, be careful not to use a letter that is alreadyassigned to a menu or command associated with a menu item. The same letterkey combinations can be used for commands associated with different menu
items but not for those associated with the same menu or menu item.

26 | Chapter 14 Setting up and Customizing

Toolbars
You can customize toolbars in several ways, including choosing which icons are visible andlocking the position of a docked toolbar, as described in Chapter 1, Introducing LibreOffice, of theGetting Started Guide. This section describes how to create new toolbars and add or delete icons(commands) on a toolbar.
To get to the toolbar customization dialog (Figure 26), do any of the following:• On the toolbar, right-click in the toolbar and choose Customize Toolbar.
• Choose View > Toolbars > Customize on the Menu bar and go to the Toolbars tab.
• Choose Tools > Customize on the Menu bar and go to the Toolbars tab.

Figure 26: The Toolbars tab of the Customize dialog
To customize a toolbar:
1) In the Scope drop-down list on the upper right of the Customize dialog, choose whether tosave this changed toolbar for Calc or for a selected document.
2) In the Target drop-down list, select the toolbar that you want to customize. The current
toolbar content is displayed in the Assigned Commands list.
3) Select the command to be added in the Available Commands list. You can narrow yoursearch by using the Search box on the top left or choosing a category in the drop-down
list just below.

Customizing the user interface | 27

4) Click on the large right arrow to add the command to the Assigned Commands list for the
toolbar. Use the up and down arrows in the far right to position the command in the
toolbar.
5) To remove a command from a toolbar, select it in the Assigned Commands list and click
the large left arrow.
6) To show or hide a command assigned to a toolbar, mark or clear the checkbox next to its
icon in the Assigned Commands list.
7) To insert a separator, use the command in the Insert drop-down under Customize at thebottom right.
8) To rename a toolbar item, select it in the Assigned Commands list and choose Rename in
the Modify drop-down.
9) Use the Reset button to reset Calc’s toolbar configuration to the default settings.
10) When you have finished making all your changes, click OK to save them.

Creating a new toolbar
To create a new toolbar:
1) Choose Tools > Customize on the Menu bar.
2) On the Toolbars tab of the Customize dialog, click the button next to the Target field andselect Add in the drop-down list, to display the Name dialog.
3) On the Name dialog, type the new toolbar's name and choose in the Save In drop-down
list where to save this new toolbar – for Calc or for the open document.
The new toolbar now appears on the list of toolbars in the Customize dialog.
To add commands to the new toolbar, follow the instructions earlier for modifying a toolbar.

Choosing icons for toolbar commands
Toolbar buttons can be displayed with Icon only, Text only, or Icon and text. This selection ismade through the menu that drops down from the button to the right of the Target field. However,
not all of the commands have associated icons.
To choose an icon for a command, select the command and click Modify > Change Icon. On the
Change Icon dialog (Figure 27), scroll through the available icons, select one, and click OK to
assign it to the command.

Figure 27: Change Icon dialog

28 | Chapter 14 Setting up and CustomizingTo use a custom icon, create it in a graphics program and import it into LibreOffice by clicking theImport button on the Change Icon dialog. For best quality, custom icons should be 24 x 24 pixels
in size; other sizes will be scaled automatically.
Select Modify > Reset Icon to revert to the option’s default icon.

Tabbed interface
By default, Calc’s commands are grouped in cascading menus and in toolbars filled with icons. In
addition, LibreOffice provides other user interface variants, displaying contextual groups ofcommands and contents. You can find more information about these variants and how to switch
between them in Chapter 15, User Interface Variants.
Three of LibreOffice’s user interface variants utilize a notebook bar and these are the Tabbed,
Tabbed Compact, and Groupedbar Compact options. In these variants, the area at the top of theworkspace is divided into tabs, where each tab displays a set of icons grouped by context. The
context can change depending on the object selected in the document, for example a table or animage.
Use the checkboxes on the Notebookbar tab of the Customize dialog (Figure 28) to show andhide the individual options on the various tabs that are provided in the most recently selected of
the three tabbed user interfaces (with Tabbed being the initial default).

Figure 28: The Notebookbar tab of the Customize dialog
Use the Reset button to reset the displayed configuration to the default settings.

Customizing the user interface | 29

Keyboard shortcuts
In addition to using the built-in keyboard shortcuts (listed in Appendix A), you can define yourown. You can assign shortcuts to standard LibreOffice functions or your own macros and savethem for use with the entire LibreOffice suite or only for Calc.

CautionAvoid reassigning your operating system’s predefined shortcut keys.

Note
Shortcut keys that are grayed out in the listing on the Customize dialog, such as
F1 and F10, are not available for reassignment.
To adapt shortcut keys to your needs, use the Customize dialog, as described below.
1) Select Tools > Customize on the Menu bar and select the Keyboard tab. (Figure 29).

Figure 29: Customizing a keyboard shortcut
2) To have the shortcut key assignment available only with Calc, select Calc in the upper
right corner of the page; otherwise select LibreOffice to make it available to every
component.
3) Next select the required function in the Category and Function lists.
4) Now select the desired shortcut keys in the Shortcut Keys list and click the Modify button
at the upper right.
5) Click OK to accept the change. Now the chosen shortcut keys will execute the function
chosen in step 3)) above whenever they are pressed.
30 | Chapter 14 Setting up and CustomizingNote
All existing shortcut keys for the currently selected Function are listed in the Keys
selection box. If the Keys list is empty, it indicates that the chosen key
combination is free for use. If it were not, and you wanted to reassign a shortcut
key combination that is already in use, you must first delete the existing key.

Saving changes to a file
Changes to the shortcut key assignments can be saved in a keyboard configuration file for use at
a later time, thus permitting you to create and apply different configurations as the need arises, orto create a standard keyboard configuration file to be distributed among many users. To save
keyboard shortcuts to a file:1) After making your keyboard shortcut assignments, click the Save button on the right of
the Customize dialog (Figure 29).
2) In the Save Keyboard Configuration dialog, enter a name for the keyboard configuration
file in the File name box, or select an existing file (the file extension is .cfg for
Configuration).
3) Click Save. A confirmation dialog appears if you are about to overwrite an existing file,otherwise there will be no feedback and the file will be saved.

Loading a saved keyboard configuration
To load a saved keyboard configuration file and replace your existing configuration, click the
Load button on the right of the Customize dialog, and then select the configuration file from theLoad Keyboard Configuration dialog.

Resetting the shortcut keys
To reset all of the keyboard shortcuts to their default values, click the Reset button on the right of
the Customize dialog (adjacent to the Load and Save buttons). Use this feature with care as no
confirmation dialog will be displayed; the defaults will be set without any further notice or user
input.

Running macros from key combinations
You can also define shortcut key combinations that will run macros. These shortcut keys arestrictly user-defined; none are built in. For more information on macros, see Chapter 12, Macros.

Assigning macros to events
In LibreOffice, when something happens, we say that an event occurred. For example, adocument was opened, a key was pressed, or the mouse moved. You can associate a macrowith an event, so the macro is run when the event occurs. A common use is to assign the “opendocument” event to run a macro that performs certain setup tasks for the document.
To associate a macro with an event, use the Events tab of the Customize dialog. For more
information, see Chapter 13, Getting Started with Macros, in the Getting Started Guide.

Adding functionality with extensions
An extension is a package that can be installed into LibreOffice to add new functionality.
Although individual extensions can be found in different places, the official LibreOffice extension
repository is at https://extensions.libreoffice.org/. These extensions are free of charge.
Extensions from other sources may be free or may be available for a fee. Check the descriptions
to see what licenses and fees apply to the ones that interest you.
Adding functionality with extensions | 31

Installing extensions
To install an extension that is listed in the repository, follow these steps:
1) In LibreOffice, select Tools > Extension Manager on the Menu bar, or press
Ctrl+Alt+E. In the Extension Manager dialog, click the Get more extensions online
link.
2) A browser window will open. Find the extension you want and download it to yourcomputer.
3) After the extension is downloaded and saved, return to the Extension Manager dialog andclick Add. Find and select the extension you want to install and click Open. The
extension begins installing. You may be asked to accept a license agreement.
4) When the installation is complete, the extension is listed in the Extension Manager dialog.
To install an extension that is not listed in the repository, download the extension, then continue
with step 3)) above.

Updating extensions
Click the Check for Updates button on the Extension Manager dialog to check for updates toinstalled extensions.

Removing and disabling extensions
To remove (uninstall) an extension that you installed, select the extension in the main window of
the Extension Manager dialog and click the Remove button.
To disable an extension without removing (uninstalling) it, select the extension in the main
window of the Extension Manager dialog and click the Disable button, which then changes to
Enable.

Tip
Extensions can be installed from some other places in Calc, such as the Gallery
on the Sidebar, the Templates dialog, the Language Settings > Writing Aids pageof the Tools > Options dialog and the Icon Style section in the View page of theTools > Options dialog. In each of these places you will find a small button
leading to a filtered view of the items in the Extensions dialog.

Note
In some cases, the Remove and Disable buttons may be unavailable, forexample when you have selected one of the extensions that was bundled withyour LibreOffice installation.

32 | Chapter 14 Setting up and Customizing

Figure 30: The Extension Manager dialog

Adding custom colors
The method for defining a custom color is as follows:
1) Insert any drawing object, such as a square, into any document.
2) Right-click on the object and choose Area in the context menu.
3) Click on the Color button on the Area tab (Figure 31). In the Palette drop-down in the
Colors area, choose which palette you wish to add the new color to. Under New, define
the new color using RGB or Hex notation, or click the Pick button to select the color on
the Pick a Color dialog (Figure 32).
4) Click Add in the lower left corner, enter a name for the new color in the pop-up dialog,
and click OK to save.
5) Delete the drawing object from the document, if it is not needed.

Adding custom colors | 33

Figure 31: Defining a new color

Figure 32: Defining a color by picking it from a color chart

34 | Chapter 14 Setting up and Customizing