Calc Guide 24.2

Preface

This document is Copyright © 2024 by the LibreOffice Documentation Team. Contributors are listed below. You may distribute it and/or modify it under the terms of either the GNU General Public License (https://www.gnu.org/licenses/gpl.html), version 3 or later, or the Creative Commons Attribution License (https://creativecommons.org/licenses/by/4.0/), version 4.0 or later.

All trademarks within this guide belong to their legitimate owners.

## Contributors

To this edition

 Steve Fanning Olivier Hallot

To previous editions

 Jean Hollis Weber Kieran Peckett Peter Schofield Dave Barton Olivier Hallot Steve Fanning Leo Moons Kees Kriek Gordon Bates Felipe Viggiano Zbyszek Zak Skip Masonsmith B. Antonio Fernández

## Feedback

• Note

Everything you send to a forum, including your email address and any other personal information that is written in the message, is publicly archived and cannot be deleted. Emails sent to the forum are moderated.

## Publication date and software version

Published March 2024. Based on LibreOffice 24.2 Community.Other versions of LibreOffice may differ in appearance and functionality.

Who is this book for?

This book is for beginner to advanced users of Calc, the spreadsheet component of LibreOffice. You may be new to spreadsheet software, or you may be familiar with another program. If you have never used LibreOffice before, or you want an introduction to all of its components, you might like to read the Getting Started Guide first. If you have never used spreadsheets before, you might also like to read a book or find some tutorials about the use of spreadsheets.

# What’s in this book?

This book introduces the features and functions of LibreOffice Calc. It is not a tutorial on using spreadsheets. Some chapters assume familiarity with basic spreadsheet usage when describing how to use Calc.

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 all of the data. Functions can be used to create formulas to perform complex calculations on data.

Some of the other features provided by Calc include:

• Database functions, to arrange, store, and filter data.

• Data statistics tools, to perform complex data analysis.

• Dynamic charts: a wide range of 2D, 3D charts, and sparklines

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

• Import and export of spreadsheets from multiple formats, including HTML, CSV, and Data Interchange Format.

# Where to get more help

This book, the other LibreOffice user guides, the built-in Help system, and user support systems assume that you are familiar with your computer and basic functions such as starting a program, opening and saving files.

## Help system

LibreOffice comes with an extensive online Help system. This is the first line of support for using LibreOffice. Windows and Linux users can choose to download and install the offline Help for use when not connected to the Internet; the offline Help is installed with the program on macOS.

To display the Help system, press F1 or select Help > LibreOffice Help from the Menu bar (Figure 1). If you do not have the offline help installed on your computer and you are connected to the Internet, your default browser will open the online Help pages on the LibreOffice website.

The Help menu includes links to other LibreOffice information and support facilities.

• Note

The following options indicated by a symbol are only accessible if your computer is connected to the Internet.

• What’s This? – For quick tips when a toolbar is visible, place the mouse pointer over any of the icons to see a small box (“tooltip”) with a brief explanation of the icon’s function. For a more detailed explanation, select Help > What’s This? and hold the pointer over the icon. In addition, you can choose whether to activate extended tips using Tools > Options > LibreOffice > General > Extended tips.

• User Guides – Opens your default browser at the English Documentation page of the LibreOffice website https://documentation.libreoffice.org/en/english-documentation/. There you will find copies of user guides and other useful information.

• Show Tip of the Day – Opens a dialog showing a useful piece of information to help expand your knowledge of LibreOffice.

• Search Commands – Opens a dialog where you can search for commands that are present in the main menu and then run a selected command. More information about this facility can be found in Chapter 1, Introduction.

• Get Help Online – Opens your default browser at the Ask LibreOffice forum of questions and answers from the LibreOffice community https://ask.libreoffice.org/c/english/5.

• Send Feedback – Opens your default browser at the Feedback page of the LibreOffice website https://www.libreoffice.org/get-help/feedback/. From there you can report bugs, suggest new features, and communicate with others in the LibreOffice community.

• Restart in Safe Mode – Opens a dialog where you can restart LibreOffice and reset the software to its default settings.

• Get Involved – Opens your default browser at the Get Involved page of the LibreOffice website https://www.libreoffice.org/community/get-involved/. There you can choose a topic of interest to help improve the program.

• Donate to LibreOffice – Opens your default browser at the Support LibreOffice page of the LibreOffice website https://donate.libreoffice.org/.

• Check for Updates – Opens a dialog and checks the LibreOffice website for updates to your version of the software.

• About LibreOffice – Opens a dialog, which displays information about the version of LibreOffice and the operating system you are using. This information will often be requested if you ask the community for help or assistance with the software. A button is provided to enable you to copy this information to the clipboard so that you can subsequently paste it into a forum post, an email, or a bug report.

## Other free online support

The LibreOffice community not only develops software, but provides free, volunteer-based support. In addition to the Help menu links above, there are other online community support options available, see Table 1.

Table 1: Free LibreOffice support

 Resource Support FAQs Answers to frequently asked questionshttps://wiki.documentfoundation.org/Faq Mailing lists Free community support is provided by a network of experienced usershttps://www.libreoffice.org/get-help/mailing-lists/ Forum As in the mailing lists above, but through forumhttps://community.documentfoundation.org/ Questions & Answers and Knowledge Base Free community assistance is provided in a question and answer formatted web service. Search similar topics or open a new one in https://ask.libreoffice.org/c/english/5 The service is available in several other languages; click on the language button to open a dialog for language selection. Native language support The LibreOffice website in various languageshttps://www.libreoffice.org/community/nlc/ Mailing lists for native languageshttps://wiki.documentfoundation.org/Local_Mailing_Lists Information about social networkinghttps://wiki.documentfoundation.org/Website/Web_Sites_services Accessibility options Information about available accessibility options https://www.libreoffice.org/get-help/accessibility/

## Paid support and training

You can also pay for support through service contracts from a vendor or consulting firm specializing in LibreOffice. For information about certified professional support, see The Document Foundation’s website: https://www.documentfoundation.org/gethelp/support/.

# What you see may be different

## Illustrations

LibreOffice runs on Windows, Linux, and macOS operating systems, each of which has several versions and can be customized by users (fonts, colors, themes, window managers). The illustrations in this guide were taken from a variety of computers and operating systems. Therefore, some illustrations will not look exactly like what you see on your computer display.

Also, some of the dialogs may be different because of the settings selected in LibreOffice. You can either use dialogs from your computer’s operating system or from LibreOffice. The differences affect mainly Open, Save, and Print dialogs. To change which dialogs are used, go to Tools > Options > LibreOffice > General and select or deselect the option Use LibreOffice dialogs.

## Icons

The LibreOffice community has created icons for several icon sets: Breeze, Colibre, Elementary, Karasa Jaga, Sifr, and Sukapura; some of these are also available in dark, SVG (Scalable Vector Graphics), and SVG+dark versions. Each user can select a preferred set. The icons in this guide have been taken from a variety of LibreOffice installations that use different sets of icons. The icons for some of the many tools available in LibreOffice may then differ from the ones used in this guide.

To change the icon set used, go to Tools > Options > LibreOffice > View. Choose from the drop-down list under Icon Theme.

• Note

The Galaxy, Oxygen, and Tango icon sets are no longer included as part of the standard installation package. They can be added back by downloading and installing the following extensions:https://extensions.libreoffice.org/extensions/galaxy-icon-themehttps://extensions.libreoffice.org/extensions/oxygen-icon-themehttps://extensions.libreoffice.org/en/extensions/show/tango-icon-theme-for-libreofficeSome of the previously included gallery backgrounds are now only available as an extension from:https://extensions.libreoffice.org/extensions/legacy-gallery-backgrounds

# Using LibreOffice on macOS

Some keystrokes and menu items are different on macOS from those used in Windows and Linux. Table 2 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 this guide.

Table 2: Example keystrokes for different environments

 Windows or Linux macOS equivalent Effect Tools > Options on Menu bar LibreOffice > Preferences Access to setup options Right-click Ctrl+click and/or right-click depending on computer setup Opens a context menu Ctrl or Control ⌘ and/or Cmd or Command, depending on keyboard Used with other keys Alt ⌥ and/or Alt or Option depending on keyboard Used with other keys F11 ⌘+T Open the Styles deck in the Sidebar

# What are all these things called?

The terms used in LibreOffice for most parts of the user interface (the parts of the program you see and use, in contrast to the behind-the-scenes code that actually makes it work) are the same as for most other programs.

A dialog is a special type of window. Its purpose is to inform you of something, or request input from you, or both. It provides controls to use to specify how to carry out an action. The technical names for common controls are shown in Figure 2. In most cases the technical terms are not used in this book, but it is useful to know them because the Help and other sources of information often use them.

In most cases, you can interact only with the dialog (not the document itself) as long as the dialog remains open. When you close the dialog after use (usually, clicking OK or another button saves your changes and closes the dialog), then you can again work with your document.

Some dialogs can be left open as you work, so you can switch back and forth between the dialog and your document. An example of this type is the Find and Replace dialog.

Figure 2: Dialog showing common controls

1. Tabbed page (not strictly speaking a control).

2. Radio buttons (only one can be selected at a time).

3. Checkbox (more than one can be selected at a time).

4. Spin box (click the up and down arrows to change the number shown in the text box next to it, or type in the text box).

5. Thumbnail or preview.

6. Drop-down list from which to select an item.

7. Push buttons.

It is based on code from Apache OpenOffice made available under the Apache License 2.0 but also includes software that differs from version to version under a variety of other Open Source licenses. New code is available under GNU Lesser General Public License (LGPL) 3.0 and MPL 2.0.

May I distribute LibreOffice to anyone? May I sell it? May I use it in my business?

Yes.

How many computers may I install it on?

As many as you like.

Is LibreOffice available in my language?

LibreOffice has been translated (localized) into over 100 languages, so your language is probably supported. Additionally, there are over 100 spelling, hyphenation, and thesaurus dictionaries available for languages, and dialects that do not have a localized program interface. The dictionaries are available by selecting the Dictionary tag filter from the LibreOffice Extension Center https://extensions.libreoffice.org/ .

LibreOffice is developed and maintained by volunteers and has the backing of several organizations.

Why do I need Java to run LibreOffice? Is it written in Java?

LibreOffice is not written in Java; it is mostly written in the C++ language. Java is one of several languages that can be used to extend the software. The Java JRE (Java Runtime Environment) is only required for some features. The most notable one is the HSQLDB relational database engine used by the Base component of LibreOffice. For Calc, Java is needed to run some of the solver engines described in Chapter 10, Data Analysis.

• Note

If you want to use LibreOffice features that require Java it is important that the correct 32 bit or 64 bit edition matches the installed version of LibreOffice.

How can I contribute to LibreOffice?

You can help with the development and user support of LibreOffice in many ways, and you do not need to be a programmer. To start, check out this webpage: https://whatcanidoforlibreoffice.org.

May I distribute the PDF of this book, or print and sell copies?

Yes, as long as you meet the requirements of one of the licenses in the copyright statement at the beginning of this book. You do not have to request special permission. We request that you share with the project some of the profits you make from sales of books, in consideration of all the work we have put into producing them.

Donate to LibreOffice: https://www.libreoffice.org/donate/.

# What's new in this guide

Information about the LibreOffice 24.2 Community release can be found in this announcement: https://blog.documentfoundation.org/blog/2024/01/31/libreoffice-24-2/.

This book has been updated from Calc Guide 7.6. It covers changes that are visible in the Calc user interface, including the following areas:

• Live font preview when using the Font Name menus on the Properties deck of the Sidebar and on the Formatting toolbar (Chapters 1 and 4).

• Interactions to switch between sheets operate cyclically (Chapter 1 and Appendix A).

• Option to view or hide column/row highlighting (Chapters 1 and 15).

• Additional metadata fields on the Description tab of the Properties dialog (Chapter 1).

• On Windows platforms, support for Alt+NumPad codes covering full Unicode range (Chapter 2 and Appendix A).

• Text description of highlighted character on drop-down from Insert Special Characters toolbar icon (Chapter 2).

• Password strength meters on several dialogs (Chapters 4, 7, and 12).

• Search field on Functions deck of Sidebar (Chapter 8).

• Support for FTP protocol removed from Hyperlink dialog (Chapter 11).

• Changes to auto-recovery and backup options (Chapter 15).

• Search function on Tools - Options dialogs (Chapter 15 and several figures throughout document).

• Security warnings converted from dialogs to infobars (Chapter 15).

• Modify button renamed Assign on Customize dialog (Chapter 15).

• Language Settings menu of Tools - Options dialogs renamed to Languages and Locales (various text and figure changes throughout document).

LibreOffice 24.2 Community also incorporates many other changes, including:

• New password-based ODF encryption that hides metadata better and is more resistant to tampering.

• Clarification of macro security options to make it clear exactly what is allowed and what is not.

• Accessibility improvements.

• Improvements in interoperability with Microsoft’s proprietary file formats.

Release Notes are here: https://wiki.documentfoundation.org/ReleaseNotes/24.2.