Excel Hacks: 100 Industrial-Strength Tips and Tools

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：

Credits 

Preface 

Chapter 1. Reducing Workbook and Worksheet Frustration

       1. Create a Personal View of Your Workbooks 

       2. Enter Data into Multiple Worksheets Simultaneously

       3. Prevent Users from Performing Certain Actions 

       4. Prevent Seemingly Unnecessary Prompts 

       5. Hide Worksheets So That They Cannot Be Unhidden 

       6. Customize the Templates Dialog and Default Workbook 

       7. Create an Index of Sheets in Your Workbook 

       8. Limit the Scrolling Range of Your Worksheet 

       9. Lock and Protect Cells Containing Formulas 

       10. Find Duplicate Data using Conditional Formatting 

       11. Tie Custom Toolbars to a Particular Workbook 

       12. Outsmart Excel's Relative Reference Handler 

       13. Remove Phantom Workbook Links 

       14. Reduce Workbook Bloat 

       15. Extract Data from a Corrupt Workbook 

Chapter 2. Hacking Excel's Built-in Features

       16. Validate Data Based on a List on Another Worksheet 

       17. Control Conditional Formatting with Checkboxes 

       18. Identify Formulas with Conditional Formatting 

       19. Count or Sum Cells That Meet Conditional Formatting Criteria 

       20. Highlight Every Other Row or Column 

       21. Create 3D Effects in Tables or Cells 

       22. Turn Conditional Formatting and Data Validation On and Off with a Checkbox 

       23. Support Multiple Lists in a ComboBox 

       24. Create Validation Lists That Change Based on a Selection from Another List 

       25. Force Data Validation to Reference a List on Another Worksheet 

       26. Use Replace- to Remove Unwanted Characters 

       27. Convert Text Numbers to Real Numbers 

       28. Customize Cell Comments 

       29. Sort by More Than Three Columns 

       30. Random Sorting 

       31. Manipulate Data with the Advanced Filter 

       32. Create Custom Number Formats 

       33. Add More Levels of Undo to Excel for Windows 

       34. Create Custom Lists 

       35. Boldface Excel Subtotals 

       36. Convert Excel Formulas and Functions to Values 

       37. Automatically Add Data to a Validation List 

       38. Hack Excel's Date and Time Features 

Chapter 3. Naming Hacks

       39. Address Data by Name 

       40. Use the Same Name for Ranges on Different Worksheets 

       41. Create Custom Functions Using Names 

       42. Create Ranges That Expand and Contract 

       43. Nest Dynamic Ranges for Maximum Flexibility 

       44. Identify Named Ranges on a Worksheet 

Chapter 4. Hacking PivotTables

       45. PivotTables: A Hack in Themselves 

       46. Share PivotTables but Not Their Data 

       47. Automate PivotTable Creation 

       48. Move PivotTable Grand Totals 

       49. Efficiently Pivot Another Workbook's Data 

Chapter 5. Charting Hacks

       50. Explode a Single Slice from a Pie Chart 

       51. Create Two Sets of Slices in One Pie Chart 

       52. Create Charts That Adjust to Data 

       53. Interact with Your Charts Using Custom Controls 

       54. Three Quick Ways to Update Your Charts 

       55. Hack Together a Simple Thermometer Chart 

       56. Create a Column Chart with Variable Widths and Heights 

       57. Create a Speedometer Chart 

       58. Link Chart Text Elements to a Cell 

       59. Hack Chart Data So That Blank Cells Are Not Plotted 

Chapter 6. Hacking Formulas and Functions

       60. Add Descriptive Text to Your Formulas 

       61. Move Relative Formulas Without Changing References 

       62. Compare Two Excel Ranges 

       63. Fill All Blank Cells in a List 

       64. Make Your Formulas Increment by Rows When You Copy Across Columns 

       65. Convert Dates to Excel Formatted Dates 

       66. Sum or Counting Cells While Avoiding Error Values

       67. Reduce the Impact of Volatile Functions on Recalculation 

       68. Count Only One Instance of Each Entry in a List 

       69. Sum Every Second, Third, or nth Row or Cell 

       70. Find the nth Occurrence of a Value 

       71. Make the Excel Subtotal Function Dynamic 

       72. Add Date Extensions 

       73. Convert Numbers with the Negative Sign on the Right to Excel Numbers 

       74. Display Negative Time Values 

       75. Use the VLOOKUP Function Across Multiple Tables 

       76. Show Total Time as Days, Hours, and Minutes 

       77. Determine the Number of Specified Days in Any Month 

       78. Construct Mega-Formulas 

       79. Hack Mega-Formulas that Reference Other Workbooks

       80. Hack One of Excel's Database Functions to Take the Place of Many Functions 

Chapter 7. Macro Hacks

       81. Speed Up Code While Halting Screen Flicker 

       82. Run a Macro at a Set Time 

       83. Use CodeName to Reference Sheets in Excel Workbooks 

       84. Connect Buttons to Macros Easily 

       85. Create a Workbook Splash Screen 

       86. Display a "Please Wait" Message 

       87. Have a Cell Ticked or Unticked upon Selection 

       88. Count or Sum Cells That Have a Specified Fill Color 

       89. Add the Microsoft Excel Calendar Control to Any Excel Workbook 

       90. Password-Protect and Unprotect All Excel Worksheets in One Fell Swoop 

       91. Retrieve a Workbook's Name and Path 

       92. Get Around Excel's Three-Criteria Limit for Conditional Formatting 

       93. Run Procedures on Protected Worksheets 

       94. Distribute Macros 

Chapter 8. Connecting Excel to the World

       95. Load an XML Document into Excel 

       96. Save to SpreadsheetML and Extracting Data 

       97. Create Spreadsheets using SpreadsheetML 

       98. Import Data Directly into Excel 

       99. Access SOAP Web Services from Excel 

       100. Create Excel Spreadsheets Using Other Environments 

Glossary 

Index