Download Libre Office Calc Guide PDF

TitleLibre Office Calc Guide
TagsSpreadsheet Microsoft Excel Icon (Computing) Portable Document Format Human–Computer Interaction
File Size9.3 MB
Total Pages399
Table of Contents
                            Copyright
Preface
	Who is this book for?
	What's in this book?
	Where to get more help
		Help system
		Free online support
		Paid support and training
	What you see may be different
	Using LibreOffice on a Mac
	What are all these things called?
	Who wrote this book?
	Acknowledgements
	Frequently asked questions
Chapter 1 Introducing Calc
	What is Calc?
	Spreadsheets, sheets, and cells
	Parts of the main Calc window
		Title bar
		Menu bar
		Toolbars
			Displaying or hiding toolbars
			Palettes and tear-off toolbars
			Moving toolbars
			Docking/floating windows and toolbars
			Customizing toolbars
		Formatting toolbar
		Formula Bar
		Right-click (context) menus
		Individual cells
		Sheet tabs
		Status bar
	Starting new spreadsheets
		Starting a new document from a template
	Opening existing spreadsheets
	Opening CSV files
	Saving spreadsheets
		Saving a document automatically
		Saving as a Microsoft Excel document
		Saving as a CSV file
		Saving in other formats
	Password protection
		Protecting a spreadsheet
		Changing an existing password
	Navigating within spreadsheets
		Going to a particular cell
		Moving from cell to cell
			Customizing the effect of the Enter key
		Moving from sheet to sheet
	Selecting items in a sheet or spreadsheet
		Selecting cells
		Selecting columns and rows
		Selecting sheets
	Working with columns and rows
		Inserting columns and rows
		Deleting columns and rows
	Working with sheets
		Inserting new sheets
		Moving and copying sheets
			Using the mouse
			Using a dialog
		Deleting sheets
		Renaming sheets
	Viewing Calc
		Using zoom
		Freezing rows and columns
		Splitting the screen
			Splitting the screen horizontally
			Splitting the screen vertically
			Removing split views
	Using the Navigator
		Moving quickly through a document
		Choosing a drag mode
	Using document properties
Chapter 2 Entering, Editing, and Formatting Data
	Introduction
	Entering data using the keyboard
		Entering numbers
		Entering text
		Entering numbers as text
		Entering dates and times
		Entering special characters
			Inserting dashes
		Deactivating automatic changes
	Speeding up data entry
		Using the Fill tool on cells
			Using a fill series
			Defining a fill series
		Using selection lists
	Sharing content between sheets
	Validating cell contents
	Editing data
		Removing data from a cell
		Replacing all the data in a cell
		Changing part of the data in a cell
	Formatting data
		Formatting multiple lines of text
			Using automatic wrapping
			Using manual line breaks
		Shrinking text to fit the cell
		Formatting numbers
		Formatting the font
		Choosing font effects
		Setting cell alignment and orientation
		Formatting the cell borders
		Formatting the cell background
	Autoformatting cells and sheets
		Defining a new AutoFormat
	Formatting spreadsheets using themes
		Value Highlighting
	Using conditional formatting
	Hiding and showing data
		Outline group controls
		Filtering which cells are visible
	Sorting records
	Finding and replacing in Calc
		Using the Find toolbar
		Using the Find & Replace dialog
		Finding and replacing formulas or values
		Finding and replacing text
		Finding and replacing cell styles
		Using wildcards (regular expressions)
Chapter 3 Creating Charts and Graphs
	Introduction
	Creating a chart
		Choosing a chart type
		Changing data ranges and axes labels
		Selecting data series
		Adding or changing titles, legend, and grids
	Editing charts
		Changing the chart type
		Adding or removing chart elements
			Data labels
			Trend lines
			Mean value lines
			Y error bars
	Formatting charts
		Moving chart elements
		Changing the chart area background
		Changing the chart graphic background
		Changing colors
	Formatting 3D charts
		Rotation and perspective
		Appearance
		Illumination
		Rotating 3D charts interactively
	Formatting the chart elements
		Formatting axes and inserting grids
		Formatting data labels
		Hierarchical axis labels
		Choosing and formatting symbols
	Adding drawing objects to charts
	Resizing and moving the chart
		Using the Position and Size dialog
	Gallery of chart types
		Column charts
		Bar charts
		Pie charts
		Donut charts
		Area charts
		Line charts
		Scatter or XY charts
		Bubble charts
		Net charts
		Stock charts
		Column and line charts
