Openoffice Org 3 2 Calc Guide OpenOffice.org 3.x

2015-07-27

• Chapter 1 Introducing Calc
  • What is Calc?
  • Spreadsheets, sheets, and cells
  • Parts of the main Calc window
    • Title bar
    • Menu bar
    • Toolbars
    • Formula Bar
    • Individual cells
    • Sheet tabs
    • Status bar
  • Starting new spreadsheets
  • Opening existing spreadsheets
  • Opening CSV files
  • 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
      • Customizing the effects 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
    • 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
• 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
  • 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)
• 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
    • Formating data 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
    • 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
    • Printer options
    • Selecting sheets to print
    • Selecting the page order, details, and scale
  • 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
    • Inserting a page break
      • Row break
      • Column break
    • Deleting a page break
  • 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
      • Err:503 Division by zero
      • #VALUE Non-existent value and #REF! Incorrect references
    • Color coding for input
    • The Detective
  • Examples of functions
    • Basic arithmetic and statistic functions
      • Basic arithmetic
      • Simple statistics
      • Using these functions
    • Rounding off numbers
      • Rounding methods
  • Using regular expressions in functions
  • Advanced functions
• Chapter 8 Using the DataPilot
  • Introduction
  • Examples with step by step instructions
    • Example 1: Sales volume overview
      • Practical problems and questions
      • Solution
      • Advantages
    • Example 2: Timekeeping
      • Practical problems and questions
      • Solution
      • Differences and advantages
    • Example 3: Frequency distribution
      • Solution with a matrix formula
      • Solution with the DataPilot
  • DataPilot functions in detail
    • The database (preconditions)
    • Start
    • Data source
      • Calc spreadsheet
      • Registered data source
      • External data (OLAP)
    • The DataPilot dialog
      • Basic layout
      • More options
      • More settings for the fields
      • Data fields
      • Row and column fields
      • Page fields
    • Working with the results of the DataPilot
      • 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 the result
      • Drilling (showing details)
      • Filtering
      • Updating (refreshing) changed values
      • Cell formatting
    • Multiple data fields
    • Shortcuts
  • Function GETPIVOTDATA
    • Difficulty
    • Syntax
      • First syntax variation
      • Second syntax variation
• Chapter 9 Data Analysis
  • Introduction
  • Consolidating data
  • Creating 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 OOo
    • User Data options
    • Print options
    • Color options
    • Security options
    • 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
    • Sort Lists options
    • Changes options
    • Grid options
      • Grid section
      • Resolution section
    • Print options
  • 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
    • Using extensions
• Appendix A Keyboard Shortcuts
  • Introduction
  • Navigation and selection shortcuts
  • Function and arrow key shortcuts
  • Cell formatting shortcuts
  • DataPilot 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