Excel Hacks, 2nd Edition

Excel Hacks, 2nd Edition

by David Hawley, Raina Hawley
Released June 2007
Publisher(s): O'Reilly Media, Inc.
ISBN: 9780596528348

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.

Buy on Amazon Buy on ebooks.com
Start your free trial

Book description

Millions of users create and share Excel spreadsheets every day, but few go deeply enough to learn the techniques that will make their work much easier. There are many ways to take advantage of Excel's advanced capabilities without spending hours on advanced study. Excel Hacks provides more than 130 hacks -- clever tools, tips and techniques -- that will leapfrog your work beyond the ordinary.

Now expanded to include Excel 2007, this resourceful, roll-up-your-sleeves guide gives you little known "backdoor" tricks for several Excel versions using different platforms and external applications. Think of this book as a toolbox. When a need arises or a problem occurs, you can simply use the right tool for the job. Hacks are grouped into chapters so you can find what you need quickly, including ways to:

  • Reduce workbook and worksheet frustration -- manage how users interact with worksheets, find and highlight information, and deal with debris and corruption.
  • Analyze and manage data -- extend and automate these features, moving beyond the limited tasks they were designed to perform.
  • Hack names -- learn not only how to name cells and ranges, but also how to create names that adapt to the data in your spreadsheet.
  • Get the most out of PivotTables -- avoid the problems that make them frustrating and learn how to extend them.
  • Create customized charts -- tweak and combine Excel's built-in charting capabilities.
  • Hack formulas and functions -- subjects range from moving formulas around to dealing with datatype issues to improving recalculation time.
  • Make the most of macros -- including ways to manage them and use them to extend other features.
  • Use the enhanced capabilities of Microsoft Office 2007 to combine Excel with Word, Access, and Outlook.
You can either browse through the book or read it from cover to cover, studying the procedures and scripts to learn more about Excel. However you use it, Excel Hacks will help you increase productivity and give you hours of "hacking" enjoyment along the way.

