Transcription
CopyrightThis document is Copyright 2020 by the LibreOffice Documentation Team. Contributors are listedbelow. You may distribute it and/or modify it under the terms of either the GNU General PublicLicense (http://www.gnu.org/licenses/gpl.html), version 3 or later, or the Creative CommonsAttribution License (http://creativecommons.org/licenses/by/4.0/), version 4.0 or later.All trademarks within this guide belong to their legitimate owners.ContributorsThis book is adapted and updated from the LibreOffice 6.2 Calc Guide.To this editionSteve FanningGordon BatesLeo MoonsDrew JensenKees KriekRandolph GamoJean Hollis WeberSimon BrydonGabriel GodoyChristian ChenalPierre-Yves SamynAndy BrownHazel RussmanMartin SaffronCathy CrumbleySteve FanningMartin J FoxGabriel GodoyPeter SchofieldLaurent Balland-PoirierShelagh MantonStephen BuckBarbara M. TobiasDave BartonKees KriekZachary ParlimanTo previous editionsJohn A SmithAndrew PitonyakBarbara DupreyJohn A SmithPhilippe ClémentPeter KupferIain RobertsJared KobosOlivier HallotClaire WoodFeedbackPlease direct any comments or suggestions about this document to the Documentation Team’smailing list: hing you send to a mailing list, including your email address and any otherpersonal information that is written in the message, is publicly archived and cannot bedeleted.Publication date and software versionPublished June 2020. Based on LibreOffice 6.4.Documentation for LibreOffice is available at https://documentation.libreoffice.org/en/
ContentsCopyright. 2Preface.7Who is this book for?. 8What's in this book?. 8Where to get more help. 8What you see may be different.10Using LibreOffice on macOS.11What are all these things called?.11Frequently asked questions.12What’s new in LibreOffice Calc 6.4?.13Chapter 1 Introduction.14What is Calc?. 15Spreadsheets, sheets, and cells.15Calc main window. 15Creating, opening, and saving spreadsheets.21Password protection. 25Navigating within spreadsheets.25Selecting items in a spreadsheet.28Working with columns and rows.30Working with sheets. 32Viewing Calc. 34Using the Navigator. 36Using document properties.38Chapter 2 Entering, Editing, and Formatting Data.41Introduction. 42Entering data. 42Speeding up data entry. 47Merging and splitting cells. 51Sharing content between sheets.51Validating cell contents. 52Editing data. 56Formatting data. 60AutoFormat of cells and sheets.65Using themes. 66Value highlighting. 67Using conditional formatting.68Hiding and showing data. 72Sorting records. 79Find and replace. 82Chapter 3 Creating Charts and Graphs.86Introduction. 87Chart Wizard. 87Contents 3
Using the Sidebar to change chart settings.93Modifying charts. 95Selecting and moving chart elements.99Changing chart type. 100Titles, subtitles, and axis names.100Legends. 102Formatting chart backgrounds.103Data range and series. 104Axes. 111Data labels. 118Grids. 1223D charts. 123Trend and mean value lines.126Error bars. 131Adding drawing objects to charts.134Resizing, moving, and positioning charts.135Selecting multiple charts. 139Changing overall appearance of charts.139Copying, exporting, and deleting charts.140Gallery of chart types. 142Chapter 4 Using Styles and Templates.159What are styles? Why use them?.160Calc style types. 160Applying cell styles. 161Applying page styles. 163Creating new (custom) styles.163Managing styles. 173What is a template? Why use one?.174Using a template to create a spreadsheet.175Creating a template. 176Editing a template. 177Adding templates obtained from other sources.178Setting the default template.179Organizing templates. 180Chapter 5 Using Graphics.183Introduction. 184Adding graphics (images) to a spreadsheet.184Modifying images. 188Positioning, resizing, and arranging images.190Using LibreOffice’s drawing tools.192Additional tools. 194Using Fontwork. 195Chapter 6 Printing, Exporting, E-mailing, and Signing.197Printing. 1984 Calc Guide 6.4
Using print ranges. 202Page breaks. 206Printing options for page styles.207Headers and footers. 209Exporting to PDF. 211Exporting to other formats.217E-mailing spreadsheets. 217Digital signing of documents.218Removing personal data. 220Chapter 7 Using Formulas and Functions.221Introduction. 222Setting up a spreadsheet. 222Creating formulas. 223Understanding functions. 237Strategies for creating formulas and functions.243Finding and fixing errors. 245Examples of functions. 248Using wildcards and regular expressions in functions.250Advanced functions. 252Chapter 8 Using Pivot Tables.253Introduction. 254The Pivot Table Layout dialog.256Working with the results of the pivot table.267Using pivot table results elsewhere.280Using pivot charts. 283Chapter 9 Data Analysis.287Introduction. 288Consolidating data. 288Creating subtotals. 291Using scenarios. 297Using the Multiple Operations tool.301Using Goal Seek. 306Using the Solver. 308Using Statistics tools. 312Chapter 10 Linking Data.334Using multiple sheets. 335Referencing other documents.342Using hyperlinks and URLs.343Linking to external data. 348Linking to registered data sources.355Embedding spreadsheets. 361XML Source. 367Chapter 11 Sharing and Reviewing Spreadsheets.371Contents 5
Introduction. 372Sharing spreadsheets. 372Reviewing documents. 374Merging documents. 379Comparing documents. 380Saving versions. 380Chapter 12 Macros.382Introduction. 383Using the macro recorder. 383Write your own functions. 387Accessing cells directly. 396Sorting. 398Overview of BeanShell, JavaScript, and Python macros.399Conclusion. 405Chapter 13 Calc as a Simple Database.406Introduction. 407Associating a range with a name.408Sorting. 417Filtering. 421Useful database-like functions.429Chapter 14 Setting up and Customizing.433Introduction. 434LibreOffice options. 434Options for loading and saving documents.438Calc-specific options. 441Default colors for charts. 452AutoCorrect functions. 453Customizing the user interface.454Assigning macros to events.463Adding functionality with extensions.463Adding custom colors. 464Appendix A Keyboard Shortcuts.467Introduction. 468Windows / Linux keyboard shortcuts.468macOS keyboard shortcuts.476Appendix B Error Codes.484Introduction to Calc error codes.485Error codes displayed within cells.486General error codes. 486Index.489Alphabetical index. 4906 Calc Guide 6.4
Calc GuidePreface
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 youhave never used LibreOffice before, or you want an introduction to all of its components, you mightlike to read Getting Started with LibreOffice first. If you have never used spreadsheets before, youmight 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 usingspreadsheets. Some chapters assume familiarity with basic spreadsheet usage when describinghow to use Calc.You can enter data (usually numerical) in a spreadsheet and then manipulate this data to producecertain results. Alternatively, you can enter data and then use Calc in a ‘What if.’ manner bychanging 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. Dynamic charts: a wide range of 2D and 3D charts. Macros, for recording and executing repetitive tasks; scripting languages supported includeLibreOffice 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 PostScript. Export of spreadsheets into multiple formats, including HTML, CSV, and PDF.Where to get more helpThis book, the other LibreOffice user guides, the built-in Help system, and user support systemsassume that you are familiar with your computer and basic functions such as starting a program,opening and saving files.Help systemLibreOffice comes with an extensive Help system. This is the first line of support for usingLibreOffice. Windows and Linux users can choose to download and install the offline Help for usewhen 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). Ifyou 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.NoteThe following options indicated by a ‡ sign are only accessible if your computer isconnected to the Internet. 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 amore detailed explanation, select Help What's This? and hold the pointer over the icon.8 Where to get more help
In addition, you can choose whether to activate Extended Tips using Tools Options LibreOffice General. User Guides‡ – Opens your default browser at the Documentation page of the LibreOfficewebsite ocumentation/. There you will findcopies of User Guides and other useful information. Get Help Online‡ – Opens your default browser at the Ask LibreOffice forum of questionsand 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://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 window where you will have the option to restartLibreOffice and reset the software to its default settings. Donate to LibreOffice‡ – Opens your default browser at the Donation page of theLibreOffice website https://donate.libreoffice.org/. There you can choose to make adonation to support LibreOffice. License Information – outlining the licenses under which LibreOffice is made available. Check for Updates‡ – Opens a dialog window and checks the LibreOffice website forupdates to your version of the software. About LibreOffice – Opens a dialog window and displays information about the version ofLibreOffice and the operating system you are using. This information will often berequested if you ask the community for help or assistance with the software.Figure 1: The Help MenuOther free online supportThe 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 optionsavailable, see the table below.Free LibreOffice supportFAQsAnswers to frequently asked iling listsFree community support is provided by a network of experienced lists/Other free online support 9
Free LibreOffice supportQuestions & AnswersandKnowledge BaseFree community assistance is provided in a Question & Answer formattedweb service. Search similar topics or open a new one inhttps://ask.libreoffice.org/en/questionsThe service is available in several other languages; just replace /en/ withde, es, fr, ja, ko, nl, pt, tr, and many others in the web address above.Native languagesupportThe LibreOffice website in various /Mailing lists for native languageshttps://wiki.documentfoundation.org/Local Mailing ListsInformation about social ite/Web Sites servicesAccessibility optionsInformation about available accessibility i
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 the table below. Free LibreOffice support FAQs Answers to frequ
