Books & Videos

Table of Contents

  1. Chapter 1 Reducing Workbook and Worksheet Frustration

    1. Hacks 1–16

    2. Create a Personal View of Your Workbooks

    3. Enter Data into Multiple Worksheets Simultaneously

    4. Prevent Users from Performing Certain Actions

    5. Prevent Seemingly Unnecessary Prompts

    6. Hide Worksheets So That They Cannot Be Unhidden

    7. Customize the Templates Dialog and Default Workbook

    8. Create an Index of Sheets in Your Workbook

    9. Limit the Scrolling Range of Your Worksheet

    10. Lock and Protect Cells Containing Formulas

    11. Find Duplicate Data Using Conditional Formatting

    12. Find Data That Appears Two or More Times Using Conditional Formatting

    13. Tie Custom Toolbars to a Particular Workbook

    14. Outsmart Excel's Relative Reference Handler

    15. Remove Phantom Workbook Links

    16. Reduce Workbook Bloat

    17. Extract Data from a Corrupt Workbook

  2. Chapter 2 Hacking Excel's Built-in Features

    1. Hacks 17–43

    2. Validate Data Based on a List on Another Worksheet

    3. Control Conditional Formatting with Checkboxes

    4. Identify Formulas with Conditional Formatting

    5. Count or Sum Cells That Meet Conditional Formatting Criteria

    6. Highlight Every Other Row or Column

    7. Create 3-D Effects in Tables or Cells

    8. Turn Conditional Formatting and Data Validation On and Off with a Checkbox

    9. Support Multiple Lists in a ComboBox

    10. Create Validation Lists That Change Based on a Selection from Another List

    11. Use Replace… to Remove Unwanted Characters

    12. Convert Text Numbers to Real Numbers

    13. Extract the Numeric Portion of a Cell Entry

    14. Customize Cell Comments

    15. Sort by More Than Three Columns

    16. Random Sorting

    17. Manipulate Data with the Advanced Filter

    18. Create Custom Number Formats

    19. Add More Levels of Undo to Excel for Windows

    20. Create Custom Lists

    21. Boldface Excel Subtotals

    22. Convert Excel Formulas and Functions to Values

    23. Automatically Add Data to a Validation List

    24. Hack Excel's Date and Time Features

    25. Enable Grouping and Outlining on a Protected Worksheet

    26. Prevent Blanks/Missing Fields in a Table

    27. Provide Decreasing Data Validation Lists

    28. Add a Custom List to the Fill Handle

  3. Chapter 3 Naming Hacks

    1. Hacks 44–49

    2. Address Data by Name

    3. Use the Same Name for Ranges on Different Worksheets

    4. Create Custom Functions Using Names

    5. Create Ranges That Expand and Contract

    6. Nest Dynamic Ranges for Maximum Flexibility

    7. Identify Named Ranges on a Worksheet

  4. Chapter 4 Hacking PivotTables

    1. Hacks 50–54

    2. PivotTables: A Hack in Themselves

    3. Share PivotTables but Not Their Data

    4. Automate PivotTable Creation

    5. Move PivotTable Grand Totals

    6. Efficiently Pivot Another Workbook's Data

  5. Chapter 5 Charting Hacks

    1. Hacks 55–72

    2. Explode a Single Slice from a Pie Chart

    3. Create Two Sets of Slices in One Pie Chart

    4. Create Charts That Adjust to Data

    5. Interact with Your Charts Using Custom Controls

    6. Four Quick Ways to Update Your Charts

    7. Hack Together a Simple Thermometer Chart

    8. Create a Column Chart with Variable Widths and Heights

    9. Create a Speedometer Chart

    10. Link Chart Text Elements to a Cell

    11. Hack Chart Data So That Empty or FALSE Formula Cells Are Not Plotted

    12. Add a Directional Arrow to the End of a Line Series

    13. Place an Arrow on the End of a Horizontal (X) Axis

    14. Correct Narrow Columns When Using Dates

    15. Position Axis Labels

    16. Tornado Chart

    17. Gauge Chart

    18. Conditional Highlighting Axis Labels

    19. Create Totals on a Stacked Column Chart

  6. Chapter 6 Hacking Formulas and Functions

    1. Hacks 73–105

    2. Add Descriptive Text to Your Formulas

    3. Move Relative Formulas Without Changing References

    4. Compare Two Excel Ranges

    5. Fill All Blank Cells in a List

    6. Make Your Formulas Increment by Rows When You Copy Across Columns

    7. Convert Dates to Excel Formatted Dates

    8. Sum or Count Cells While Avoiding Error Values

    9. Reduce the Impact of Volatile Functions on Recalculation

    10. Count Only One Instance of Each Entry in a List

    11. Sum Every Second, Third, or Nth Row or Cell

    12. Find the Nth Occurrence of a Value

    13. Make the Excel Subtotal Function Dynamic

    14. Add Date Extensions

    15. Convert Numbers with the Negative Sign on the Right to Excel Numbers

    16. Display Negative Time Values

    17. Use the VLOOKUP Function Across Multiple Tables

    18. Show Total Time As Days, Hours, and Minutes

    19. Determine the Number of Specified Days in Any Month

    20. Construct Mega-Formulas

    21. Hack Mega-Formulas that Reference Other Workbooks

    22. Hack One of Excel's Database Functions to Take the Place of Many Functions

    23. Extract Specified Words from a Text String

    24. Count Words in a Cell or Range of Cells

    25. Return a Worksheet Name to a Cell

    26. Sum Cells with Multiple Criteria

    27. Count Cells with Multiple Criteria

    28. Calculate a Sliding Tax Scale

    29. Add/Subtract Months from a Date

    30. Find the Last Day of Any Given Month

    31. Calculate a Person's Age

    32. Return the Weekday of a Date

    33. Evaluate a Text Equation

    34. Lookup from Within a Cell

  7. Chapter 7 Macro Hacks

    1. Hacks 106–134

    2. Speed Up Code While Halting Screen Flicker

    3. Run a Macro at a Set Time

    4. Use CodeNames to Reference Sheets in Excel Workbooks

    5. Connect Buttons to Macros Easily

    6. Create a Workbook Splash Screen

    7. Display a "Please Wait" Message

    8. Have a Cell Ticked or Unticked upon Selection

    9. Count or Sum Cells That Have a Specified Fill Color

    10. Add the Microsoft Excel Calendar Control to Any Excel Workbook

    11. Password-Protect and Unprotect All Excel Worksheets in One Fell Swoop

    12. Retrieve a Workbook's Name and Path

    13. Get Around Excel's Three-Criteria Limit for Conditional Formatting

    14. Run Procedures on Protected Worksheets

    15. Distribute Macros

    16. Delete Rows Based on a Condition

    17. Track and Report Changes in Excel

    18. Automatically Add Date/Time to a Cell upon Entry

    19. Create a List of Workbook Hyperlinks

    20. Advanced Find

    21. Find a Number Between Two Numbers

    22. Convert Formula References from Relative to Absolute

    23. Name a Workbook with the Text in a Cell

    24. Hide and Restore Toolbars in Excel

    25. Sort Worksheets

    26. Password-Protect a Worksheet from Viewing

    27. Change Text to Upper- or Proper Case

    28. Force Text to Upper- or Proper Case

    29. Prevent Case Sensitivity in VBA Code

    30. Display AutoFilter Criteria

  8. Chapter 8 Cross-Application Hacks

    1. Hacks 135–138

    2. Import Data from Access 2007 into Excel 2007

    3. Retrieve Data from Closed Workbooks

    4. Automate Word from Excel

    5. Automate Outlook from Excel

  1. Colophon