Table of contents

  1. Dedication
  2. Credits
    1. About the Authors
    2. Contributors
    3. Acknowledgments
  3. Preface
    1. Why Excel Hacks?
    2. Getting and Using the Hacks
    3. How to Use This Book
    4. How This Book Is Organized
    5. Windows, Macintosh, and Earlier Excel Versions
    6. Conventions Used in This Book
    7. Using Code Examples
    8. Safari® Enabled
    9. How to Contact Us
  4. 1. Reducing Workbook and Worksheet Frustration
    1. 1.1. Hacks 1–16
      1. 1.1.1. The 80/20 Rule
      2. 1.1.2. Structural Tips
      3. 1.1.3. Formatting Tips
      4. 1.1.4. Formula Tips
    2. 1. Create a Personal View of Your Workbooks
    3. 2. Enter Data into Multiple Worksheets Simultaneously
      1. 1.3.1. Grouping Worksheets Manually
      2. 1.3.2. Grouping Worksheets Automatically
    4. 3. Prevent Users from Performing Certain Actions
      1. 1.4.1. Preventing Save As… in a Workbook
      2. 1.4.2. Preventing Users from Printing a Workbook
      3. 1.4.3. Preventing Users from Inserting More Worksheets
    5. 4. Prevent Seemingly Unnecessary Prompts
      1. 1.5.1. Enabling Macros When You Don’t Have Any
      2. 1.5.2. Prompting to Save Nonexistent Changes
      3. 1.5.3. Stopping Excel’s Warning Prompts for Recorded Macros
    6. 5. Hide Worksheets So That They Cannot Be Unhidden
    7. 6. Customize the Templates Dialog and Default Workbook
      1. 1.7.1. Creating Your Own Template Tab
      2. 1.7.2. Using a Custom Default Workbook
    8. 7. Create an Index of Sheets in Your Workbook
      1. 1.8.1. Creating an Index Sheet by Hand
      2. 1.8.2. Auto-Generate an Index Using VBA
      3. 1.8.3. Link to the Index from a Context Menu
    9. 8. Limit the Scrolling Range of Your Worksheet
      1. 1.9.1. Hiding Rows and Columns
      2. 1.9.2. Specifying a Valid Range
      3. 1.9.3. Activating Only the Used Range
    10. 9. Lock and Protect Cells Containing Formulas
      1. 1.10.1. Locking Formula Cells
      2. 1.10.2. Data Validation
      3. 1.10.3. Auto-Toggle Worksheet Protection
    11. 10. Find Duplicate Data Using Conditional Formatting
    12. 11. Find Data That Appears Two or More Times Using Conditional Formatting
    13. 12. Tie Custom Toolbars to a Particular Workbook
    14. 13. Outsmart Excel’s Relative Reference Handler
    15. 14. Remove Phantom Workbook Links
    16. 15. Reduce Workbook Bloat
      1. 1.16.1. Eliminating Superfluous Formatting
      2. 1.16.2. Clean Up Your Macros
      3. 1.16.3. Honing Data Sources
      4. 1.16.4. Cleaning Corrupted Workbooks
    17. 16. Extract Data from a Corrupt Workbook
      1. 1.17.1. If You Can Open Your Workbook
      2. 1.17.2. If You Cannot Open Your File
  5. 2. Hacking Excel’s Built-in Features
    1. 2.1. Hacks 17–43
    2. 17. Validate Data Based on a List on Another Worksheet
      1. 2.2.1. Method 1: Named Ranges
      2. 2.2.2. Method 2: the INDIRECT Function
      3. 2.2.3. The Pros and Cons of Both Methods
    3. 18. Control Conditional Formatting with Checkboxes
      1. 2.3.1. Setting Up Checkboxes for Conditional Formatting
      2. 2.3.2. Toggling Number Highlighting On and Off
    4. 19. Identify Formulas with Conditional Formatting
    5. 20. Count or Sum Cells That Meet Conditional Formatting Criteria
      1. 2.5.1. An Alternate Path
    6. 21. Highlight Every Other Row or Column
      1. 2.6.1. Highlighting Dynamically
    7. 22. Create 3-D Effects in Tables or Cells
      1. 2.7.1. Using a 3-D Effect on a Table of Data
    8. 23. Turn Conditional Formatting and Data Validation On and Off with a Checkbox
    9. 24. Support Multiple Lists in a ComboBox
    10. 25. Create Validation Lists That Change Based on a Selection from Another List
    11. 26. Use Replace… to Remove Unwanted Characters
    12. 27. Convert Text Numbers to Real Numbers
      1. 2.12.1. Using Paste Special
      2. 2.12.2. Using the TEXT Functions
    13. 28. Extract the Numeric Portion of a Cell Entry
    14. 29. Customize Cell Comments
      1. 2.14.1. Adding a Picture
      2. 2.14.2. Extracting Comment Text
    15. 30. Sort by More Than Three Columns
    16. 31. Random Sorting
    17. 32. Manipulate Data with the Advanced Filter
    18. 33. Create Custom Number Formats
    19. 34. Add More Levels of Undo to Excel for Windows
    20. 35. Create Custom Lists
    21. 36. Boldface Excel Subtotals
      1. 2.21.1. Hacking the Hack
    22. 37. Convert Excel Formulas and Functions to Values
      1. 2.22.1. Using Paste Special
      2. 2.22.2. Using Copy Here As Values Only
      3. 2.22.3. Using a Macro
    23. 38. Automatically Add Data to a Validation List
    24. 39. Hack Excel’s Date and Time Features
      1. 2.24.1. Adding Beyond 24 Hours
      2. 2.24.2. Time and Date Calculations
      3. 2.24.3. Real Dates and Times
      4. 2.24.4. A Date Bug?
    25. 40. Enable Grouping and Outlining on a Protected Worksheet
    26. 41. Prevent Blanks/Missing Fields in a Table
    27. 42. Provide Decreasing Data Validation Lists
    28. 43. Add a Custom List to the Fill Handle
  6. 3. Naming Hacks
    1. 3.1. Hacks 44–49
    2. 44. Address Data by Name
    3. 45. Use the Same Name for Ranges on Different Worksheets
      1. 3.3.1. Using Relative References
    4. 46. Create Custom Functions Using Names
      1. 3.4.1. Using Names with Intersect
    5. 47. Create Ranges That Expand and Contract
    6. 48. Nest Dynamic Ranges for Maximum Flexibility
    7. 49. Identify Named Ranges on a Worksheet
      1. 3.7.1. Method 1
      2. 3.7.2. Method 2
  7. 4. Hacking PivotTables
    1. 4.1. Hacks 50–54
    2. 50. PivotTables: A Hack in Themselves
      1. 4.2.1. Why Are They Called PivotTables?
      2. 4.2.2. What Are PivotTables Good For?
      3. 4.2.3. Why Use PivotTables When Spreadsheets Already Offer So Much Analysis Capability?
      4. 4.2.4. PivotCharts Extend PivotTables
      5. 4.2.5. Creating Tables and Lists for Use in PivotTables
      6. 4.2.6. PivotTable Creation
    3. 51. Share PivotTables but Not Their Data
    4. 52. Automate PivotTable Creation
      1. 4.4.1. Save Time with a Macro
    5. 53. Move PivotTable Grand Totals
    6. 54. Efficiently Pivot Another Workbook’s Data
  8. 5. Charting Hacks
    1. 5.1. Hacks 55–72
    2. 55. Explode a Single Slice from a Pie Chart
    3. 56. Create Two Sets of Slices in One Pie Chart
    4. 57. Create Charts That Adjust to Data
      1. 5.4.1. Plotting the Last x Number of Readings
    5. 58. Interact with Your Charts Using Custom Controls
      1. 5.5.1. Using a Dynamic Named Range Linked to a Scrollbar
      2. 5.5.2. Using a Dynamic Named Range Linked to a Drop-Down List
    6. 59. Four Quick Ways to Update Your Charts
      1. 5.6.1. Using Drag-and-Drop
      2. 5.6.2. Using the Formula Bar
      3. 5.6.3. Dragging the Bounding Area
      4. 5.6.4. Using Paste Special
    7. 60. Hack Together a Simple Thermometer Chart
    8. 61. Create a Column Chart with Variable Widths and Heights
    9. 62. Create a Speedometer Chart
    10. 63. Link Chart Text Elements to a Cell
    11. 64. Hack Chart Data So That Empty or FALSE Formula Cells Are Not Plotted
      1. 5.11.1. Hiding Rows or Columns
      2. 5.11.2. Using #N/A to Plot Blank Cells
    12. 65. Add a Directional Arrow to the End of a Line Series
    13. 66. Place an Arrow on the End of a Horizontal (X) Axis
      1. 5.13.1. In Excel 2007
      2. 5.13.2. In Older Excel Versions
    14. 67. Correct Narrow Columns When Using Dates
    15. 68. Position Axis Labels
      1. 5.15.1. Changing Label Position
      2. 5.15.2. Reversing Label Order
    16. 69. Tornado Chart
    17. 70. Gauge Chart
    18. 71. Conditional Highlighting Axis Labels
    19. 72. Create Totals on a Stacked Column Chart
  9. 6. Hacking Formulas and Functions
    1. 6.1. Hacks 73–105
    2. 73. Add Descriptive Text to Your Formulas
    3. 74. Move Relative Formulas Without Changing References
    4. 75. Compare Two Excel Ranges
      1. 6.4.1. Method 1: Using True or False
      2. 6.4.2. Method 2: Using Conditional Formatting
    5. 76. Fill All Blank Cells in a List
      1. 6.5.1. Method 1: Filling Blanks via a Formula
      2. 6.5.2. Method 2: Filling Blanks via a Macro
    6. 77. Make Your Formulas Increment by Rows When You Copy Across Columns
    7. 78. Convert Dates to Excel Formatted Dates
    8. 79. Sum or Count Cells While Avoiding Error Values
    9. 80. Reduce the Impact of Volatile Functions on Recalculation
    10. 81. Count Only One Instance of Each Entry in a List
      1. 6.10.1. Before Excel 2007
      2. 6.10.2. Excel 2007
      3. 6.10.3. Using a Pivot Table
    11. 82. Sum Every Second, Third, or Nth Row or Cell
      1. 6.11.1. Using an Array Formula
      2. 6.11.2. Using SUMPRODUCT
      3. 6.11.3. Using DSUM
    12. 83. Find the Nth Occurrence of a Value
    13. 84. Make the Excel Subtotal Function Dynamic
    14. 85. Add Date Extensions
    15. 86. Convert Numbers with the Negative Sign on the Right to Excel Numbers
    16. 87. Display Negative Time Values
      1. 6.16.1. Method 1: Changing Excel’s Default Date System
      2. 6.16.2. Method 2: Using the TEXT Function
      3. 6.16.3. Method 3: Using a Custom Format
    17. 88. Use the VLOOKUP Function Across Multiple Tables
    18. 89. Show Total Time As Days, Hours, and Minutes
    19. 90. Determine the Number of Specified Days in Any Month
    20. 91. Construct Mega-Formulas
    21. 92. Hack Mega-Formulas that Reference Other Workbooks
    22. 93. Hack One of Excel’s Database Functions to Take the Place of Many Functions
      1. 6.22.1. Using DCOUNT to Filter on Two Criteria
      2. 6.22.2. Making the Comparison Operators Interchangeable
    23. 94. Extract Specified Words from a Text String
      1. 6.23.1. Getting the Last Word
      2. 6.23.2. Getting the First Word
      3. 6.23.3. Get the Nth Word
    24. 95. Count Words in a Cell or Range of Cells
      1. 6.24.1. SUBSTITUTE
      2. 6.24.2. LEN
      3. 6.24.3. Putting It Together
      4. 6.24.4. Hacking the Hack
    25. 96. Return a Worksheet Name to a Cell
      1. 6.25.1. Create a List of Worksheet Names
      2. 6.25.2. Extract Worksheet Names Only
      3. 6.25.3. Use the List in Formulas
      4. 6.25.4. Make the Range Address Variable
    26. 97. Sum Cells with Multiple Criteria
      1. 6.26.1. SUMIF
      2. 6.26.2. DSUM
      3. 6.26.3. SUMPRODUCT
      4. 6.26.4. SUM and IF
    27. 98. Count Cells with Multiple Criteria
      1. 6.27.1. Array Formulas
      2. 6.27.2. SUMPRODUCT
    28. 99. Calculate a Sliding Tax Scale
      1. 6.28.1. Using IF/SUM
      2. 6.28.2. Using a VLOOKUP Formula
      3. 6.28.3. Using a Custom Function
    29. 100. Add/Subtract Months from a Date
      1. 6.29.1. EDATE
      2. 6.29.2. Without EDATE
    30. 101. Find the Last Day of Any Given Month
      1. 6.30.1. Using Formulas
      2. 6.30.2. Using EOMONTH
      3. 6.30.3. Using a Custom Function
    31. 102. Calculate a Person’s Age
    32. 103. Return the Weekday of a Date
      1. 6.32.1. Get the Weekday as a Number
      2. 6.32.2. Return the Weekday as Weekday Name
      3. 6.32.3. Return the Weekday as Weekday Text
    33. 104. Evaluate a Text Equation
    34. 105. Lookup from Within a Cell
      1. 6.34.1. CHOOSE and MATCH
      2. 6.34.2. Keeping It Clean and Global
      3. 6.34.3. Lookup Scale
  10. 7. Macro Hacks
    1. 7.1. Hacks 106–134
    2. 106. Speed Up Code While Halting Screen Flicker
    3. 107. Run a Macro at a Set Time
    4. 108. Use CodeNames to Reference Sheets in Excel Workbooks
    5. 109. Connect Buttons to Macros Easily
    6. 110. Create a Workbook Splash Screen
    7. 111. Display a “Please Wait” Message
    8. 112. Have a Cell Ticked or Unticked upon Selection
    9. 113. Count or Sum Cells That Have a Specified Fill Color
    10. 114. Add the Microsoft Excel Calendar Control to Any Excel Workbook
    11. 115. Password-Protect and Unprotect All Excel Worksheets in One Fell Swoop
    12. 116. Retrieve a Workbook’s Name and Path
    13. 117. Get Around Excel’s Three-Criteria Limit for Conditional Formatting
    14. 118. Run Procedures on Protected Worksheets
    15. 119. Distribute Macros
      1. 7.15.1. Add a Menu Item
    16. 120. Delete Rows Based on a Condition
      1. 7.16.1. With AutoFilter
      2. 7.16.2. Without AutoFilter
    17. 121. Track and Report Changes in Excel
      1. 7.17.1. Track Changes on a Particular Worksheet
      2. 7.17.2. Track Changes on All Worksheets in One Workbook
    18. 122. Automatically Add Date/Time to a Cell upon Entry
    19. 123. Create a List of Workbook Hyperlinks
      1. 7.19.1. The Code
      2. 7.19.2. Running the Hack
    20. 124. Advanced Find
      1. 7.20.1. The UserForm
      2. 7.20.2. The Code
      3. 7.20.3. Running the Hack
    21. 125. Find a Number Between Two Numbers
      1. 7.21.1. The Code
      2. 7.21.2. Running the Hack
    22. 126. Convert Formula References from Relative to Absolute
      1. 7.22.1. Less Complicated Formulas
      2. 7.22.2. Mega or Array Formulas
    23. 127. Name a Workbook with the Text in a Cell
      1. 7.23.1. The Code
      2. 7.23.2. Running the Hack
    24. 128. Hide and Restore Toolbars in Excel
      1. 7.24.1. Attaching Your Toolbar to the Workbook
      2. 7.24.2. Coding the Toolbar Show and Restore
    25. 129. Sort Worksheets
      1. 7.25.1. The Code
      2. 7.25.2. Running the Hack
    26. 130. Password-Protect a Worksheet from Viewing
      1. 7.26.1. The Code
      2. 7.26.2. Running the Hack
    27. 131. Change Text to Upper- or Proper Case
      1. 7.27.1. The Code
      2. 7.27.2. Running the Hack
    28. 132. Force Text to Upper- or Proper Case
      1. 7.28.1. The Code
      2. 7.28.2. Running the Hack
      3. 7.28.3. Hacking the Hack
    29. 133. Prevent Case Sensitivity in VBA Code
      1. 7.29.1. Ucase Function
      2. 7.29.2. Option Compare Text
    30. 134. Display AutoFilter Criteria
  11. 8. Cross-Application Hacks
    1. 8.1. Hacks 135–138
    2. 135. Import Data from Access 2007 into Excel 2007
      1. 8.2.1. The Code
      2. 8.2.2. Running the Hack
    3. 136. Retrieve Data from Closed Workbooks
      1. 8.3.1. Excel 2007 and Windows Vista
      2. 8.3.2. Windows XP
    4. 137. Automate Word from Excel
      1. 8.4.1. The Code
      2. 8.4.2. Running the Hack
    5. 138. Automate Outlook from Excel
      1. 8.5.1. The Code
      2. 8.5.2. Running the Hack
  12. Index
  13. About the Authors
  14. Colophon
  15. Copyright

Product information

  • Title: Excel Hacks, 2nd Edition
  • Author(s): David Hawley, Raina Hawley
  • Release date: June 2007
  • Publisher(s): O'Reilly Media, Inc.
  • ISBN: 9780596528348