OpenOffice.org 3.3 Calc Guide

 3.3 Calc Guide

Using Spreadsheets in 3.3

Copyright

This document is Copyright ? 2005?2011 by its contributors as listed below. You may distribute it and/or modify it under the terms of either the GNU General Public License (), version 3 or later, or the Creative Commons Attribution License (), 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.

Contributors

Rick Barnes James Andrew Stephen Buck T. J. Frazier Spencer E. Harpe John Kane Jared Kobos Alexandre Martins Andrew Pitonyak Gary Schnabl Sowbhagya Sundaresan John Viestenz Sharon Whiston Michele Zarri

Peter Kupfer Krishna Aradhi Bruce Byfield Stigant Fyrwitful Regina Henschel Kirk Abbott Sigrid Kronenberger Kashmira Patel Iain Roberts Rob Scott Nikita Telang Jean Hollis Weber Claire Wood Magnus Adielsson

Martin Fox Andy Brown Nicole Cairns Ingrid Halama Peter Hillier-Brook Emma Kirsopp Shelagh Manton Anthony Petrillo Hazel Russman Jacob Starr Barbara M Tobias Stefan Weigel Linda Worthington Sandeep Samuel Medikonda

Feedback

Please direct any comments or suggestions about this document to: authors@documentation.

Publication date and software version

Published 18 April 2011. Based on 3.3.

You can download an editable version of this document from

Contents

Copyright................................................................................................................... 2 Note for Mac users.................................................................................................... 8

Chapter 1 Introducing Calc.......................................................................................................... 9 What is Calc?........................................................................................................... 10 Spreadsheets, sheets, and cells............................................................................... 10 Parts of the main Calc window................................................................................ 10 Starting new spreadsheets...................................................................................... 18 Opening existing spreadsheets................................................................................ 20 Opening CSV files.................................................................................................... 21 Saving spreadsheets................................................................................................ 22 Password protection................................................................................................. 24 Navigating within spreadsheets.............................................................................. 26 Selecting items in a sheet or spreadsheet...............................................................29 Working with columns and rows.............................................................................. 32 Working with sheets................................................................................................. 33 Viewing Calc............................................................................................................ 34 Using the Navigator................................................................................................. 38 Using document properties...................................................................................... 40

Chapter 2 Entering, Editing, and Formatting Data....................................................................43 Introduction............................................................................................................. 44 Entering data using the keyboard...........................................................................44 Speeding up data entry............................................................................................ 46 Sharing content between sheets.............................................................................. 49 Validating cell contents............................................................................................ 50 Editing data.............................................................................................................. 52 Formatting data....................................................................................................... 53 Autoformatting cells and sheets..............................................................................59 Formatting spreadsheets using themes...................................................................60 Using conditional formatting................................................................................... 60 Hiding and showing data......................................................................................... 61 Sorting records........................................................................................................ 63 Finding and replacing in Calc.................................................................................. 65

Chapter 3 Creating Charts and Graphs...................................................................................... 69 Introduction............................................................................................................. 70 Creating a chart....................................................................................................... 70 Editing charts........................................................................................................... 74 Formatting charts.................................................................................................... 79 Formatting 3D charts............................................................................................... 82 Formatting the chart elements................................................................................85

3.3 Calc Guide

3

Adding drawing objects to charts............................................................................ 87 Resizing and moving the chart................................................................................ 88 Gallery of chart types............................................................................................... 89

Chapter 4 Using Styles and Templates in Calc...........................................................................98 What is a template?................................................................................................. 99 What are styles?....................................................................................................... 99 Types of styles in Calc.............................................................................................. 99 Accessing styles..................................................................................................... 100 Applying cell styles................................................................................................ 101 Applying page styles.............................................................................................. 102 Modifying styles..................................................................................................... 103 Creating new (custom) styles.................................................................................106 Copying and moving styles.................................................................................... 107 Deleting styles....................................................................................................... 109 Creating a spreadsheet from a template...............................................................109 Creating a template............................................................................................... 110 Editing a template.................................................................................................. 111 Adding templates using the Extension Manager...................................................113 Setting a default template..................................................................................... 113 Associating a spreadsheet with a different template.............................................114 Organizing templates............................................................................................. 115

Chapter 5 Using Graphics in Calc............................................................................................. 117 Graphics in Calc..................................................................................................... 118 Adding graphics (images)...................................................................................... 118 Modifying images................................................................................................... 123 Using the picture context menu............................................................................. 128 Using Calc's drawing tools.................................................................................... 130 Positioning graphics............................................................................................... 133 Creating an image map.......................................................................................... 136

Chapter 6 Printing, Exporting, and E-mailing..........................................................................138 Quick printing........................................................................................................ 139 Controlling printing............................................................................................... 139 Using print ranges................................................................................................. 142 Page breaks............................................................................................................ 145 Printing options setup in page styles.....................................................................146 Headers and footers............................................................................................... 148 Exporting to PDF................................................................................................... 151 Exporting to XHTML.............................................................................................. 157 Saving as Web pages (HTML)................................................................................157 E-mailing spreadsheets.......................................................................................... 157 Digital signing of documents.................................................................................157

4

3.3 Calc Guide

Removing personal data........................................................................................ 158

Chapter 7 Using Formulas and Functions................................................................................159 Introduction........................................................................................................... 160 Setting up a spreadsheet....................................................................................... 160 Creating formulas.................................................................................................. 161 Understanding functions........................................................................................ 175 Strategies for creating formulas and functions.....................................................180 Finding and fixing errors....................................................................................... 182 Examples of functions............................................................................................ 187 Using regular expressions in functions..................................................................191 Advanced functions................................................................................................ 192

Chapter 8 Using the DataPilot.................................................................................................. 193 Introduction........................................................................................................... 194 Examples with step by step descriptions...............................................................194 DataPilot functions in detail................................................................................... 214 Function GETPIVOTDATA...................................................................................... 236

Chapter 9 Data Analysis........................................................................................................... 240 Introduction........................................................................................................... 241 Consolidating data................................................................................................. 241 Creating subtotals.................................................................................................. 243 Using "what if" scenarios...................................................................................... 245 Using other "what if" tools.................................................................................... 249 Working backwards using Goal Seek.....................................................................254 Using the Solver..................................................................................................... 256

Chapter 10 Linking Calc Data...................................................................................................... 259

Why use multiple sheets?....................................................................................... 260 Setting up multiple sheets..................................................................................... 260 Referencing other sheets in the spreadsheet........................................................263 Referencing other documents: links to sheets in other spreadsheets...................266 Hyperlinks and URLs............................................................................................. 267 Linking to external data......................................................................................... 270 Linking to registered data sources........................................................................274 Embedding spreadsheets....................................................................................... 278

Chapter 11 Sharing and Reviewing Documents..........................................................................283

Introduction........................................................................................................... 284 Sharing documents (collaboration)........................................................................284 Recording changes................................................................................................. 286 Adding comments to changes................................................................................288 Adding other comments......................................................................................... 290

3.3 Calc Guide

5

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download