Overview

This book presents quick and creative techniques for improving Excel spreadsheets and applying some of Excel’s less known, but often useful, features. The techniques in this book require intermediate to advanced knowledge of Excel.

Description

Excel Hacks is a 6" by 9" softcover book with approximately 300 pages. It is comprised of eight chapters, a glossary, an index, and a short preface. The table of contents lists the descriptive titles of all 100 hacks. The index is comprehensive. The O’Reilly Web site maintains a list of errata for the book. Two minor errors are listed.

Contents

“Chapter 1, Reducing Workbook and Worksheet Frustration” contains 15 hacks that cover topics such as workbook display, security, dead links, and data corruption. “Chapter 2, Hacking Excel’s Built-In Features” contains 23 hacks that cover data validation, conditional formatting, multiple lists, sorting, cell comments, and date and time features. “Chapter 3, Naming Hacks” contains six hacks related to naming cells and ranges. “Chapter 4, Hacking Pivot Tables” contains five hacks related to pivot tables (cross-tabulations on steroids). “Chapter 5, Charting Hacks” contains ten hacks. “Chapter 6, Hacking Formulas and Functions” contains 21 hacks. “Chapter 7, Macro Hacks” contains 14 hacks. “Chapter 8, Connecting Excel to the World” contains six hacks. Unfortunately, five of the hacks are not applicable to Macintosh versions of Excel.

Utility

Reviewing a book of tips requires a different approach than reviewing a book that functions more like a manual. I decided to assess the usability and value of the tips by selecting one tip from each chapter (except Chapter 8) and seeing if I could accomplish the same task using information provided in Excel’s manual and help files. A book of hacks that contains no more information than help files would have no value.

Hack 8 describes two ways to limit the scrolling range of a worksheet. Both methods work well, and the authors cover all possible situations where limiting the scrolling range can cause problems. Solutions to avoid or work around those problems are provided. The book provides far more information than the Excel and Visual Basic Editor help files.

Hack 20 describes how to generate alternating row colors using conditional formatting. The basic technique works well; however, the advanced technique (no highlighting of blank rows) does not work with Microsoft Excel X for Mac (Service Release 1), due to a Visual Basic bug. It works with Excel 2000 for Windows. This hack can be replicated (with difficulty) using information in Excel’s help files, but you must figure out how to apply the modulus formula.

Hack 41 describes how to create and name a custom function. The basic hack is easy, and Excel’s help file shows how to do it. The advanced hack uses the intersect method to create lookup functions. This impressive technique is not easy to duplicate using only Excel’s help file.

Hack 47 tells how to automate PivotTable creation. The basic technique uses the PivotTable Wizard (which is usable with minimal instructions). The advanced hack uses a macro to create a PivotTable from any database or list column. This feat cannot be replicated with information from the Visual Basic Editor’s help file.

Hack 55 shows how to make a “thermometer” chart. This hack cannot be replicated using only information from the help files.

Hack 70 tells how to find the “nth” occurrence of a value in row or column. Again, this nifty and useful hack cannot be replicated using only information from the help files.

Hack 93 describes how to run procedures on protected worksheets. The book described how to use Visual Basic code to allow macros to function even when worksheets are password protected to prevent other users from altering them. This hack can be replicated by a skilled VBA user by assembling information available from the Visual Basic Editor’s help file.

Conclusion

Excel Hacks contains many useful and interesting tips geared toward experienced Excel users. I found it easy to use, and I appreciated the clear explanations that accompanied the hack descriptions. Most of the hacks provide information not readily available from help files. However, I find that books comprised solely of list of tips are not as useful as manuals with explanations and examples, which is why my rating is Very Good instead of Excellent.