You are previewing Excel® 2010 Bible.
O'Reilly logo
Excel® 2010 Bible

Book Description

A comprehensive reference to the newest version of the world’s most popular spreadsheet application: Excel 2010

John Walkenbach's name is synonymous with excellence in computer books that decipher complex technical topics. Known as "Mr. Spreadsheet," Walkenbach shows you how to maximize the power of all the new features of Excel 2010.

An authoritative reference, this perennial bestseller proves itself indispensable no matter your level of skill, from Excel beginners and intermediate users to power users and potential power users everywhere. Fully updated for the new release, this latest edition provides comprehensive, soup-to-nuts coverage, delivering over 900 pages of Excel tips, tricks, and techniques you won’t find anywhere else.

  • Excel guru and bestselling author John Walkenbach ("Mr. Spreadsheet") guides you through every aspect of Excel

  • Delivers essential coverage of all the newest features of Excel 2010

  • Presents material in a clear, concise, logical format that is ideal for all levels of Excel experience

  • Includes a CD that contains all the templates and worksheets used in the book plus John Walkenbach's award-winning Power Utility Pak

Excel 2010 Bible serves as an excellent resource on all things Excel!

Table of Contents

  1. Copyright
  2. About the Author
  3. About the Technical Editor
  4. Credits
  5. Acknowledgments
  6. Introduction
    1. Is This Book for You?
    2. Software Versions
    3. Conventions This Book Uses
      1. Excel commands
      2. Filenames, named ranges, and your input
      3. Key names
      4. Functions
      5. Mouse conventions
    4. What the Icons Mean
    5. How This Book Is Organized
    6. How to Use This Book
    7. What's on the Companion CD
  7. I. Getting Started with Excel
    1. 1. Introducing Excel
      1. 1.1. What Is Excel Good For?
      2. 1.2. What's New in Excel 2010?
      3. 1.3. Understanding Workbooks and Worksheets
      4. 1.4. Moving around a Worksheet
        1. 1.4.1. Navigating with your keyboard
        2. 1.4.2. Navigating with your mouse
      5. 1.5. Introducing the Ribbon
        1. 1.5.1. Ribbon tabs
        2. 1.5.2. Contextual tabs
        3. 1.5.3. Types of commands on the Ribbon
        4. 1.5.4. Accessing the Ribbon by using your keyboard
      6. 1.6. Using Shortcut Menus
      7. 1.7. Customizing Your Quick Access Toolbar
      8. 1.8. Working with Dialog Boxes
        1. 1.8.1. Navigating dialog boxes
        2. 1.8.2. Using tabbed dialog boxes
      9. 1.9. Using the Task Pane
      10. 1.10. Creating Your First Excel Worksheet
        1. 1.10.1. Getting started on your worksheet
        2. 1.10.2. Filling in the month names
        3. 1.10.3. Entering the sales data
        4. 1.10.4. Formatting the numbers
        5. 1.10.5. Making your worksheet look a bit fancier
        6. 1.10.6. Summing the values
        7. 1.10.7. Creating a chart
        8. 1.10.8. Printing your worksheet
        9. 1.10.9. Saving your workbook
    2. 2. Entering and Editing Worksheet Data
      1. 2.1. Exploring the Types of Data You Can Use
        1. 2.1.1. About numeric values
        2. 2.1.2. About text entries
        3. 2.1.3. About formulas
      2. 2.2. Entering Text and Values into Your Worksheets
      3. 2.3. Entering Dates and Times into Your Worksheets
        1. 2.3.1. Entering date values
        2. 2.3.2. Entering time values
      4. 2.4. Modifying Cell Contents
        1. 2.4.1. Erasing the contents of a cell
        2. 2.4.2. Replacing the contents of a cell
        3. 2.4.3. Editing the contents of a cell
        4. 2.4.4. Learning some handy data-entry techniques
          1. 2.4.4.1. Automatically moving the cell pointer after entering data
          2. 2.4.4.2. Using navigation keys instead of pressing Enter
          3. 2.4.4.3. Selecting a range of input cells before entering data
          4. 2.4.4.4. Using Ctrl+Enter to place information into multiple cells simultaneously
          5. 2.4.4.5. Entering decimal points automatically
          6. 2.4.4.6. Using AutoFill to enter a series of values
          7. 2.4.4.7. Using AutoComplete to automate data entry
          8. 2.4.4.8. Forcing text to appear on a new line within a cell
          9. 2.4.4.9. Using AutoCorrect for shorthand data entry
          10. 2.4.4.10. Entering numbers with fractions
          11. 2.4.4.11. Simplifying data entry by using a form
          12. 2.4.4.12. Entering the current date or time into a cell
      5. 2.5. Applying Number Formatting
        1. 2.5.1. Using automatic number formatting
        2. 2.5.2. Formatting numbers by using the Ribbon
        3. 2.5.3. Using shortcut keys to format numbers
        4. 2.5.4. Formatting numbers using the Format Cells dialog box
        5. 2.5.5. Adding your own custom number formats
    3. 3. Essential Worksheet Operations
      1. 3.1. Learning the Fundamentals of Excel Worksheets
        1. 3.1.1. Working with Excel windows
          1. 3.1.1.1. Moving and resizing windows
          2. 3.1.1.2. Switching among windows
          3. 3.1.1.3. Closing windows
        2. 3.1.2. Activating a worksheet
        3. 3.1.3. Adding a new worksheet to your workbook
        4. 3.1.4. Deleting a worksheet you no longer need
        5. 3.1.5. Changing the name of a worksheet
        6. 3.1.6. Changing a sheet tab color
        7. 3.1.7. Rearranging your worksheets
        8. 3.1.8. Hiding and unhiding a worksheet
      2. 3.2. Controlling the Worksheet View
        1. 3.2.1. Zooming in or out for a better view
        2. 3.2.2. Viewing a worksheet in multiple windows
        3. 3.2.3. Comparing sheets side by side
        4. 3.2.4. Splitting the worksheet window into panes
        5. 3.2.5. Keeping the titles in view by freezing panes
        6. 3.2.6. Monitoring cells with a Watch Window
      3. 3.3. Working with Rows and Columns
        1. 3.3.1. Inserting rows and columns
        2. 3.3.2. Deleting rows and columns
        3. 3.3.3. Hiding rows and columns
        4. 3.3.4. Changing column widths and row heights
          1. 3.3.4.1. Changing column widths
          2. 3.3.4.2. Changing row heights
    4. 4. Working with Cells and Ranges
      1. 4.1. Understanding Cells and Ranges
        1. 4.1.1. Selecting ranges
        2. 4.1.2. Selecting complete rows and columns
        3. 4.1.3. Selecting noncontiguous ranges
        4. 4.1.4. Selecting multisheet ranges
        5. 4.1.5. Selecting special types of cells
        6. 4.1.6. Selecting cells by searching
      2. 4.2. Copying or Moving Ranges
        1. 4.2.1. Copying by using Ribbon commands
        2. 4.2.2. Copying by using shortcut menu commands
        3. 4.2.3. Copying by using shortcut keys
        4. 4.2.4. Copying or moving by using drag-and-drop
        5. 4.2.5. Copying to adjacent cells
        6. 4.2.6. Copying a range to other sheets
        7. 4.2.7. Using the Office Clipboard to paste
        8. 4.2.8. Pasting in special ways
        9. 4.2.9. Using the Paste Special Dialog box
          1. 4.2.9.1. Performing mathematical operations without formulas
          2. 4.2.9.2. Skipping blanks when pasting
          3. 4.2.9.3. Transposing a range
      3. 4.3. Using Names to Work with Ranges
        1. 4.3.1. Creating range names in your workbooks
          1. 4.3.1.1. Using the New Name dialog box
          2. 4.3.1.2. Using the Name box
          3. 4.3.1.3. Using the Create Names from Selection dialog box
        2. 4.3.2. Managing names
      4. 4.4. Adding Comments to Cells
        1. 4.4.1. Formatting comments
        2. 4.4.2. Changing a comment's shape
        3. 4.4.3. Reading comments
        4. 4.4.4. Printing comments
        5. 4.4.5. Hiding and showing comments
        6. 4.4.6. Selecting comments
        7. 4.4.7. Editing comments
        8. 4.4.8. Deleting comments
    5. 5. Introducing Tables
      1. 5.1. What Is a Table?
      2. 5.2. Creating a Table
      3. 5.3. Changing the Look of a Table
      4. 5.4. Working with Tables
        1. 5.4.1. Navigating in a table
        2. 5.4.2. Selecting parts of a table
        3. 5.4.3. Adding new rows or columns
        4. 5.4.4. Deleting rows or columns
        5. 5.4.5. Moving a table
        6. 5.4.6. Setting table options
        7. 5.4.7. Working with the Total Row
        8. 5.4.8. Removing duplicate rows from a table
        9. 5.4.9. Sorting and filtering a table
          1. 5.4.9.1. Sorting a table
          2. 5.4.9.2. Filtering a table
        10. 5.4.10. Converting a table back to a range
    6. 6. Worksheet Formatting
      1. 6.1. Getting to Know the Formatting Tools
        1. 6.1.1. Using the formatting tools of the Home Tab
        2. 6.1.2. Using the Mini toolbar
        3. 6.1.3. Using the Format Cells dialog box
      2. 6.2. Using Different Fonts to Format Your Worksheet
      3. 6.3. Changing Text Alignment
        1. 6.3.1. Choosing horizontal alignment options
        2. 6.3.2. Choosing vertical alignment options
        3. 6.3.3. Wrapping or shrinking text to fit the cell
        4. 6.3.4. Merging worksheet cells to create additional text space
        5. 6.3.5. Displaying text at an angle
        6. 6.3.6. Controlling the text direction
      4. 6.4. Using Colors and Shading
      5. 6.5. Adding Borders and Lines
      6. 6.6. Adding a Background Image to a Worksheet
      7. 6.7. Using Named Styles for Easier Formatting
        1. 6.7.1. Applying styles
        2. 6.7.2. Modifying an existing style
        3. 6.7.3. Creating new styles
        4. 6.7.4. Merging styles from other workbooks
        5. 6.7.5. Controlling styles with templates
      8. 6.8. Understanding Document Themes
        1. 6.8.1. Applying a theme
        2. 6.8.2. Customizing a theme
    7. 7. Understanding Excel Files
      1. 7.1. Creating a New Workbook
      2. 7.2. Opening an Existing Workbook
        1. 7.2.1. Using the Favorite Links
        2. 7.2.2. Filtering filenames
        3. 7.2.3. Choosing your file display preferences
      3. 7.3. Saving a Workbook
      4. 7.4. Using AutoRecover
        1. 7.4.1. Recovering versions of the current workbook
        2. 7.4.2. Recovering unsaved work
      5. 7.5. Specifying a Password
      6. 7.6. Organizing Your Files
      7. 7.7. Other Workbook Info Options
        1. 7.7.1. Security Warning section
        2. 7.7.2. Compatibility Mode section
        3. 7.7.3. Permissions section
        4. 7.7.4. Prepare for Sharing section
        5. 7.7.5. Versions section
      8. 7.8. Closing Workbooks
      9. 7.9. Safeguarding Your Work
      10. 7.10. Excel File Compatibility
        1. 7.10.1. Checking compatibility
        2. 7.10.2. Recognizing the Excel 2010 file formats
        3. 7.10.3. Saving a file for use with an older version of Excel
    8. 8. Using and Creating Templates
      1. 8.1. Exploring Excel Templates
        1. 8.1.1. Viewing templates
        2. 8.1.2. Creating a workbook from a template
        3. 8.1.3. Modifying a template
      2. 8.2. Understanding Custom Excel Templates
        1. 8.2.1. Working with the default templates
          1. 8.2.1.1. Using the workbook template to change workbook defaults
          2. 8.2.1.2. Using the worksheet template to change worksheet defaults
          3. 8.2.1.3. Editing your templates
          4. 8.2.1.4. Resetting the default workbook and worksheet settings
        2. 8.2.2. Creating custom templates
          1. 8.2.2.1. Saving your custom templates
          2. 8.2.2.2. Ideas for creating templates
    9. 9. Printing Your Work
      1. 9.1. Printing with One Click
      2. 9.2. Changing Your Page View
        1. 9.2.1. Normal view
        2. 9.2.2. Page Layout view
        3. 9.2.3. Page Break Preview
      3. 9.3. Adjusting Common Page Setup Settings
        1. 9.3.1. Choosing your printer
        2. 9.3.2. Specifying what you want to print
        3. 9.3.3. Changing page orientation
        4. 9.3.4. Specifying paper size
        5. 9.3.5. Printing multiple copies of a report
        6. 9.3.6. Adjusting page margins
        7. 9.3.7. Understanding page breaks
          1. 9.3.7.1. Inserting a page break
          2. 9.3.7.2. Removing manual page breaks
        8. 9.3.8. Printing row and column titles
        9. 9.3.9. Scaling printed output
        10. 9.3.10. Printing cell gridlines
        11. 9.3.11. Printing row and column headers
        12. 9.3.12. Using a background image
      4. 9.4. Adding a Header or Footer to Your Reports
        1. 9.4.1. Selecting a predefined header or footer
        2. 9.4.2. Understanding header and footer element codes
        3. 9.4.3. Other header and footer options
      5. 9.5. Copying Page Setup Settings across Sheets
      6. 9.6. Preventing Certain Cells from Being Printed
      7. 9.7. Preventing Objects from Being Printed
      8. 9.8. Creating Custom Views of Your Worksheet
  8. II. Working with Formulas and Functions
    1. 10. Introducing Formulas and Functions
      1. 10.1. Understanding Formula Basics
        1. 10.1.1. Using operators in formulas
        2. 10.1.2. Understanding operator precedence in formulas
        3. 10.1.3. Using functions in your formulas
          1. 10.1.3.1. Examples of formulas that use functions
          2. 10.1.3.2. Function arguments
          3. 10.1.3.3. More about functions
      2. 10.2. Entering Formulas into Your Worksheets
        1. 10.2.1. Entering formulas manually
        2. 10.2.2. Entering formulas by pointing
        3. 10.2.3. Pasting range names into formulas
        4. 10.2.4. Inserting functions into formulas
        5. 10.2.5. Function entry tips
      3. 10.3. Editing Formulas
      4. 10.4. Using Cell References in Formulas
        1. 10.4.1. Using relative, absolute, and mixed references
        2. 10.4.2. Changing the types of your references
        3. 10.4.3. Referencing cells outside the worksheet
          1. 10.4.3.1. Referencing cells in other worksheets
          2. 10.4.3.2. Referencing cells in other workbooks
      5. 10.5. Using Formulas in Tables
        1. 10.5.1. Summarizing data in a table
        2. 10.5.2. Using formulas within a table
        3. 10.5.3. Referencing data in a table
      6. 10.6. Correcting Common Formula Errors
        1. 10.6.1. Handling circular references
        2. 10.6.2. Specifying when formulas are calculated
      7. 10.7. Using Advanced Naming Techniques
        1. 10.7.1. Using names for constants
        2. 10.7.2. Using names for formulas
        3. 10.7.3. Using range intersections
        4. 10.7.4. Applying names to existing references
      8. 10.8. Tips for Working with Formulas
        1. 10.8.1. Don't hard-code values
        2. 10.8.2. Using the Formula bar as a calculator
        3. 10.8.3. Making an exact copy of a formula
        4. 10.8.4. Converting formulas to values
    2. 11. Creating Formulas That Manipulate Text
      1. 11.1. A Few Words about Text
      2. 11.2. Text Functions
        1. 11.2.1. Working with character codes
          1. 11.2.1.1. The CODE function
          2. 11.2.1.2. The CHAR function
        2. 11.2.2. Determining whether two strings are identical
        3. 11.2.3. Joining two or more cells
        4. 11.2.4. Displaying formatted values as text
        5. 11.2.5. Displaying formatted currency values as text
        6. 11.2.6. Repeating a character or string
        7. 11.2.7. Creating a text histogram
        8. 11.2.8. Padding a number
        9. 11.2.9. Removing excess spaces and nonprinting characters
        10. 11.2.10. Counting characters in a string
        11. 11.2.11. Changing the case of text
        12. 11.2.12. Extracting characters from a string
        13. 11.2.13. Replacing text with other text
        14. 11.2.14. Finding and searching within a string
        15. 11.2.15. Searching and replacing within a string
      3. 11.3. Advanced Text Formulas
        1. 11.3.1. Counting specific characters in a cell
        2. 11.3.2. Counting the occurrences of a substring in a cell
        3. 11.3.3. Extracting a filename from a path specification
        4. 11.3.4. Extracting the first word of a string
        5. 11.3.5. Extracting the last word of a string
        6. 11.3.6. Extracting all but the first word of a string
        7. 11.3.7. Extracting first names, middle names, and last names
        8. 11.3.8. Removing titles from names
        9. 11.3.9. Creating an ordinal number
        10. 11.3.10. Counting the number of words in a cell
    3. 12. Working with Dates and Times
      1. 12.1. How Excel Handles Dates and Times
        1. 12.1.1. Understanding date serial numbers
        2. 12.1.2. Entering dates
        3. 12.1.3. Understanding time serial numbers
        4. 12.1.4. Entering times
        5. 12.1.5. Formatting dates and times
        6. 12.1.6. Problems with dates
          1. 12.1.6.1. Excel's leap year bug
          2. 12.1.6.2. Pre-1900 dates
          3. 12.1.6.3. Inconsistent date entries
      2. 12.2. Date-Related Worksheet Functions
        1. 12.2.1. Displaying the current date
        2. 12.2.2. Displaying any date
        3. 12.2.3. Generating a series of dates
        4. 12.2.4. Converting a nondate string to a date
        5. 12.2.5. Calculating the number of days between two dates
        6. 12.2.6. Calculating the number of work days between two dates
        7. 12.2.7. Offsetting a date using only work days
        8. 12.2.8. Calculating the number of years between two dates
        9. 12.2.9. Calculating a person's age
        10. 12.2.10. Determining the day of the year
        11. 12.2.11. Determining the day of the week
        12. 12.2.12. Determining the date of the most recent Sunday
        13. 12.2.13. Determining the first day of the week after a date
        14. 12.2.14. Determining the nth occurrence of a day of the week in a month
        15. 12.2.15. Calculating dates of holidays
          1. 12.2.15.1. New Year's Day
          2. 12.2.15.2. Martin Luther King, Jr. Day
          3. 12.2.15.3. Presidents' Day
          4. 12.2.15.4. Easter
          5. 12.2.15.5. Memorial Day
          6. 12.2.15.6. Independence Day
          7. 12.2.15.7. Labor Day
          8. 12.2.15.8. Columbus Day
          9. 12.2.15.9. Veterans Day
          10. 12.2.15.10. Thanksgiving Day
          11. 12.2.15.11. Christmas Day
        16. 12.2.16. Determining the last day of a month
        17. 12.2.17. Determining whether a year is a leap year
        18. 12.2.18. Determining a date's quarter
      3. 12.3. Time-Related Functions
        1. 12.3.1. Displaying the current time
        2. 12.3.2. Displaying any time
        3. 12.3.3. Calculating the difference between two times
        4. 12.3.4. Summing times that exceed 24 hours
        5. 12.3.5. Converting from military time
        6. 12.3.6. Converting decimal hours, minutes, or seconds to a time
        7. 12.3.7. Adding hours, minutes, or seconds to a time
        8. 12.3.8. Rounding time values
        9. 12.3.9. Working with non–time-of-day values
    4. 13. Creating Formulas That Count and Sum
      1. 13.1. Counting and Summing Worksheet Cells
      2. 13.2. Basic Counting Formulas
        1. 13.2.1. Counting the total number of cells
        2. 13.2.2. Counting blank cells
        3. 13.2.3. Counting nonblank cells
        4. 13.2.4. Counting numeric cells
        5. 13.2.5. Counting text cells
        6. 13.2.6. Counting nontext cells
        7. 13.2.7. Counting logical values
        8. 13.2.8. Counting error values in a range
      3. 13.3. Advanced Counting Formulas
        1. 13.3.1. Counting cells by using the COUNTIF function
        2. 13.3.2. Counting cells based on multiple criteria
          1. 13.3.2.1. Using And criteria
          2. 13.3.2.2. Using Or criteria
          3. 13.3.2.3. Combining And and Or criteria
        3. 13.3.3. Counting the most frequently occurring entry
        4. 13.3.4. Counting the occurrences of specific text
          1. 13.3.4.1. Entire cell contents
          2. 13.3.4.2. Partial cell contents
          3. 13.3.4.3. Total occurrences in a range
        5. 13.3.5. Counting the number of unique values
        6. 13.3.6. Creating a frequency distribution
          1. 13.3.6.1. The FREQUENCY function
          2. 13.3.6.2. Using formulas to create a frequency distribution
          3. 13.3.6.3. Using the Analysis ToolPak to create a frequency distribution
          4. 13.3.6.4. Using a pivot table to create a frequency distribution
      4. 13.4. Summing Formulas
        1. 13.4.1. Summing all cells in a range
        2. 13.4.2. Computing a cumulative sum
        3. 13.4.3. Summing the "top n" values
      5. 13.5. Conditional Sums Using a Single Criterion
        1. 13.5.1. Summing only negative values
        2. 13.5.2. Summing values based on a different range
        3. 13.5.3. Summing values based on a text comparison
        4. 13.5.4. Summing values based on a date comparison
      6. 13.6. Conditional Sums Using Multiple Criteria
        1. 13.6.1. Using And criteria
        2. 13.6.2. Using Or criteria
        3. 13.6.3. Using And and Or criteria
    5. 14. Creating Formulas That Look Up Values
      1. 14.1. Introducing Lookup Formulas
      2. 14.2. Functions Relevant to Lookups
      3. 14.3. Basic Lookup Formulas
        1. 14.3.1. The VLOOKUP function
        2. 14.3.2. The HLOOKUP function
        3. 14.3.3. The LOOKUP function
        4. 14.3.4. Combining the MATCH and INDEX functions
      4. 14.4. Specialized Lookup Formulas
        1. 14.4.1. Looking up an exact value
        2. 14.4.2. Looking up a value to the left
        3. 14.4.3. Performing a case-sensitive lookup
        4. 14.4.4. Looking up a value from multiple lookup tables
        5. 14.4.5. Determining letter grades for test scores
        6. 14.4.6. Calculating a grade-point average
        7. 14.4.7. Performing a two-way lookup
        8. 14.4.8. Performing a two-column lookup
        9. 14.4.9. Determining the cell address of a value within a range
        10. 14.4.10. Looking up a value by using the closest match
    6. 15. Creating Formulas for Financial Applications
      1. 15.1. The Time Value of Money
      2. 15.2. Loan Calculations
        1. 15.2.1. Worksheet functions for calculating loan information
          1. 15.2.1.1. PMT
          2. 15.2.1.2. PPMT
          3. 15.2.1.3. IPMT
          4. 15.2.1.4. RATE
          5. 15.2.1.5. NPER
          6. 15.2.1.6. PV
        2. 15.2.2. A loan calculation example
        3. 15.2.3. Credit card payments
        4. 15.2.4. Creating a loan amortization schedule
        5. 15.2.5. Summarizing loan options by using a data table
          1. 15.2.5.1. Creating a one-way data table
          2. 15.2.5.2. Creating a two-way data table
        6. 15.2.6. Calculating a loan with irregular payments
      3. 15.3. Investment Calculations
        1. 15.3.1. Future value of a single deposit
          1. 15.3.1.1. Calculating simple interest
          2. 15.3.1.2. Calculating compound interest
          3. 15.3.1.3. Calculating interest with continuous compounding
        2. 15.3.2. Future value of a series of deposits
      4. 15.4. Depreciation Calculations
    7. 16. Introducing Array Formulas
      1. 16.1. Understanding Array Formulas
        1. 16.1.1. A multicell array formula
        2. 16.1.2. A single-cell array formula
        3. 16.1.3. Creating an array constant
        4. 16.1.4. Array constant elements
      2. 16.2. Understanding the Dimensions of an Array
        1. 16.2.1. One-dimensional horizontal arrays
        2. 16.2.2. One-dimensional vertical arrays
        3. 16.2.3. Two-dimensional arrays
      3. 16.3. Naming Array Constants
      4. 16.4. Working with Array Formulas
        1. 16.4.1. Entering an array formula
        2. 16.4.2. Selecting an array formula range
        3. 16.4.3. Editing an array formula
        4. 16.4.4. Expanding or contracting a multicell array formula
      5. 16.5. Using Multicell Array Formulas
        1. 16.5.1. Creating an array from values in a range
        2. 16.5.2. Creating an array constant from values in a range
        3. 16.5.3. Performing operations on an array
        4. 16.5.4. Using functions with an array
        5. 16.5.5. Transposing an array
        6. 16.5.6. Generating an array of consecutive integers
      6. 16.6. Using Single-Cell Array Formulas
        1. 16.6.1. Counting characters in a range
        2. 16.6.2. Summing the three smallest values in a range
        3. 16.6.3. Counting text cells in a range
        4. 16.6.4. Eliminating intermediate formulas
        5. 16.6.5. Using an array in lieu of a range reference
    8. 17. Performing Magic with Array Formulas
      1. 17.1. Working with Single-Cell Array Formulas
        1. 17.1.1. Summing a range that contains errors
        2. 17.1.2. Counting the number of error values in a range
        3. 17.1.3. Summing the n largest values in a range
        4. 17.1.4. Computing an average that excludes zeros
        5. 17.1.5. Determining whether a particular value appears in a range
        6. 17.1.6. Counting the number of differences in two ranges
        7. 17.1.7. Returning the location of the maximum value in a range
        8. 17.1.8. Finding the row of a value's nth occurrence in a range
        9. 17.1.9. Returning the longest text in a range
        10. 17.1.10. Determining whether a range contains valid values
        11. 17.1.11. Summing the digits of an integer
        12. 17.1.12. Summing rounded values
        13. 17.1.13. Summing every nth value in a range
        14. 17.1.14. Removing non-numeric characters from a string
        15. 17.1.15. Determining the closest value in a range
        16. 17.1.16. Returning the last value in a column
        17. 17.1.17. Returning the last value in a row
        18. 17.1.18. Ranking data with an array formula
      2. 17.2. Working with Multicell Array Formulas
        1. 17.2.1. Returning only positive values from a range
        2. 17.2.2. Returning nonblank cells from a range
        3. 17.2.3. Reversing the order of cells in a range
        4. 17.2.4. Sorting a range of values dynamically
        5. 17.2.5. Returning a list of unique items in a range
        6. 17.2.6. Displaying a calendar in a range
  9. III. Creating Charts and Graphics
    1. 18. Getting Started Making Charts
      1. 18.1. What Is a Chart?
      2. 18.2. Understanding How Excel Handles Charts
        1. 18.2.1. Embedded charts
        2. 18.2.2. Chart sheets
      3. 18.3. Creating a Chart
      4. 18.4. Hands On: Creating and Customizing a Chart
        1. 18.4.1. Selecting the data
        2. 18.4.2. Choosing a chart type
        3. 18.4.3. Experimenting with different layouts
        4. 18.4.4. Trying another view of the data
        5. 18.4.5. Trying other chart types
        6. 18.4.6. Trying other chart styles
      5. 18.5. Working with Charts
        1. 18.5.1. Resizing a chart
        2. 18.5.2. Moving a chart
        3. 18.5.3. Copying a chart
        4. 18.5.4. Deleting a chart
        5. 18.5.5. Adding chart elements
        6. 18.5.6. Moving and deleting chart elements
        7. 18.5.7. Formatting chart elements
        8. 18.5.8. Printing charts
      6. 18.6. Understanding Chart Types
        1. 18.6.1. Choosing a chart type
        2. 18.6.2. Column
        3. 18.6.3. Bar
        4. 18.6.4. Line
        5. 18.6.5. Pie
        6. 18.6.6. XY (scatter)
        7. 18.6.7. Area
        8. 18.6.8. Doughnut
        9. 18.6.9. Radar
        10. 18.6.10. Surface
        11. 18.6.11. Bubble
        12. 18.6.12. Stock
      7. 18.7. Learning More
    2. 19. Learning Advanced Charting
      1. 19.1. Selecting Chart Elements
        1. 19.1.1. Selecting with the mouse
        2. 19.1.2. Selecting with the keyboard
        3. 19.1.3. Selecting with the Chart Element control
      2. 19.2. User Interface Choices for Modifying Chart Elements
        1. 19.2.1. Using the Format dialog box
        2. 19.2.2. Using the Ribbon
        3. 19.2.3. Using the Mini toolbar
      3. 19.3. Modifying the Chart Area
      4. 19.4. Modifying the Plot Area
      5. 19.5. Working with Chart Titles
      6. 19.6. Working with a Legend
      7. 19.7. Working with Gridlines
      8. 19.8. Modifying the Axes
        1. 19.8.1. Value axis
        2. 19.8.2. Category axis
      9. 19.9. Working with Data Series
        1. 19.9.1. Deleting a data series
        2. 19.9.2. Adding a new data series to a chart
        3. 19.9.3. Changing data used by a series
          1. 19.9.3.1. Changing the data range by dragging the range outline
          2. 19.9.3.2. Using the Edit Series dialog box
          3. 19.9.3.3. Editing the Series formula
        4. 19.9.4. Displaying data labels in a chart
        5. 19.9.5. Handling missing data
        6. 19.9.6. Adding error bars
        7. 19.9.7. Adding a trendline
        8. 19.9.8. Modifying 3-D charts
        9. 19.9.9. Creating combination charts
        10. 19.9.10. Displaying a data table
      10. 19.10. Creating Chart Templates
      11. 19.11. Learning Some Chart-Making Tricks
        1. 19.11.1. Creating picture charts
        2. 19.11.2. Creating a thermometer chart
        3. 19.11.3. Creating a gauge chart
        4. 19.11.4. Displaying conditional colors in a column chart
        5. 19.11.5. Creating a comparative histogram
        6. 19.11.6. Creating a Gantt chart
        7. 19.11.7. Plotting mathematical functions with one variable
        8. 19.11.8. Plotting mathematical functions with two variables
    3. 20. Visualizing Data Using Conditional Formatting
      1. 20.1. About Conditional Formatting
      2. 20.2. Specifying Conditional Formatting
        1. 20.2.1. Formatting types you can apply
        2. 20.2.2. Making your own rules
      3. 20.3. Conditional Formats That Use Graphics
        1. 20.3.1. Using data bars
          1. 20.3.1.1. A simple data bar
          2. 20.3.1.2. Using data bars in lieu of a chart
        2. 20.3.2. Using color scales
          1. 20.3.2.1. A color scale example
          2. 20.3.2.2. An extreme color scale example
        3. 20.3.3. Using icon sets
          1. 20.3.3.1. An icon set example
          2. 20.3.3.2. Another icon set example
      4. 20.4. Creating Formula-Based Rules
        1. 20.4.1. Understanding relative and absolute references
        2. 20.4.2. Conditional formatting formula examples
          1. 20.4.2.1. Identifying weekend days
          2. 20.4.2.2. Displaying alternate-row shading
          3. 20.4.2.3. Creating checkerboard shading
          4. 20.4.2.4. Shading groups of rows
          5. 20.4.2.5. Displaying a total only when all values are entered
      5. 20.5. Working with Conditional Formats
        1. 20.5.1. Managing rules
        2. 20.5.2. Copying cells that contain conditional formatting
        3. 20.5.3. Deleting conditional formatting
        4. 20.5.4. Locating cells that contain conditional formatting
    4. 21. Creating Sparkline Graphics
      1. 21.1. Sparkline Types
      2. 21.2. Creating Sparklines
      3. 21.3. Customizing Sparklines
        1. 21.3.1. Sizing Sparkline cells
        2. 21.3.2. Handling hidden or missing data
        3. 21.3.3. Changing the Sparkline type
        4. 21.3.4. Changing Sparkline colors and line width
        5. 21.3.5. Highlighting certain data points
        6. 21.3.6. Adjusting Sparkline axis scaling
        7. 21.3.7. Faking a reference line
      4. 21.4. Specifying a Date Axis
      5. 21.5. Auto-Updating Sparklines
      6. 21.6. Displaying a Sparkline for a Dynamic Range
    5. 22. Enhancing Your Work with Pictures and Drawings
      1. 22.1. Using Shapes
        1. 22.1.1. Inserting a Shape
        2. 22.1.2. Adding text to a Shape
        3. 22.1.3. Formatting Shapes
        4. 22.1.4. Grouping objects
        5. 22.1.5. Aligning and spacing objects
        6. 22.1.6. Reshaping Shapes
        7. 22.1.7. Printing objects
      2. 22.2. Using SmartArt
        1. 22.2.1. Inserting SmartArt
        2. 22.2.2. Customizing SmartArt
        3. 22.2.3. Changing the layout
        4. 22.2.4. Changing the style
        5. 22.2.5. Learning more about SmartArt
      3. 22.3. Using WordArt
      4. 22.4. Working with Other Graphic Types
        1. 22.4.1. About graphics files
        2. 22.4.2. Using the Clip Art task pane
        3. 22.4.3. Inserting graphics files
        4. 22.4.4. Inserting screenshots
        5. 22.4.5. Displaying a worksheet background image
      5. 22.5. Using the Equation Editor
  10. IV. Using Advanced Excel Features
    1. 23. Customizing the Excel User Interface
      1. 23.1. Customizing the Quick Access Toolbar
        1. 23.1.1. About the Quick Access toolbar
        2. 23.1.2. Adding new commands to the Quick Access toolbar
        3. 23.1.3. Other Quick Access toolbar actions
      2. 23.2. Customizing the Ribbon
        1. 23.2.1. Why customize the Ribbon?
        2. 23.2.2. What can be customized
        3. 23.2.3. How to customize the Ribbon
    2. 24. Using Custom Number Formats
      1. 24.1. About Number Formatting
        1. 24.1.1. Automatic number formatting
        2. 24.1.2. Formatting numbers by using the Ribbon
        3. 24.1.3. Using shortcut keys to format numbers
        4. 24.1.4. Using the Format Cells dialog box to format numbers
      2. 24.2. Creating a Custom Number Format
        1. 24.2.1. Parts of a number format string
        2. 24.2.2. Custom number format codes
      3. 24.3. Custom Number Format Examples
        1. 24.3.1. Scaling values
          1. 24.3.1.1. Displaying values in thousands
        2. 24.3.2. Displaying values in hundreds
        3. 24.3.3. Displaying values in millions
        4. 24.3.4. Adding zeros to a value
        5. 24.3.5. Displaying leading zeros
        6. 24.3.6. Displaying fractions
        7. 24.3.7. Displaying a negative sign on the right
        8. 24.3.8. Formatting dates and times
        9. 24.3.9. Displaying text with numbers
        10. 24.3.10. Suppressing certain types of entries
        11. 24.3.11. Filling a cell with a repeating character
    3. 25. Using Data Validation
      1. 25.1. About Data Validation
      2. 25.2. Specifying Validation Criteria
      3. 25.3. Types of Validation Criteria You Can Apply
      4. 25.4. Creating a Drop-Down List
      5. 25.5. Using Formulas for Data Validation Rules
      6. 25.6. Understanding Cell References
      7. 25.7. Data Validation Formula Examples
        1. 25.7.1. Accepting text only
        2. 25.7.2. Accepting a larger value than the previous cell
        3. 25.7.3. Accepting nonduplicate entries only
        4. 25.7.4. Accepting text that begins with a specific character
        5. 25.7.5. Accepting dates by the day of the week
        6. 25.7.6. Accepting only values that don't exceed a total
        7. 25.7.7. Creating a dependent list
    4. 26. Creating and Using Worksheet Outlines
      1. 26.1. Introducing Worksheet Outlines
      2. 26.2. Creating an Outline
        1. 26.2.1. Preparing the data
        2. 26.2.2. Creating an outline automatically
        3. 26.2.3. Creating an outline manually
      3. 26.3. Working with Outlines
        1. 26.3.1. Displaying levels
        2. 26.3.2. Adding data to an outline
        3. 26.3.3. Removing an outline
        4. 26.3.4. Hiding the outline symbols
    5. 27. Linking and Consolidating Worksheets
      1. 27.1. Linking Workbooks
      2. 27.2. Creating External Reference Formulas
        1. 27.2.1. Understanding link formula syntax
        2. 27.2.2. Creating a link formula by pointing
        3. 27.2.3. Pasting links
      3. 27.3. Working with External Reference Formulas
        1. 27.3.1. Creating links to unsaved workbooks
        2. 27.3.2. Opening a workbook with external reference formulas
        3. 27.3.3. Changing the startup prompt
        4. 27.3.4. Updating links
        5. 27.3.5. Changing the link source
        6. 27.3.6. Severing links
      4. 27.4. Avoiding Potential Problems with External Reference Formulas
        1. 27.4.1. Renaming or moving a source workbook
        2. 27.4.2. Using the Save As command
        3. 27.4.3. Modifying a source workbook
        4. 27.4.4. Intermediary links
      5. 27.5. Consolidating Worksheets
        1. 27.5.1. Consolidating worksheets by using formulas
        2. 27.5.2. Consolidating worksheets by using Paste Special
        3. 27.5.3. Consolidating worksheets by using the Consolidate command
        4. 27.5.4. A workbook consolidation example
        5. 27.5.5. Refreshing a consolidation
        6. 27.5.6. More about consolidation
    6. 28. Excel and the Internet
      1. 28.1. Understanding How Excel Uses HTML
      2. 28.2. Understanding the Different Web Formats
        1. 28.2.1. Creating an HTML file
        2. 28.2.2. Creating a single file Web page
      3. 28.3. Opening an HTML File
      4. 28.4. Working with Hyperlinks
        1. 28.4.1. Inserting a hyperlink
        2. 28.4.2. Using hyperlinks
      5. 28.5. Using Web Queries
      6. 28.6. Other Internet-Related Features
    7. 29. Sharing Data with Other Office Applications
      1. 29.1. Copying and Pasting
      2. 29.2. Copying from Excel to Word
        1. 29.2.1. Pasting static information
        2. 29.2.2. Pasting a link
      3. 29.3. Embedding Objects in a Worksheet
        1. 29.3.1. Embedding Word documents
        2. 29.3.2. Embedding other types of documents
      4. 29.4. Embedding an Excel Workbook in a Word Document
        1. 29.4.1. Embedding a workbook in Word by copying
        2. 29.4.2. Embedding a saved workbook in Word
        3. 29.4.3. Creating a new Excel object in Word
    8. 30. Using Excel in a Workgroup
      1. 30.1. Using Excel on a Network
      2. 30.2. Understanding File Reservations
      3. 30.3. Sharing Workbooks
        1. 30.3.1. Understanding shared workbooks
        2. 30.3.2. Designating a workbook as a shared workbook
        3. 30.3.3. Controlling the advanced sharing settings
          1. 30.3.3.1. Tracking changes
          2. 30.3.3.2. Updating changes
          3. 30.3.3.3. Resolving conflicting changes between users
          4. 30.3.3.4. Controlling the Include in Personal View settings
      4. 30.4. Tracking Workbook Changes
        1. 30.4.1. Turning Track Changes on and off
        2. 30.4.2. Reviewing the changes
    9. 31. Protecting Your Work
      1. 31.1. Types of Protection
      2. 31.2. Protecting a Worksheet
        1. 31.2.1. Unlocking cells
        2. 31.2.2. Sheet protection options
        3. 31.2.3. Assigning user permissions
      3. 31.3. Protecting a Workbook
        1. 31.3.1. Requiring a password to open a workbook
        2. 31.3.2. Protecting a workbook's structure
        3. 31.3.3. Protecting a workbook's windows
      4. 31.4. VB Project Protection
      5. 31.5. Related Topics
        1. 31.5.1. Saving a worksheet as a PDF file
        2. 31.5.2. Marking a workbook final
        3. 31.5.3. Inspecting a workbook
        4. 31.5.4. Using a digital signature
          1. 31.5.4.1. Getting a digital ID
          2. 31.5.4.2. Signing a workbook
    10. 32. Making Your Worksheets Error-Free
      1. 32.1. Finding and Correcting Formula Errors
        1. 32.1.1. Mismatched parentheses
        2. 32.1.2. Cells are filled with hash marks
        3. 32.1.3. Blank cells are not blank
        4. 32.1.4. Extra space characters
        5. 32.1.5. Formulas returning an error
          1. 32.1.5.1. #DIV/0! errors
          2. 32.1.5.2. #N/A errors
          3. 32.1.5.3. #NAME? errors
          4. 32.1.5.4. #NULL! errors
          5. 32.1.5.5. #NUM! errors
          6. 32.1.5.6. #REF! errors
          7. 32.1.5.7. #VALUE! errors
        6. 32.1.6. Absolute/relative reference problems
        7. 32.1.7. Operator precedence problems
        8. 32.1.8. Formulas are not calculated
        9. 32.1.9. Actual versus displayed values
        10. 32.1.10. Floating point number errors
        11. 32.1.11. "Phantom link" errors
      2. 32.2. Using Excel Auditing Tools
        1. 32.2.1. Identifying cells of a particular type
        2. 32.2.2. Viewing formulas
        3. 32.2.3. Tracing cell relationships
          1. 32.2.3.1. Identifying precedents
          2. 32.2.3.2. Identifying dependents
        4. 32.2.4. Tracing error values
        5. 32.2.5. Fixing circular reference errors
        6. 32.2.6. Using background error-checking feature
        7. 32.2.7. Using the Excel Formula Evaluator
      3. 32.3. Searching and Replacing
        1. 32.3.1. Searching for information
        2. 32.3.2. Replacing information
        3. 32.3.3. Searching for formatting
      4. 32.4. Spell Checking Your Worksheets
      5. 32.5. Using AutoCorrect
  11. V. Analyzing Data with Excel
    1. 33. Getting Data from External Database Files
      1. 33.1. Understanding External Database Files
      2. 33.2. Importing Access Tables
      3. 33.3. Retrieving Data with Query: An Example
        1. 33.3.1. The database file
        2. 33.3.2. The task
        3. 33.3.3. Selecting a data source
        4. 33.3.4. Using the Query Wizard
          1. 33.3.4.1. Query Wizard: Choosing the columns
          2. 33.3.4.2. Query Wizard: Filtering data
          3. 33.3.4.3. Query Wizard: Sort order
          4. 33.3.4.4. Query Wizard: Finish
        5. 33.3.5. Specifying a location for the data
      4. 33.4. Working with Data Returned by Query
        1. 33.4.1. Adjusting the external data range properties
        2. 33.4.2. Refreshing a query
        3. 33.4.3. Deleting a query
        4. 33.4.4. Changing your query
      5. 33.5. Using Query without the Wizard
        1. 33.5.1. Creating a query manually
        2. 33.5.2. Using multiple database tables
        3. 33.5.3. Adding and editing records in external database tables
        4. 33.5.4. Formatting data
      6. 33.6. Learning More about Query
    2. 34. Introducing Pivot Tables
      1. 34.1. About Pivot Tables
        1. 34.1.1. A pivot table example
        2. 34.1.2. Data appropriate for a pivot table
      2. 34.2. Creating a Pivot Table
        1. 34.2.1. Specifying the data
        2. 34.2.2. Specifying the location for the pivot table
        3. 34.2.3. Laying out the pivot table
        4. 34.2.4. Formatting the pivot table
        5. 34.2.5. Modifying the pivot table
      3. 34.3. More Pivot Table Examples
        1. 34.3.1. Question 1
        2. 34.3.2. Question 2
        3. 34.3.3. Question 3
        4. 34.3.4. Question 4
        5. 34.3.5. Question 5
        6. 34.3.6. Question 6
        7. 34.3.7. Question 7
      4. 34.4. Learning More
    3. 35. Analyzing Data with Pivot Tables
      1. 35.1. Working with Non-Numeric Data
      2. 35.2. Grouping Pivot Table Items
        1. 35.2.1. A manual grouping example
        2. 35.2.2. Automatic grouping examples
          1. 35.2.2.1. Grouping by date
          2. 35.2.2.2. Grouping by time
      3. 35.3. Creating a Frequency Distribution
      4. 35.4. Creating a Calculated Field or Calculated Item
        1. 35.4.1. Creating a calculated field
        2. 35.4.2. Inserting a calculated item
      5. 35.5. Filtering Pivot Tables with Slicers
      6. 35.6. Referencing Cells within a Pivot Table
      7. 35.7. Creating Pivot Charts
        1. 35.7.1. A pivot chart example
        2. 35.7.2. More about pivot charts
      8. 35.8. Another Pivot Table Example
      9. 35.9. Producing a Report with a Pivot Table
    4. 36. Performing Spreadsheet What-If Analysis
      1. 36.1. A What-If Example
      2. 36.2. Types of What-If Analyses
      3. 36.3. Manual What-If Analysis
      4. 36.4. Creating Data Tables
        1. 36.4.1. Creating a one-input data table
        2. 36.4.2. Creating a two-input data table
      5. 36.5. Using Scenario Manager
        1. 36.5.1. Defining scenarios
        2. 36.5.2. Displaying scenarios
        3. 36.5.3. Modifying scenarios
        4. 36.5.4. Merging scenarios
        5. 36.5.5. Generating a scenario report
    5. 37. Analyzing Data Using Goal Seeking and Solver
      1. 37.1. What-If Analysis, in Reverse
      2. 37.2. Single-Cell Goal Seeking
        1. 37.2.1. A goal-seeking example
        2. 37.2.2. More about goal seeking
      3. 37.3. Introducing Solver
        1. 37.3.1. Appropriate problems for Solver
        2. 37.3.2. A simple Solver example
        3. 37.3.3. More about Solver
      4. 37.4. Solver Examples
        1. 37.4.1. Solving simultaneous linear equations
        2. 37.4.2. Minimizing shipping costs
        3. 37.4.3. Allocating resources
        4. 37.4.4. Optimizing an investment portfolio
    6. 38. Analyzing Data with the Analysis ToolPak
      1. 38.1. The Analysis ToolPak: An Overview
      2. 38.2. Installing the Analysis ToolPak Add-in
      3. 38.3. Using the Analysis Tools
      4. 38.4. Introducing the Analysis ToolPak Tools
        1. 38.4.1. Analysis of Variance
        2. 38.4.2. Correlation
        3. 38.4.3. Covariance
        4. 38.4.4. Descriptive Statistics
        5. 38.4.5. Exponential Smoothing
        6. 38.4.6. F-Test (two-sample test for variance)
        7. 38.4.7. Fourier Analysis
        8. 38.4.8. Histogram
        9. 38.4.9. Moving Average
        10. 38.4.10. Random Number Generation
        11. 38.4.11. Rank and Percentile
        12. 38.4.12. Regression
        13. 38.4.13. Sampling
        14. 38.4.14. t-Test
        15. 38.4.15. z-Test (two-sample test for means)
  12. VI. Programming Excel with VBA
    1. 39. Introducing Visual Basic for Applications
      1. 39.1. Introducing VBA Macros
      2. 39.2. Displaying the Developer Tab
      3. 39.3. About Macro Security
      4. 39.4. Saving Workbooks That Contain Macros
      5. 39.5. Two Types of VBA Macros
        1. 39.5.1. VBA Sub procedures
        2. 39.5.2. VBA functions
      6. 39.6. Creating VBA Macros
        1. 39.6.1. Recording VBA macros
          1. 39.6.1.1. Recording your actions to create VBA code: The basics
          2. 39.6.1.2. Recording a macro: A simple example
          3. 39.6.1.3. Examining the macro
          4. 39.6.1.4. Testing the macro
          5. 39.6.1.5. Editing the macro
          6. 39.6.1.6. Another example
          7. 39.6.1.7. Running the macro
          8. 39.6.1.8. Examining the macro
          9. 39.6.1.9. Re-recording the macro
          10. 39.6.1.10. Testing the macro
        2. 39.6.2. More about recording VBA macros
          1. 39.6.2.1. Absolute versus relative recording
          2. 39.6.2.2. Storing macros in your Personal Macro Workbook
          3. 39.6.2.3. Assigning a macro to a shortcut key
          4. 39.6.2.4. Assigning a macro to a button
        3. 39.6.3. Writing VBA code
          1. 39.6.3.1. The basics: Entering and editing code
          2. 39.6.3.2. How VBA works
          3. 39.6.3.3. Objects and collections
          4. 39.6.3.4. Properties
          5. 39.6.3.5. Methods
          6. 39.6.3.6. Variables
          7. 39.6.3.7. Controlling execution
            1. 39.6.3.7.1. The If-Then construct
            2. 39.6.3.7.2. For-Next loops
            3. 39.6.3.7.3. The With-End With construct
            4. 39.6.3.7.4. The Select Case construct
          8. 39.6.3.8. A macro that can't be recorded
      7. 39.7. Learning More
    2. 40. Creating Custom Worksheet Functions
      1. 40.1. Overview of VBA Functions
      2. 40.2. An Introductory Example
        1. 40.2.1. A custom function
        2. 40.2.2. Using the function in a worksheet
        3. 40.2.3. Analyzing the custom function
      3. 40.3. About Function Procedures
      4. 40.4. Executing Function Procedures
        1. 40.4.1. Calling custom functions from a procedure
        2. 40.4.2. Using custom functions in a worksheet formula
      5. 40.5. Function Procedure Arguments
        1. 40.5.1. A function with no argument
        2. 40.5.2. A function with one argument
        3. 40.5.3. Another function with one argument
        4. 40.5.4. A function with two arguments
        5. 40.5.5. A function with a range argument
      6. 40.6. Debugging Custom Functions
      7. 40.7. Inserting Custom Functions
      8. 40.8. Learning More
    3. 41. Creating UserForms
      1. 41.1. Why Create UserForms?
      2. 41.2. UserForm Alternatives
        1. 41.2.1. The InputBox function
        2. 41.2.2. The MsgBox function
      3. 41.3. Creating UserForms: An Overview
        1. 41.3.1. Working with UserForms
        2. 41.3.2. Adding controls
        3. 41.3.3. Changing the properties of a control
        4. 41.3.4. Handling events
        5. 41.3.5. Displaying a UserForm
      4. 41.4. A UserForm Example
        1. 41.4.1. Creating the UserForm
        2. 41.4.2. Testing the UserForm
        3. 41.4.3. Creating an event-handler procedure
      5. 41.5. Another UserForm Example
        1. 41.5.1. Creating the UserForm
        2. 41.5.2. Testing the UserForm
        3. 41.5.3. Creating event-handler procedures
        4. 41.5.4. Testing the UserForm
        5. 41.5.5. Making the macro available from a worksheet button
        6. 41.5.6. Making the macro available on your Quick Access toolbar
      6. 41.6. More on Creating UserForms
        1. 41.6.1. Adding accelerator keys
        2. 41.6.2. Controlling tab order
      7. 41.7. Learning More
    4. 42. Using UserForm Controls in a Worksheet
      1. 42.1. Why Use Controls on a Worksheet?
      2. 42.2. Using Controls
        1. 42.2.1. Adding a control
        2. 42.2.2. About design mode
        3. 42.2.3. Adjusting properties
        4. 42.2.4. Common properties
        5. 42.2.5. Linking controls to cells
        6. 42.2.6. Creating macros for controls
      3. 42.3. Reviewing the Available ActiveX Controls
        1. 42.3.1. CheckBox
        2. 42.3.2. ComboBox
        3. 42.3.3. CommandButton
        4. 42.3.4. Image
        5. 42.3.5. Label
        6. 42.3.6. ListBox
        7. 42.3.7. OptionButton
        8. 42.3.8. ScrollBar
        9. 42.3.9. SpinButton
        10. 42.3.10. TextBox
        11. 42.3.11. ToggleButton
    5. 43. Working with Excel Events
      1. 43.1. Understanding Events
      2. 43.2. Entering Event-Handler VBA Code
      3. 43.3. Using Workbook-Level Events
        1. 43.3.1. Using the Open event
        2. 43.3.2. Using the SheetActivate event
        3. 43.3.3. Using the NewSheet event
        4. 43.3.4. Using the BeforeSave event
        5. 43.3.5. Using the BeforeClose event
      4. 43.4. Working with Worksheet Events
        1. 43.4.1. Using the Change event
        2. 43.4.2. Monitoring a specific range for changes
        3. 43.4.3. Using the SelectionChange event
        4. 43.4.4. Using the BeforeRightClick event
      5. 43.5. Using Non-Object Events
        1. 43.5.1. Using the OnTime event
        2. 43.5.2. Using the OnKey event
    6. 44. VBA Examples
      1. 44.1. Working with Ranges
        1. 44.1.1. Copying a range
        2. 44.1.2. Copying a variable-size range
        3. 44.1.3. Selecting to the end of a row or column
        4. 44.1.4. Selecting a row or column
        5. 44.1.5. Moving a range
        6. 44.1.6. Looping through a range efficiently
        7. 44.1.7. Prompting for a cell value
        8. 44.1.8. Determining the type of selection
        9. 44.1.9. Identifying a multiple selection
        10. 44.1.10. Counting selected cells
      2. 44.2. Working with Workbooks
        1. 44.2.1. Saving all workbooks
        2. 44.2.2. Saving and closing all workbooks
      3. 44.3. Working with Charts
        1. 44.3.1. Modifying the chart type
        2. 44.3.2. Modifying chart properties
        3. 44.3.3. Applying chart formatting
      4. 44.4. VBA Speed Tips
        1. 44.4.1. Turning off screen updating
        2. 44.4.2. Preventing alert messages
        3. 44.4.3. Simplifying object references
        4. 44.4.4. Declaring variable types
    7. 45. Creating Custom Excel Add-Ins
      1. 45.1. What Is an Add-In?
      2. 45.2. Working with Add-Ins
      3. 45.3. Why Create Add-Ins?
      4. 45.4. Creating Add-Ins
      5. 45.5. An Add-In Example
        1. 45.5.1. Setting up the workbook
        2. 45.5.2. Procedures in Module1
        3. 45.5.3. About the UserForm
        4. 45.5.4. Testing the workbook
        5. 45.5.5. Adding descriptive information
        6. 45.5.6. Protecting the project
        7. 45.5.7. Creating the add-in
        8. 45.5.8. Creating the user interface for your add-in macro
        9. 45.5.9. Installing the add-in
  13. VII. Appendixes
    1. A. Worksheet Function Reference
    2. B. What's on the CD-ROM
      1. B.1. System Requirements
      2. B.2. Using the CD
      3. B.3. What's on the CD
        1. B.3.1. Applications
        2. B.3.2. eBook version of Excel 2010 Bible
        3. B.3.3. Sample files for Excel 2010 Bible
          1. B.3.3.1. Chapter 01
          2. B.3.3.2. Chapter 02
          3. B.3.3.3. Chapter 04
          4. B.3.3.4. Chapter 05
          5. B.3.3.5. Chapter 06
          6. B.3.3.6. Chapter 10
          7. B.3.3.7. Chapter 11
          8. B.3.3.8. Chapter 12
          9. B.3.3.9. Chapter 13
          10. B.3.3.10. Chapter 14
          11. B.3.3.11. Chapter 15
          12. B.3.3.12. Chapter 16
          13. B.3.3.13. Chapter 17
          14. B.3.3.14. Chapter 18
          15. B.3.3.15. Chapter 19
          16. B.3.3.16. Chapter 20
          17. B.3.3.17. Chapter 21
          18. B.3.3.18. Chapter 22
          19. B.3.3.19. Chapter 24
          20. B.3.3.20. Chapter 25
          21. B.3.3.21. Chapter 26
          22. B.3.3.22. Chapter 27
          23. B.3.3.23. Chapter 28
          24. B.3.3.24. Chapter 33
          25. B.3.3.25. Chapter 34
          26. B.3.3.26. Chapter 35
          27. B.3.3.27. Chapter 36
          28. B.3.3.28. Chapter 37
          29. B.3.3.29. Chapter 38
          30. B.3.3.30. Chapter 39
          31. B.3.3.31. Chapter 40
          32. B.3.3.32. Chapter 41
          33. B.3.3.33. Chapter 42
          34. B.3.3.34. Chapter 43
          35. B.3.3.35. Chapter 44
          36. B.3.3.36. Chapter 45
      4. B.4. Troubleshooting
        1. B.4.1. Customer Care
    3. C. Additional Excel Resources
      1. C.1. The Excel Help System
      2. C.2. Microsoft Technical Support
        1. C.2.1. Support options
        2. C.2.2. Microsoft Knowledge Base
        3. C.2.3. Microsoft Excel Home Page
        4. C.2.4. Microsoft Office Home Page
      3. C.3. Internet Newsgroups
        1. C.3.1. Accessing newsgroups by using a newsreader
        2. C.3.2. Accessing newsgroups by using a Web browser
        3. C.3.3. Searching newsgroups
      4. C.4. Internet Web sites
        1. C.4.1. The Spreadsheet Page
        2. C.4.2. Daily Dose of Excel
        3. C.4.3. Jon Peltier's Excel Page
        4. C.4.4. Pearson Software Consulting
        5. C.4.5. Contextures
        6. C.4.6. David McRitchie's Excel Pages
        7. C.4.7. Pointy Haired Dilbert
        8. C.4.8. Mr. Excel
    4. D. Excel Shortcut Keys
  14. Wiley Publishing, Inc. End-User License Agreement