Chapter 4 Using Styles and Templates in Calc
	What is a template?
	What are styles?
	Types of styles in Calc
		Cell styles
		Page styles
	Accessing styles
	Applying cell styles
		Using the Styles and Formatting window
		Using Fill Format mode
		Using the Apply Style list
		Assigning styles to shortcut keys
	Applying page styles
	Modifying styles
		Style organizer
		Cell style options
			Numbers
			Font
			Font effects
			Alignment
			Borders
			Background
			Cell protection
		Page style options
			Page
			Borders
			Background
			Header
			Footer
			Sheet
	Creating new (custom) styles
		Creating a new style using the Style dialog
		Creating a new style from a selection
		Creating a new style by dragging and dropping
	Copying and moving styles
	Deleting styles
	Creating a spreadsheet from a template
	Creating a template
	Editing a template
		Updating a spreadsheet from a changed template
	Adding templates using the Extension Manager
	Setting a default template
		Setting a custom template as the default
		Resetting the default template
	Associating a spreadsheet with a different template
	Organizing templates
		Creating a template folder
		Deleting a template folder
		Moving a template
		Deleting a template
		Importing a template
		Exporting a template
Chapter 5 Using Graphics in Calc
	Graphics in Calc
	Adding graphics (images)
		Inserting an image file
			Insert Picture dialog
			Drag and drop
		Linking an image file
			Embedding linked images
		Inserting an image from the clipboard
		Inserting an image from the Gallery
	Modifying images
		Using the Picture toolbar
		Choosing a graphics mode
		Using graphic filters
		Adjusting colors
		Setting transparency
		Customizing lines, areas, and shadows
		Cropping pictures
		Resizing an image
		Rotating a picture
	Using the picture context menu
		Text
		Position and Size
		Original Size
		Description
		Name
		Flip
		Assign Macro
		Group
	Using Calc’s drawing tools
		Set or change properties for drawing objects
		Resizing a drawing object
		Grouping drawing objects
	Positioning graphics
		Arranging graphics
		Anchoring graphics
		Aligning graphics
	Creating an image map
Chapter 6 Printing, Exporting, and E‑mailing
	Quick printing
	Controlling printing
		Selecting general printing options
		Printing multiple pages on a single sheet of paper
		Selecting sheets to print
	Using print ranges
		Defining a print range
		Adding to the print range
		Removing print ranges
		Editing a print range
		Viewing print ranges
		Printing rows or columns on every page
		Defining a custom print range
	Page breaks
		Inserting a page break
			Row break
			Column break
		Deleting a page break
	Printing options setup in page styles
	Headers and footers
		Setting a header or a footer
		Header or footer appearance
		Setting the contents of the header or footer
	Exporting to PDF
		Quick export to PDF
		Controlling PDF content and quality
			General page of PDF Options dialog
			Initial View page of PDF Options dialog
			User Interface page of PDF Options dialog
			Links page of PDF Options dialog
			Security page of PDF Options dialog
	Exporting to XHTML
	Saving as Web pages (HTML)
	E-mailing spreadsheets
		E-mailing a spreadsheet to several recipients
	Digital signing of documents
	Removing personal data
Chapter 7 Using Formulas and Functions
	Introduction
	Setting up a spreadsheet
		The trap of fixed values
		Lack of documentation
		Error-checking formulas
	Creating formulas
		Operators in formulas
		Operator types
			Arithmetic operators
			Comparative operators
			Text operators
			Reference operators
		Relative and absolute references
			Relative referencing
			Absolute referencing
		Order of calculation
		Calculations linking sheets
	Understanding functions
		Understanding the structure of functions
		Nested functions
		Function Wizard
	Strategies for creating formulas and functions
		Place a unique formula in each cell
		Break formulas into parts and combine the parts
		Use the Basic editor to create functions
	Finding and fixing errors
		Error messages
		Examples of common errors
			#DIV/0! Division by zero
			#VALUE No result and #REF Incorrect references
		Color coding for input
		The Detective
	Examples of functions
		Basic arithmetic and statistics
			Basic arithmetic
			Simple statistics
			Using these functions
		Rounding off numbers
			Rounding methods
	Using regular expressions in functions
	Advanced functions
