book

Excel Hacks, 2nd Edition

Name: Excel Hacks, 2nd Edition
ISBN: 9780596528348

by David Hawley, Raina Hawley

June 2007

Beginner

410 pages

10h 2m

English

O'Reilly Media, Inc.

Read now

Unlock full access

Dedication
Credits
About the AuthorsContributorsAcknowledgments
Preface
Why Excel Hacks?Getting and Using the HacksHow to Use This BookHow This Book Is OrganizedWindows, Macintosh, and Earlier Excel VersionsConventions Used in This BookUsing Code ExamplesSafari® EnabledHow to Contact Us
1. Reducing Workbook and Worksheet Frustration
1.1. Hacks 1–161.1.1. The 80/20 Rule1.1.2. Structural Tips1.1.3. Formatting Tips1.1.4. Formula Tips1. Create a Personal View of Your Workbooks2. Enter Data into Multiple Worksheets Simultaneously1.3.1. Grouping Worksheets Manually1.3.2. Grouping Worksheets Automatically3. Prevent Users from Performing Certain Actions1.4.1. Preventing Save As… in a Workbook1.4.2. Preventing Users from Printing a Workbook1.4.3. Preventing Users from Inserting More Worksheets4. Prevent Seemingly Unnecessary Prompts1.5.1. Enabling Macros When You Don’t Have Any1.5.2. Prompting to Save Nonexistent Changes1.5.3. Stopping Excel’s Warning Prompts for Recorded Macros5. Hide Worksheets So That They Cannot Be Unhidden6. Customize the Templates Dialog and Default Workbook1.7.1. Creating Your Own Template Tab1.7.2. Using a Custom Default Workbook7. Create an Index of Sheets in Your Workbook1.8.1. Creating an Index Sheet by Hand1.8.2. Auto-Generate an Index Using VBA1.8.3. Link to the Index from a Context Menu8. Limit the Scrolling Range of Your Worksheet1.9.1. Hiding Rows and Columns1.9.2. Specifying a Valid Range1.9.3. Activating Only the Used Range9. Lock and Protect Cells Containing Formulas1.10.1. Locking Formula Cells1.10.2. Data Validation1.10.3. Auto-Toggle Worksheet Protection10. Find Duplicate Data Using Conditional Formatting11. Find Data That Appears Two or More Times Using Conditional Formatting12. Tie Custom Toolbars to a Particular Workbook13. Outsmart Excel’s Relative Reference Handler14. Remove Phantom Workbook Links15. Reduce Workbook Bloat1.16.1. Eliminating Superfluous Formatting1.16.2. Clean Up Your Macros1.16.3. Honing Data Sources1.16.4. Cleaning Corrupted Workbooks16. Extract Data from a Corrupt Workbook1.17.1. If You Can Open Your Workbook1.17.2. If You Cannot Open Your File
2. Hacking Excel’s Built-in Features
2.1. Hacks 17–4317. Validate Data Based on a List on Another Worksheet2.2.1. Method 1: Named Ranges2.2.2. Method 2: the INDIRECT Function2.2.3. The Pros and Cons of Both Methods18. Control Conditional Formatting with Checkboxes2.3.1. Setting Up Checkboxes for Conditional Formatting2.3.2. Toggling Number Highlighting On and Off19. Identify Formulas with Conditional Formatting20. Count or Sum Cells That Meet Conditional Formatting Criteria2.5.1. An Alternate Path21. Highlight Every Other Row or Column2.6.1. Highlighting Dynamically22. Create 3-D Effects in Tables or Cells2.7.1. Using a 3-D Effect on a Table of Data23. Turn Conditional Formatting and Data Validation On and Off with a Checkbox24. Support Multiple Lists in a ComboBox25. Create Validation Lists That Change Based on a Selection from Another List26. Use Replace… to Remove Unwanted Characters27. Convert Text Numbers to Real Numbers2.12.1. Using Paste Special2.12.2. Using the TEXT Functions28. Extract the Numeric Portion of a Cell Entry29. Customize Cell Comments2.14.1. Adding a Picture2.14.2. Extracting Comment Text30. Sort by More Than Three Columns31. Random Sorting32. Manipulate Data with the Advanced Filter33. Create Custom Number Formats34. Add More Levels of Undo to Excel for Windows35. Create Custom Lists36. Boldface Excel Subtotals2.21.1. Hacking the Hack37. Convert Excel Formulas and Functions to Values2.22.1. Using Paste Special2.22.2. Using Copy Here As Values Only2.22.3. Using a Macro38. Automatically Add Data to a Validation List39. Hack Excel’s Date and Time Features2.24.1. Adding Beyond 24 Hours2.24.2. Time and Date Calculations2.24.3. Real Dates and Times2.24.4. A Date Bug?40. Enable Grouping and Outlining on a Protected Worksheet41. Prevent Blanks/Missing Fields in a Table42. Provide Decreasing Data Validation Lists43. Add a Custom List to the Fill Handle
3. Naming Hacks
3.1. Hacks 44–4944. Address Data by Name45. Use the Same Name for Ranges on Different Worksheets3.3.1. Using Relative References46. Create Custom Functions Using Names3.4.1. Using Names with Intersect47. Create Ranges That Expand and Contract48. Nest Dynamic Ranges for Maximum Flexibility49. Identify Named Ranges on a Worksheet3.7.1. Method 13.7.2. Method 2
4. Hacking PivotTables
4.1. Hacks 50–5450. PivotTables: A Hack in Themselves4.2.1. Why Are They Called PivotTables?4.2.2. What Are PivotTables Good For?4.2.3. Why Use PivotTables When Spreadsheets Already Offer So Much Analysis Capability?4.2.4. PivotCharts Extend PivotTables4.2.5. Creating Tables and Lists for Use in PivotTables4.2.6. PivotTable Creation51. Share PivotTables but Not Their Data52. Automate PivotTable Creation4.4.1. Save Time with a Macro53. Move PivotTable Grand Totals54. Efficiently Pivot Another Workbook’s Data
5. Charting Hacks
5.1. Hacks 55–7255. Explode a Single Slice from a Pie Chart56. Create Two Sets of Slices in One Pie Chart57. Create Charts That Adjust to Data5.4.1. Plotting the Last x Number of Readings58. Interact with Your Charts Using Custom Controls5.5.1. Using a Dynamic Named Range Linked to a Scrollbar5.5.2. Using a Dynamic Named Range Linked to a Drop-Down List59. Four Quick Ways to Update Your Charts5.6.1. Using Drag-and-Drop5.6.2. Using the Formula Bar5.6.3. Dragging the Bounding Area5.6.4. Using Paste Special60. Hack Together a Simple Thermometer Chart61. Create a Column Chart with Variable Widths and Heights62. Create a Speedometer Chart63. Link Chart Text Elements to a Cell64. Hack Chart Data So That Empty or FALSE Formula Cells Are Not Plotted5.11.1. Hiding Rows or Columns5.11.2. Using #N/A to Plot Blank Cells65. Add a Directional Arrow to the End of a Line Series66. Place an Arrow on the End of a Horizontal (X) Axis5.13.1. In Excel 20075.13.2. In Older Excel Versions67. Correct Narrow Columns When Using Dates68. Position Axis Labels5.15.1. Changing Label Position5.15.2. Reversing Label Order69. Tornado Chart70. Gauge Chart71. Conditional Highlighting Axis Labels72. Create Totals on a Stacked Column Chart
6. Hacking Formulas and Functions
6.1. Hacks 73–10573. Add Descriptive Text to Your Formulas74. Move Relative Formulas Without Changing References75. Compare Two Excel Ranges6.4.1. Method 1: Using True or False6.4.2. Method 2: Using Conditional Formatting76. Fill All Blank Cells in a List6.5.1. Method 1: Filling Blanks via a Formula6.5.2. Method 2: Filling Blanks via a Macro77. Make Your Formulas Increment by Rows When You Copy Across Columns78. Convert Dates to Excel Formatted Dates79. Sum or Count Cells While Avoiding Error Values80. Reduce the Impact of Volatile Functions on Recalculation81. Count Only One Instance of Each Entry in a List6.10.1. Before Excel 20076.10.2. Excel 20076.10.3. Using a Pivot Table82. Sum Every Second, Third, or Nth Row or Cell6.11.1. Using an Array Formula6.11.2. Using SUMPRODUCT6.11.3. Using DSUM83. Find the Nth Occurrence of a Value84. Make the Excel Subtotal Function Dynamic85. Add Date Extensions86. Convert Numbers with the Negative Sign on the Right to Excel Numbers87. Display Negative Time Values6.16.1. Method 1: Changing Excel’s Default Date System6.16.2. Method 2: Using the TEXT Function6.16.3. Method 3: Using a Custom Format88. Use the VLOOKUP Function Across Multiple Tables89. Show Total Time As Days, Hours, and Minutes90. Determine the Number of Specified Days in Any Month91. Construct Mega-Formulas92. Hack Mega-Formulas that Reference Other Workbooks93. Hack One of Excel’s Database Functions to Take the Place of Many Functions6.22.1. Using DCOUNT to Filter on Two Criteria6.22.2. Making the Comparison Operators Interchangeable94. Extract Specified Words from a Text String6.23.1. Getting the Last Word6.23.2. Getting the First Word6.23.3. Get the Nth Word95. Count Words in a Cell or Range of Cells6.24.1. SUBSTITUTE6.24.2. LEN6.24.3. Putting It Together6.24.4. Hacking the Hack96. Return a Worksheet Name to a Cell6.25.1. Create a List of Worksheet Names6.25.2. Extract Worksheet Names Only6.25.3. Use the List in Formulas6.25.4. Make the Range Address Variable97. Sum Cells with Multiple Criteria6.26.1. SUMIF6.26.2. DSUM6.26.3. SUMPRODUCT6.26.4. SUM and IF98. Count Cells with Multiple Criteria6.27.1. Array Formulas6.27.2. SUMPRODUCT99. Calculate a Sliding Tax Scale6.28.1. Using IF/SUM6.28.2. Using a VLOOKUP Formula6.28.3. Using a Custom Function100. Add/Subtract Months from a Date6.29.1. EDATE6.29.2. Without EDATE101. Find the Last Day of Any Given Month6.30.1. Using Formulas6.30.2. Using EOMONTH6.30.3. Using a Custom Function102. Calculate a Person’s Age103. Return the Weekday of a Date6.32.1. Get the Weekday as a Number6.32.2. Return the Weekday as Weekday Name6.32.3. Return the Weekday as Weekday Text104. Evaluate a Text Equation105. Lookup from Within a Cell6.34.1. CHOOSE and MATCH6.34.2. Keeping It Clean and Global6.34.3. Lookup Scale
7. Macro Hacks
7.1. Hacks 106–134106. Speed Up Code While Halting Screen Flicker107. Run a Macro at a Set Time108. Use CodeNames to Reference Sheets in Excel Workbooks109. Connect Buttons to Macros Easily110. Create a Workbook Splash Screen111. Display a “Please Wait” Message112. Have a Cell Ticked or Unticked upon Selection113. Count or Sum Cells That Have a Specified Fill Color114. Add the Microsoft Excel Calendar Control to Any Excel Workbook115. Password-Protect and Unprotect All Excel Worksheets in One Fell Swoop116. Retrieve a Workbook’s Name and Path117. Get Around Excel’s Three-Criteria Limit for Conditional Formatting118. Run Procedures on Protected Worksheets119. Distribute Macros7.15.1. Add a Menu Item120. Delete Rows Based on a Condition7.16.1. With AutoFilter7.16.2. Without AutoFilter121. Track and Report Changes in Excel7.17.1. Track Changes on a Particular Worksheet7.17.2. Track Changes on All Worksheets in One Workbook122. Automatically Add Date/Time to a Cell upon Entry123. Create a List of Workbook Hyperlinks7.19.1. The Code7.19.2. Running the Hack124. Advanced Find7.20.1. The UserForm7.20.2. The Code7.20.3. Running the Hack125. Find a Number Between Two Numbers7.21.1. The Code7.21.2. Running the Hack126. Convert Formula References from Relative to Absolute7.22.1. Less Complicated Formulas7.22.2. Mega or Array Formulas127. Name a Workbook with the Text in a Cell7.23.1. The Code7.23.2. Running the Hack128. Hide and Restore Toolbars in Excel7.24.1. Attaching Your Toolbar to the Workbook7.24.2. Coding the Toolbar Show and Restore129. Sort Worksheets7.25.1. The Code7.25.2. Running the Hack130. Password-Protect a Worksheet from Viewing7.26.1. The Code7.26.2. Running the Hack131. Change Text to Upper- or Proper Case7.27.1. The Code7.27.2. Running the Hack132. Force Text to Upper- or Proper Case7.28.1. The Code7.28.2. Running the Hack7.28.3. Hacking the Hack133. Prevent Case Sensitivity in VBA Code7.29.1. Ucase Function7.29.2. Option Compare Text134. Display AutoFilter Criteria

