[PDF] [PDF] LibreOffice Calc 70 Guide

View - Download [PDF] LibreOffice Calc 70 Guide

Previous PDF

Next PDF

Copyright

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

Contributors

This book is adapted and updated from the LibreOffice 6.4 Calc Guide.

To this edition

Steve FanningGordon BatesKees Kriek

Annie NguyenFelipe ViggianoSamantha Hamilton

Olivier HallotJean Hollis Weber

To previous editions

John A SmithJean Hollis WeberMartin J Fox

Andrew Pitonyak Simon BrydonGabriel Godoy

Barbara DupreyGabriel GodoyPeter Schofield

John A SmithChristian ChenalLaurent Balland-Poirier Philippe ClémentPierre-Yves SamynShelagh Manton

Peter KupferAndy BrownStephen Buck

Iain RobertsHazel RussmanBarbara M. Tobias

Jared KobosMartin SaffronDave Barton

Olivier HallotCathy CrumbleyKees Kriek

Claire WoodSteve FanningZachary Parliman

Gordon BatesLeo MoonsRandolph Gamo

Drew Jensen

Feedback

Please direct any comments or suggestions about this document to the Documentation Team's mailing list: documentation@global.libreoffice.org. Note Everything you send to a mailing list, including your email address and any other personal information that is written in the message, is publicly archived and cannot be deleted.

Publication date and software version

Published November 2020. Based on LibreOffice 7.0. Documentation for LibreOffice is available at http://documentation.libreoffice.org/en/

Contents

Who is this book for?.................................................................................................................8

What's in this book?..................................................................................................................8

Where to get more help.............................................................................................................8

What you see may be different................................................................................................11

Using LibreOffice on macOS...................................................................................................11

What are all these things called?............................................................................................12

Frequently asked questions....................................................................................................13

What's new in LibreOffice Calc 7.0?........................................................................................14

Chapter 1 Introduction....................................................................................................15

What is Calc?..........................................................................................................................16

Spreadsheets, sheets, and cells.............................................................................................16

Calc main window...................................................................................................................16

Creating, opening, and saving spreadsheets..........................................................................23

Password protection................................................................................................................27

Navigating within spreadsheets...............................................................................................27

Selecting items in a spreadsheet............................................................................................31

Working with columns and rows..............................................................................................33

Working with sheets................................................................................................................35

Viewing Calc...........................................................................................................................37

Using the Navigator.................................................................................................................39

Using document properties.....................................................................................................41

Regular expressions...............................................................................................................43

Chapter 2 Entering, Editing, and Formatting Data.......................................................48

Entering data...........................................................................................................................49

Speeding up data entry...........................................................................................................54

Merging and splitting cells.......................................................................................................58

Sharing content between sheets.............................................................................................58

Validating cell contents............................................................................................................59

Editing data.............................................................................................................................63

Formatting data.......................................................................................................................68

AutoFormat of cells and sheets...............................................................................................75

Using themes..........................................................................................................................76

Value highlighting....................................................................................................................76

Using conditional formatting....................................................................................................77

Hiding and showing data.........................................................................................................83

Sorting records........................................................................................................................91

Find and replace.....................................................................................................................94

Chapter 3 Creating Charts and Graphs.........................................................................99

LibreOffice Calc 7.0 Guide | 3

Chart Wizard.........................................................................................................................100

Using the Sidebar to change chart settings...........................................................................107

Modifying charts....................................................................................................................109

Selecting and moving chart elements....................................................................................113

Changing chart type..............................................................................................................114

Titles, subtitles, and axis names............................................................................................114

Formatting chart backgrounds...............................................................................................118

Data range and series...........................................................................................................119

Data labels............................................................................................................................134

3D charts...............................................................................................................................140

Trend and mean value lines..................................................................................................144

Error bars..............................................................................................................................150

Adding drawing objects to charts..........................................................................................153

Resizing, moving, and positioning charts..............................................................................154

Selecting multiple charts.......................................................................................................158

Changing overall appearance of charts.................................................................................159

Copying, exporting, and deleting charts................................................................................160

Gallery of chart types............................................................................................................162

Chapter 4 Using Styles and Templates.......................................................................179

What are styles? Why use them?..........................................................................................180

Calc style types.....................................................................................................................180

Applying cell styles................................................................................................................181

Applying page styles.............................................................................................................184

Creating new (custom) styles................................................................................................184

Managing styles....................................................................................................................195

What is a template? Why use one?.......................................................................................197

Using a template to create a spreadsheet.............................................................................197

Creating a template...............................................................................................................198

Editing a template.................................................................................................................199

Adding templates obtained from other sources.....................................................................200

Setting the default template...................................................................................................201

Organizing templates............................................................................................................202

Chapter 5 Using Graphics............................................................................................205

Adding graphics (images) to a spreadsheet..........................................................................206

Modifying images..................................................................................................................210

Positioning, resizing, and arranging images..........................................................................212

Using LibreOffice's drawing tools..........................................................................................215

Additional tools......................................................................................................................218

Using Fontwork.....................................................................................................................218

Chapter 6 Printing, Exporting, E-mailing, and Signing.............................................220

4| LibreOffice Calc 7.0 Guide

