OpenOffice Calc 3.x User Guide

Table of Contents

Introducing Calc

What is Calc?

Spreadsheets, sheets and cells

Entering, Editing, and Formatting Data

Parts of the main Calc window

Starting new spreadsheets and opening existing spreadsheets

Starting new spreadsheets

Opening existing spreadsheets

Opening CSV files

Saving spreadsheets

Saving spreadsheets

Password protection

Saving a document automatically

Saving as a Microsoft Excel document

Saving as a CSV file

Saving in other formats

Navigating within spreadsheets

Going to a particular cell

Moving from cell to cell

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

Deleting sheets

Renaming sheets

Viewing Calc

Using zoom

Freezing rows and columns

Splitting the screen

Using the Navigator

Moving quickly through a document

Choosing a drag mode

Creating Charts and Graphs

Entering data using the keyboard

Entering numbers

Entering text

Entering numbers as text

Entering dates and times

Entering special characters

Deactivating automatic changes

Speeding up data entry

Using the Fill tool on cells

Using selection lists

Using the DataForm extension

Sharing content between sheets

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

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, themes, and conditional formatting

Autoformatting cells and sheets

Defining a new AutoFormat

Formatting spreadsheets using themes

Using conditional formatting

Hiding and showing data

Outline group controls

Filtering which cells are visible

Sorting records

Finding and replacing in Calc

Finding and replacing formulas or values

Finding and replacing text

Finding and replacing cell styles

Using wildcards (regular expressions)

Using Styles and Templates in Calc

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

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

Using the Object Properties dialog

Scaling axes

Formatting data labels and axis labels

Choosing and formatting symbols

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

Net charts

Stock charts

Column and line charts

Graphics in Calc

Introduction

Types of styles in Calc

Cell styles

Page styles

Accessing and applying styles

Accessing styles

Applying cell styles

Applying page styles

Modifying styles

Style organizer

Cell style options

Page style options

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, moving, and deleting styles

Copying and moving styles

Deleting styles

Creating a spreadsheet from a template

Creating and editing 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

Printing, Exporting, and E-mailing

Quick printing

Controlling printing

Printer options

Selecting sheets to print

Selecting the page order, details, and scale

Adding graphics (images)

Inserting an image from a file

Inserting an image using drag and drop

Inserting an image from a graphics program

Inserting an image from the Gallery

Inserting an image from the clipboard

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

Arrangement

Anchoring

Graphic file types supported

Using Functions and Creating Formulas

Using functions

Using print ranges

Defining a print range

Adding to the print range

Removing a print range

Editing a print range

Printing rows or columns on every page

Defining a custom print range

Page breaks, headers and footers

Page breaks

Headers and footers

Exporting to PDF

Quick export to PDF

Controlling PDF content and quality

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

Using the DataPilot

Introduction

Examples with step by step descriptions

Understanding functions

Understanding the structure of functions

Advanced structure

Creating formulas

Introduction

Operators in formulas

Operator types

Order of calculation

Calculations linking sheets

Strategies for creating formulas

Finding and fixing errors

Error messages

Color coding for input

The Detective

Examples: Common errors

Examples of functions

Basic arithmetic and statistic functions

Rounding off numbers

Using regular expressions in functions

Data Analysis

DataPilot example 1: Sales volume overview

DataPilot example 2: Timekeeping

DataPilot example 3: Frequency distribution

DataPilot functions in detail

The database (preconditions)

Start

Data source

The DataPilot dialog

Layout and grouping of results

Start the dialog

Change layout by using drag and drop

Grouping rows or columns

Grouping of categories with scalar values

Grouping of categories with date or time values

Grouping without the automatic creation of intervals

Sorting, drilling, filtering, refreshing

Sorting of the results

Drill (show details)

Filtering

Updating (refreshing) changed values

Multiple data fields

Shortcuts

Function GETPIVOTDATA

Difficulty

Syntax

Linking Calc Data: Sharing data in and out of Calc

Consolidating data

Creating subtotals

Using “what if” scenarios

Creating scenarios

Settings

Working with scenarios using the Navigator

Multiple operations: Providing alternate versions of formulas

Multiple operations in columns or rows

Multiple operations across rows and columns

Working backwards using Goal Seek

Goal Seek example

Using the Solver

Sharing and Reviewing Documents

Using multiple sheets

Why use multiple sheets?

Identifying sheets

Inserting new sheets

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

Creating hyperlinks

Editing hyperlinks

Removing hyperlinks

Relative and absolute hyperlinks

Linking to external data

Using the External Data dialog

Using the Navigator

How to find the required data range or table

Embedding spreadsheets

Object Linking and Embedding (OLE)

Dynamic Data Exchange (DDE)

Calc Macros

Introduction: Automating repetitive tasks

Sharing documents among reviewers

Preparing a document for review (optional)

Identifying copies of spreadsheets

Recording changes

Adding comments to changes

Editing comments

Adding notes

Editing notes

Formatting notes

Finding notes using the Navigator

Reviewing changes

Viewing changes

Accepting or rejecting changes

Merging documents

Comparing documents

Saving versions

Calc Macros

Introduction

Using the macro recorder

Write your own functions

Using a macro as a function

Passing arguments to a macro

Writing macros that act like built-in functions

Accessing cells directly

Sorting

Conclusion

Keyboard Shortcuts

Introduction

Associating a range to a name

Named range

Database range

Sorting data

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, INDIRECT, OFFSET, INDEX

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

Description of Functions

Navigation and selection shortcuts

Function and arrow key shortcuts

Cell formatting shortcuts

DataPilot shortcut keys

Calc Error Codes

Introduction to Calc error codes