Excel Hacks

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 R