Excel Hacks |
By David Hawley, Raina Hawley |
|
Publisher | : O'Reilly |
Pub Date | : March 2004 |
ISBN | : 0-596-00625-X |
Pages | : 304 |
| |
| Copyright |
| | Dedication |
| | Credits |
| | | About the Authors |
| | | Contributors |
| | | Acknowledgments |
| | Preface |
| | | Why Excel Hacks? |
| | | Getting and Using the Hacks |
| | | How to Use This Book |
| | | How This Book Is Organized |
| | | Windows and Macintosh Users |
| | | Conventions Used in This Book |
| | | Using Code Examples |
| | | How to Contact Us |
| | Chapter 1. Reducing Workbook and Worksheet Frustration |
| | | Hacks #1-15 |
| | | Section 1. Create a Personal View of Your Workbooks |
| | | Section 2. Enter Data into Multiple Worksheets Simultaneously |
| | | Section 3. Prevent Users from Performing Certain Actions |
| | | Section 4. Prevent Seemingly Unnecessary Prompts |
| | | Section 5. Hide Worksheets So That They Cannot Be Unhidden |
| | | Section 6. Customize the Templates Dialog and Default Workbook |
| | | Section 7. Create an Index of Sheets in Your Workbook |
| | | Section 8. Limit the Scrolling Range of Your Worksheet |
| | | Section 9. Lock and Protect Cells Containing Formulas |
| | | Section 10. Find Duplicate Data using Conditional Formatting |
| | | Section 11. Tie Custom Toolbars to a Particular Workbook |
| | | Section 12. Outsmart Excel's Relative Reference Handler |
| | | Section 13. Remove Phantom Workbook Links |
| | | Section 14. Reduce Workbook Bloat |
| | | Section 15. Extract Data from a Corrupt Workbook |
| | Chapter 2. Hacking Excel's Built-in Features |
| | | Hacks #16-38 |
| | | Section 16. Validate Data Based on a List on Another Worksheet |
| | | Section 17. Control Conditional Formatting with Checkboxes |
| | | Section 18. Identify Formulas with Conditional Formatting |
| | | Section 19. Count or Sum Cells That Meet Conditional Formatting Criteria |
| | | Section 20. Highlight Every Other Row or Column |
| | | Section 21. Create 3D Effects in Tables or Cells |
| | | Section 22. Turn Conditional Formatting and Data Validation On and Off with a Checkbox |
| | | Section 23. Support Multiple Lists in a ComboBox |
| | | Section 24. Create Validation Lists That Change Based on a Selection from Another List |
| | | Section 25. Force Data Validation to Reference a List on Another Worksheet |
| | | Section 26. Use Replace... to Remove Unwanted Characters |
| | | Section 27. Convert Text Numbers to Real Numbers |
| | | Section 28. Customize Cell Comments |
| | | Section 29. Sort by More Than Three Columns |
| | | Section 30. Random Sorting |
| | | Section 31. Manipulate Data with the Advanced Filter |
| | | Section 32. Create Custom Number Formats |
| | | Section 33. Add More Levels of Undo to Excel for Windows |
| | | Section 34. Create Custom Lists |
| | | Section 35. Boldface Excel Subtotals |
| | | Section 36. Convert Excel Formulas and Functions to Values |
| | | Section 37. Automatically Add Data to a Validation List |
| | | Section 38. Hack Excel's Date and Time Features |
| | Chapter 3. Naming Hacks |
| | | Hacks #39-44 |
| | | Section 39. Address Data by Name |
| | | Section 40. Use the Same Name for Ranges on Different Worksheets |
| | | Section 41. Create Custom Functions Using Names |
| | | Section 42. Create Ranges That Expand and Contract |
| | | Section 43. Nest Dynamic Ranges for Maximum Flexibility |
| | | Section 44. Identify Named Ranges on a Worksheet |
| | Chapter 4. Hacking PivotTables |
| | | Hacks #46-49 |
| | | Section 45. PivotTables: A Hack in Themselves |
| | | Section 46. Share PivotTables but Not Their Data |
| | | Section 47. Automate PivotTable Creation |
| | | Section 48. Move PivotTable Grand Totals |
| | | Section 49. Efficiently Pivot Another Workbook's Data |
| | Chapter 5. Charting Hacks |
| | | Hacks #50-59 |
| | | Section 50. Explode a Single Slice from a Pie Chart |
| | | Section 51. Create Two Sets of Slices in One Pie Chart |
| | | Section 52. Create Charts That Adjust to Data |
| | | Section 53. Interact with Your Charts Using Custom Controls |
| | | Section 54. Three Quick Ways to Update Your Charts |
| | | Section 55. Hack Together a Simple Thermometer Chart |
| | | Section 56. Create a Column Chart with Variable Widths and Heights |
| | | Section 57. Create a Speedometer Chart |
| | | Section 58. Link Chart Text Elements to a Cell |
| | | Section 59. Hack Chart Data So That Blank Cells Are Not Plotted |
| | Chapter 6. Hacking Formulas and Functions |
| | | Hacks #60-80 |
| | | Section 60. Add Descriptive Text to Your Formulas |
| | | Section 61. Move Relative Formulas Without Changing References |
| | | Section 62. Compare Two Excel Ranges |
| | | Section 63. Fill All Blank Cells in a List |
| | | Section 64. Make Your Formulas Increment by Rows When You Copy Across Columns |
| | | Section 65. Convert Dates to Excel Formatted Dates |
| | | Section 66. Sum or Counting Cells While Avoiding Error Values |
| | | Section 67. Reduce the Impact of Volatile Functions on Recalculation |
| | | Section 68. Count Only One Instance of Each Entry in a List |
| | | Section 69. Sum Every Second, Third, or nth Row or Cell |
| | | Section 70. Find the nth Occurrence of a Value |
| | | Section 71. Make the Excel Subtotal Function Dynamic |
| | | Section 72. Add Date Extensions |
| | | Section 73. Convert Numbers with the Negative Sign on the Right to Excel Numbers |
| | | Section 74. Display Negative Time Values |
| | | Section 75. Use the VLOOKUP Function Across Multiple Tables |
| | | Section 76. Show Total Time as Days, Hours, and Minutes |
| | | Section 77. Determine the Number of Specified Days in Any Month |
| | | Section 78. Construct Mega-Formulas |
| | | Section 79. Hack Mega-Formulas that Reference Other Workbooks |
| | | Section 80. Hack One of Excel's Database Functions to Take the Place of Many Functions |
| | Chapter 7. Macro Hacks |
| | | Hacks #81-94 |
| | | Section 81. Speed Up Code While Halting Screen Flicker |
| | | Section 82. Run a Macro at a Set Time |
| | | Section 83. Use CodeName to Reference Sheets in Excel Workbooks |
| | | Section 84. Connect Buttons to Macros Easily |
| | | Section 85. Create a Workbook Splash Screen |
| | | Section 86. Display a "Please Wait" Message |
| | | Section 87. Have a Cell Ticked or Unticked upon Selection |
| | | Section 88. Count or Sum Cells That Have a Specified Fill Color |
| | | Section 89. Add the Microsoft Excel Calendar Control to Any Excel Workbook |
| | | Section 90. Password-Protect and Unprotect All Excel Worksheets in One Fell Swoop |
| | | Section 91. Retrieve a Workbook's Name and Path |
| | | Section 92. Get Around Excel's Three-Criteria Limit for Conditional Formatting |
| | | Section 93. Run Procedures on Protected Worksheets |
| | | Section 94. Distribute Macros |
| | Chapter 8. Connecting Excel to the World |
| | | Hacks #95-100 |
| | | Section 95. Load an XML Document into Excel |
| | | Section 96. Save to SpreadsheetML and Extracting Data |
| | | Section 97. Create Spreadsheets using SpreadsheetML |
| | | Section 98. Import Data Directly into Excel |
| | | Section 99. Access SOAP Web Services from Excel |
| | | Section 100. Create Excel Spreadsheets Using Other Environments |
|
| | Glossary |
| | Colophon |