John Walkenbach's Favorite Excel 2010 Tips and Tricks

John Walkenbach's Favorite Excel 2010 Tips and Tricks

by John Walkenbach
Released July 2010
Publisher(s): Wiley
ISBN: 9780470475379

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

Build robust Excel 2010 apps quickly and efficiently

Known as "Mr. Spreadsheet," John Walkenbach's name is synonymous with excellence in computer books that explain the complexities of various topics. With this collection of favorite Excel tips and tricks, you get a unique look at ways to make Excel 2010 more efficient than you ever imagined.

Packed with easy-to-understand advice regarding all aspects of Excel, this book shares improved ways of speeding up application development with Excel and maximizing the power of Excel to create robust applications.

  • Addresses the extensive changes to the 2010 version of Excel and shares tricks and shortcuts for making your Excel experience as successful and efficient as possible

  • Reveals ways to deal with function arguments, create "impossible" charts, and tame the Ribbon bar

  • Discusses absolute vs. relative references, change data entry orientation, and sort more than three columns

  • Demonstrates ways to enter fake data for testing purposes

With John Walkenbach's Favorite Excel 2010 Tips and Tricks, you'll get a jump start on mastering the extensive changes to the 2010 version of Excel.

Table of contents

  1. Copyright
  2. About the Author
  3. Publisher's Acknowledgments
  4. INTRODUCTION
    1. What You Should Know
    2. What You Should Have
    3. Conventions Used in This Book
      1. Formula listings
      2. VBA code listings
      3. Key names
      4. The Ribbon
      5. Functions, procedures, and named ranges
      6. Mouse conventions
      7. What the icons mean
    4. Entering VBA Code
    5. How This Book Is Organized
    6. How to Use This Book
    7. About the Power Utility Pak Offer
    8. Reach Out
  5. 1. Basic Excel Usage
    1. 1.1. Understanding Excel Versions
    2. 1.2. Maximizing Ribbon Efficiency
    3. 1.3. Understanding Protected View
    4. 1.4. Selecting Cells Efficiently
      1. 1.4.1. Selecting a range by using the shift and arrow keys
      2. 1.4.2. Selecting the current region
      3. 1.4.3. Selecting a range by Shift+clicking
      4. 1.4.4. Selecting noncontiguous ranges
      5. 1.4.5. Selecting entire rows
      6. 1.4.6. Selecting entire columns
      7. 1.4.7. Selecting multisheet ranges
    5. 1.5. Making "Special" Range Selections
    6. 1.6. Undoing, Redoing, and Repeating
      1. 1.6.1. Undoing
      2. 1.6.2. Redoing
      3. 1.6.3. Repeating
    7. 1.7. Discovering Some Useful Shortcut Keys
    8. 1.8. Navigating Sheets in a Workbook
    9. 1.9. Resetting the Used Area of a Worksheet
    10. 1.10. Understanding Workbooks versus Windows
    11. 1.11. Customizing the Quick Access Toolbar
      1. 1.11.1. About the Quick Access toolbar
      2. 1.11.2. Adding new commands to the Quick Access toolbar
      3. 1.11.3. Performing other Quick Access toolbar actions
    12. 1.12. Customizing the Ribbon
    13. 1.13. Accessing the Ribbon with Your Keyboard
    14. 1.14. Recovering Your Work
      1. 1.14.1. Recovering versions of the current workbook
      2. 1.14.2. Recovering unsaved work
    15. 1.15. Customizing the Default Workbook
      1. 1.15.1. Changing defaults in the Excel Options dialog box
      2. 1.15.2. Creating a book.xltx template
    16. 1.16. Using Document Themes
      1. 1.16.1. Applying a theme
      2. 1.16.2. Customizing a theme
    17. 1.17. Hiding User Interface Elements
      1. 1.17.1. From the Ribbon
      2. 1.17.2. From the keyboard
      3. 1.17.3. From the Excel Options dialog box
      4. 1.17.4. A setting that requires a macro
    18. 1.18. Hiding Columns or Rows
      1. 1.18.1. Hiding
      2. 1.18.2. Unhiding
    19. 1.19. Hiding Cell Contents
    20. 1.20. Taking Pictures of Ranges
      1. 1.20.1. Creating a static image of a range
      2. 1.20.2. Creating a live image of a range
    21. 1.21. Performing Inexact Searches
    22. 1.22. Replacing Formatting
    23. 1.23. Changing the Excel Color Scheme
    24. 1.24. Limiting the Usable Area in a Worksheet
      1. 1.24.1. Setting the ScrollArea property
      2. 1.24.2. Using worksheet protection
    25. 1.25. Using an Alternative to Cell Comments
    26. 1.26. Understanding the Excel Help System
    27. 1.27. Making a Worksheet "Very Hidden"
    28. 1.28. Working with the Backstage View
  6. 2. Data Entry
    1. 2.1. Understanding the Types of Data
      1. 2.1.1. Entering values
      2. 2.1.2. Entering dates and times
      3. 2.1.3. Entering text
      4. 2.1.4. Entering formulas
    2. 2.2. Moving the Cell Pointer after Entering Data
    3. 2.3. Selecting a Range of Input Cells before Entering Data
    4. 2.4. Using AutoComplete to Automate Data Entry
    5. 2.5. Removing Duplicate Rows
    6. 2.6. Keeping Titles in View
    7. 2.7. Automatically Filling a Range with a Series
    8. 2.8. Working with Fractions
    9. 2.9. Resizing the Formula Bar
    10. 2.10. Proofing Your Data with Audio
      1. 2.10.1. Adding speech commands to the Ribbon
      2. 2.10.2. Using the speech commands
    11. 2.11. Controlling Automatic Hyperlinks
      1. 2.11.1. Overriding an automatic hyperlink
      2. 2.11.2. Turning off automatic hyperlinks
      3. 2.11.3. Removing existing hyperlinks
    12. 2.12. Entering Credit Card Numbers
    13. 2.13. Using the Excel Built-In Data Entry Form
    14. 2.14. Customizing and Sharing AutoCorrect Entries
    15. 2.15. Restricting Cursor Movement to Input Cells
    16. 2.16. Controlling the Office Clipboard
    17. 2.17. Creating a Drop-Down List in a Cell
  7. 3. Formatting
    1. 3.1. Using the Mini Toolbar
    2. 3.2. Indenting Cell Contents
    3. 3.3. Quick Number Formatting
    4. 3.4. Creating Custom Number Formats
      1. 3.4.1. Parts of a number format string
      2. 3.4.2. Custom number format codes
    5. 3.5. Using Custom Number Formats to Scale Values
    6. 3.6. Using Custom Date and Time Formatting
    7. 3.7. Examining Some Useful Custom Number Formats
      1. 3.7.1. Hiding zeros
      2. 3.7.2. Displaying leading zeros
      3. 3.7.3. Formatting percentages
      4. 3.7.4. Displaying fractions
      5. 3.7.5. Repeating text
      6. 3.7.6. Displaying a negative sign on the right
      7. 3.7.7. Suppressing certain types of entries
    8. 3.8. Updating Old Fonts
    9. 3.9. Understanding Conditional Formatting Visualization
      1. 3.9.1. Data bars
      2. 3.9.2. Color scales
      3. 3.9.3. Icon sets
    10. 3.10. Showing Text and a Value in a Cell
      1. 3.10.1. Using concatenation
      2. 3.10.2. Using the TEXT function
      3. 3.10.3. Using a custom number format
    11. 3.11. Merging Cells
    12. 3.12. Formatting Individual Characters in a Cell
    13. 3.13. Displaying Times That Exceed 24 Hours
    14. 3.14. Fixing Non-Numeric Numbers
    15. 3.15. Adding a Frame to a Range
    16. 3.16. Dealing with Gridlines, Borders, and Underlines
    17. 3.17. Inserting a Watermark
    18. 3.18. Adding a Background Image to a Worksheet
    19. 3.19. Wrapping Text in a Cell
    20. 3.20. Seeing All Characters in a Font
    21. 3.21. Entering Special Characters
    22. 3.22. Using Named Styles
      1. 3.22.1. Using the Style Gallery
      2. 3.22.2. Modifying an existing style
      3. 3.22.3. Creating new styles
      4. 3.22.4. Merging styles from other workbooks
  8. 4. Basic Formulas and Functions
    1. 4.1. Using Formula AutoComplete
    2. 4.2. Knowing When to Use Absolute References
    3. 4.3. Knowing When to Use Mixed References
    4. 4.4. Changing the Type of a Cell Reference
    5. 4.5. Converting a Vertical Range to a Table
    6. 4.6. AutoSum Tricks
    7. 4.7. Using the Status Bar Selection Statistics Feature
    8. 4.8. Converting Formulas to Values
    9. 4.9. Transforming Data without Using Formulas
    10. 4.10. Transforming Data by Using Temporary Formulas
    11. 4.11. Deleting Values While Keeping Formulas
    12. 4.12. Summing Across Sheets
    13. 4.13. Dealing with Function Arguments
    14. 4.14. Annotating a Formula without Using a Comment
    15. 4.15. Making an Exact Copy of a Range of Formulas
    16. 4.16. Monitoring Formula Cells from Any Location
    17. 4.17. Displaying and Printing Formulas
    18. 4.18. Avoiding Error Displays in Formulas
      1. 4.18.1. Using the ISERROR function
      2. 4.18.2. Using the IFERROR function
    19. 4.19. Using Goal Seeking
    20. 4.20. Understanding the Secret about Names
    21. 4.21. Using Named Constants
    22. 4.22. Using Functions in Names
    23. 4.23. Creating a List of Names
    24. 4.24. Using Dynamic Names
    25. 4.25. Creating Worksheet-Level Names
    26. 4.26. Working with Pre-1900 Dates
    27. 4.27. Working with Negative Time Values
  9. 5. Useful Formula Examples
    1. 5.1. Calculating Holidays
      1. 5.1.1. New Year's Day
      2. 5.1.2. Martin Luther King Jr. Day
      3. 5.1.3. Presidents' Day
      4. 5.1.4. Easter
      5. 5.1.5. Memorial Day
      6. 5.1.6. Independence Day
      7. 5.1.7. Labor Day
      8. 5.1.8. Columbus Day
      9. 5.1.9. Veterans Day
      10. 5.1.10. Thanksgiving Day
      11. 5.1.11. Christmas Day
    2. 5.2. Calculating a Weighted Average
    3. 5.3. Calculating a Person's Age
      1. 5.3.1. Method 1
      2. 5.3.2. Method 2
      3. 5.3.3. Method 3
    4. 5.4. Ranking Values
    5. 5.5. Converting Inches to Feet and Inches
    6. 5.6. Using the DATEDIF Function
    7. 5.7. Counting Characters in a Cell
      1. 5.7.1. Counting all characters in a cell
      2. 5.7.2. Counting specific characters in a cell
      3. 5.7.3. Counting the occurrences of a substring in a cell
    8. 5.8. Numbering Weeks
    9. 5.9. Using a Pivot Table Instead of Formulas
      1. 5.9.1. Inserting subtotals
      2. 5.9.2. Using formulas
      3. 5.9.3. Using a pivot table
    10. 5.10. Expressing a Number as an Ordinal
    11. 5.11. Extracting Words from a String
      1. 5.11.1. Extracting the first word of a string
      2. 5.11.2. Extracting the last word of a string
      3. 5.11.3. Extracting all except the first word of a string
    12. 5.12. Parsing Names
    13. 5.13. Removing Titles from Names
    14. 5.14. Generating a Series of Dates
      1. 5.14.1. Using AutoFill
      2. 5.14.2. Using formulas
    15. 5.15. Determining Specific Dates
      1. 5.15.1. Determining the day of the year
      2. 5.15.2. Determining the day of the week
      3. 5.15.3. Determining the date of the most recent Sunday
      4. 5.15.4. Determining the first day of the week after a date
      5. 5.15.5. Determining the nth occurrence of a day of the week in a month
      6. 5.15.6. Determining the last day of a month
      7. 5.15.7. Determining a date's quarter
    16. 5.16. Displaying a Calendar in a Range
    17. 5.17. Various Methods of Rounding Numbers
      1. 5.17.1. Rounding to the nearest multiple
      2. 5.17.2. Rounding currency values
      3. 5.17.3. Using the INT and TRUNC functions
      4. 5.17.4. Rounding to n significant digits
    18. 5.18. Rounding Time Values
    19. 5.19. Using the New AGGREGATE Function
    20. 5.20. Returning the Last Nonblank Cell in a Column or Row
    21. 5.21. Using the COUNTIF Function
    22. 5.22. Counting Cells That Meet Multiple Criteria
      1. 5.22.1. Using "And" criteria
      2. 5.22.2. Using "Or" criteria
      3. 5.22.3. Combining "And" and "Or" criteria
    23. 5.23. Counting Nonduplicated Entries in a Range
    24. 5.24. Calculating Single-Criterion Conditional Sums
      1. 5.24.1. Summing only negative values
      2. 5.24.2. Summing values based on a different range
      3. 5.24.3. Summing values based on a text comparison
      4. 5.24.4. Summing values based on a date comparison
    25. 5.25. Calculating Multiple-Criterion Conditional Sums
      1. 5.25.1. Using "And" criteria
      2. 5.25.2. Using "Or" criteria
      3. 5.25.3. Using "And" and "Or" criteria
    26. 5.26. Looking Up an Exact Value
    27. 5.27. Performing a Two-Way Lookup
      1. 5.27.1. Using a formula
      2. 5.27.2. Using implicit intersection
    28. 5.28. Performing a Two-Column Lookup
    29. 5.29. Performing a Lookup by Using an Array
      1. 5.29.1. Using a lookup table
      2. 5.29.2. Using an array
    30. 5.30. Using the INDIRECT Function
      1. 5.30.1. Specifying rows indirectly
      2. 5.30.2. Specifying worksheet names indirectly
      3. 5.30.3. Making a cell reference unchangeable
    31. 5.31. Creating Megaformulas
  10. 6. Conversions and Mathematical Calculations
    1. 6.1. Converting Between Measurement Systems
    2. 6.2. Converting Temperatures
    3. 6.3. Solving Simultaneous Equations
    4. 6.4. Solving Recursive Equations
    5. 6.5. Generating Random Numbers
      1. 6.5.1. Using the RAND function
      2. 6.5.2. Using the RANDBETWEEN function
      3. 6.5.3. Using the Analysis Toolpak add-in
    6. 6.6. Calculating Roots
    7. 6.7. Calculating a Remainder
  11. 7. Charts and Graphics
    1. 7.1. Creating a Text Chart Directly in a Range
    2. 7.2. Selecting Elements in a Chart
      1. 7.2.1. Selecting with the mouse
      2. 7.2.2. Selecting with the keyboard
      3. 7.2.3. Selecting with the Chart Element control
    3. 7.3. Creating a Self-Expanding Chart
    4. 7.4. Creating Combination Charts
    5. 7.5. Creating a Gantt Chart
    6. 7.6. Creating a Gauge Chart
    7. 7.7. Using Pictures in Charts
    8. 7.8. Plotting Mathematical Functions
      1. 7.8.1. Plotting single-variable mathematical functions
      2. 7.8.2. Plotting two-variable mathematical functions
    9. 7.9. Using High-Low Lines in a Chart
    10. 7.10. Linking Chart Text to Cells
    11. 7.11. Creating a Chart Template
    12. 7.12. Saving a Chart as a Graphics File
      1. 7.12.1. Method 1: Paste the chart into a graphics program
      2. 7.12.2. Method 2: Save as an HTML file
      3. 7.12.3. Method 3: Use a VBA macro
    13. 7.13. Saving a Range as a Graphic Image
    14. 7.14. Making Charts the Same Size
    15. 7.15. Resetting All Chart Formatting
    16. 7.16. Freezing a Chart
      1. 7.16.1. Converting a chart into a picture
      2. 7.16.2. Converting range references into arrays
    17. 7.17. Creating Picture Effects with a Chart
    18. 7.18. Creating Sparkline Graphics
    19. 7.19. Selecting Objects on a Worksheet
      1. 7.19.1. Ctrl+click
      2. 7.19.2. The Selection and Visibility pane
      3. 7.19.3. The Go to Special dialog box
      4. 7.19.4. The Select Object tool
    20. 7.20. Making a Greeting Card
    21. 7.21. Enhancing Text Formatting in Shapes
    22. 7.22. Using Images as Line Chart Markers
    23. 7.23. Changing the Shape of a Cell Comment
    24. 7.24. Adding an Image to a Cell Comment
    25. 7.25. Enhancing Images
  12. 8. Data Analysis and Lists
    1. 8.1. Using the Table Feature
      1. 8.1.1. Understanding what a table is
      2. 8.1.2. Range versus table
      3. 8.1.3. Creating a table
    2. 8.2. Working with Tables
      1. 8.2.1. Navigating in a table
      2. 8.2.2. Selecting parts of a table
      3. 8.2.3. Adding new rows or columns
      4. 8.2.4. Deleting rows or columns
      5. 8.2.5. Moving a table
      6. 8.2.6. Sorting and filtering a table
    3. 8.3. Using Formulas with a Table
      1. 8.3.1. Working with the total row
      2. 8.3.2. Using formulas within a table
      3. 8.3.3. Referencing data in a table
    4. 8.4. Numbering Rows in a Table
    5. 8.5. Using Custom Views with Filtering
    6. 8.6. Putting Advanced Filter Results on a Different Sheet
    7. 8.7. Comparing Two Ranges by Using Conditional Formatting
    8. 8.8. Randomizing a List
    9. 8.9. Filling the Gaps in a Report
    10. 8.10. Creating a List from a Summary Table
    11. 8.11. Finding Duplicates by Using Conditional Formatting
    12. 8.12. Creating a Quick Frequency Tabulation
    13. 8.13. Controlling References to Cells within a Pivot Table
    14. 8.14. Grouping Items by Date in a Pivot Table
    15. 8.15. Unlinking a Pivot Table from Its Source
    16. 8.16. Using Pivot Table Slicers
  13. 9. Working with Files
    1. 9.1. Understanding the New Excel File Formats
      1. 9.1.1. Recognizing the new Excel file formats
      2. 9.1.2. The Office compatibility pack
      3. 9.1.3. Saving a file for use with an older version of Excel
    2. 9.2. Importing a Text File into a Worksheet Range
    3. 9.3. Getting Data from a Web Page
      1. 9.3.1. Pasting static information
      2. 9.3.2. Pasting refreshable information
      3. 9.3.3. Opening the Web page directly
    4. 9.4. Displaying a Workbook's Full Path
      1. 9.4.1. Go backstage
      2. 9.4.2. Use a formula
        1. 9.4.2.1. Add a control to your Quick Access toolbar
      3. 9.4.3. Display the Document panel
      4. 9.4.4. Use a macro
    5. 9.5. Using Document Properties
    6. 9.6. Inspecting a Workbook
    7. 9.7. Finding the Missing No to All Button When Closing Files
    8. 9.8. Getting a List of Filenames
    9. 9.9. Using Workspace Files
  14. 10. Printing
    1. 10.1. Controlling What Gets Printed
      1. 10.1.1. Displaying the Quick Print button
      2. 10.1.2. Adjusting common page setup settings
    2. 10.2. Displaying Repeated Rows or Columns on a Printout
    3. 10.3. Printing Noncontiguous Ranges on a Single Page
      1. 10.3.1. Breaking out the Camera tool
      2. 10.3.2. Shooting with the Camera
    4. 10.4. Preventing Objects from Printing
    5. 10.5. Page-Numbering Tips
      1. 10.5.1. Applying basic page numbering
      2. 10.5.2. Changing the starting page number
    6. 10.6. Adding and Removing Page Breaks
      1. 10.6.1. Forcing a page break to appear where you want it
      2. 10.6.2. Removing page breaks you've added
    7. 10.7. Saving to a PDF File
    8. 10.8. Making Your Printout Fit on One Page
    9. 10.9. Printing the Contents of a Cell in a Header or Footer
    10. 10.10. Copying Page Setup Settings Across Sheets
    11. 10.11. Printing Cell Comments
    12. 10.12. Printing a Giant Banner
  15. 11. Spotting, Fixing, and Preventing Errors
    1. 11.1. Using the Excel Error-Checking Features
    2. 11.2. Identifying Formula Cells
      1. 11.2.1. Using Go To Special
      2. 11.2.2. Using conditional formatting
    3. 11.3. Dealing with Floating-Point Number Problems
    4. 11.4. Removing Excess Spaces
    5. 11.5. Viewing Names Graphically
    6. 11.6. Locating Phantom Links
    7. 11.7. Understanding Displayed versus Actual Values
    8. 11.8. Tracing Cell Relationships
      1. 11.8.1. Identifying precedents
      2. 11.8.2. Identifying dependents
  16. 12. Basic VBA and Macros
    1. 12.1. Learning about Macros and VBA
      1. 12.1.1. What is a macro?
      2. 12.1.2. What can a macro do?
    2. 12.2. Recording a Macro
      1. 12.2.1. Creating the macro
      2. 12.2.2. Examining the macro
      3. 12.2.3. Testing the macro
    3. 12.3. Executing Macros
      1. 12.3.1. Use the Macro dialog box
      2. 12.3.2. Use the Visual Basic Editor window
      3. 12.3.3. Use a shortcut key
      4. 12.3.4. Assign the macro to a button
      5. 12.3.5. Assign the macro to a shape
      6. 12.3.6. Add a button to your Quick Access toolbar
      7. 12.3.7. Add a button to the Ribbon
    4. 12.4. Understanding Functions Versus Subs
      1. 12.4.1. VBA Sub procedures
      2. 12.4.2. VBA functions
    5. 12.5. Creating Simple Worksheet Functions
      1. 12.5.1. Returning the user name
      2. 12.5.2. Determine whether a cell contains a formula
      3. 12.5.3. Returning a worksheet name
      4. 12.5.4. Returning a workbook name
      5. 12.5.5. Reversing a string
      6. 12.5.6. Extracting the nth element from a string
    6. 12.6. Describing Function Arguments
    7. 12.7. Making Excel Talk
    8. 12.8. Understanding Custom Function Limitations
    9. 12.9. Executing a Ribbon Command with a Macro
    10. 12.10. Understanding Security Issues Related to Macros
    11. 12.11. Using a Personal Macro Workbook

Product information

  • Title: John Walkenbach's Favorite Excel 2010 Tips and Tricks
  • Author(s): John Walkenbach
  • Release date: July 2010
  • Publisher(s): Wiley
  • ISBN: 9780470475379