Calc Guide 7.4

Preface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 edition
Jean Hollis WeberDave Barton
Leo MoonsFelipe Viggiano

Kieran Peckett
Olivier Hallot
Kees Kriek
Zbyszek Zak

Peter Schofield
Steve Fanning
Gordon Bates

Feedback
Please direct any comments or suggestions about this document to the Documentation Team’sforum 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.

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

Who is this book for?......................................................................................................4
What’s in this book?........................................................................................................4
Where to get more help...................................................................................................4Help system................................................................................................................................4
Other free online support............................................................................................................6Paid support and training............................................................................................................6What you see may be different.......................................................................................6
Illustrations..................................................................................................................................6Icons...........................................................................................................................................7Using LibreOffice on macOS..........................................................................................7
What are all these things called?...................................................................................7
Frequently asked questions........................................................................................... 8
What’s new in LibreOffice Calc 7.4 Community?..........................................................9Preface | 3

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 toproduce 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 ofthe 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 and 3D charts.
• 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 connectedto 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.

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

4 | Preface

Figure 1: The Help menu
• What’s This? – For quick tips when a toolbar is visible, place the mouse pointer over anyof 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 overthe 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 theLibreOffice 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 bit of information to help
expand your knowledge of LibreOffice.
• Search Commands – Opens a dialog where you can search for commands that arepresent in the main menu and then run a selected command. More information about thisfacility 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/en/questions/.
• Send Feedback‡ – Opens your default browser at the Feedback page of the LibreOfficewebsite https://community.documentfoundation.org/c/documentation/loguides/. From
there you can report bugs, suggest new features, and communicate with others in theLibreOffice community.
• Restart in Safe Mode – Opens a dialog where you can restart LibreOffice and reset thesoftware to its default settings.
• Get Involved‡ – Opens your default browser at the Get Involved page of the LibreOfficewebsite 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 Donation page of theLibreOffice website https://donate.libreoffice.org/.
Where to get more help | 5

• License Information – Outlines the licenses under which LibreOffice is made available.
• Check for Updates‡ – Opens a dialog and checks the LibreOffice website for updates toyour version of the software.
• About LibreOffice – Opens a dialog, which displays information about the version ofLibreOffice 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 isprovided to enable you to copy this information to the clipboard so that you cansubsequently 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
FAQs

Answers to frequently asked questions
https://wiki.documentfoundation.org/Faq

Mailing lists

Free community support is provided by a network of experiencedusers
https://www.libreoffice.org/get-help/mailing-lists/

Forum

As in the mailing lists above, but through forum
https://community.documentfoundation.org/

Questions & AnswersandKnowledge BaseFree 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/en/questions.
The service is available in several other languages; just replace /en/with de, es, fr, ja, ko, nl, pt, tr, and many others in the web address
above.

Native language
supportThe LibreOffice website in various languages
https://www.libreoffice.org/community/nlc/
Mailing lists for native languages
https://wiki.documentfoundation.org/Local_Mailing_Lists
Information about social networking
https://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.
6 | Preface

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 toTools > Options > LibreOffice > General and select or deselect the option Use LibreOfficedialogs.

Icons
The LibreOffice community has created icons for several icon sets: Colibre, Breeze, Breeze
Dark, Elementary, Karasa Jaga, Sifr, Sifr Dark, and Sukapura. 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 thendiffer from the ones used in this guide.
To change the icon set used, go to Tools > Options > LibreOffice > View. Choose from thedrop-down list under Icon Style.

NoteThe Galaxy, Oxygen, and Tango icon sets are no longer included as part of thestandard installation package. They can be added back by downloading andinstalling the following extensions:
https://extensions.libreoffice.org/extensions/galaxy-icon-theme
https://extensions.libreoffice.org/extensions/oxygen-icon-theme
https://extensions.libreoffice.org/en/extensions/show/tango-icon-theme-forlibreofficeSome 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 andLinux. Table 2 gives some common substitutions for the instructions in this document. For a moredetailed list, see the application Help and Appendix A, Keyboard Shortcuts, to this guide.
Table 2: Example keystrokes for different environments
Windows or LinuxmacOS equivalent

EffectTools > Options
menu selection

LibreOffice > Preferences

Access setup options

Right-click

Control+click and/or rightclick depending on computer
setupOpen a context menu

Ctrl (Control)⌘ (Command)

Used with other keys

F11

⌘+T

Open the Styles deck in theSidebar

Using LibreOffice on macOS | 7

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 technicalnames for common controls are shown in Figure 2. In most cases the technical terms are notused 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.

8 | PrefaceFrequently asked questions
How is LibreOffice Licensed?
LibreOffice is distributed under the Open Source Initiative (OSI) approved Mozilla PublicLicense (MPL). See https://www.libreoffice.org/about-us/licenses/.
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 isprobably supported. Additionally, there are over 100 spelling, hyphenation, and thesaurus
dictionaries available for languages, and dialects that do not have a localized programinterface. The dictionaries are available by selecting the Dictionary tag filter from theLibreOffice Extension Center https://extensions.libreoffice.org/.
How can you make it for free?
LibreOffice is developed and maintained by volunteers and has the backing of severalorganizations.
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 ofseveral languages that can be used to extend the software. The Java JRE (Java RuntimeEnvironment) 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 isneeded to run some of the solver engines described in Chapter 9, Data Analysis.
Java is available at no cost. More information and download links to the appropriate
edition for your operating system can be found at:https://java.com/en/download/manual.jsp.

Note
If you want to use LibreOffice features that require Java it is important that thecorrect 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, andyou 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 copyrightstatement 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 ofbooks, in consideration of all the work we have put into producing them.
Donate to LibreOffice: https://www.libreoffice.org/donate/.

Frequently asked questions | 9What’s new in LibreOffice Calc 7.4 Community?
Information about the LibreOffice 7.4 Community release can be found in this announcement:
https://blog.documentfoundation.org/blog/2022/08/18/libreoffice-7-4-community/.
This user guide has been updated from Calc Guide 7.2. Significant changes include:• Calc now supports and uses 16,384 columns.
• Sort options and sort items are easier to access with the most frequently used sort
options on the first tab (see Chapter 2).
• Easier to access a sheet in a spreadsheet with a large number of sheets. (Sheet >Navigate > Go to Sheet) opens a dialog to select or search for sheets by name (seeChapter 7).
• Hidden columns/rows can be highlighted with an indicator using (View > Hidden
Row/Column Indicator) (see Chapter 1).
• Added COUNTA, PRODUCT, STDEV, STDEVP, VAR, VARP functions to drop-down
AutoSum widget on Formula bar (see Chapter 1).
• AutoInput has extended options for handling multiple suggestion matches (see Chapter2).
Other speed improvements or performance enhancements to this version include:•Opening XLSM, XLSX files, import / export of CSV files, and export of Excel files.

•Handling of files with many columns, many formulas or large charts.

•Lookup functions, COUNTIF, and SUMIFS, especially with unsorted data.

Full Release Notes for versions 7.3 and 7.4 can be found at:
7.3: https://wiki.documentfoundation.org/ReleaseNotes/7.3
7.4: https://wiki.documentfoundation.org/ReleaseNotes/7.4

10 | Preface