OzGrid.comMicrosoft Excel Templates, Training and Add-ins, Business Software and more!

Excel Book. Excel Hacks By D & R Hawley of OzGrid.com

Table of Contents

Back to: Working examples | Buy Excel Hacks | Book Description



By D & R Hawley of Ozgrid.com

100 Excel Hacks for every day Excel problems! Aimed at the intermediate+ power user.

As large number of hacks in this book require working examples, we have included these in the hope that they will help owners of Excel Hacks  save time by copying  working examples directly into their own Excel Workbooks.   

Should you have any question at all about anything in the book, please post your question to our Free Excel Forum in the Excel/VBA category. You must first register here, only an email, username and password is required. Once registered, you may wish to download the free Excel HTML Maker Add-in to better describe your problem.

You may also wish to browse our list of Frequently Asked Questions on Excel. For many more free Excel Workbook Downloads, please go to Free Excel Downloads 1, Free Excel Downloads 2Free Excel Downloads 3

Table of Contents

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



Microsoft Excel Add-ins Financial Software
Microsoft Excel Training & Tutoring Conversion Software
Microsoft Excel Templates Construction and Estimating Software
Excel, Word, Access Password Recovery Financial Calculators
Corrupt Excel, Word, Access File Recovery Business Planning
Time & Project Management Software Excel on the WWW
Windows & Internet Software Database Software
Barcode, Fonts, ActiveX, labels and DLL's OUR CURRENT SOFTWARE SPECIALS!
Charting Software The Analysis Add-ins Collection