You are previewing Excel 2003: The Missing Manual.
O'Reilly logo
Excel 2003: The Missing Manual

Book Description

Whether you are an Excel neophyte, a sophisticate who knows the program inside out, or an intermediate-level plodder eager to hone your skills, Excel: The Missing Manual is sure to become your go-to resource for all things Excel. Covering all the features of Excel 2002 and 2003, the most recent versions for Windows, Excel: The Missing Manual is an easy-to-read, thorough and downright enjoyable guide to one of the world's most popular, (and annoyingly complicated!) computer programs. Never a candidate for "the most user-friendly of Microsoft programs," Excel demands study, practice and dedication to gain even a working knowledge of the basics. Excel 2003 is probably even tougher to use than any previous version of Excel. However, despite its fairly steep learning curve, this marvelously rich program enables users of every stripe to turn data into information using tools to analyze, communicate, and share knowledge. Excel can help you to collaborate effectively, and protect and control access to your work. Power users can take advantage of industry-standard Extensible Markup Language (XML) data to connect to business processes. To unleash the power of the program and mine the full potential of their database talents, users need an authorative and friendly resource. None is more authoritative or friendlier than Excel: The Missing Manual. Not only does the book provide exhaustive coverage of the basics, it provides numerous tips and tricks, as well as advanced data analysis, programming and Web interface knowledge that pros can adopt for their latest project. Neophytes will find everything they need to create professional spreadsheets and become confident users. Excel: The Missing Manual covers: worksheet basics, formulas and functions, organizing worksheets, charts and graphics, advanced data analysis, sharing data with the rest of the world, and programming. If you buy just one book about using Excel, this has GOT to be it. This book has all you need to help you excel at Excel.

