1. Chapter 1 Reducing Workbook and Worksheet Frustration

   1. Hacks #1-15

   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. Tie Custom Toolbars to a Particular Workbook

   13. Outsmart Excel's Relative Reference Handler

   14. Remove Phantom Workbook Links

   15. Reduce Workbook Bloat

   16. Extract Data from a Corrupt Workbook

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

   1. Hacks #16-38

   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 3D 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. Force Data Validation to Reference a List on Another Worksheet

   12. Use Replace... to Remove Unwanted Characters

   13. Convert Text Numbers to Real Numbers

   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

3. Chapter 3 Naming Hacks

   1. Hacks #39-44

   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 #46-49

   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 #50-59

   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. Three 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 Blank Cells Are Not Plotted

6. Chapter 6 Hacking Formulas and Functions

   1. Hacks #60-80

   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 Counting 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

7. Chapter 7 Macro Hacks

   1. Hacks #81-94

   2. Speed Up Code While Halting Screen Flicker

   3. Run a Macro at a Set Time

   4. Use CodeName 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

8. Chapter 8 Connecting Excel to the World

   1. Hacks #95-100

   2. Load an XML Document into Excel

   3. Save to SpreadsheetML and Extracting Data

   4. Create Spreadsheets using SpreadsheetML

   5. Import Data Directly into Excel

   6. Access SOAP Web Services from Excel

   7. Create Excel Spreadsheets Using Other Environments

9. Chapter 9 Glossary

1. Colophon

Our look is the result of reader comments, our own experimentation, and feedback from distribution channels. Distinctive covers complement our distinctive approach to technical topics, breathing personality and life into potentially dry subjects. The tool on the cover of Excel Hacks is a trowel. The trowel shown is the type that is generally used in gardening tasks such as removing stones from dirt meant for planting, removing weeds, and digging up and/or planting the plants themselves. Mary Brady was the production editor and proofreader for Excel Hacks. Audrey Doyle was the copyeditor. Philip Dangler and Claire Cloutier provided quality control. Lucie Haskins wrote the index.

Hanna Dyer designed the cover of this book, based on a series design by Edie Freedman. The cover image is from the Stockbyte Work Tools CD. Emma Colby produced the cover layout with QuarkXPress 4.1 using Adobe's Helvetica Neue and ITC Garamond fonts.

David Futato designed the interior layout. This book was converted by Julie Hawks to FrameMaker 5.5.6 with a format conversion tool created by Erik Ray, Jason McIntosh, Neil Walls, and Mike Sierra that uses Perl and XML technologies. The text font is Linotype Birka; the heading font is Adobe Helvetica Neue Condensed; and the code font is LucasFont's TheSans Mono Condensed. The illustrations that appear in the book were produced by Robert Romano and Jessamyn Read using Macromedia FreeHand 9 and Adobe Photoshop 6. This colophon was written by Mary Brady.