| |
| |
| Credits | |
| |
| |
| Preface | |
| |
| |
| |
| Reducing Workbook and Worksheet Frustration | |
| |
| |
| |
| Create a Personal View of Your Workbooks | |
| |
| |
| |
| Enter Data into Multiple Worksheets Simultaneously | |
| |
| |
| |
| Prevent Users from Performing Certain Actions | |
| |
| |
| |
| Prevent Seemingly Unnecessary Prompts | |
| |
| |
| |
| Hide Worksheets So That They Cannot Be Unhidden | |
| |
| |
| |
| Customize the Templates Dialog and Default Workbook | |
| |
| |
| |
| Create an Index of Sheets in Your Workbook | |
| |
| |
| |
| Limit the Scrolling Range of Your Worksheet | |
| |
| |
| |
| Lock and Protect Cells Containing Formulas | |
| |
| |
| |
| Find Duplicate Data Using Conditional Formatting | |
| |
| |
| |
| Find Data That Appears Two or More Times Using Conditional Formatting | |
| |
| |
| |
| Tie Custom Toolbars to a Particular Workbook | |
| |
| |
| |
| Outsmart Excel's Relative Reference Handler | |
| |
| |
| |
| Remove Phantom Workbook Links | |
| |
| |
| |
| Reduce Workbook Bloat | |
| |
| |
| |
| Extract Data from a Corrupt Workbook | |
| |
| |
| |
| Hacking Excel's Built-in Features | |
| |
| |
| |
| Validate Data Based on a List on Another Worksheet | |
| |
| |
| |
| Control Conditional Formatting with Checkboxes | |
| |
| |
| |
| Identify Formulas with Conditional Formatting | |
| |
| |
| |
| Count or Sum Cells That Meet Conditional Formatting Criteria | |
| |
| |
| |
| Highlight Every Other Row or Column | |
| |
| |
| |
| Create 3-D Effects in Tables or Cells | |
| |
| |
| |
| Turn Conditional Formatting and Data Validation On and Off with a Checkbox | |
| |
| |
| |
| Support Multiple Lists in a ComboBox | |
| |
| |
| |
| Create Validation Lists That Change Based on a Selection from Another List | |
| |
| |
| |
| Use Replace...to Remove Unwanted Characters | |
| |
| |
| |
| Convert Text Numbers to Real Numbers | |
| |
| |
| |
| Extract the Numeric Portion of a Cell Entry | |
| |
| |
| |
| Customize Cell Comments | |
| |
| |
| |
| Sort by More Than Three Columns | |
| |
| |
| |
| Random Sorting | |
| |
| |
| |
| Manipulate Data with the Advanced Filter | |
| |
| |
| |
| Create Custom Number Formats | |
| |
| |
| |
| Add More Levels of Undo to Excel for Windows | |
| |
| |
| |
| Create Custom Lists | |
| |
| |
| |
| Boldface Excel Subtotals | |
| |
| |
| |
| Convert Excel Formulas and Functions to Values | |
| |
| |
| |
| Automatically Add Data to a Validation List | |
| |
| |
| |
| Hack Excel's Date and Time Features | |
| |
| |
| |
| Enable Grouping and Outlining on a Protected Worksheet | |
| |
| |
| |
| Prevent Blanks/Missing Fields in a Table | |
| |
| |
| |
| Provide Decreasing Data Validation Lists | |
| |
| |
| |
| Add a Custom List to the Fill Handle | |
| |
| |
| |
| Naming Hacks | |
| |
| |
| |
| Address Data by Name | |
| |
| |
| |
| Use the Same Name for Ranges on Different Worksheets | |
| |
| |
| |
| Create Custom Functions Using Names | |
| |
| |
| |
| Create Ranges That Expand and Contract | |
| |
| |
| |
| Nest Dynamic Ranges for Maximum Flexibility | |
| |
| |
| |
| Identify Named Ranges on a Worksheet | |
| |
| |
| |
| Hacking PivotTables | |
| |
| |
| |
| PivotTables: A Hack in Themselves | |
| |
| |
| |
| Share PivotTables but Not Their Data | |
| |
| |
| |
| Automate PivotTable Creation | |
| |
| |
| |
| Move PivotTable Grand Totals | |
| |
| |
| |
| Efficiently Pivot Another Workbook's Data | |
| |
| |
| |
| Charting Hacks | |
| |
| |
| |
| Explode a Single Slice from a Pie Chart | |
| |
| |
| |
| Create Two Sets of Slices in One Pie Chart | |
| |
| |
| |
| Create Charts That Adjust to Data | |
| |
| |
| |
| Interact with Your Charts Using Custom Controls | |
| |
| |
| |
| Four Quick Ways to Update Your Charts | |
| |
| |
| |
| Hack Together a Simple Thermometer Chart | |
| |
| |
| |
| Create a Column Chart with Variable Widths and Heights | |
| |
| |
| |
| Create a Speedometer Chart | |
| |
| |
| |
| Link Chart Text Elements to a Cell | |
| |
| |
| |
| Hack Chart Data So That Empty or False Formula Cells Are Not Plotted | |
| |
| |
| |
| Add a Directional Arrow to the End of a Line Series | |
| |
| |
| |
| Place an Arrow on the End of a Horizontal (X) Axis | |
| |
| |
| |
| Correct Narrow Columns When Using Dates | |
| |
| |
| |
| Position Axis Labels | |
| |
| |
| |
| Tornado Chart | |
| |
| |
| |
| Gauge Chart | |
| |
| |
| |
| Conditional Highlighting Axis Labels | |
| |
| |
| |
| Create Totals on a Stacked Column Chart | |
| |
| |
| |
| Hacking Formulas and Functions | |
| |
| |
| |
| Add Descriptive Text to Your Formulas | |
| |
| |
| |
| Move Relative Formulas Without Changing References | |
| |
| |
| |
| Compare Two Excel Ranges | |
| |
| |
| |
| Fill All Blank Cells in a List | |
| |
| |
| |
| Make Your Formulas Increment by Rows When You Copy Across Columns | |
| |
| |
| |
| Convert Dates to Excel Formatted Dates | |
| |
| |
| |
| Sum or Count Cells While Avoiding Error Values | |
| |
| |
| |
| Reduce the Impact of Volatile Functions on Recalculation | |
| |
| |
| |
| Count Only One Instance of Each Entry in a List | |
| |
| |
| |
| Sum Every Second, Third, or Nth Row or Cell | |
| |
| |
| |
| Find the Nth Occurrence of a Value | |
| |
| |
| |
| Make the Excel Subtotal Function Dynamic | |
| |
| |
| |
| Add Date Extensions | |
| |
| |
| |
| Convert Numbers with the Negative Sign on the Right to Excel Numbers | |
| |
| |
| |
| Display Negative Time Values | |
| |
| |
| |
| Use the VLOOKUP Function Across MultipIe Tables | |
| |
| |
| |
| Show Total Time As Days, Hours, and Minutes | |
| |
| |
| |
| Determine the Number of Specified Days in Any Month | |
| |
| |
| |
| Construct Mega-Formulas | |
| |
| |
| |
| Hack Mega-Formulas that Reference Other Workbooks | |
| |
| |
| |
| Hack One of Excel's Database Functions to Take the Place of Many Functions | |
| |
| |
| |
| Extract Specified Words from a Text String | |
| |
| |
| |
| Count Words in a Cell or Range of Cells | |
| |
| |
| |
| Return a Worksheet Name to a Cell | |
| |
| |
| |
| Sum Cells with Multiple Criteria | |
| |
| |
| |
| Count Cells with Multiple Criteria | |
| |
| |
| |
| Calculate a Sliding Tax Scale | |
| |
| |
| |
| Add/Subtract Months from a Date | |
| |
| |
| |
| Find the Last Day of Any Given Month | |
| |
| |
| |
| Calculate a Person's Age | |
| |
| |
| |
| Return the Weekday of a Date | |
| |
| |
| |
| Evaluate a Text Equation | |
| |
| |
| |
| Lookup from Within a Cell | |
| |
| |
| |
| Macro Hacks | |
| |
| |
| |
| Speed Up Code While Halting Screen Flicker | |
| |
| |
| |
| Run a Macro at a Set Time | |
| |
| |
| |
| Use CodeNames to Reference Sheets in Excel Workbooks | |
| |
| |
| |
| Connect Buttons to Macros Easily | |
| |
| |
| |
| Create a Workbook Splash Screen | |
| |
| |
| |
| Display a "Please Wait" Message | |
| |
| |
| |
| Have a Cell Ticked or Unticked upon Selection | |
| |
| |
| |
| Count or Sum Cells That Have a Specified Fill Color | |
| |
| |
| |
| Add the Microsoft Excel Calendar Control to Any Excel Workbook | |
| |
| |
| |
| Password-Protect and Unprotect All Excel Worksheets in One Fell Swoop | |
| |
| |
| |
| Retrieve a Workbook's Name and Path | |
| |
| |
| |
| Get Around Excel's Three-Criteria Limit for Conditional Formatting | |
| |
| |
| |
| Run Procedures on Protected Worksheets | |
| |
| |
| |
| Distribute Macros | |
| |
| |
| |
| Delete Rows Based on a Condition | |
| |
| |
| |
| Track and Report Changes in Excel | |
| |
| |
| |
| Automatically Add Date/Time to a Cell upon Entry | |
| |
| |
| |
| Create a List of Workbook Hyperlinks | |
| |
| |
| |
| Advanced Find | |
| |
| |
| |
| Find a Number Between Two Numbers | |
| |
| |
| |
| Convert Formula References from Relative to Absolute | |
| |
| |
| |
| Name a Workbook with the Text in a Cell | |
| |
| |
| |
| Hide and Restore Toolbars in Excel | |
| |
| |
| |
| Sort Worksheets | |
| |
| |
| |
| Password-Protect a Worksheet from Viewing | |
| |
| |
| |
| Change Text to Upper- or Proper Case | |
| |
| |
| |
| Force Text to Upper- or Proper Case | |
| |
| |
| |
| Prevent Case Sensitivity in VBA Code | |
| |
| |
| |
| Display AutoFilter Criteria | |
| |
| |
| |
| Cross-Application Hacks | |
| |
| |
| |
| Import Data from Access 2007 into Excel 2007 | |
| |
| |
| |
| Retrieve Data from Closed Workbooks | |
| |
| |
| |
| Automate Word from Excel | |
| |
| |
| |
| Automate Outlook from Excel | |
| |
| |
| Index | |