Excel® 2007 Bible

Excel® 2007 Bible

by John Walkenbach
Released January 2007
Publisher(s): Wiley
ISBN: 9780470044032

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

  • This book is a single reference that’s indispensable for Excel beginners, intermediate users, power users, and would-be 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 readers won’t find anywhere else

  • John Walkenbach, aka "Mr. Spreadsheet," is one of the liworld’s leading authorities on Excel

  • Thoroughly updated to cover the revamped Excel interface, new file formats, enhanced interactivity with other Office applications, and upgraded collaboration features

  • Includes a valuable CD-ROM with templates and worksheets from the book

Note: CD-ROM/DVD and other supplementary materials are not included as part of eBook file.

Table of contents

  1. Copyright
    1. Dedication
  2. About the Author
  3. Credits
  4. Acknowledgments
  5. 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
  6. I. Getting Started with Excel
    1. 1. Introducing Excel
      1. What Is Excel Good For?
      2. Understanding Workbooks and Worksheets
      3. Moving Around a Worksheet
        1. Navigating with your keyboard
        2. Navigating with your mouse
      4. Introducing the Ribbon
        1. Ribbon tabs
        2. Contextual tabs
        3. Types of commands on the Ribbon
        4. Accessing the Ribbon using your keyboard
      5. Using the shortcut menus
      6. Customizing your Quick Access Toolbar
      7. Working with Dialog Boxes
        1. Navigating dialog boxes
        2. Using tabbed dialog boxes
      8. Using the Taskbar
      9. Creating Your First Excel Worksheet
        1. Getting started on your worksheet
        2. Filling in the month names
        3. Entering the sales data
        4. Formatting the numbers
        5. Making your worksheet look a bit fancier
        6. Summing the values
        7. Creating a chart
        8. Printing your worksheet
        9. Saving your workbook
    2. 2. What’s New in Excel 2007?
      1. A New User Interface
      2. Larger Worksheets
      3. New File Formats
      4. Worksheet Tables
      5. Styles and Themes
      6. Better Looking Charts
      7. Page Layout View
      8. Enhanced Conditional Formatting
      9. Consolidated Options
      10. SmartArt
      11. Formula AutoComplete
      12. Collaboration Features
      13. Compatibility Checker
      14. Improved Pivot Tables
      15. New Worksheet Functions
      16. Other New Features
    3. 3. Entering and Editing Worksheet Data
      1. Exploring the Types of Data You Can Use
        1. About numerical values
        2. About text entries
        3. About formulas
      2. Entering Text and Values into Your Worksheets
      3. Entering Dates and Times into Your Worksheets
        1. Entering date values
        2. Entering time values
      4. Modifying Cell Contents
        1. Erasing the contents of a cell
        2. Replacing the contents of a cell
        3. Editing the contents of a cell
        4. Learning some handy data-entry techniques
          1. Automatically moving the cell pointer after entering data
          2. Using arrow keys instead of pressing Enter
          3. Selecting a range of input cells before entering data
          4. Using Ctrl+Enter to place information into multiple cells simultaneously
          5. Entering decimal points automatically
          6. Using AutoFill to enter a series of values
          7. Using AutoComplete to automate data entry
          8. Forcing text to appear on a new line within a cell
          9. Using AutoCorrect for shorthand data entry
          10. Entering numbers with fractions
          11. Simplifying data entry by using a form
          12. Entering the current date or time into a cell
      5. Applying Number Formatting
        1. Improving readability by formatting numbers
          1. Using automatic number formatting
          2. Formatting numbers by using the Ribbon
          3. Using shortcut keys to format numbers
          4. Formatting numbers using the Format Cells dialog box
        2. Adding your own custom number formats
    4. 4. Essential Worksheet Operations
      1. Learning the Fundamentals of Excel Worksheets
        1. Working with Excel’s windows
          1. Moving and resizing windows
          2. Switching among windows
          3. Closing windows
        2. Activating a worksheet
        3. Adding a new worksheet to your workbook
        4. Deleting a worksheet you no longer need
        5. Changing the name of a worksheet
        6. Changing a sheet tab’s color
        7. Rearranging your worksheets
        8. Hiding and unhiding a worksheet
      2. Controlling the Worksheet View
        1. Zooming in or out for a better view
        2. Viewing a worksheet in multiple windows
        3. Comparing sheets side by side
        4. Splitting the worksheet window into panes
        5. Keeping the titles in view by freezing panes
        6. Monitoring cells with a Watch Window
      3. Working with Rows and Columns
        1. Inserting rows and columns
        2. Deleting rows and columns
        3. Hiding rows and columns
        4. Changing column widths and row heights
          1. Changing column widths
          2. Changing row heights
    5. 5. Working with Cells and Ranges
      1. Understanding Cells and Ranges
        1. Selecting ranges
        2. Selecting complete rows and columns
        3. Selecting noncontiguous ranges
        4. Selecting multisheet ranges
        5. Selecting special types of cells
        6. Selecting cells by searching
      2. Copying or Moving Ranges
        1. Copying by using Ribbon commands
        2. Copying by using shortcut menu commands
        3. Copying by using shortcut keys
        4. Copying or moving by using drag-and-drop
        5. Copying to adjacent cells
        6. Copying a range to other sheets
        7. Using the Office Clipboard to paste
        8. Pasting in special ways
        9. Using the Paste Special Dialog box
          1. Performing mathematical operations without formulas
          2. Skipping blanks when pasting
          3. Transposing a range
      3. Using Names to Work with Ranges
        1. Creating range names in your workbooks
          1. Using the New Name dialog box
          2. Using the Name box
          3. Using the Create Names From Selection dialog box
        2. Managing Names
      4. Adding Comments to Cells
        1. Formatting comments
        2. Changing a comment’s shape
        3. Reading comments
        4. Hiding and showing comments
        5. Editing comments
        6. Deleting comments
    6. 6. Introducing Tables
      1. What Is a Table?
      2. Creating a Table
      3. Changing the Look of a Table
      4. Working with Tables
        1. Navigating in a table
        2. Selecting parts of a table
        3. Adding new rows or columns
        4. Deleting rows or columns
        5. Moving a table
        6. Setting table options
        7. Working with the Total Row
        8. Removing duplicate rows from a table
        9. Sorting and filtering a table
          1. Sorting a table
          2. Filtering a table
        10. Converting a table back to a range
    7. 7. Worksheet Formatting
      1. Getting to Know the Formatting Tools
        1. Using the Formatting Tools in the Home Tab
        2. Using the Mini toolbar
        3. Using the Format Cells dialog box
      2. Using Formatting in Your Worksheets
        1. Using different fonts
        2. Changing text alignment
          1. Choosing horizontal alignment options
          2. Choosing vertical alignment options
          3. Wrapping or shrinking text to fit the cell
          4. Merging worksheet cells to create additional text space
          5. Displaying text at an angle
          6. Controlling the text direction
        3. Using colors and shading
        4. Adding borders and lines
        5. Adding a background image to a worksheet
      3. Using Named Styles for Easier Formatting
        1. Applying styles
        2. Modifying an existing style
        3. Creating new styles
        4. Merging styles from other workbooks
        5. Controlling styles with templates
      4. Understanding Document Themes
        1. Applying a theme
        2. Customizing a theme
    8. 8. Understanding Excel’s Files
      1. Excel File Operations
        1. Creating a new workbook
        2. Opening an existing workbook
          1. Selecting a different location
          2. Using the My Places bar
          3. Filtering filenames
          4. Choosing your file display preferences
        3. Saving a Workbook
        4. Using AutoRecover
        5. Specifying a password
        6. Other workbook options
          1. Setting workbook properties
          2. Inspecting a document
          3. Encrypting a document
          4. Restricting permissions
          5. Adding a digital signature
          6. Marking a document final
          7. Checking compatibility
        7. Closing workbooks
        8. Safeguarding your work
      2. Excel File Compatibility
        1. Recognizing the Excel 2007 file formats
        2. Saving a file for use with an older version of Excel
    9. 9. Using and Creating Templates
      1. Exploring Excel Templates
        1. Viewing templates
        2. Creating a workbook from a template
        3. Modifying a template
      2. Understanding Custom Excel Templates
        1. Working with the default templates
          1. Using the workbook template to change workbook defaults
          2. Using the worksheet template to change worksheet defaults
          3. Editing your templates
          4. Resetting the default workbook and worksheet settings
        2. Creating custom templates
          1. Saving your custom templates
          2. Ideas for creating templates
    10. 10. Printing Your Work
      1. Printing with One Click
      2. Changing Your Page View
        1. Normal View
        2. Page Layout View
        3. Page Break Preview
      3. Adjusting Common Page Setup Settings
        1. Adjusting the page margins
        2. Changing the page orientation
        3. Specifying the paper size
        4. Specifying the print area
        5. Understanding page breaks
          1. Inserting a page break
          2. Removing page breaks you’ve added
        6. Using a background image
        7. Printing row and column titles
        8. Scaling printed output
        9. Printing cell gridlines
        10. Printing row and column headers
      4. Adding a Header or Footer to Your Reports
        1. Selecting a predefined header or footer
        2. Understanding header and footer element codes
        3. Other header and footer options
      5. Adjusting the Settings in the Print Dialog Box
        1. Choosing your printer
        2. Specifying what you want to print
        3. Printing multiple copies of a report
      6. Preventing Certain Cells from Being Printed
      7. Preventing Objects from being Printed
      8. Creating Custom Views of Your Worksheet
  7. II. Working with Formulas and Functions
    1. 11. Introducing Formulas and Functions
      1. Understanding Formula Basics
        1. Using operators in formulas
        2. Understanding operator precedence in formulas
        3. Using functions in your formulas
          1. Examples of formulas that use functions
          2. Function arguments
          3. More about functions
      2. Entering Formulas into Your Worksheets
        1. Entering formulas manually
        2. Entering formulas by pointing
        3. Pasting range names into formulas
        4. Inserting functions into formulas
        5. Function entry tips
      3. Editing Formulas
      4. Using Cell References in Formulas
        1. Using relative, absolute, and mixed references
        2. Changing the types of your references
        3. Referencing cells outside the worksheet
          1. Referencing cells in other worksheets
          2. Referencing cells in other workbooks
      5. Using Formulas In Tables
        1. Summarizing data in a table
        2. Using formulas within a table
        3. Referencing data in a table
      6. Correcting Common Formula Errors
        1. Handling circular references
        2. Specifying when formulas are calculated
      7. Using Advanced Naming Techniques
        1. Using names for constants
        2. Using names for formulas
        3. Using range intersections
        4. Applying names to existing references
      8. Tips for Working with Formulas
        1. Don’t hard-code values
        2. Using the Formula bar as a calculator
        3. Making an exact copy of a formula
        4. Converting formulas to values
    2. 12. Creating Formulas That Manipulate Text
      1. A Few Words About Text
        1. How many characters in a cell?
        2. Numbers as text
      2. Text Functions
        1. Determining whether a cell contains text
        2. Working with character codes
          1. The CODE function
          2. The CHAR function
        3. Determining whether two strings are identical
        4. Joining two or more cells
        5. Displaying formatted values as text
        6. Displaying formatted currency values as text
        7. Repeating a character or string
        8. Creating a text histogram
        9. Padding a number
        10. Removing excess spaces and nonprinting characters
        11. Counting characters in a string
        12. Changing the case of text
        13. Extracting characters from a string
        14. Replacing text with other text
        15. Finding and searching within a string
        16. Searching and replacing within a string
      3. Advanced Text Formulas
        1. Counting specific characters in a cell
        2. Counting the occurrences of a substring in a cell
        3. Extracting a filename from a path specification
        4. Extracting the first word of a string
        5. Extracting the last word of a string
        6. Extracting all but the first word of a string
        7. Extracting first names, middle names, and last names
        8. Removing titles from names
        9. Creating an ordinal number
        10. Counting the number of words in a cell
    3. 13. Working with Dates and Times
      1. How Excel Handles Dates and Times
        1. Understanding date serial numbers
        2. Entering dates
        3. Understanding time serial numbers
        4. Entering times
        5. Formatting dates and times
        6. Problems with dates
          1. Excel’s leap year bug
          2. Pre-1900 dates
          3. Inconsistent date entries
      2. Date-Related Functions
        1. Displaying the current date
        2. Displaying any date
        3. Generating a series of dates
        4. Converting a nondate string to a date
        5. Calculating the number of days between two dates
        6. Calculating the number of work days between two dates
        7. Offsetting a date using only work days
        8. Calculating the number of years between two dates
        9. Calculating a person’s age
        10. Determining the day of the year
        11. Determining the day of the week
        12. Determining the date of the most recent Sunday
        13. Determining the first day of the week after a date
        14. Determining the nth occurrence of a day of the week in a month
        15. Calculating dates of holidays
          1. New Year’s Day
          2. Martin Luther King, Jr. Day
          3. Presidents’ Day
          4. Memorial Day
          5. Independence Day
          6. Labor Day
          7. Veterans Day
          8. Columbus Day
          9. Thanksgiving Day
          10. Christmas Day
        16. Determining the last day of a month
        17. Determining whether a year is a leap year
        18. Determining a date’s quarter
      3. Time-Related Functions
        1. Displaying the current time
        2. Displaying any time
        3. Calculating the difference between two times
        4. Summing times that exceed 24 hours
        5. Converting from military time
        6. Converting decimal hours, minutes, or seconds to a time
        7. Adding hours, minutes, or seconds to a time
        8. Rounding time values
        9. Working with non-time-of-day values
    4. 14. Creating Formulas That Count and Sum
      1. Counting and Summing Worksheet Cells
      2. Basic Counting Formulas
        1. Counting the total number of cells
        2. Counting blank cells
        3. Counting nonblank cells
        4. Counting numeric cells
        5. Counting text cells
        6. Counting nontext cells
        7. Counting logical values
        8. Counting error values in a range
      3. Advanced Counting Formulas
        1. Counting cells by using the COUNTIF function
        2. Counting cells by using multiple criteria
          1. Using And criteria
          2. Using Or criteria
          3. Combining And and Or criteria
        3. Counting the most frequently occurring entry
        4. Counting the occurrences of specific text
          1. Entire cell contents
          2. Partial cell contents
          3. Total occurrences in a range
        5. Counting the number of unique values
        6. Creating a frequency distribution
          1. The FREQUENCY function
          2. Using formulas to create a frequency distribution
          3. Using the Analysis ToolPak to create a frequency distribution
          4. Using a pivot table to create a frequency distribution
      4. Summing Formulas
        1. Summing all cells in a range
        2. Computing a cumulative sum
        3. Summing the “top n” values
      5. Conditional Sums Using a Single Criterion
        1. Summing only negative values
        2. Summing values based on a different range
        3. Summing values based on a text comparison
        4. Summing values based on a date comparison
      6. Conditional Sums Using Multiple Criteria
        1. Using And criteria
        2. Using Or criteria
        3. Using And and Or criteria
    5. 15. Creating Formulas That Look Up Values
      1. Introducing Lookup Formulas
      2. Functions Relevant to Lookups
      3. Basic Lookup Formulas
        1. The VLOOKUP function
        2. The HLOOKUP function
        3. The LOOKUP function
        4. Combining the MATCH and INDEX functions
      4. Specialized Lookup Formulas
        1. Looking up an exact value
        2. Looking up a value to the left
        3. Performing a case-sensitive lookup
        4. Choosing among multiple lookup tables
        5. Determining letter grades for test scores
        6. Calculating a grade-point average
        7. Performing a two-way lookup
        8. Performing a two-column lookup
        9. Determining the cell address of a value within a range
        10. Looking up a value by using the closest match
    6. 16. Creating Formulas for Financial Applications
      1. The Time Value of Money
      2. Loan Calculations
        1. Worksheet functions for calculating loan information
          1. The PMT function
          2. The PPMT function
          3. The IPMT Function
          4. The RATE function
          5. The NPER function
          6. The PV Function
        2. A loan calculation example
        3. Credit-card payments
        4. Creating a loan amortization schedule
        5. Summarizing loan options by using a data table
          1. Creating a one-way data table
          2. Creating a two-way data table
        6. Calculating a loan with irregular payments
      3. Investment Calculations
        1. Future value of a single deposit
          1. Calculating simple interest
          2. Calculating compound interest
          3. Calculating interest with continuous compounding
        2. Future value of a series of deposits
      4. Depreciation Calculations
    7. 17. Introducing Array Formulas
      1. Understanding Array Formulas
        1. A multicell array formula
        2. A single-cell array formula
        3. Creating an array constant
        4. Array constant elements
      2. Understanding the Dimensions of an Array
        1. One-dimensional horizontal arrays
        2. One-dimensional vertical arrays
        3. Two-dimensional arrays
      3. Naming Array Constants
      4. Working with Array Formulas
        1. Entering an array formula
        2. Selecting an array formula range
        3. Editing an array formula
        4. Expanding or contracting a multicell array formula
      5. Using Multicell Array Formulas
        1. Creating an array from values in a range
        2. Creating an array constant from values in a range
        3. Performing operations on an array
        4. Using functions with an array
        5. Transposing an array
        6. Generating an array of consecutive integers
      6. Using Single-Cell Array Formulas
        1. Counting characters in a range
        2. Summing the three smallest values in a range
        3. Counting text cells in a range
        4. Eliminating intermediate formulas
        5. Using an array in lieu of a range reference
    8. 18. Performing Magic with Array Formulas
      1. Working with Single-Cell Array Formulas
        1. Summing a range that contains errors
        2. Counting the number of error values in a range
        3. Summing the n largest values in a range
        4. Computing an average that excludes zeros
        5. Determining whether a particular value appears in a range
        6. Counting the number of differences in two ranges
        7. Returning the location of the maximum value in a range
        8. Finding the row of a value’s nth occurrence in a range
        9. Returning the longest text in a range
        10. Determining whether a range contains valid values
        11. Summing the digits of an integer
        12. Summing rounded values
        13. Summing every nth value in a range
        14. Removing non-numeric characters from a string
        15. Determining the closest value in a range
        16. Returning the last value in a column
        17. Returning the last value in a row
        18. Ranking data with an array formula
      2. Working with Multicell Array Formulas
        1. Returning only positive values from a range
        2. Returning nonblank cells from a range
        3. Returning a list of unique items in a range
        4. Displaying a calendar in a range
  8. III. Creating Charts and Graphics
    1. 19. Getting Started Making Charts
      1. What Is a Chart?
      2. Understanding How Excel Handles Charts
        1. Embedded charts
        2. Chart sheets
      3. Creating a Chart
      4. Hands On: Creating and Customizing a Chart
        1. Selecting the data
        2. Choosing a chart type
        3. Experimenting with different layouts
        4. Trying another view of the data
        5. Trying other chart types
        6. Trying other chart styles
      5. Working with Charts
        1. Moving and resizing a chart
        2. Copying a chart
        3. Deleting a chart
        4. Adding chart elements
        5. Moving and deleting chart elements
        6. Formatting chart elements
        7. Printing Charts
      6. Understanding Chart Types
        1. Choosing a chart type
        2. Chart type examples
          1. Column charts
          2. Bar charts
          3. Line charts
          4. Pie charts
          5. XY (scatter) charts
          6. Area charts
          7. Doughnut charts
          8. Radar charts
          9. Surface charts
          10. Bubble charts
          11. Stock charts
      7. Learning More
    2. 20. Learning Advanced Charting
      1. Understanding Chart Customization
      2. Selecting Chart Elements
        1. Selecting with the mouse
        2. Selecting with the keyboard
        3. Selecting with the Chart Element control
      3. User Interface Choices for Modifying Chart Elements
        1. Using the Format dialog box
        2. Using the Ribbon and Mini Toolbar
      4. Modifying the Chart Area
      5. Modifying the Plot Area
      6. Working with Chart Titles
      7. Working with the Legend
      8. Working with Gridlines
      9. Modifying the Axes
        1. Value axis options
        2. Category axis options
      10. Working with Data Series
        1. Deleting a data series
        2. Adding a new data series to a chart
        3. Changing data used by a series
          1. Changing the data range by dragging the range outline
          2. Using the Edit Series dialog box
          3. Editing the Series formula
        4. Displaying data labels in a chart
        5. Handling missing data
        6. Adding error bars
        7. Adding a trendline
        8. Modifying 3-D charts
        9. Creating combination charts
        10. Displaying a data table
      11. Creating Chart Templates
      12. Learning Some Chart-Making Tricks
        1. Creating picture charts
        2. Creating a thermometer chart
        3. Creating a gauge chart
        4. Displaying conditional colors in a column chart
        5. Creating a comparative histogram
        6. Creating a Gantt chart
        7. Plotting mathematical functions with one variable
        8. Plotting mathematical functions with two variables
    3. 21. Visualizing Data Using Conditional Formatting
      1. About Conditional Formatting
      2. Specifying Conditional Formatting
        1. Formatting types you can apply
        2. Making your own rules
      3. Conditional Formats That Use Graphics
        1. Using data bars
          1. A data bar example
          2. Using data bars in lieu of a chart
        2. Using color scales
          1. A color scale example
          2. An extreme color scale example
        3. Using Icon Sets
          1. An icon set example
          2. Another icon set example
          3. Displaying only one icon
      4. Creating Formula-Based Rules
        1. Understanding relative and absolute references
        2. Conditional formatting formula examples
          1. Identifying weekend days
          2. Displaying alternate-row shading
          3. Creating checkerboard shading
          4. Shading groups of rows
          5. Displaying a total only when all values are entered
      5. Working with Conditional Formats
        1. Managing rules
        2. Copying cells that contain conditional formatting
        3. Deleting conditional formatting
        4. Find and Replace limitations
        5. Locating cells that contain conditional formatting
    4. 22. Enhancing Your Work with Pictures and Drawings
      1. Using Shapes
        1. Inserting a Shape
        2. Adding text to a Shape
        3. Formatting Shapes
        4. Grouping objects
        5. Aligning and spacing objects
        6. Reshaping Shapes
        7. Printing objects
      2. Using SmartArt
        1. Inserting SmartArt
        2. Customizing SmartArt
        3. Changing the layout
        4. Changing the Style
        5. Learning more about SmartArt
      3. Using WordArt
      4. Working with Other Graphic Types
        1. About graphics files
        2. Using the Clip Art task pane
        3. Inserting graphics files
        4. Copying graphics by using the Clipboard
        5. Displaying a worksheet background image
  9. IV. Using Advanced Excel Features
    1. 23. Customizing the Quick Access Toolbar
      1. About the QAT
      2. Adding New Commands to the QAT
      3. Other QAT Actions
      4. Behind the Scenes
    2. 24. Using Custom Number Formats
      1. About Number Formatting
        1. Automatic number formatting
        2. Formatting numbers by using the Ribbon
        3. Using shortcut keys to format numbers
        4. Using the Format Cells dialog box to format numbers
      2. Creating a Custom Number Format
        1. Parts of a number format string
        2. Custom number format codes
      3. Custom Number Format Examples
        1. Scaling values
          1. Displaying values in thousands
          2. Displaying values in hundreds
          3. Displaying values in millions
          4. Adding zeros to a value
        2. Displaying leading zeros
        3. Displaying fractions
        4. Displaying a negative sign on the right
        5. Formatting dates and times
        6. Displaying text with numbers
        7. Suppressing certain types of entries
        8. Filling a cell with a repeating character
    3. 25. Using Data Validation
      1. About Data Validation
      2. Specifying Validation Criteria
      3. Types of Validation Criteria You Can Apply
      4. Creating a Drop-Down List
      5. Using Formulas for Data Validation Rules
      6. Understanding Cell References
      7. Data Validation Examples
        1. Accepting text only
        2. Accepting a larger value than the previous cell
        3. Accepting nonduplicate entries only
        4. Accepting text that begins with A
        5. Accepting only a date that’s a Monday
        6. Accepting only values that don’t exceed a total
    4. 26. Creating and Using Worksheet Outlines
      1. Introducing Worksheet Outlines
      2. Creating an Outline
        1. Preparing the data
        2. Creating an outline automatically
        3. Creating an outline manually
      3. Working with Outlines
        1. Displaying levels
        2. Adding data to an outline
        3. Removing an outline
        4. Hiding the outline symbols
    5. 27. Linking and Consolidating Worksheets
      1. Linking Workbooks
      2. Creating External Reference Formulas
        1. Understanding the link formula syntax
        2. Creating a link formula by pointing
        3. Pasting links
      3. Working with External Reference Formulas
        1. Creating links to unsaved workbooks
        2. Opening a workbook with external reference formulas
        3. Changing the startup prompt
        4. Updating links
        5. Changing the link source
        6. Severing links
      4. Avoiding Potential Problems with External Reference Formulas
        1. Renaming or moving a source workbook
        2. Using the Save As command
        3. Modifying a source workbook
        4. Intermediary links
      5. Consolidating Worksheets
        1. Consolidating worksheets by using formulas
        2. Consolidating worksheets by using Paste Special
        3. Consolidating worksheets by using the Consolidate command
          1. An example
          2. Refreshing a consolidation
          3. More about consolidation
    6. 28. Excel and the Internet
      1. Understanding How Excel Uses HTML
      2. Understanding the Different Web Formats
        1. Creating an HTML file
        2. Creating a single file Web page
      3. Opening an HTML File
      4. Working with Hyperlinks
        1. Inserting a hyperlink
        2. Using hyperlinks
      5. Using Web Queries
    7. 29. Sharing Data with Other Applications
      1. Understanding Data Sharing
      2. Copying and Pasting
      3. Copying from Excel to Word
        1. Pasting static information
        2. Pasting a link
      4. Embedding Objects in a Worksheet
        1. Embedding Word documents
        2. Embedding other types of documents
      5. Embedding an Excel Workbook in a Word Document
        1. Embedding a workbook in Word by copying
        2. Embedding a saved workbook in Word
        3. Creating a new Excel object in Word
    8. 30. Using Excel in a Workgroup
      1. Using Excel on a Network
      2. Understanding File Reservations
      3. Sharing Workbooks
        1. Understanding shared workbooks
        2. Designating a workbook as a shared workbook
        3. Controlling the advanced sharing settings
          1. Tracking changes
          2. Updating changes
          3. Resolving conflicting changes between users
          4. Controlling the Include in Personal View settings
      4. Tracking Workbook Changes
        1. Turning Track Changes on and off
        2. Reviewing the changes
    9. 31. Protecting Your Work
      1. Types of Protection
      2. Worksheet Protection
        1. Unlocking cells
        2. Sheet protection options
        3. Assigning User Permissions
      3. Workbook Protection
        1. Requiring a password to open a workbook
        2. Protecting a workbook’s structure
        3. Protecting a workbook’s windows
      4. VB Project Protection
      5. Related Topics
        1. Saving a workbook as a PDF file
        2. Marking a workbook final
        3. Inspecting a workbook
        4. Using a digital signature
          1. Getting a digital ID
          2. Signing a workbook
    10. 32. Making Your Worksheets Error-Free
      1. Finding and Correcting Formula Errors
        1. Mismatched parentheses
        2. Cells are filled with hash marks
        3. Blank cells are not blank
        4. Extra space characters
        5. Formulas returning an error
          1. #DIV/0! errors
          2. #N/A errors
          3. #NAME? errors
          4. #NULL! errors
          5. #NUM! errors
          6. #REF! errors
          7. #VALUE! errors
        6. Absolute/relative reference problems
        7. Operator precedence problems
        8. Formulas are not calculated
        9. Actual versus displayed values
        10. Floating point number errors
        11. “Phantom link” errors
      2. Using Excel’s Auditing Tools
        1. Identifying cells of a particular type
        2. Viewing formulas
        3. Tracing cell relationships
          1. Identifying precedents
          2. Identifying dependents
        4. Tracing error values
        5. Fixing circular reference errors
        6. Using background error-checking feature
        7. Using Excel Formula Evaluator
      3. Searching and Replacing
        1. Searching for information
        2. Replacing information
        3. Searching for formatting
      4. Spell Checking Your Worksheets
      5. Using AutoCorrect
  10. V. Analyzing Data with Excel
    1. 33. Using Microsoft Query with External Database Files
      1. Understanding External Database Files
      2. Importing Access Tables
      3. Retrieving Data with Query: An Example
        1. The database file
        2. The task
        3. Using Query to get the data
          1. Selecting a data source
          2. Using the Query Wizard
          3. Query Wizard: Choosing the columns
          4. Query Wizard: Filtering data
          5. Query Wizard: Sort order
          6. Query Wizard: Finish
          7. Specifying a location for the data
      4. Working with Data Returned by Query
        1. Adjusting the external data range properties
        2. Refreshing a query
        3. Deleting a query
        4. Changing your query
      5. Using Query Without the Wizard
        1. Creating a query manually
        2. Using multiple database tables
        3. Adding and editing records in external database tables
        4. Formatting data
      6. Learning More about Query
    2. 34. Introducing Pivot Tables
      1. About Pivot Tables
        1. A pivot table example
        2. Data appropriate for a pivot table
      2. Creating a Pivot Table
        1. Specifying the data
        2. Specifying the location for the pivot table
        3. Laying out the pivot table
        4. Formatting the pivot table
        5. Modifying the pivot table
      3. More Pivot Table Examples
        1. Question 1
        2. Question 2
        3. Question 3
        4. Question 4
        5. Question 5
        6. Question 6
      4. Learning More
    3. 35. Analyzing Data with Pivot Tables
      1. Working with Non-Numeric Data
      2. Grouping Pivot Table Items
        1. A manual grouping example
        2. Viewing grouped data
        3. Automatic grouping examples
          1. Grouping by date
        4. Grouping by time
      3. Creating a Frequency Distribution
      4. Creating a Calculated Field or Calculated Item
        1. Creating a calculated field
        2. Inserting a calculated item
      5. Referencing Cells within a Pivot Table
      6. Creating Pivot Charts
        1. A pivot chart example
        2. More about pivot charts
      7. Another Pivot Table Example
      8. Producing a Report with a Pivot Table
    4. 36. Performing Spreadsheet What-If Analysis
      1. A What-If Example
      2. Types of What-If Analyses
      3. Manual What-If Analysis
      4. Creating Data Tables
        1. Creating a one-input data table
        2. Creating a two-input data table
      5. Using Scenario Manager
        1. Defining scenarios
        2. Displaying scenarios
        3. Modifying scenarios
        4. Merging scenarios
        5. Generating a scenario report
    5. 37. Analyzing Data Using Goal Seek and Solver
      1. What-If Analysis, in Reverse
      2. Single-Cell Goal Seeking
        1. A goal-seeking example
        2. More about Goal Seeking
      3. Introducing Solver
        1. Appropriate problems for Solver
        2. A simple Solver example
        3. More about Solver
      4. Solver Examples
        1. Solving simultaneous linear equations
        2. Minimizing shipping costs
        3. Allocating resources
        4. Optimizing an investment portfolio
    6. 38. Analyzing Data with the Analysis ToolPak
      1. The Analysis ToolPak: An Overview
      2. Installing the Analysis ToolPak Add-in
      3. Using the Analysis Tools
      4. Introducing the Analysis ToolPak Tools
        1. The Analysis of variance tool
        2. The Correlation tool
        3. The Covariance tool
        4. The Descriptive Statistics tool
        5. The Exponential Smoothing tool
        6. The F-Test (two-sample test for variance) tool
        7. The Fourier Analysis tool
        8. The Histogram tool
        9. The Moving Average tool
        10. The Random Number Generation tool
        11. The Rank and Percentile tool
        12. The Regression tool
        13. The Sampling tool
        14. The t-Test tool
        15. The z-Test (Two-Sample Test for Means) tool
  11. VI. Programming Excel with VBA
    1. 39. Introducing Visual Basic for Applications
      1. Introducing VBA Macros
      2. Displaying the Developer tab
      3. About Macro Security
      4. Saving Workbooks That Contain Macros
      5. Two Types of VBA Macros
        1. VBA Sub procedures
        2. VBA functions
      6. Creating VBA Macros
        1. Recording VBA macros
          1. Recording your actions to create VBA code: The basics
          2. Recording a macro: A simple example
          3. Examining the macro
          4. Testing the macro
          5. Editing the macro
          6. Another example
          7. Examining the macro
          8. Testing the macro
        2. More about recording VBA macros
          1. Absolute versus relative recording
          2. Storing macros in your Personal Macro Workbook
          3. Assigning a macro to a shortcut key
          4. Assigning a macro to a button
        3. Writing VBA code
          1. The basics: Entering and editing code
          2. How VBA works
          3. Objects and collections
          4. Properties
          5. Methods
          6. Variables
          7. Controlling execution
            1. The If-Then construct
            2. For-Next loops
            3. The With-End With construct
            4. The Select Case construct
          8. A macro that can’t be recorded
      7. Learning More
    2. 40. Creating Custom Worksheet Functions
      1. Overview of VBA Functions
      2. An Introductory Example
        1. A custom function
        2. Using the function in a worksheet
        3. Analyzing the custom function
      3. About Function Procedures
      4. Executing Function Procedures
        1. Calling custom functions from a procedure
        2. Using custom functions in a worksheet formula
      5. Function Procedure Arguments
        1. A function with no argument
        2. A function with one argument
        3. Another function with one argument
        4. A function with two arguments
        5. A function with a range argument
      6. Debugging Custom Functions
      7. Inserting Custom Functions
      8. Learning More
    3. 41. Creating UserForms
      1. Why Create UserForms?
      2. UserForm Alternatives
        1. The InputBox function
        2. The MsgBox function
      3. Creating UserForms: An Overview
        1. Working with UserForms
        2. Adding controls
        3. Changing the properties of a control
        4. Handling events
        5. Displaying a UserForm
      4. A UserForm Example
        1. Creating the UserForm
        2. Testing the UserForm
        3. Creating an event-handler procedure
      5. Another UserForm Example
        1. Creating the UserForm
        2. Testing the UserForm
        3. Creating event-handler procedures
        4. Testing the UserForm
        5. Making the macro available from a worksheet button
        6. Making the macro available on your Quick Access Toolbar
      6. More on Creating UserForms
        1. Adding accelerator keys
        2. Controlling tab order
      7. Learning More
    4. 42. Using UserForm Controls in a Worksheet
      1. Why Use Controls on a Worksheet?
      2. Using Controls
        1. Adding a control
        2. About design mode
        3. Adjusting properties
        4. Common properties
        5. Linking controls to cells
        6. Creating macros for controls
      3. Reviewing the Available ActiveX Controls
        1. CheckBox control
        2. ComboBox control
        3. CommandButton control
        4. Image control
        5. Label control
        6. ListBox control
        7. OptionButton control
        8. ScrollBar control
        9. SpinButton control
        10. TextBox controls
        11. ToggleButton control
    5. 43. Working with Excel Events
      1. Understanding Events
        1. Entering event-handler VBA code
      2. Using Workbook-Level Events
        1. Using the Open event
        2. Using the SheetActivate event
        3. Using the NewSheet event
        4. Using the BeforeSave event
        5. Using the BeforeClose event
      3. Working with Worksheet Events
        1. Using the Change event
        2. Monitoring a specific range for changes
        3. Using the SelectionChange event
        4. Using the BeforeRightClick event
      4. Using Non-Object Events
        1. Using the OnTime event
        2. Using the OnKey event
    6. 44. VBA Examples
      1. Working with Ranges
        1. Copying a range
        2. Copying a variable-size range
        3. Selecting to the end of a row or column
        4. Selecting a row or column
        5. Moving a range
        6. Looping through a range efficiently
        7. Prompting for a cell value
        8. Determining the type of selection
        9. Identifying a multiple selection
        10. Counting selected cells
      2. Working with Workbooks
        1. Saving all workbooks
        2. Saving and closing all workbooks
      3. Working with Charts
        1. Modifying the chart type
        2. Modifying chart properties
        3. Applying chart formatting
      4. VBA Speed Tips
        1. Turning off screen updating
        2. Preventing alert messages
        3. Simplifying object references
        4. Declaring variable types
    7. 45. Creating Custom Excel Add-Ins
      1. What Is an Add-In?
      2. Working with Add-Ins
      3. Why Create Add-Ins?
      4. Creating Add-Ins
      5. An Add-In Example
        1. Setting up the workbook
        2. Procedures in Module1
        3. About the UserForm
        4. Testing the workbook
        5. Adding descriptive information
        6. Protecting the project
        7. Creating the add-in
        8. Creating the user interface for your add-in macro
        9. Installing the add-in
  12. VII. Appendixes
    1. A. Worksheet Function Reference
    2. B. What’s on the CD-ROM
      1. System Requirements
      2. Using the CD
      3. What’s on the CD
        1. eBook version of Excel 2007 Bible
        2. Sample files for the Excel 2007 Bible
          1. Chapter 01
          2. Chapter 03
          3. Chapter 05
          4. Chapter 06
          5. Chapter 07
          6. Chapter 11
          7. Chapter 12
          8. Chapter 13
          9. Chapter 14
          10. Chapter 15
          11. Chapter 16
          12. Chapter 18
          13. Chapter 19
          14. Chapter 20
          15. Chapter 21
          16. Chapter 24
          17. Chapter 25
          18. Chapter 26
          19. Chapter 27
          20. Chapter 28
          21. Chapter 33
          22. Chapter 34
          23. Chapter 35
          24. Chapter 36
          25. Chapter 37
          26. Chapter 38
          27. Chapter 39
          28. Chapter 40
          29. Chapter 41
          30. Chapter 42
          31. Chapter 43
          32. Chapter 44
          33. Chapter 45
      4. Troubleshooting
      5. Customer Care
    3. C. Additional Excel Resources
      1. The Excel Help System
      2. Microsoft Technical Support
        1. Support options
        2. Microsoft Knowledge Base
        3. Microsoft Excel Home Page
        4. Microsoft Office Home Page
      3. Internet Newsgroups
        1. Accessing newsgroups by using a newsreader
        2. Accessing newsgroups by using a Web browser
        3. Searching newsgroups
      4. Internet Web Sites
        1. The Spreadsheet Page
        2. Daily Dose of Excel
        3. Jon Peltier’s Excel Page
        4. Pearson Software Consulting
        5. Stephen Bullen’s Excel Page
        6. David McRitchie’s Excel Pages
        7. Mr. Excel
    4. D. Excel Shortcut Keys
    5. Wiley Publishing, Inc. End-User License Agreement

Product information

  • Title: Excel® 2007 Bible
  • Author(s): John Walkenbach
  • Release date: January 2007
  • Publisher(s): Wiley
  • ISBN: 9780470044032