Excel® 2010 Bible

Excel® 2010 Bible

by John Walkenbach
Released June 2010
Publisher(s): Wiley
ISBN: 9780470474877

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

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
  7. Getting Started with Excel
    1. Introducing Excel
      1. What Is Excel Good For?
      2. What's New in Excel 2010?
      3. Understanding Workbooks and Worksheets
      4. Moving around a Worksheet
      5. Introducing the Ribbon
      6. Using Shortcut Menus
      7. Customizing Your Quick Access Toolbar
      8. Working with Dialog Boxes
      9. Using the Task Pane
      10. Creating Your First Excel Worksheet
    2. Entering and Editing Worksheet Data
      1. Exploring the Types of Data You Can Use
      2. Entering Text and Values into Your Worksheets
      3. Entering Dates and Times into Your Worksheets
      4. Modifying Cell Contents
      5. Applying Number Formatting
    3. Essential Worksheet Operations
      1. Learning the Fundamentals of Excel Worksheets
      2. Controlling the Worksheet View
      3. Working with Rows and Columns
    4. Working with Cells and Ranges
      1. Understanding Cells and Ranges
      2. Copying or Moving Ranges
      3. Using Names to Work with Ranges
      4. Adding Comments to Cells
    5. Introducing Tables
      1. What Is a Table?
      2. Creating a Table
      3. Changing the Look of a Table
      4. Working with Tables
    6. Worksheet Formatting
      1. Getting to Know the Formatting Tools
      2. Using Different Fonts to Format Your Worksheet
      3. Changing Text Alignment
      4. Using Colors and Shading
      5. Adding Borders and Lines
      6. Adding a Background Image to a Worksheet
      7. Using Named Styles for Easier Formatting
      8. Understanding Document Themes
    7. Understanding Excel Files
      1. Creating a New Workbook
      2. Opening an Existing Workbook
      3. Saving a Workbook
      4. Using AutoRecover
      5. Specifying a Password
      6. Organizing Your Files
      7. Other Workbook Info Options
      8. Closing Workbooks
      9. Safeguarding Your Work
      10. Excel File Compatibility
    8. Using and Creating Templates
      1. Exploring Excel Templates
      2. Understanding Custom Excel Templates
    9. Printing Your Work
      1. Printing with One Click
      2. Changing Your Page View
      3. Adjusting Common Page Setup Settings
      4. Adding a Header or Footer to Your Reports
      5. Copying Page Setup Settings across Sheets
      6. Preventing Certain Cells from Being Printed
      7. Preventing Objects from Being Printed
      8. Creating Custom Views of Your Worksheet
  8. Working with Formulas and Functions
    1. Introducing Formulas and Functions
      1. Understanding Formula Basics
      2. Entering Formulas into Your Worksheets
      3. Editing Formulas
      4. Using Cell References in Formulas
      5. Using Formulas in Tables
      6. Correcting Common Formula Errors
      7. Using Advanced Naming Techniques
      8. Tips for Working with Formulas
    2. Creating Formulas That Manipulate Text
      1. A Few Words about Text
      2. Text Functions
      3. Advanced Text Formulas
    3. Working with Dates and Times
      1. How Excel Handles Dates and Times
      2. Date-Related Worksheet Functions
      3. Time-Related Functions
    4. Creating Formulas That Count and Sum
      1. Counting and Summing Worksheet Cells
      2. Basic Counting Formulas
      3. Advanced Counting Formulas
      4. Summing Formulas
      5. Conditional Sums Using a Single Criterion
      6. Conditional Sums Using Multiple Criteria
    5. Creating Formulas That Look Up Values
      1. Introducing Lookup Formulas
      2. Functions Relevant to Lookups
      3. Basic Lookup Formulas
      4. Specialized Lookup Formulas
    6. Creating Formulas for Financial Applications
      1. The Time Value of Money
      2. Loan Calculations
      3. Investment Calculations
      4. Depreciation Calculations
    7. Introducing Array Formulas
      1. Understanding Array Formulas
      2. Understanding the Dimensions of an Array
      3. Naming Array Constants
      4. Working with Array Formulas
      5. Using Multicell Array Formulas
      6. Using Single-Cell Array Formulas
    8. Performing Magic with Array Formulas
      1. Working with Single-Cell Array Formulas
      2. Working with Multicell Array Formulas
  9. Creating Charts and Graphics
    1. Getting Started Making Charts
      1. What Is a Chart?
      2. Understanding How Excel Handles Charts
      3. Creating a Chart
      4. Hands On: Creating and Customizing a Chart
      5. Working with Charts
      6. Understanding Chart Types
      7. Learning More
    2. Learning Advanced Charting
      1. Selecting Chart Elements
      2. User Interface Choices for Modifying Chart Elements
      3. Modifying the Chart Area
      4. Modifying the Plot Area
      5. Working with Chart Titles
      6. Working with a Legend
      7. Working with Gridlines
      8. Modifying the Axes
      9. Working with Data Series
      10. Creating Chart Templates
      11. Learning Some Chart-Making Tricks
    3. Visualizing Data Using Conditional Formatting
      1. About Conditional Formatting
      2. Specifying Conditional Formatting
      3. Conditional Formats That Use Graphics
      4. Creating Formula-Based Rules
      5. Working with Conditional Formats
    4. Creating Sparkline Graphics
      1. Sparkline Types
      2. Creating Sparklines
      3. Customizing Sparklines
      4. Specifying a Date Axis
      5. Auto-Updating Sparklines
      6. Displaying a Sparkline for a Dynamic Range
    5. Enhancing Your Work with Pictures and Drawings
      1. Using Shapes
      2. Using SmartArt
      3. Using WordArt
      4. Working with Other Graphic Types
      5. Using the Equation Editor
  10. Using Advanced Excel Features
    1. Customizing the Excel User Interface
      1. Customizing the Quick Access Toolbar
      2. Customizing the Ribbon
    2. Using Custom Number Formats
      1. About Number Formatting
      2. Creating a Custom Number Format
      3. Custom Number Format Examples
    3. 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 Formula Examples
    4. Creating and Using Worksheet Outlines
      1. Introducing Worksheet Outlines
      2. Creating an Outline
      3. Working with Outlines
    5. Linking and Consolidating Worksheets
      1. Linking Workbooks
      2. Creating External Reference Formulas
      3. Working with External Reference Formulas
      4. Avoiding Potential Problems with External Reference Formulas
      5. Consolidating Worksheets
    6. Excel and the Internet
      1. Understanding How Excel Uses HTML
      2. Understanding the Different Web Formats
      3. Opening an HTML File
      4. Working with Hyperlinks
      5. Using Web Queries
      6. Other Internet-Related Features
    7. Sharing Data with Other Office Applications
      1. Copying and Pasting
      2. Copying from Excel to Word
      3. Embedding Objects in a Worksheet
      4. Embedding an Excel Workbook in a Word Document
    8. Using Excel in a Workgroup
      1. Using Excel on a Network
      2. Understanding File Reservations
      3. Sharing Workbooks
      4. Tracking Workbook Changes
    9. Protecting Your Work
      1. Types of Protection
      2. Protecting a Worksheet
      3. Protecting a Workbook
      4. VB Project Protection
      5. Related Topics
    10. Making Your Worksheets Error-Free
      1. Finding and Correcting Formula Errors
      2. Using Excel Auditing Tools
      3. Searching and Replacing
      4. Spell Checking Your Worksheets
      5. Using AutoCorrect
  11. Analyzing Data with Excel
    1. Getting Data from External Database Files
      1. Understanding External Database Files
      2. Importing Access Tables
      3. Retrieving Data with Query: An Example
      4. Working with Data Returned by Query
      5. Using Query without the Wizard
      6. Learning More about Query
    2. Introducing Pivot Tables
      1. About Pivot Tables
      2. Creating a Pivot Table
      3. More Pivot Table Examples
      4. Learning More
    3. Analyzing Data with Pivot Tables
      1. Working with Non-Numeric Data
      2. Grouping Pivot Table Items
      3. Creating a Frequency Distribution
      4. Creating a Calculated Field or Calculated Item
      5. Filtering Pivot Tables with Slicers
      6. Referencing Cells within a Pivot Table
      7. Creating Pivot Charts
      8. Another Pivot Table Example
      9. Producing a Report with a Pivot Table
    4. 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
      5. Using Scenario Manager
    5. Analyzing Data Using Goal Seeking and Solver
      1. What-If Analysis, in Reverse
      2. Single-Cell Goal Seeking
      3. Introducing Solver
      4. Solver Examples
    6. 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
  12. Programming Excel with VBA
    1. 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
      6. Creating VBA Macros
      7. Learning More
    2. Creating Custom Worksheet Functions
      1. Overview of VBA Functions
      2. An Introductory Example
      3. About Function Procedures
      4. Executing Function Procedures
      5. Function Procedure Arguments
      6. Debugging Custom Functions
      7. Inserting Custom Functions
      8. Learning More
    3. Creating UserForms
      1. Why Create UserForms?
      2. UserForm Alternatives
      3. Creating UserForms: An Overview
      4. A UserForm Example
      5. Another UserForm Example
      6. More on Creating UserForms
      7. Learning More
    4. Using UserForm Controls in a Worksheet
      1. Why Use Controls on a Worksheet?
      2. Using Controls
      3. Reviewing the Available ActiveX Controls
    5. Working with Excel Events
      1. Understanding Events
      2. Entering Event-Handler VBA Code
      3. Using Workbook-Level Events
      4. Working with Worksheet Events
      5. Using Non-Object Events
    6. VBA Examples
      1. Working with Ranges
      2. Working with Workbooks
      3. Working with Charts
      4. VBA Speed Tips
    7. 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
  13. Appendixes
    1. Worksheet Function Reference
    2. What's on the CD-ROM
      1. System Requirements
      2. Using the CD
      3. What's on the CD
      4. Troubleshooting
    3. Additional Excel Resources
      1. The Excel Help System
      2. Microsoft Technical Support
      3. Internet Newsgroups
      4. Internet Web sites
    4. Excel Shortcut Keys
  14. Wiley Publishing, Inc. End-User License Agreement

Product information

  • Title: Excel® 2010 Bible
  • Author(s): John Walkenbach
  • Release date: June 2010
  • Publisher(s): Wiley
  • ISBN: 9780470474877