Chapter 8 Using Pivot Tables
	Important note
	Introduction
		Database preconditions
		Data sources
			Calc spreadsheet
			Registered data source
		Creating a Pivot Table
	The DataPilot dialog
		Basic layout
		More options
		More settings for the fields: Field options
			Options for Data Fields
			Options for Row and Column Fields
			Options for Page Fields
	Working with the results of the DataPilot (the Pivot Table)
		Changing the layout
		Grouping rows or columns
		Grouping of categories with scalar values
		Grouping without automatic creation of intervals
		Sorting the result
			Select sort order from drop-down menus on each column heading
			Sort manually by using drag and drop
			Sort automatically
		Drilling (showing details)
		Filtering
		Updating (refreshing) changed values
		Cell formatting
		Using shortcuts
	Using Pivot Table results elsewhere
		The problem
		The solution: Function GETPIVOTDATA
			Syntax
			First syntax variation
			Second syntax variation
Chapter 9 Data Analysis
	Introduction
	Consolidating data
	Creating subtotals
		Using the SUBTOTAL function
		Using Data > Subtotals
	Using “what if” scenarios
		Creating scenarios
		Settings
		Changing scenarios
			Changing scenario properties
			Changing scenario cell values
		Working with scenarios using the Navigator
		Tracking values in scenarios
	Using other “what if” tools
		Multiple operations in columns or rows
			Calculating with one formula and one variable
			Calculating with several formulas simultaneously
		Multiple operations across rows and columns
			Calculating with two variables
	Working backwards using Goal Seek
		Goal Seek example
	Using the Solver
		Solver example
Chapter 10 Linking Calc Data
	Why use multiple sheets?
	Setting up multiple sheets
		Identifying sheets
		Inserting new sheets
		Inserting sheets from a different spreadsheet
		Renaming sheets
	Referencing other sheets
		Creating the reference with the mouse
		Creating the reference with the keyboard
	Referencing other documents
		Creating the reference with the mouse
		Creating the reference with the keyboard
	Hyperlinks and URLs
		Relative and absolute hyperlinks
		Creating hyperlinks
		Editing hyperlinks
		Removing hyperlinks
	Linking to external data
		Using the External Data dialog
		Using the Navigator
		How to find the required data range or table
	Linking to registered data sources
		Viewing data sources
		Editing data sources
		Launching Base to work on data sources
		Using data sources in Calc spreadsheets
	Embedding spreadsheets
		Object Linking and Embedding (OLE)
			Other OLE objects
			Non-linked OLE object
			Linked OLE object
		Dynamic Data Exchange (DDE)
			DDE link in Calc
			DDE link in Writer
Chapter 11 Sharing and Reviewing Documents
	Introduction
	Sharing documents (collaboration)
		Setting up a spreadsheet for sharing
		Opening a shared spreadsheet
		Saving a shared spreadsheet
	Recording changes
		Preparing a document for review (optional)
		Identifying copies of spreadsheets
		Recording changes (tutorial)
	Adding comments to changes
		Editing change comments
	Adding other comments
		Editing comments
		Formatting comments
		Finding comments using the Navigator
	Reviewing changes
		Viewing changes
		Accepting or rejecting changes
	Merging documents
	Comparing documents
	Saving versions
Chapter 12 Calc Macros
	Introduction
	Using the macro recorder
	Write your own functions
		Using a macro as a function
		Passing arguments to a macro
		Arguments are passed as values
		Writing macros that act like built-in functions
	Accessing cells directly
	Sorting
	Conclusion
Chapter 13 Calc as a Simple Database
	Introduction
	Associating a range with a name
		Named range
		Database range
	Sorting
	Filters
		Auto filters
		Standard filters
		Advanced filters
		Manipulating filtered data
	Calc functions similar to database functions
		Count and sum cells that match conditions: COUNTIF and SUMIF
		Ignore filtered cells using SUBTOTAL
		Using formulas to find data
			Search a block of data using VLOOKUP
			Search a block of data using HLOOKUP
			Search a row or column using LOOKUP
			Use MATCH to find the index of a value in a range
			Examples
		ADDRESS returns a string with a cell’s address
		INDIRECT converts a string to a cell or range
		OFFSET returns a cell or range offset from another
		INDEX returns cells inside a specified range
	Database-specific functions
	Conclusion
