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