Using print ranges.................................................................................................................225

Page breaks..........................................................................................................................230

Printing options for page styles.............................................................................................231

Headers and footers..............................................................................................................233

Exporting to PDF...................................................................................................................236

Exporting to other formats.....................................................................................................243

E-mailing spreadsheets.........................................................................................................243

Digital signing of documents.................................................................................................243

Removing personal data.......................................................................................................246

Chapter 7 Using Formulas and Functions..................................................................247

Setting up a spreadsheet......................................................................................................248

Creating formulas..................................................................................................................249

Understanding functions.......................................................................................................263

Strategies for creating formulas and functions......................................................................269

Finding and fixing errors........................................................................................................272

Examples of functions...........................................................................................................275

Volatile / non-volatile functions..............................................................................................277

Using wildcards and regular expressions in functions...........................................................278

Advanced functions...............................................................................................................280

Chapter 8 Using Pivot Tables.......................................................................................282

The Pivot Table Layout dialog...............................................................................................285

Working with the results of the pivot table.............................................................................296

Using pivot table results elsewhere.......................................................................................308

Using pivot charts..................................................................................................................311

Chapter 9 Data Analysis...............................................................................................316

Consolidating data................................................................................................................317

Creating subtotals.................................................................................................................320

Using scenarios.....................................................................................................................326

Using the Multiple Operations tool........................................................................................330

Using Goal Seek...................................................................................................................336

Using the Solver....................................................................................................................338

Using Statistics tools.............................................................................................................343

Chapter 10 Linking Data...............................................................................................366

Using multiple sheets............................................................................................................367

Referencing other documents...............................................................................................374

Using hyperlinks and URLs...................................................................................................375

Linking to external data.........................................................................................................381

Linking to registered data sources........................................................................................388

Embedding spreadsheets......................................................................................................394

XML Source..........................................................................................................................400

LibreOffice Calc 7.0 Guide | 5

Chapter 11 Sharing and Reviewing Spreadsheets....................................................404

Sharing spreadsheets...........................................................................................................405

Reviewing documents...........................................................................................................407

Merging documents...............................................................................................................413

Comparing documents..........................................................................................................413

Saving versions.....................................................................................................................414

Chapter 12 Macros........................................................................................................416

On Visual Basic for Application (VBA) compatibility...............................................................417

Using the macro recorder......................................................................................................417

Write your own functions.......................................................................................................422

Accessing cells directly.........................................................................................................431

Overview of BeanShell, JavaScript, and Python macros.......................................................434

Working with VBA macros.....................................................................................................440

Chapter 13 Calc as a Database....................................................................................444

Associating a range with a name..........................................................................................446

Useful database-like functions..............................................................................................468

Chapter 14 Setting up and Customizing.....................................................................472

LibreOffice options................................................................................................................473

Options for loading and saving documents...........................................................................477

Calc-specific options.............................................................................................................481

Default colors for charts........................................................................................................491

AutoCorrect functions............................................................................................................492

Customizing the user interface..............................................................................................493

Assigning macros to events..................................................................................................502

Adding functionality with extensions......................................................................................502

Adding custom colors............................................................................................................503

Appendix A Keyboard Shortcuts.................................................................................505

Windows / Linux keyboard shortcuts.....................................................................................506

macOS keyboard shortcuts...................................................................................................515

Appendix B Error Codes...............................................................................................523

Introduction to Calc error codes............................................................................................524

Error codes displayed within cells.........................................................................................525

General error codes..............................................................................................................525

Alphabetical Index.................................................................................................................528

6| LibreOffice Calc 7.0 Guide

Calc Guide

Preface

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 and 3D charts. •Macros, for recording and executing repetitive tasks; scripting languages supported include LibreOffice Basic, Python, BeanShell, and JavaScript. •Ability to open, edit, and save Microsoft Excel and other spreadsheets. •Import of spreadsheets from multiple formats, including HTML, CSV, and Data

Interchange Format.

•Export of spreadsheets into multiple formats, including HTML, CSV, Data Interchange

Format, and PDF.

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 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 LibreOffice Help from the Help menu (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.

8 | LibreOffice Calc 7.0 Guide

Figure 1: The Help menu

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.

Note The What's This? facility and extended tips are being improved but in LibreOffice 7.0 this work is incomplete and so they are not fully functional. •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 bit of information to help expand your knowledge of LibreOffice.quotesdbs_dbs41.pdfusesText_41

[PDF] libreoffice calc fonction

[PDF] libreoffice calcul automatique

[PDF] ln(a)/ln(b)

[PDF] a t on besoin d admirer des individus extraordinaires

[PDF] bts culture générale 2017 corrigé

[PDF] notre societe a t elle besoin de heros

[PDF] ln(a+b) = ln(a) + ln(b)

[PDF] ln(a)*ln(b)

[PDF] corrige culture générale bts 2017

[PDF] curriculum et programme scolaire

[PDF] curriculum scolaire définition

[PDF] curriculum scolaire pdf

[PDF] qu'est-ce qu'un programme d'enseignement

[PDF] définition d'un programme scolaire pdf

[PDF] qu'est ce qu'un programme scolaire