Cover image for Excel Hacks

Book description

If you think that getting creative with Excel means the underhanded tweaking of numbers, think again. Excel Hacks shows even the most experienced users how to do things with Excel they might never have thought of doing--and lets them have a little fun while they're at it. Microsoft Excel is not just the dominant spreadsheet in the world; it's also one of the most popular applications ever created. Its success lies not only in its power and flexibility, but also in its streamlined, familiar interface that casually conceals its considerable capabilities. You don't need to know everything that Excel can do in order to use it effectively, but if you're like the millions of Excel power users looking to improve productivity, then Excel Hacks will show you a wide variety of Excel tasks you can put to use, most of which are off the beaten path. With this book, Excel power users can bring a hacker's creative approach to both common and uncommon Excel topics--"hackers" in this sense being those who like to tinker with technology to improve it. The "100 Industrial Strength Tips and Tools" in Excel Hacks include little known "backdoor" adjustments for everything from reducing workbook and worksheet frustration to hacking built-in features such as pivot tables, charts, formulas and functions, and even the macro language. This resourceful roll-up-your-sleeves guide is for intermediate to advanced Excel users eager to explore new ways to make Excel do things--from data analysis to worksheet management to import/export--that you never thought possible. Excel Hacks will help you increase productivity with Excel and give you hours of "hacking" enjoyment along the way.