Chapter 14 Setting up and Customizing Calc
	Introduction
	Choosing options that affect all of LibreOffice
		User Data options
		Print options
		Color options
		Security options
			Security options and warnings
		Appearance options
	Choosing options for loading and saving documents
		General Load/Save options
		VBA Properties Load/Save options
		Microsoft Office Load/Save options
		HTML compatibility Load/Save options
	Choosing options for Calc
		General options for Calc
			Metrics section
			Updating section
			Input settings section
		View options for Calc
			Visual aids section
			Display section
			Objects section
			Window section
			Zoom section
		Calculate options
			Iterative references section
			Date section
			Other options
		Formula options
		Sort Lists options
		Changes options
		Compatibility options
		Grid options
			Grid section
			Resolution section
		Print options
	Changing default colors for charts
	Controlling Calc’s AutoCorrect functions
	Customizing the user interface
		Customizing the menu font
		Customizing menu content
			Creating a new menu
			Modifying existing menus
			Adding a command to a menu
			Modifying menu entries
		Customizing toolbars
			Creating a new toolbar
			Adding a command to a toolbar
			Choosing icons for toolbar commands
		Customizing keyboard shortcuts
			Example: Assigning styles to shortcut keys
			Saving changes to a file
			Loading a saved keyboard configuration
			Resetting the shortcut keys
			Running macros from key combinations
	Adding functionality with extensions
		Installing extensions
Appendix A Keyboard Shortcuts
	Introduction
	Note for Mac users
	Navigation and selection shortcuts
	Function key shortcuts
	Arrow key shortcuts
	Cell formatting shortcuts
	Pivot table shortcuts
Appendix B Description of Functions
	Functions available in Calc
		Terminology: numbers and arguments
	Mathematical functions
	Financial analysis functions
		A note about dates
		A note about interest rates
	Statistical analysis functions
	Date and time functions
	Logical functions
	Informational functions
	Database functions
	Array functions
	Spreadsheet functions
	Text functions
	Add-in functions
Appendix C Calc Error Codes
	Introduction to Calc error codes
	Error codes displayed within cells
	General error codes
Index
                        
Document Text Contents
Page 1

LibreOffice 3.4
Calc Guide

Using Spreadsheets in LibreOffice

Page 2

Copyright

This document is Copyright © 2012 by its contributors as 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/3.0/), version 3.0 or later. Note that Chapter 8,
Using the DataPilot, is licensed under the Creative Commons Attribution-Share Alike License,
version 3.0. All trademarks within this guide belong to their legitimate owners.

Contributors
John A Smith Jean Hollis Weber Martin J Fox
Andrew Pitonyak Simon Brydon Gabriel Godoy
Barbara Duprey

Cover art:
Drew Jensen Christoph Noack Klaus-Jürgen Weghorn
Jean Hollis Weber

Feedback
Please direct any comments or suggestions about this document to:
[email protected]

Acknowledgments
This book is adapted from Getting Started with OpenOffice.org 3.3. The contributors to that book
are listed on page 13.

Publication date and software version
Published 17 July 2012. Based on LibreOffice 3.4.

Documentation for LibreOffice is available at http://www.libreoffice.org/get-help/documentation

http://www.libreoffice.org/get-help/documentation
mailto:[email protected]
http://creativecommons.org/licenses/by-sa/3.0/
http://creativecommons.org/licenses/by/3.0/
http://www.gnu.org/licenses/gpl.html

Page 199

For example, if you drag region and employee into the Row Fields area, the sum will be divided
into the employees. Within the employees will be the listing for the different regions (see Figure
183).

Figure 183: DataPilot field order for analysis, and resulting layout in pivot table

Fields that are placed into the Page Fields area appear in the result above as a drop down list. The
summary in your result takes only that part of your base data into account that you have selected.
For example, if you use employee as a Page Field, you can filter the result shown for each
employee.

To remove a field from the white layout area, just drag it past the border and drop it (the cursor will
change to a crossed symbol), or select it and click the Remove button.

