Transcription
OpenOffice.org 3.3Calc GuideUsing Spreadsheets in OpenOffice.org 3.3
CopyrightThis document is Copyright 2005–2011 by its contributors as listed below. You maydistribute 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 CreativeCommons Attribution License (http://creativecommons.org/licenses/by/3.0/), version3.0 or later. Note that Chapter 8, Using the DataPilot, is licensed under the CreativeCommons Attribution-Share Alike License, version 3.0.ContributorsRick BarnesJames AndrewStephen BuckT. J. FrazierSpencer E. HarpeJohn KaneJared KobosAlexandre MartinsAndrew PitonyakGary SchnablSowbhagya SundaresanJohn ViestenzSharon WhistonMichele ZarriPeter KupferKrishna AradhiBruce ByfieldStigant FyrwitfulRegina HenschelKirk AbbottSigrid KronenbergerKashmira PatelIain RobertsRob ScottNikita TelangJean Hollis WeberClaire WoodMagnus AdielssonMartin FoxAndy BrownNicole CairnsIngrid HalamaPeter Hillier-BrookEmma KirsoppShelagh MantonAnthony PetrilloHazel RussmanJacob StarrBarbara M TobiasStefan WeigelLinda WorthingtonSandeep Samuel MedikondaFeedbackPlease direct any comments or suggestions about this document to:authors@documentation.openoffice.orgPublication date and software versionPublished 18 April 2011. Based on OpenOffice.org 3.3.You can downloadan editable version of this document entation/
ContentsCopyright. 2Note for Mac users. 8Chapter 1Introducing Calc. 9What is Calc?. 10Spreadsheets, sheets, and cells. 10Parts of the main Calc window. 10Starting new spreadsheets. 18Opening existing spreadsheets. 20Opening CSV files. 21Saving spreadsheets. 22Password protection. 24Navigating within spreadsheets. 26Selecting items in a sheet or spreadsheet.29Working with columns and rows. 32Working with sheets. 33Viewing Calc. 34Using the Navigator. 38Using document properties. 40Chapter 2Entering, Editing, and Formatting Data.43Introduction. 44Entering data using the keyboard.44Speeding up data entry. 46Sharing content between sheets. 49Validating cell contents. 50Editing data. 52Formatting data. 53Autoformatting cells and sheets.59Formatting spreadsheets using themes.60Using conditional formatting. 60Hiding and showing data. 61Sorting records. 63Finding and replacing in Calc. 65Chapter 3Creating Charts and Graphs. 69Introduction. 70Creating a chart. 70Editing charts. 74Formatting charts. 79Formatting 3D charts. 82Formatting the chart elements.85OpenOffice.org 3.3 Calc Guide3
Adding drawing objects to charts. 87Resizing and moving the chart. 88Gallery of chart types. 89Chapter 4Using Styles and Templates in Calc.98What is a template?. 99What are styles?. 99Types of styles in Calc. 99Accessing styles. 100Applying cell styles. 101Applying page styles. 102Modifying styles. 103Creating new (custom) styles.106Copying and moving styles. 107Deleting styles. 109Creating a spreadsheet from a template.109Creating a template. 110Editing a template. 111Adding templates using the Extension Manager.113Setting a default template. 113Associating a spreadsheet with a different template.114Organizing templates. 115Chapter 5Using Graphics in Calc. 117Graphics in Calc. 118Adding graphics (images). 118Modifying images. 123Using the picture context menu. 128Using Calc’s drawing tools. 130Positioning graphics. 133Creating an image map. 136Chapter 6Printing, Exporting, and E-mailing.138Quick printing. 139Controlling printing. 139Using print ranges. 142Page breaks. 145Printing options setup in page styles.146Headers and footers. 148Exporting to PDF. 151Exporting to XHTML. 157Saving as Web pages (HTML).157E-mailing spreadsheets. 157Digital signing of documents.1574OpenOffice.org 3.3 Calc Guide
Removing personal data. 158Chapter 7Using Formulas and Functions.159Introduction. 160Setting up a spreadsheet. 160Creating formulas. 161Understanding functions. 175Strategies for creating formulas and functions.180Finding and fixing errors. 182Examples of functions. 187Using regular expressions in functions.191Advanced functions. 192Chapter 8Using the DataPilot. 193Introduction. 194Examples with step by step descriptions.194DataPilot functions in detail. 214Function GETPIVOTDATA. 236Chapter 9Data Analysis. 240Introduction. 241Consolidating data. 241Creating subtotals. 243Using “what if” scenarios. 245Using other “what if” tools. 249Working backwards using Goal Seek.254Using the Solver. 256Chapter 10Linking Calc Data. 259Why use multiple sheets?. 260Setting up multiple sheets. 260Referencing other sheets in the spreadsheet.263Referencing other documents: links to sheets in other spreadsheets.266Hyperlinks and URLs. 267Linking to external data. 270Linking to registered data sources.274Embedding spreadsheets. 278Chapter 11Sharing and Reviewing Documents.283Introduction. 284Sharing documents (collaboration).284Recording changes. 286Adding comments to changes.288Adding other comments. 290OpenOffice.org 3.3 Calc Guide5
Reviewing changes. 291Merging documents. 294Comparing documents. 295Saving versions. 295Chapter 12Calc Macros. 298Introduction. 299Using the macro recorder. 299Write your own functions. 303Accessing cells directly. 310Sorting. 311Conclusion. 312Chapter 13Calc as a Simple Database. 313Introduction. 314Associating a range with a name.315Sorting. 320Filters. 322Calc functions similar to database functions.330Database-specific functions. 339Conclusion. 340Chapter 14Setting up and Customizing Calc.341Introduction. 342Choosing options that affect all of OOo.342Choosing options for loading and saving documents.347Choosing options for Calc. 350Controlling Calc’s AutoCorrect functions.358Customizing the user interface. 358Adding functionality with extensions.366Appendix AKeyboard Shortcuts. 369Introduction. 370Navigation and selection shortcuts.370Function and arrow key shortcuts.371Cell formatting shortcuts. 373DataPilot shortcuts. 374Appendix BDescription of Functions. 375Functions available in Calc. 376Mathematical functions. 376Financial analysis functions. 381Statistical analysis functions. 393Date and time functions. 4016OpenOffice.org 3.3 Calc Guide
Logical functions. 404Informational functions. 405Database functions. 407Array functions. 408Spreadsheet functions. 410Text functions. 414Add-in functions. 417Appendix CCalc Error Codes. 421Introduction to Calc error codes.422Error codes displayed within cells.423General error codes. 424Index. 427OpenOffice.org 3.3 Calc Guide7
Note for Mac usersSome keystrokes and menu items are different on a Mac from those used in Windowsand Linux. The table below gives some common substitutions for the instructions inthis chapter. For a more detailed list, see the application Help.8Windows/LinuxMac equivalentEffectTools Optionsmenu selectionOpenOffice.org PreferencesAccess setup optionsRight-clickControl clickOpen context menuCtrl (Control)z (Command)Used with other keysF5Shift z F5Open the NavigatorF11z TOpen Styles & Formatting windowOpenOffice.org 3.3 Calc Guide
1ChapterIntroducing Calc
What is Calc?Calc is the spreadsheet component of OpenOffice.org (OOo). You can enter data(usually numerical) in a spreadsheet and then manipulate this data to produce certainresults.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 theentire spreadsheet or sheet.Other features provided by Calc include: Functions, which can be used to create formulas to perform complexcalculations on data 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 languagessupported include OpenOffice.org Basic, Python, BeanShell, and JavaScript Ability to open, edit, and save Microsoft Excel spreadsheets Import and export of spreadsheets in multiple formats, including HTML, CSV,PDF, and PostScriptNoteIf you want to use macros written in Microsoft Excel using the VBAmacro code in OOo, you must first edit the code in the OOo Basic IDEeditor. See Chapter 12 (Calc Macros).Spreadsheets, sheets, and cellsCalc works with elements called spreadsheets. Spreadsheets consist of a number ofindividual sheets, each sheet containing cells arranged in rows and columns. Aparticular cell is identified by its row number and column letter.Cells hold the individual elements—text, numbers, formulas, and so on—that make upthe data to display and manipulate.Each spreadsheet can have many sheets, and each sheet can have many individualcells. In Calc 3.3, each sheet can have a maximum of 1,048,576 (65,536 rows in Calc3.2 and earlier) and a maximum of 1024 columnsParts of the main Calc windowWhen Calc is started, the main window looks similar to Figure 1.Note10If any part of the Calc window in Figure 1 is not shown, you can display itusing the View menu. For example, View Status Bar will toggle (showor hide) the Status Bar. It is not always necessary to display all the parts,as shown; show or hide any of them, as desired.OpenOffice.org 3.3 Calc Guide
Figure 1: Parts of the Calc windowTitle barThe Title bar, located at the top, shows the name of the current spreadsheet. Whenthe spreadsheet is newly created, its name is Untitled X, where X is a number. Whenyou save a spreadsheet for the first time, you are prompted to enter a name of yourchoice.Menu barUnder the Title bar is the Menu bar. When you choose one of the menus, a submenuappears with other options. You can modify the Menu bar, as discussed in Chapter 14(Setting Up and Customizing Calc). File contains commands that apply to the entire document such as Open,Save, Wizards, Export as PDF, and Digital Signatures. Edit contains commands for editing the document such as Undo, Changes,Compare Document, and Find and Replace. View contains commands for modifying how the Calc user interface looks suchas Toolbars, Full Screen, and Zoom. Insert contains commands for inserting elements such as cells, rows,columns, sheets, and pictures into a spreadsheet. Format contains commands for modifying the layout of a spreadsheet such asStyles and Formatting, Paragraph, and Merge Cells. Tools contains functions such as Spelling, Share Document, Cell Contents,Gallery, and Macros. Data contains commands for manipulating data in your spreadsheet such asDefine Range, Sort, Filter, and DataPilot.Chapter 1 Introducing Calc11
Window contains commands for the display window such as New Window,Split, and Freeze. Help contains links to the Help file bundled with the software, What's This?,Support, Registration, and Check for Updates.ToolbarsCalc has several types of toolbars: docked (fixed in place), floating, and tear-off.Docked toolbars can be moved to different locations or made to float, and floatingtoolbars can be docked.Four toolbars are located under the Menu bar by default: the Standard toolbar, theFind toolbar, the Formatting toolbar, and the Formula Bar.The icons (buttons) on these toolbars provide a wide range of common commandsand functions. You can also modify these toolbars, as discussed in Chapter 14(Setting Up and Customizing Calc).Placing the mouse pointer over any of the icons displays a small box, called a tooltip.It gives a brief explanation of the icon’s function. For a more detailed explanation,choose Help What’s This? and hover the mouse pointer over the icon. To turn thisfeature off again, click once or press the Esc key twice. Tips and extended tips can beturned on or off from Tools Options OpenOffice.org General.Displaying or hiding toolbarsTo display or hide toolbars, choose View Toolbars, then click on the name of atoolbar in the list. An active toolbar shows a check mark beside its name. Tear-offtoolbars are not listed in the View menu.Palettes and tear-off toolbarsToolbar icons with a small triangle to the right will display palettes, tear-off toolbars,and other ways of selecting things, depending on the icon.An example of a palette is shown in Figure 2. It is displayed by clicking the smalltriangle to the right of the Borders toolbar icon.Figure 2: Toolbar paletteAn example of a tear-off toolbar is shown in Figure 3. Tear-off toolbars can be floatingor docked along an edge of the screen or in one of the existing toolbar areas. To movea floating tear-off toolbar, drag it by the title bar.12OpenOffice.org 3.3 Calc Guide
Figure 3: Example of a tear-off toolbarMoving toolbarsTo move a docked toolbar, place the mouse pointer over the toolbar handle, holddown the left mouse button, drag the toolbar to the new location, and then releasethe mouse button.Figure 4: Moving a docked toolbarTo move a floating toolbar, click on its title bar and drag it to a new location, asshown in Figure 3.Docking/floating windows and toolbarsToolbars and some windows, such as the Navigator and the Styles and Formattingwindow, are dockable. You can move, resize, or dock them to an edge.To dock a window or toolbar, hold down the Control key and double-click on theframe of the floating window (or in a vacant area near the icons at the top of thefloating window) to dock it in its last position.To undock a window, hold down the Control key and double-click on the frame (or avacant area near the icons at the top) of the docked window.Chapter 1 Introducing Calc13
Figure 5: Control double-click to dock or undockCustomizing toolbarsYou can customize toolbars in several ways, including choosing which icons arevisible and locking the position of a docked toolbar.To access a toolbar’s customization options, use the down-arrow at the end of thetoolbar or on its title bar (Figure 6).Figure 6: Customizing toolbarsTo show or hide icons defined for the selected toolbar, choose Visible Buttons fromthe drop-down menu. Visible icons are indicated by a border around the icon (Figure7). Click on icons to hide or show them on the toolbar.You can also add icons and create new toolbars, as described in Chapter 16.Figure 7: Selection of visible toolbar iconsFormatting toolbarIn the Formatting toolbar, the three boxes on the left are the Apply Style, FontName, and Font Size lists (see Figure 8). They show the current settings for theselected cell or area. (The Apply Style list may not be visible by default.) Click thedown-arrow to the right of each box to open the list.14OpenOffice.org 3.3 Calc Guide
Figure 8: Apply Style, Font Name and Font Size listsNoteIf any of the icons (buttons) in Figure 8 is not shown, you can display it byclicking the small triangle at the right end of the Formatting toolbar,selecting Visible Buttons in the drop-down menu, and selecting thedesired icon (for example, Apply Style) in the drop-down list. It is notalways necessary to display all the toolbar buttons, as shown; show orhide any of them, as desired.Formula BarOn the left hand side of the Formula Bar is a small text box, called the Name Box,with a letter and number com
Import and export of spreadsheets in multiple formats, including HTML, CSV, PDF, and PostScript Note If you want to use macros written in Microsoft Excel using the VBA macro code in OOo, you must first edit the code in the OOo Basic IDE editor. See Chapter 12 (Calc Macros). Spreadsheets, sheets, and cells Calc works with elements called .