Table of Contents

  1. Excel Hacks
    1. SPECIAL OFFER: Upgrade this ebook with O’Reilly
    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 and Macintosh Users
      6. Conventions Used in This Book
      7. Using Code Examples
      8. How to Contact Us
    4. 1. Reducing Workbook and Worksheet Frustration
      1. Hacks #1-15
        1. The 80/20 Rule
        2. Structural Tips
        3. Formatting Tips
        4. Formula Tips
      2. Hack #1. Create a Personal View of Your Workbooks
      3. Hack #2. Enter Data into Multiple Worksheets Simultaneously
        1. Grouping Worksheets Manually
        2. Grouping Worksheets Automatically
      4. Hack #3. Prevent Users from Performing Certain Actions
        1. Preventing Save As... in a Workbook
        2. Preventing Users from Printing a Workbook
        3. Preventing Users from Inserting More Worksheets
      5. Hack #4. Prevent Seemingly Unnecessary Prompts
        1. Enabling Macros When You Don't Have Any
        2. Prompting to Save Nonexistent Changes
        3. Stopping Excel's Warning Prompts for Recorded Macros
      6. Hack #5. Hide Worksheets So That They Cannot Be Unhidden
      7. Hack #6. Customize the Templates Dialog and Default Workbook
        1. Creating Your Own Template Tab
        2. Using a Custom Default Workbook
      8. Hack #7. Create an Index of Sheets in Your Workbook
      9. Hack #8. Limit the Scrolling Range of Your Worksheet
      10. Hack #9. Lock and Protect Cells Containing Formulas
      11. Hack #10. Find Duplicate Data using Conditional Formatting
      12. Hack #11. Tie Custom Toolbars to a Particular Workbook
      13. Hack #12. Outsmart Excel's Relative Reference Handler
      14. Hack #13. Remove Phantom Workbook Links
      15. Hack #14. Reduce Workbook Bloat
        1. Eliminating Superfluous Formatting
        2. Honing Data Sources
        3. Cleaning Corrupted Workbooks
      16. Hack #15. Extract Data from a Corrupt Workbook
        1. If You Can Open Your Workbook
        2. If You Cannot Open Your File
    5. 2. Hacking Excel's Built-in Features
      1. Hacks #16-38
      2. Hack #16. Validate Data Based on a List on Another Worksheet
        1. Method 1: Named Ranges
        2. Method 2: the INDIRECT Function
        3. The Pros and Cons of Both Methods
      3. Hack #17. Control Conditional Formatting with Checkboxes
        1. Setting Up Checkboxes for Conditional Formatting
        2. Toggling Number Highlighting On and Off
      4. Hack #18. Identify Formulas with Conditional Formatting
      5. Hack #19. Count or Sum Cells That Meet Conditional Formatting Criteria
        1. An Alternate Path
      6. Hack #20. Highlight Every Other Row or Column
      7. Hack #21. Create 3D Effects in Tables or Cells
        1. Using a 3D Effect on a Table of Data
      8. Hack #22. Turn Conditional Formatting and Data Validation On and Off with a Checkbox
      9. Hack #23. Support Multiple Lists in a ComboBox
      10. Hack #24. Create Validation Lists That Change Based on a Selection from Another List
      11. Hack #25. Force Data Validation to Reference a List on Another Worksheet
        1. Method 1: Named Ranges
        2. Method 2: the INDIRECT Function
        3. The Pros and Cons of Each Method
      12. Hack #26. Use Replace... to Remove Unwanted Characters
      13. Hack #27. Convert Text Numbers to Real Numbers
      14. Hack #28. Customize Cell Comments
      15. Hack #29. Sort by More Than Three Columns
      16. Hack #30. Random Sorting
      17. Hack #31. Manipulate Data with the Advanced Filter
      18. Hack #32. Create Custom Number Formats
      19. Hack #33. Add More Levels of Undo to Excel for Windows
      20. Hack #34. Create Custom Lists
      21. Hack #35. Boldface Excel Subtotals
        1. Hacking the Hack
      22. Hack #36. Convert Excel Formulas and Functions to Values
        1. Using Paste Special
        2. Using Copy Here as Values Only
        3. Using a Macro
      23. Hack #37. Automatically Add Data to a Validation List
      24. Hack #38. Hack Excel's Date and Time Features
        1. Adding Beyond 24 Hours
        2. Time and Date Calculations
        3. Real Dates and Times
        4. A Date Bug?
    6. 3. Naming Hacks
      1. Hacks #39-44
      2. Hack #39. Address Data by Name
      3. Hack #40. Use the Same Name for Ranges on Different Worksheets
      4. Hack #41. Create Custom Functions Using Names
      5. Hack #42. Create Ranges That Expand and Contract
      6. Hack #43. Nest Dynamic Ranges for Maximum Flexibility
      7. Hack #44. Identify Named Ranges on a Worksheet
        1. Method 1
        2. Method 2
    7. 4. Hacking PivotTables
      1. Hacks #46-49
      2. Hack #45. PivotTables: A Hack in Themselves
        1. Why Are They Called PivotTables?
        2. What Are PivotTables Good For?
        3. Why Use PivotTables When Spreadsheets Already Offer So Much Analysis Capability?
        4. PivotCharts Extend PivotTables
        5. Creating Tables and Lists for Use in PivotTables
        6. The PivotTable and PivotChart Wizard
      3. Hack #46. Share PivotTables but Not Their Data
      4. Hack #47. Automate PivotTable Creation
      5. Hack #48. Move PivotTable Grand Totals
      6. Hack #49. Efficiently Pivot Another Workbook's Data
    8. 5. Charting Hacks
      1. Hacks #50-59
      2. Hack #50. Explode a Single Slice from a Pie Chart
      3. Hack #51. Create Two Sets of Slices in One Pie Chart
      4. Hack #52. Create Charts That Adjust to Data
        1. Plotting the Last x Number of Readings
      5. Hack #53. Interact with Your Charts Using Custom Controls
        1. Using a Dynamic Named Range Linked to a Scrollbar
        2. Using a Dynamic Named Range Linked to a Drop-Down List
      6. Hack #54. Three Quick Ways to Update Your Charts
        1. Using Drag-and-Drop
        2. Using the Formula Bar
        3. Dragging the Bounding Area
      7. Hack #55. Hack Together a Simple Thermometer Chart
      8. Hack #56. Create a Column Chart with Variable Widths and Heights
      9. Hack #57. Create a Speedometer Chart
      10. Hack #58. Link Chart Text Elements to a Cell
      11. Hack #59. Hack Chart Data So That Blank Cells Are Not Plotted
        1. Hiding Rows or Columns
    9. 6. Hacking Formulas and Functions
      1. Hacks #60-80
      2. Hack #60. Add Descriptive Text to Your Formulas
      3. Hack #61. Move Relative Formulas Without Changing References
      4. Hack #62. Compare Two Excel Ranges
        1. Method 1: Using True or False
        2. Method 2: Using Conditional Formatting
      5. Hack #63. Fill All Blank Cells in a List
        1. Method 1: Filling Blanks Via a Formula
        2. Method 2: Filling Blanks Via a Macro
      6. Hack #64. Make Your Formulas Increment by Rows When You Copy Across Columns
      7. Hack #65. Convert Dates to Excel Formatted Dates
      8. Hack #66. Sum or Counting Cells While Avoiding Error Values
      9. Hack #67. Reduce the Impact of Volatile Functions on Recalculation
      10. Hack #68. Count Only One Instance of Each Entry in a List
      11. Hack #69. Sum Every Second, Third, or nth Row or Cell
      12. Hack #70. Find the nth Occurrence of a Value
      13. Hack #71. Make the Excel Subtotal Function Dynamic
      14. Hack #72. Add Date Extensions
      15. Hack #73. Convert Numbers with the Negative Sign on the Right to Excel Numbers
      16. Hack #74. Display Negative Time Values
        1. Method 1: Changing Excel's Default Date System
        2. Method 2: Using the TEXT Function
        3. Method 3: Using a Custom Format
      17. Hack #75. Use the VLOOKUP Function Across Multiple Tables
      18. Hack #76. Show Total Time as Days, Hours, and Minutes
      19. Hack #77. Determine the Number of Specified Days in Any Month
      20. Hack #78. Construct Mega-Formulas
      21. Hack #79. Hack Mega-Formulas that Reference Other Workbooks
      22. Hack #80. Hack One of Excel's Database Functions to Take the Place of Many Functions
    10. 7. Macro Hacks
      1. Hacks #81-94
      2. Hack #81. Speed Up Code While Halting Screen Flicker
      3. Hack #82. Run a Macro at a Set Time
      4. Hack #83. Use CodeName to Reference Sheets in Excel Workbooks
      5. Hack #84. Connect Buttons to Macros Easily
      6. Hack #85. Create a Workbook Splash Screen
      7. Hack #86. Display a "Please Wait" Message
      8. Hack #87. Have a Cell Ticked or Unticked upon Selection
      9. Hack #88. Count or Sum Cells That Have a Specified Fill Color
      10. Hack #89. Add the Microsoft Excel Calendar Control to Any Excel Workbook
      11. Hack #90. Password-Protect and Unprotect All Excel Worksheets in One Fell Swoop
      12. Hack #91. Retrieve a Workbook's Name and Path
      13. Hack #92. Get Around Excel's Three-Criteria Limit for Conditional Formatting
      14. Hack #93. Run Procedures on Protected Worksheets
      15. Hack #94. Distribute Macros
    11. 8. Connecting Excel to the World
      1. Hacks #95-100
      2. Hack #95. Load an XML Document into Excel
      3. Hack #96. Save to SpreadsheetML and Extracting Data
      4. Hack #97. Create Spreadsheets using SpreadsheetML
      5. Hack #98. Import Data Directly into Excel
        1. Running the Hack
        2. Hacking the Hack
          1. Making the Query Dynamic
          2. Using Different Data
          3. Graphing Results
      6. Hack #99. Access SOAP Web Services from Excel
      7. Hack #100. Create Excel Spreadsheets Using Other Environments
    12. 9. Glossary
    13. About the Authors
    14. Colophon
    15. SPECIAL OFFER: Upgrade this ebook with O’Reilly