Frye: Excel Annoyances—Taming the Wild Spreadsheet

EXTREMELY USEFUL

I freely admit a secret foible: I love Excel. Curtis Frye (author of an Excel Pocket Guide I have found essential) has produced another gem for the Excel user: Excel Annoyances, How to Fix the Most ANNOYING Things About Your Favorite Spreadsheet. Frye claims that the "the river of Excel annoyances runs deep and treacherous," and after years of battling its currents, I agree. This book has what you need to stay on course.

The book is organized into chapters based on what you need to do with Excel: enter data, format, write formulas, manipulate your data, chart, print and exchange data, and write or use macros. A special section details the annoyances specific to Excel 2003, and an extensive index supplements the table of contents. The book is aimed at all levels of user, from the very fresh newbie (how to use the series and fill tools, for example) to the sophisticated user (taming VBA code).

Each annoyance is succinctly presented in a vignette, followed by the solution (with lots of illustrations not shown in my review):

The Annoyance: I filtered a data list..., but when I try to copy the filtered rows and paste them into another worksheet, Excel pastes the hidden rows as well. I can tell I've selected more than the visible cells because as I select the cells, the Name box tells me I've selected an area that's 74 rows by 5 columns. How do I copy and paste just the visible rows in my data list?
The Fix: To copy just the visible cells to the clipboard, you need to add the Select Visible Cells button to your toolbar. To add this button, choose Tools —> Customize, select the Commands Tab, Select Edit from the Categories list, scroll down in the righthand Commands list until you find the Select Visible Cells button, and drag the button to any toolbar. Then, to copy and paste just the visible cells, follow these steps:

1. Filter the data list and highlight the visible cells.
2. Click the Select Visible Cells toolbar button.
3. Select Edit —> Copy.
4. Choose Edit —> Paste.

I love the way Frye goes the extra step, helping the user make the program more useful. Even when I have already solved the particular annoyance he cites, the solutions ring true.

Data Entry Annoyances: My "wow" moment in this section came from a sidebar note about how to use the sometimes-annoying AutoCorrect feature to insert chunks of customized "boilerplate" text. Since I hate to type, I'll definitely be using this hitherto-unnoticed feature.

Format Annoyances: I haven't used Conditional Format much, in part because of the completely non-intuitive way Excel calls out colors from its palette. I once spent the better part of a morning trying (and failing) to copy a custom palette from one worksheet to another. Frye sets out the solution in three simple steps.

Formula Annoyances: This entire section is wonderful (I love the power of Excel formulas), so it's hard to pick a single best. The tips on rounding hours to the next quarter-hour or tenth-hour and dropping your own VBA procedure into a formula were most ingenious.

Data Manipulation Annoyances: Hands-down, the best tip for me in this section is the solution to the "what-if" analysis annoyance. Frye guides us through creating separate "scenario" values using Excel's Scenario Manager (a feature I had been unaware of until I read this book).

Note: This section also covers Pivot Tables in some detail. I bought two Excel How-To Books last year to help me learn to use pivot tables, and neither gave me the same superlative guidance as these 14 pages of section 4.

Chart Annoyances: I'm not one who uses Excel's chart function frequently, but one tip really hit the memory buzzer. I once had a Powerpoint presentation that included several Excel charts in it. Sometimes they would print, but sometimes they wouldn't. Frye told me—in a single sentence—why that happens.

Exchanging Data Annoyances: At last! A step-by-step guide to moving data from Excel to Access. (To a help-desk tech, this page alone will be worth the cost of this book.) In addition, a tip on publishing worksheets on a web page details one of the idiosyncrasies of interactive Excel, and how to solve it in the HTML source view.

Funniest Annoyance Vignette: (about workbook permissions) "Ok, you've seriously scared me. Isn't there any way to be sure my workbook data doesn't leak out? I mean, I've got J.Lo's phone number in there."

Printing Annoyances: I am a strong believer in commenting any customized effort, and spreadsheets are no exception. Problem is, Excel makes it extremely difficult to print comments with your worksheet. Frye's instructions let you print such comments in context. (In a strong second place is the tip on how to suppress printout of those annoying "#VALUE" cell errors.)

Customization, Macro and VBA Annoyances: This whole section is gold waiting to be mined by the expert Excel user. The cutest tip is the one that shows you how to edit the default smiley-face for custom Toolbar buttons. There are lots of targeted macros and VBA procedures, plus references to online sources for helpful third-party utilities.

Excel 2003 Annoyances: The best tip here is in the section intro: why starting PhotoShop after Excel 2003 is running will sometimes crash your computer. The section also covers how not to be driven crazy by the Extensible Markup Language (XML).

As if all these solutions were not enough, Frye includes dozens of links to free creative Excel goodies. My favorite: a downloadable spreadsheet Tetris game. Frye notes, "If the United States' leading economic indicators drop suddenly after Excel Annoyances hits the store shelves, blame it on this sidebar."
0596007280,0596005342


If I had any complaint, it would be the choice of red to indicate cell commands, formulas and other Excel text. It was probably done to limit the ink to two colors, black and red. Nevertheless, the red text elements are difficult to read, which means they are easy to misread.