8. Cross-Application Hacks
8.1. Hacks 135–138135. Import Data from Access 2007 into Excel 20078.2.1. The Code8.2.2. Running the Hack136. Retrieve Data from Closed Workbooks8.3.1. Excel 2007 and Windows Vista8.3.2. Windows XP137. Automate Word from Excel8.4.1. The Code8.4.2. Running the Hack138. Automate Outlook from Excel8.5.1. The Code8.5.2. Running the Hack
Index
About the Authors
Colophon
Copyright

Content preview from Excel Hacks, 2nd Edition

Credits

About the Authors

David and Raina Hawley provide business applications, software, development, consultancy, training, and tutoring in all aspects of Excel and VBA for Excel through OzGrid Business Applications in Western Australia.

David Hawley has spent the last 15 years creating business applications using Excel and VBA for Excel on a day-to-day basis. He produces a monthly newsletter containing information on the use of Excel and VBA for Excel. He runs and maintains one of the largest Excel forums in the world on the OzGrid web site.

Raina Hawley lectures in industry and in the college education system, and is a registered workplace assessor. Raina runs the OzGrid office, administration, consultancy, development, and training side of the business, and works in Excel solutions alongside her husband.

David and Raina offer hundreds of Excel Add-Ins and business software designed for data analysis in all industry areas through their web site at http://www.ozgrid.com. The web site contains over 50,000 pages of free Excel information. They live in Bunbury, Western Australia, with their two children.

Contributors

The following people contributed their hacks, writing, and inspiration to this book:

Andy Pope is a programmer working in London. He has been using computers since the mid ’80s. His current role involves writing customized solutions for reporting projects utilizing the MS Office products via VBA. Andy also runs his own web site (http://www.andypope.info). His contributions ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Publisher Resources

ISBN: 9780596528348Errata Page

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design