Table of Contents

  1. Excel: The Missing Manual
    1. SPECIAL OFFER: Upgrade this ebook with O’Reilly
    2. A Note Regarding Supplemental Files
    3. The Missing Credits
      1. About the Author
      2. About the Creative Team
      3. Acknowledgements
      4. The Missing Manual Series
    4. Introduction
      1. What Excel Is all About
      2. About This Book
        1. About the Outline
        2. About→ These→ Arrows
        3. The Very Basics
        4. Examples
        5. About MissingManuals.com
      3. Safari-Enabled
    5. I. Worksheet Basics
      1. 1. Creating and Navigating Worksheets
        1. 1.1. Creating a Basic Worksheet
          1. 1.1.1. Starting a New Worksheet
          2. 1.1.2. Adding the Column Titles
          3. 1.1.3. Adding Data
        2. 1.2. Editing Data
        3. 1.3. Navigating in Excel
          1. 1.3.1. The Menus
          2. 1.3.2. The Task Pane
          3. 1.3.3. The Toolbars
            1. 1.3.3.1. Moving toolbars
            2. 1.3.3.2. Toolbars with missing buttons
            3. 1.3.3.3. Toolbar tricks
          4. 1.3.4. The Formula Bar
          5. 1.3.5. The Status Bar
        4. 1.4. Saving Files
          1. 1.4.1. Saving Your Spreadsheet in Other Formats
          2. 1.4.2. Saving Your Spreadsheet with a Password
          3. 1.4.3. Disaster Recovery
        5. 1.5. Opening Files
          1. 1.5.1. Searching for Files
      2. 2. Adding Information to Worksheets
        1. 2.1. Adding Different Types of Data
          1. 2.1.1. Controlling Your Data Types
            1. 2.1.1.1. How Excel decides your data is text
            2. 2.1.1.2. How Excel decides your data is numeric
            3. 2.1.1.3. How Excel decides your data is a date or time
        2. 2.2. Quick Ways to Add Data
          1. 2.2.1. AutoComplete
          2. 2.2.2. AutoCorrect
          3. 2.2.3. AutoFill
            1. 2.2.3.1. Custom AutoFill Lists
          4. 2.2.4. AutoFit
          5. 2.2.5. Undo and Redo
            1. 2.2.5.1. Undo quirks
      3. 3. Moving Data Around a Worksheet
        1. 3.1. Selecting Cells
          1. 3.1.1. Making Continuous Range Selections
          2. 3.1.2. Making Non-Contiguous Selections
          3. 3.1.3. Automatically Selecting Your Data
          4. 3.1.4. Making Selections with the Keyboard
        2. 3.2. Moving Cells Around
          1. 3.2.1. A Simple Cut-and-Paste or Copy-and-Paste
          2. 3.2.2. A Fancy Cut-and-Paste or Copy-and-Paste
          3. 3.2.3. The Clipboard
          4. 3.2.4. Special Pasting
        3. 3.3. Adding and Moving Columns or Rows
          1. 3.3.1. Inserting Columns
          2. 3.3.2. Inserting Rows
          3. 3.3.3. Inserting Copied or Cut Cells
          4. 3.3.4. Deleting Columns and Rows
      4. 4. Formatting Worksheets
        1. 4.1. Formatting Cell Values
          1. 4.1.1. Formatting Numbers
            1. 4.1.1.1. General
            2. 4.1.1.2. Number
            3. 4.1.1.3. Currency
            4. 4.1.1.4. Accounting
            5. 4.1.1.5. Percentage
            6. 4.1.1.6. Fraction
            7. 4.1.1.7. Scientific
            8. 4.1.1.8. Text
          2. 4.1.2. Formatting Dates and Times
          3. 4.1.3. Special Formats for Special Numbers
          4. 4.1.4. Custom Formats
            1. 4.1.4.1. Creating a custom format
            2. 4.1.4.2. Custom format string codes
            3. 4.1.4.3. Date and time format strings
            4. 4.1.4.4. Number format strings
            5. 4.1.4.5. Text format strings
        2. 4.2. Formatting Cell Appearance
          1. 4.2.1. Alignment and Orientation
          2. 4.2.2. Fonts and Color
            1. 4.2.2.1. Special characters
          3. 4.2.3. Borders and Patterns
        3. 4.3. Smart Ways to Apply Formatting
          1. 4.3.1. Shortcuts with the Toolbars
            1. 4.3.1.1. Drawing borders
            2. 4.3.1.2. Formatting individual characters
          2. 4.3.2. AutoFormat
          3. 4.3.3. The Format Painter
          4. 4.3.4. Using Styles
            1. 4.3.4.1. Creating a style
            2. 4.3.4.2. Applying a style
            3. 4.3.4.3. Transferring styles
          5. 4.3.5. Conditional Formatting
      5. 5. Managing Worksheets and Workbooks
        1. 5.1. Worksheets and Workbooks
          1. 5.1.1. Adding, Removing, and Hiding Worksheets
          2. 5.1.2. Naming and Rearranging Worksheets
          3. 5.1.3. Grouping Sheets
            1. 5.1.3.1. Moving, copying, deleting, or hiding grouped worksheets
            2. 5.1.3.2. Formatting cells, columns, and rows in grouped worksheets
            3. 5.1.3.3. Entering data or changing cells in grouped worksheets
            4. 5.1.3.4. Cutting, copying, and pasting cells in grouped worksheets
            5. 5.1.3.5. Adjusting printing and display options in grouped worksheets
          4. 5.1.4. Moving Worksheets from One Workbook to Another
        2. 5.2. Find and Replace
          1. 5.2.1. The Basic Find
          2. 5.2.2. More Advanced Searches
          3. 5.2.3. Finding Formatted Cells
          4. 5.2.4. Finding and Replacing Values
        3. 5.3. Spell Check
          1. 5.3.1. Spell Checking Options
      6. 6. Viewing and Printing Worksheets
        1. 6.1. Controlling Your View
          1. 6.1.1. Zooming
          2. 6.1.2. Viewing Distant Parts of a Spreadsheet at Once
          3. 6.1.3. Freezing Columns or Rows
          4. 6.1.4. Hiding Data
          5. 6.1.5. Saving View Settings
          6. 6.1.6. Viewing Multiple Workbooks at Once
        2. 6.2. Printing
          1. 6.2.1. How to Print an Excel File
          2. 6.2.2. Customizing Print Settings
            1. 6.2.2.1. Page settings
            2. 6.2.2.2. Margins settings
            3. 6.2.2.3. Creating headers and footers
            4. 6.2.2.4. Sheet settings
          3. 6.2.3. Getting a Print Snapshot
          4. 6.2.4. Page Break Preview: A Bird's-Eye View of Your Worksheet
    6. II. Formulas and Functions
      1. 7. Building Basic Formulas
        1. 7.1. Creating a Basic Formula
          1. 7.1.1. Excel's Order of Operations
          2. 7.1.2. Cell References
          3. 7.1.3. How Excel Formats Cells that Contain Cell References
          4. 7.1.4. Functions
          5. 7.1.5. Specifying Cell Ranges
          6. 7.1.6. Formula Errors
          7. 7.1.7. Logical Operators
        2. 7.2. Formula Shortcuts
          1. 7.2.1. Point-and-Click Formula Creation
          2. 7.2.2. Point-and-Click Formula Editing
          3. 7.2.3. Using the Insert Function Button to Quickly Find and Use Functions
          4. 7.2.4. The Analysis ToolPak
        3. 7.3. Copying Formulas
          1. 7.3.1. Absolute Cell References
          2. 7.3.2. Partial Fixed References
          3. 7.3.3. Referring to Other Worksheets and Workbooks
      2. 8. Math and Statistical Functions
        1. 8.1. Rounding Numbers
          1. 8.1.1. ROUND( ), ROUNDDOWN( ), ROUNDUP( ): Rounding Numbers
          2. 8.1.2. MROUND( ), CEILING( ), and FLOOR( ): More Rounding Functions
          3. 8.1.3. INT( ) and TRUNC( ): Chopping Off Non-Whole Numbers
          4. 8.1.4. EVEN( ) and ODD( ): Rounding Up to Even or Odd Values
        2. 8.2. Groups of Numbers
          1. 8.2.1. SUM( ): Summing Up Numbers
          2. 8.2.2. COUNT( ), COUNTA( ), and COUNTBLANK( ): Counting Items in a List
          3. 8.2.3. MAX( ) and MIN( ): Finding Maximum and Minimum Values
          4. 8.2.4. LARGE( ), SMALL( ), and RANK( ): Ranking Your Numbers
          5. 8.2.5. AVERAGE( ) and MEDIAN( ): Finding Average or Median Values
          6. 8.2.6. MODE( ): Finding Numbers that Frequently Occur in a List
          7. 8.2.7. PERCENTILE( ) and PERCENTRANK( ): Advanced Ranking Functions
          8. 8.2.8. FREQUENCY( ): Identifying to Which Category a Number Belongs
        3. 8.3. General Math Functions
          1. 8.3.1. PRODUCT( ), FACT( ), POWER( ), and SQRT( ): Products, Factorials, Powers, and Square Roots
          2. 8.3.2. QUOTIENT( ) and MOD( ): Higher Division
          3. 8.3.3. ABS( ) and SIGN( ): Absolute Value and Determining a Number's Sign
          4. 8.3.4. RAND( ) and RANDBETWEEN( ): Generating Random Numbers
          5. 8.3.5. GCD( ) and LCM( ): Greatest and Least Common Denominator
          6. 8.3.6. COMBIN( ) and PERMUT( ): Figuring Combinations and Permutations
        4. 8.4. Trigonometry and Advanced Math
        5. 8.5. Advanced Statistics
      3. 9. Financial Functions
        1. 9.1. The World of Finance
        2. 9.2. Financial Functions
          1. 9.2.1. FV( ): Future Value
          2. 9.2.2. PV( ): Present Value
          3. 9.2.3. PMT( ), PPMT( ), and IPMT( ): Calculating the Number of Payments You Need to Make
          4. 9.2.4. NPER( ): Figuring Out How Much Time You'll Need to Pay Off a Loan or Meet an Investment Target
          5. 9.2.5. RATE( ): Figuring the Interest Rate You Need to Achieve Future Value
          6. 9.2.6. NPV( ) and IRR( ): Net Present Value and Internal Rate of Return
        3. 9.3. Depreciation
        4. 9.4. Other Financial Functions
      4. 10. Manipulating Dates, Times, and Text
        1. 10.1. Manipulating Text
          1. 10.1.1. CONCATENATE( ): Joining Strings of Text Together
          2. 10.1.2. LEFT( ), MID( ), and RIGHT( ): Copying Portions of a Text String
          3. 10.1.3. LEN( ), FIND( ), and SEARCH( ): Counting Characters in a String
          4. 10.1.4. UPPER( ), LOWER( ), and PROPER( ): Changing Capitalization
          5. 10.1.5. TRIM( ) and CLEAN( ): Removing Unwanted Spaces and Non-Printing Characters
          6. 10.1.6. SUBSTITUTE( ): Replacing One Sequence of Characters with Another
          7. 10.1.7. TEXT( ), VALUE( ), FIXED( ), and DOLLAR( ): Converting Text to Numbers and Vice Versa
          8. 10.1.8. Other Text Functions
        2. 10.2. Manipulating Dates and Times
        3. 10.3. Math with Dates and Times
        4. 10.4. Date and Time Functions
          1. 10.4.1. TODAY( ) and NOW( ): Inserting the Current Date and Time
          2. 10.4.2. DATE( ) and TIME( ): Dates and Times in Calculations
          3. 10.4.3. DAY( ), MONTH( ), and YEAR( ): More Date Calculations
          4. 10.4.4. HOUR( ), MINUTE( ), SECOND( ): More Time Calculations
          5. 10.4.5. WEEKDAY( ): Determining the Day of the Week
          6. 10.4.6. DATEDIF( ): Calculating the Difference Between Dates
          7. 10.4.7. DATEVALUE( ) and TIMEVALUE( ): Converting Dates and Times into Serial Numbers
          8. 10.4.8. DAYS360( ): Finding Out the Number of Days Between Two Dates
        5. 10.5. Analysis ToolPak Date Functions
          1. 10.5.1. EDATE( ): Calculating Future Dates
          2. 10.5.2. YEARFRAC( ): Calculating the Percentage of a Year Between Two Dates
          3. 10.5.3. EOMONTH( )
          4. 10.5.4. NETWORKDAYS( ): Counting the Number of Business Days
          5. 10.5.5. WORKDAY( ): Figuring Out When Days Will Fall in the Future
          6. 10.5.6. WEEKNUM( ): Figuring Out in Which Week a Date Falls
      5. 11. Lookup, Reference, and Information Functions
        1. 11.1. The Basic Lookup
          1. 11.1.1. VLOOKUP( ): Vertical Lookups
          2. 11.1.2. HLOOKUP( ): Horizontal Lookups
        2. 11.2. Advanced Lookups
          1. 11.2.1. MATCH( ): Finding the Position of Items in a Range
          2. 11.2.2. INDEX( ): Retrieving the Value from a Cell
          3. 11.2.3. Performing a "Left Lookup"
          4. 11.2.4. Performing a Double Lookup
          5. 11.2.5. The Lookup Wizard
          6. 11.2.6. OFFSET( ): Moving Cell References to a New Location
          7. 11.2.7. Other Reference and Lookup Functions
          8. 11.2.8. INDIRECT( ) and ADDRESS( ): Working with Cell References Stored as Text
          9. 11.2.9. TRANSPOSE( ): Changing Rows into Columns and Vice Versa
        3. 11.3. Information Functions
          1. 11.3.1. The "IS" Functions: Checking the Value Inside a Cell
          2. 11.3.2. TYPE( ) and ERROR.TYPE( ): Finding a Value's Data Type or Error Type
          3. 11.3.3. INFO( ) and CELL( ): Gathering Info About Your Computer and Your Worksheet's Cells
        4. 11.4. Tutorial: Quickly Generating Invoices from a Product Catalog
      6. 12. Advanced Formula Writing
        1. 12.1. Conditions in Formulas
          1. 12.1.1. IF( ): Building Conditional Formulas
          2. 12.1.2. COUNTIF( ): Counting Only the Cells You Specify
          3. 12.1.3. SUMIF( ): Adding Only the Cells You Specify
          4. 12.1.4. SUMPRODUCT( ): Multiplying Two Ranges
        2. 12.2. Descriptive Names for Cell References
          1. 12.2.1. Creating and Using a Named Range
          2. 12.2.2. Managing Named Ranges
          3. 12.2.3. Naming Formulas and Constants
          4. 12.2.4. Automatically Creating Named Ranges
          5. 12.2.5. Applying Names to Existing Formulas
        3. 12.3. Variable Data Tables
          1. 12.3.1. Creating a One-Variable Data Table
          2. 12.3.2. Creating a Two-Variable Data Table
        4. 12.4. Controlling Recalculation
        5. 12.5. Solving Formula Errors
          1. 12.5.1. Step-by-Step Evaluation
          2. 12.5.2. Tracing Precedents and Dependents
          3. 12.5.3. Error Checking
    7. III. Organizing Worksheets
      1. 13. Data Lists
        1. 13.1. The Basics of Data Lists
          1. 13.1.1. Creating a List
          2. 13.1.2. Editing a List
        2. 13.2. The Data Form
          1. 13.2.1. Editing with the Data Form
          2. 13.2.2. Searching with the Data Form
          3. 13.2.3. Advanced Searching
        3. 13.3. Sorting and Filtering a List
          1. 13.3.1. Applying a Simple Sort Order
          2. 13.3.2. Sorting with Multiple Criteria
          3. 13.3.3. Applying an Automatic Filter
          4. 13.3.4. Creating a Custom Filter
        4. 13.4. Performing List Calculations
          1. 13.4.1. Dynamic Calculations
          2. 13.4.2. The Total Row
          3. 13.4.3. The SUBTOTAL( ) Function
          4. 13.4.4. The Database Functions
      2. 14. Grouping and Outlining Data
        1. 14.1. Basic Data Grouping
          1. 14.1.1. Creating a Group
          2. 14.1.2. Nesting Groups Within Groups
          3. 14.1.3. Summarizing Your Data
          4. 14.1.4. Combining Data from Multiple Tables
        2. 14.2. Grouping Timesavers
          1. 14.2.1. AutoOutline
          2. 14.2.2. Automatic Subtotaling
          3. 14.2.3. Your Own Custom Grouping Toolbar
      3. 15. Templates
        1. 15.1. Understanding Templates
        2. 15.2. Starting from a Template
          1. 15.2.1. Spreadsheet Solutions Templates
          2. 15.2.2. Discovering the Finer Points of Templates
          3. 15.2.3. Office Online Templates
          4. 15.2.4. Searching for Office Online Templates
        3. 15.3. Creating Templates
          1. 15.3.1. Understanding Custom Templates
          2. 15.3.2. Building a Custom Template
          3. 15.3.3. Sharing Templates with Others
        4. 15.4. Fine-Tuning Templates
          1. 15.4.1. Data Validation
            1. 15.4.1.1. Settings
            2. 15.4.1.2. Input Message
            3. 15.4.1.3. Error Alert
          2. 15.4.2. Data Validation with Formulas and Cell References
          3. 15.4.3. Data Validation with Lists
          4. 15.4.4. Locked and Hidden Cells
          5. 15.4.5. Protecting the Entire Workbook
    8. IV. Charts and Graphics
      1. 16. Creating Basic Charts
        1. 16.1. Charting 101
          1. 16.1.1. Embedded and Standalone Charts
          2. 16.1.2. The Chart Wizard
        2. 16.2. Basic Tasks with Charts
          1. 16.2.1. Moving and Resizing a Chart
          2. 16.2.2. Changing Chart Options
          3. 16.2.3. Editing and Adding to Chart Data
          4. 16.2.4. Printing Charts
            1. 16.2.4.1. Embedded charts
            2. 16.2.4.2. Standalone charts
        3. 16.3. Practical Charting
          1. 16.3.1. Charts with Multiple Series of Numbers
          2. 16.3.2. Controlling the Data Excel Plots on the X-Axis
          3. 16.3.3. Data that Uses a Date or Time Scale
          4. 16.3.4. Non-Contiguous Chart Ranges
        4. 16.4. Chart Types
          1. 16.4.1. Column
          2. 16.4.2. Bar
          3. 16.4.3. Line
          4. 16.4.4. Pie
          5. 16.4.5. XY (Scatter)
          6. 16.4.6. Area
          7. 16.4.7. Donut
          8. 16.4.8. Radar
          9. 16.4.9. Surface
          10. 16.4.10. Bubble
          11. 16.4.11. Stock
          12. 16.4.12. Cylinder, Cone, and Pyramid
      2. 17. Formatting and Perfecting Charts
        1. 17.1. Formatting Chart Elements
          1. 17.1.1. Manipulating Chart Elements
          2. 17.1.2. The Chart Toolbar
          3. 17.1.3. Coloring the Background
          4. 17.1.4. Fancy Fills
          5. 17.1.5. Adding Titles
          6. 17.1.6. Adding a Legend
          7. 17.1.7. Adding Data Labels to a Series
          8. 17.1.8. Adding Individual Data Labels
          9. 17.1.9. Formatting Data Series and Data Points
          10. 17.1.10. Controlling a Chart's Scale
        2. 17.2. Improving Your Charts
          1. 17.2.1. Adding a Trendline
          2. 17.2.2. Adding Error Bars to Scientific Data
          3. 17.2.3. Adding a Data Table
          4. 17.2.4. Formatting 3-D Charts
          5. 17.2.5. Changing the Order of Your Data Series
          6. 17.2.6. Changing the Shape of a 3-D Column
          7. 17.2.7. Changing the Way Excel Plots Blank Values
        3. 17.3. Advanced Charting
          1. 17.3.1. Exploding Slices in a Pie
          2. 17.3.2. Grouping Slices in a Pie
          3. 17.3.3. Gaps, Widths, and Overlays in a Column Chart
          4. 17.3.4. Creating Combination Charts
      3. 18. Inserting Graphics
        1. 18.1. Adding Pictures to a Worksheet
          1. 18.1.1. Inserting a Picture
          2. 18.1.2. Positioning and Resizing a Picture
          3. 18.1.3. Picture Touch-Up
          4. 18.1.4. Compressing Pictures
        2. 18.2. Excel's Clip Art Library
        3. 18.3. Drawing Shapes
          1. 18.3.1. Drawing a Shape
          2. 18.3.2. Adding Text to a Shape
          3. 18.3.3. Layers
        4. 18.4. Drawing Graphic Objects in Charts
    9. V. Advanced Data Analysis
      1. 19. Scenarios and Goal Seeking
        1. 19.1. Using Scenarios
          1. 19.1.1. Creating a New Scenario
          2. 19.1.2. Managing Scenarios
          3. 19.1.3. Creating a Summary Report
        2. 19.2. Using Goal Seek
          1. 19.2.1. Goal Seeking with Complex Equations
        3. 19.3. Solver
          1. 19.3.1. Understanding Solver
          2. 19.3.2. Defining a Problem in Solver
          3. 19.3.3. Advanced Solver Solutions
          4. 19.3.4. Saving Solver Models
          5. 19.3.5. Configuring Solver
      2. 20. Pivot Tables
        1. 20.1. Summary Tables Revisited
          1. 20.1.1. Life Without Pivot Tables
          2. 20.1.2. Life with Pivot Tables
        2. 20.2. Building Pivot Tables
          1. 20.2.1. Preparing a Pivot Table
          2. 20.2.2. Understanding Pivot Table Regions
          3. 20.2.3. Laying Out a Pivot Table
          4. 20.2.4. Rearranging a Pivot Table
        3. 20.3. Advanced Pivot Tables
          1. 20.3.1. Creating Multi-Layered Pivot Tables
          2. 20.3.2. Hiding and Showing Details
          3. 20.3.3. Filtering Pivot Tables
          4. 20.3.4. Formatting the Pivot Table
          5. 20.3.5. Adding a Calculated Field
          6. 20.3.6. Creating Pivot Charts
    10. VI. Sharing Data with the Rest of the World
      1. 21. Worksheet Collaboration
        1. 21.1. Your Excel Identity
        2. 21.2. Using Comments
          1. 21.2.1. Inserting a Comment
          2. 21.2.2. Fine-Tuning Comments
          3. 21.2.3. Navigating Comments
          4. 21.2.4. Printing Comments
        3. 21.3. Using Change Tracking
          1. 21.3.1. Switching On Change Tracking
          2. 21.3.2. Understanding the Change Log
          3. 21.3.3. Highlighting Changes
          4. 21.3.4. Examining the Change Log
          5. 21.3.5. Accepting and Rejecting Changes
          6. 21.3.6. Merging Multiple Revisions into One Workbook
        4. 21.4. Setting Up a Review Chain
          1. 21.4.1. Sending a Workbook by Email with Excel
          2. 21.4.2. Sending a Workbook for Review
          3. 21.4.3. Routing a Workbook to Multiple People
        5. 21.5. Workbook Sharing
          1. 21.5.1. Multiple Users Without Workbook Sharing
          2. 21.5.2. Turning On Workbook Sharing
          3. 21.5.3. Understanding Workbook Sharing
      2. 22. Exchanging Data with Other Programs
        1. 22.1. Sharing Information in Windows
        2. 22.2. Embedding and Linking Objects
          1. 22.2.1. Exporting Charts
          2. 22.2.2. Editing an Embedded Object
          3. 22.2.3. Editing a Linked Object
          4. 22.2.4. Importing Objects
        3. 22.3. Transferring Data
          1. 22.3.1. Exporting Tables of Data
          2. 22.3.2. Importing Tables of Data
          3. 22.3.3. Importing Text Files
        4. 22.4. Excel and Databases
          1. 22.4.1. Defining a Data Source
          2. 22.4.2. Querying a Data Source
      3. 23. XML
        1. 23.1. XML 101
          1. 23.1.1. What Is XML, Really?
          2. 23.1.2. Three Rules of XML
            1. 23.1.2.1. The prolog
            2. 23.1.2.2. Elements
            3. 23.1.2.3. Nesting
          3. 23.1.3. XML Files and Schemas
        2. 23.2. The Excel XML Story
        3. 23.3. Linking Spreadsheets to XML
          1. 23.3.1. Mapping a Simple Document
          2. 23.3.2. Importing and Exporting XML
          3. 23.3.3. Mapping Lists
          4. 23.3.4. Gaining the Benefits of XML Mapping
      4. 24. Connecting Worksheets to the Web
        1. 24.1. Putting Worksheets on the Web
          1. 24.1.1. Saving and Publishing HTML
          2. 24.1.2. Saving a Simple Web Page
          3. 24.1.3. Creating an Interactive Web Page
        2. 24.2. Performing Web Queries
          1. 24.2.1. The Limitations of Web Queries
          2. 24.2.2. Creating a Web Query
          3. 24.2.3. The Research Task—a Web Query Alternative
        3. 24.3. Using Hyperlinks
          1. 24.3.1. Adding a Hyperlink to a Web Page or Document
          2. 24.3.2. Adding a Hyperlink to a Worksheet Location
          3. 24.3.3. The HYPERLINK( ) Function: Creating a Link on Your Own
    11. VII. Programming Excel
      1. 25. Automating Tasks with Macros
        1. 25.1. Macros 101
        2. 25.2. The Macro Recorder
          1. 25.2.1. Relative and Absolute Recording
          2. 25.2.2. Where Macros Live
          3. 25.2.3. Recording a Macro
          4. 25.2.4. Playing a Macro
        3. 25.3. Creating Practical Macros
          1. 25.3.1. Inserting a Header
          2. 25.3.2. Alternating Row Formatting
          3. 25.3.3. A Combined Task
          4. 25.3.4. Security and Macros
          5. 25.3.5. Attaching a Macro to a Toolbar or Menu
          6. 25.3.6. Attaching a Macro to a Button Inside a Worksheet
      2. 26. Programming Spreadsheets with VBA
        1. 26.1. The Visual Basic Editor
          1. 26.1.1. The Project Window
          2. 26.1.2. Modules and Macros
          3. 26.1.3. Finding and Moving Macros
          4. 26.1.4. Debugging a Macro
        2. 26.2. Understanding Macro Code
          1. 26.2.1. The Anatomy of a Macro
          2. 26.2.2. Objects 101
          3. 26.2.3. Using Properties and Methods
          4. 26.2.4. Hunting for Objects
        3. 26.3. Exploring the VBA Language
          1. 26.3.1. Entering Text in the Current Cell
          2. 26.3.2. Moving to Other Cells
          3. 26.3.3. Editing Specific Cells
          4. 26.3.4. Formatting Cells
          5. 26.3.5. Using Variables
          6. 26.3.6. Making Decisions
          7. 26.3.7. Repeating Actions with a Loop
          8. 26.3.8. Creating Custom Functions
    12. VII. Appendixes
      1. A. Getting Help in Excel
        1. A.1. Asking Questions
          1. A.1.1. Searching Without the Web in Excel 2003
          2. A.1.2. Browsing the Help System
        2. A.2. The Office Assistant
        3. A.3. Other Help Menu Options
      2. B. Customizing Excel
        1. B.1. The Customize Dialog Box
          1. B.1.1. Managing Toolbars
          2. B.1.2. Adding, Removing, and Rearranging Commands
          3. B.1.3. Using Special Commands
          4. B.1.4. Returning to Normal
          5. B.1.5. Attaching Toolbars to a Workbook
      3. C. Quick Menu Reference
        1. C.1. File Menu
          1. C.1.1. New
          2. C.1.2. Open
          3. C.1.3. Close
          4. C.1.4. Save
          5. C.1.5. Save As
          6. C.1.6. Save as Web Page
          7. C.1.7. Save Workspace
          8. C.1.8. File Search
          9. C.1.9. Permission
          10. C.1.10. Web Page Preview
          11. C.1.11. Page Setup
          12. C.1.12. Print Area
          13. C.1.13. Print Preview
          14. C.1.14. Print
          15. C.1.15. Send To
          16. C.1.16. Properties
          17. C.1.17. Exit
        2. C.2. Edit Menu
          1. C.2.1. Undo
          2. C.2.2. Repeat
          3. C.2.3. Cut
          4. C.2.4. Copy
          5. C.2.5. Office Clipboard
          6. C.2.6. Paste
          7. C.2.7. Paste Special
          8. C.2.8. Paste as Hyperlink
          9. C.2.9. Fill
          10. C.2.10. Clear
          11. C.2.11. Delete
          12. C.2.12. Delete Sheet
          13. C.2.13. Move or Copy Sheet
          14. C.2.14. Find
          15. C.2.15. Replace
          16. C.2.16. Go To
          17. C.2.17. Links
          18. C.2.18. Object
        3. C.3. View Menu
          1. C.3.1. Normal
          2. C.3.2. Page Break Preview
          3. C.3.3. Task Pane
          4. C.3.4. Toolbars
          5. C.3.5. Formula Bar
          6. C.3.6. Status Bar
          7. C.3.7. Header and Footer
          8. C.3.8. Comments
          9. C.3.9. Custom Views
          10. C.3.10. Full Screen
          11. C.3.11. Zoom
        4. C.4. Insert Menu
          1. C.4.1. Cells
          2. C.4.2. Rows
          3. C.4.3. Columns
          4. C.4.4. Worksheet
          5. C.4.5. Chart
          6. C.4.6. Symbol
          7. C.4.7. Page Break
          8. C.4.8. Function
          9. C.4.9. Name
          10. C.4.10. Comment
          11. C.4.11. Picture
          12. C.4.12. Diagram
          13. C.4.13. Object
          14. C.4.14. Hyperlink
        5. C.5. Format Menu
          1. C.5.1. Cells
          2. C.5.2. Row
          3. C.5.3. Column
          4. C.5.4. Sheet
          5. C.5.5. AutoFormat
          6. C.5.6. Conditional Formatting
          7. C.5.7. Style
        6. C.6. Tools Menu
          1. C.6.1. Spelling
          2. C.6.2. Research
          3. C.6.3. Error Checking
          4. C.6.4. Speech
          5. C.6.5. Shared Workspace
          6. C.6.6. Share Workbook
          7. C.6.7. Track Changes
          8. C.6.8. Compare and Merge Workbooks
          9. C.6.9. Protection
          10. C.6.10. Online Collaboration
          11. C.6.11. Goal Seek
          12. C.6.12. Scenarios
          13. C.6.13. Formula Auditing
          14. C.6.14. Solver
          15. C.6.15. Macro
          16. C.6.16. Add-Ins
          17. C.6.17. AutoCorrect Options
          18. C.6.18. Customize
          19. C.6.19. Options
          20. C.6.20. Data Analysis
        7. C.7. Data Menu
          1. C.7.1. Sort
          2. C.7.2. Filter
          3. C.7.3. Form
          4. C.7.4. Subtotals
          5. C.7.5. Validation
          6. C.7.6. Table
          7. C.7.7. Text to Columns
          8. C.7.8. Consolidate
          9. C.7.9. Group and Outline
          10. C.7.10. PivotTable and PivotChart Report
          11. C.7.11. Import External Data
          12. C.7.12. List
          13. C.7.13. XML
          14. C.7.14. Refresh Data
        8. C.8. Chart Menu
          1. C.8.1. Chart Type
          2. C.8.2. Source Data
          3. C.8.3. Chart Options
          4. C.8.4. Location
          5. C.8.5. Add Data
          6. C.8.6. Add Trendline
          7. C.8.7. 3-D View
        9. C.9. Window Menu
          1. C.9.1. New Window
          2. C.9.2. Arrange
          3. C.9.3. Compare Side by Side with
          4. C.9.4. Hide
          5. C.9.5. Unhide
          6. C.9.6. Split
          7. C.9.7. Freeze Panes
        10. C.10. Help Menu
          1. C.10.1. Microsoft Excel Help
          2. C.10.2. Show the Office Assistant
          3. C.10.3. Microsoft Office Online
          4. C.10.4. Contact Us
          5. C.10.5. Check for Updates
          6. C.10.6. Detect and Repair
          7. C.10.7. Activate Product
          8. C.10.8. Customer Feedback Options
          9. C.10.9. About Microsoft Office Excel
    13. Index
    14. About the Author
    15. Colophon
    16. SPECIAL OFFER: Upgrade this ebook with O’Reilly