More options
To expand the DataPilot dialog and show more options, click More.

Figure 184: Expanded dialog of the DataPilot

Selection from
Shows the sheet name and the range of cells used for the Pivot Table.

Results to
Results to defines where your result will be shown. Selecting Results to as – undefined – and
entering a cell reference tells the Pivot Table where to show the results.1 An error dialog is
displayed if you fail to enter a cell reference. Selecting Results to as - new sheet – adds a
new sheet to the spreadsheet file and places the results there. The new sheet is named using
the format Pivot Table_sheet name_X; where X is the number of the table created, 1 for first,
2 for second and so on. For the source shown in Figure 3, the new sheet for the first table
produced, would be named Pivot Table_Umsatzliste_1. Each new sheet is inserted next to
the source sheet.

1 In this case the word - undefined – is misleading because the output position is in fact defined.

204 LibreOffice 3.4 Calc Guide

Page 200

Ignore empty rows
If the source data is not in the recommended form, this option tells the Pivot Table to ignore
empty rows.

Identify categories
With this option selected, if the source data has missing entries in a list and does not meet the
recommended data structure (see Figure 185), the Pivot Table adds it to the listed category
above it. If this option is not chosen, then the Pivot Table inserts (empty) (see Figure 187).

Figure 185: Example of data with missing entries in Column A

The option Identify categories ensures that in this example rows 3 and 4 are included for the
product Apples and row 6 is included for Pears (see Figure 186).

Figure 186: Pivot Table result with Identify categories selected

Without category recognition, the Pivot Table shows an (empty) category (Figure 187).

Figure 187: Pivot Table result without Identify categories selected

Logically, the behavior with category recognition is better. A list showing missing entries is also
less useful, because you cannot use functions such as sorting or filtering.

Total columns, Total rows
With these options you can decide if the Pivot Table shows an extra row with the sums of each
column, or if it adds on the very right a column with the sums of each row. In some cases, an
added total sum is meaningless, for example if your entries are accumulated or the result of
comparisons.

Add filter
Use this option to add or hide the cell labeled Filter above the Pivot Table results. This cell is a
convenient button for additional filtering options within the Pivot Table.

Enable drill to details
With this option enabled, if you double-click on a single data cell, including a cell produced from
Total columns or Total rows, in the Pivot Table result, a new sheet opens giving a detailed

Chapter 8 Using Pivot Tables 205

Page 398

saving as CSV 29
saving as Microsoft Excel 28
saving shared 269
setting up sharing 268
sharing 268

statistical analysis functions 370
status bar 23
stock charts 102
strikethrough 62
Style dialog 113
style organizer 110
styles

accessing 107
assign to shortcut keys 109
assigning to shortcut keys 345
cell 107
copy 114
create by drag and drop 114
create from selection 114
create using Style dialog 113
delete 116
description 106
linking 113
modifying 110
move 114
organizer 110
page 107, 109
types supported 106

Styles and Formatting window 108
subtotals 226
support 10
synchronize axes 337
system font 339

T
tear-off toolbars 18
template

associate spreadsheet with different template
121
create spreadsheet from 116
delete 122
delete folder 121
description 106
edit 117
export 122
Extension Manager 119
folder 121
import 122
move 122
organize 121
reset default 120
set custom template as default 120
update document styles from 118

text functions 388
text operators 170
text overflow 333

text, data entry 50
time functions 376
title bar 16
titles of charts 81
toolbars 17

adding commands 344
choosing icons for commands 344
creating 343
description 18

tooltip 18
track changes 270
transparency 131
trend lines 85

U
underlining 61
unsaved changes indicator 23
URL Recognition 251
user data options 322
User guides 11
user interface, customizing 339
user interface, parts of 12

V
validating cell contents 55
value highlighting 333
VBA properties load/save options 328
versions

saving 279
viewing Calc 42
viewing data

filtering 68
hiding and showing 67
outline group controls 67

visible grid 337

W
wall of chart 82
wildcards 75

X
XML 327
XY charts 100

Y
Y error bars 86

Z
zoom 42

dialog 42
slider 24

#
#DIV/0! Division by zero 188
#REF Incorrect references 189
#VALUE No result 189

Index 407

Page 399

408 LibreOffice 3.4 Calc Guide

Similer Documents