October 2003 was a good month for me. I was well into a relationship that eventually resulted in an accepted marriage proposal, I’d just finished the first draft of one long book, I had seen my Excel Pocket Guide (O’Reilly) hit the store shelves, and I was fresh and ready to tackle another project. That’s when I got a call from Robert Luhn, who had recently come on board as an executive editor at O’Reilly. He asked if I’d like to put together a book proposal for O’Reilly’s retooled Annoyances series. We kicked around a few ideas and eventually came up with Excel Annoyances, the book you now hold in your hands. I liked the road paved by Steve Bass in PC Annoyances, and I liked the notion of reinventing it for Excel—so I did the proposal dance, bade my legal representatives to work out a contract, and sat down for some serious writing.
Here’s why: because practically everyone uses Excel. The installed base is huge, and it stretches across multiple versions of the program. In fact, many people prefer the earlier versions, or can’t afford to upgrade to the latest and greatest. That’s why we decided to cover every major version from Excel 97 to Excel 2003—which casts a pretty wide net.
Good thing, too, because the river of Excel annoyances runs deep and treacherous, populated by numerous species of sharp-toothed predators ready to chew up your data. How deep and treacherous? When we queried user groups across this fair land, we got 150 emails in a single week! Emails from newbies and Excel masters, homemakers and NASA engineers, all at their wits’ end because of some Excel “feature,” bug, quirk, flaw, or just-plain-dumb design decision. My job, in consonance with the fine folks at O’Reilly, was to figure out what bugged people the most, determine how to fix the problems, and also point out useful utilities, web sites, and other resources that might make their lives easier.
There’s a lot of accumulated Excel angst out there, and with each new Excel version comes more sturm and more drang. But when it comes to managing your business (or your Little League team) with Excel, you don’t have time for nail biting. You need solutions now. The faster you can navigate around the roadblocks, the faster you can make good decisions, earn that bonus, get promoted to partner, retire with full salary, and never use Excel again. To help you reach that pinnacle, dig into this tome—solutions to some of your most nettlesome Excel problems await within.
Has Excel refused to sort columns? Replaced a word without your permission? Changed a long column of dates into numbers like 000.2222335? Declined to modify a chart label? Laughed at your efforts to troubleshoot a troublesome macro? And are you just plain sick of seeing #### signs? If you answered yes to any of these questions, you need this book!
The hard part wasn’t finding annoyances; the hard part was figuring which ones to cover and which ones to cut, for lack of space. Well, we obviously haven’t covered every possible Excel annoyance, but take heart—future editions of this book will continue to explore the many twisted avenues of Excel. If you have a pet peeve not covered in Excel Annoyances, feel free to write us at annoyances@oreilly.com and tell us what’s on your mind. We’ll consider these annoyances for the next edition of this book.
This book is divided into chapters, with each chapter covering a different aspect of Excel: data entry, formulas, formatting, exchanging data, printing, charts, macros/VBA, and the newest member of the Excel family, Excel 2003. You should probably skim through the book at least once, getting an overview of the available solutions, and then return to the pages with fixes that will help you right away.
Tip
Don’t worry about typing in the long macro, VBA, and HTML code samples you’ll find in this book. They’re available for download at http://www.oreilly.com/catalog/excelannoyances/downloads.csp.
The following typographic conventions are used in this book:
Italic is used for filenames, URLs, email addresses, and emphasis.
Constant width
is used for commands, keywords, and items that should be typed verbatim.
Constant width italic is used for text that should be replaced with user-supplied values.
Menu sequences are separated by arrows, such as Data → List → Create List. Tabs, radio buttons, checkboxes, and the like are identified by name, such as “click the Options tab and check the ‘Always show full menus’ box.”
This book is here to help you get your job done. In general, you may use the code in this book in your programs and documentation. You do not need to contact us for permission unless you’re reporducing a significant portion of the code. For example, writing a program that uses several chunks of code from this book does not require permission. Selling or distributing a CD-ROM of examples from O’Reilly books does require permission. Answering a question by citing this book and quoting examples code does not require permission. Incorporating a significant amount of example code from this book into your product’s documentation does require permission.
We appreciate, but do not require, attribution. An attribution usually includes the title, author, publisher, and ISBN, for example: "Excel Annoyances by Curtis Frye. Copyright 2005 O’Reilly Media, Inc., 0-596-00728-0.”
If you feel your use of code examples falls outside fair use or the permission given here, feel free to contact us at permissions@oreilly.com.
Curtis Frye started messing around with a Texas Instruments computer way back when floppy disk drives cost hundreds of dollars. (Frugal lad that he was, he saved his programs on a cassette tape.) He went on to play with an Apple IIe in high school and helped users with Mac and DOS word processors at the Academic Computing Services center at Syracuse University. He didn’t really start pounding on computers until he went to work for The MITRE Corporation in McLean, Virginia, and used Excel as a project management tool. Part of the deal was simultaneously going to grad school at George Mason University and studying information systems, technology transfer strategies, operations research, export control regimes, C and Pascal programming, database design, and trans-border data flows. After five academic departments and 13 classes, he didn’t have an M.A., an M.S., or an M.F.A.—but he knew a bunch of neat stuff, and had a lot of fun in the process.
After five years of trying to make a go of working in an office, Curt left the Washington D.C. area in 1995 and ended up in Portland, Oregon, where he became a full-time writer. Of course, being an entry-level writer with exactly one magazine article under your belt means you had better have a day job. Curt worked as an actor, an improvisational comedian (and he still does, eight years and more than 800 shows later), theatrical carpenter, lighting technician, and lighting designer. After two years of writing articles for small publications that paid (sometimes), Curt got his big break when he answered an email on the Computer Book Publishing email list from a guy named Tim. Tim asked about books on genetic algorithms, an area Curt happened to know something about. Curt replied with a way-too-long email detailing the existing popular literature, what was covered, and what wasn’t, and speculated on a few new angles to pursue. The Tim, of course, was Tim O’Reilly, who immediately signed up Curt to help write a market research report on web commerce.
Fourteen books later (plus a passel of online courses, and a few articles) Curt is an established author, with credits including such titles as Microsoft Office Excel 2003 Step by Step, Excel Pocket Guide, Microsoft Office Excel 2003 Programming Inside Out (as lead author), Master Visually Microsoft Access 2000, and Privacy-Enhanced Business (the master’s thesis he never wrote). It’s a good life, and he’s glad there’s room in the world for someone who works from dusk to dawn, not the other way around.
Ken Bluttman wrote the first draft of Chapter 6. He has been crafting custom Office solutions for more than a decade and, appropriately enough, is the author of Developing Microsoft Office Solutions. He has consulted for dozens of leading companies in finance, insurance, energy, and health care. He also develops Oracle and SQL Server database applications, XML applications, and a variety of web sites. Ken lives in New York with his wife, son, and dog.
Laurie Ulrich Fuller Chapter 7. She has been working with computers since the early 1980s and has been teaching people how to use them since 1990. In the last decade, she’s written hundreds of training manuals and taught thousands of students at classes conducted at client sites, at various corporate training centers, and at Temple University. In the early 1990s, she created her own firm, Limehat & Company, Inc., providing training, consulting, and technical documentation for growing businesses and nonprofit organizations. In the last 10 years, the company has moved into web design and hosting services. Laurie has also authored, co-authored, and contributed to more than 20 computer books for several major publishers, including How to Do Everything with Office XP, Photoshop Elements 2 Bible, and Troubleshooting Microsoft Excel 2002.
Michael Oliver-Goodwin, the project editor, is a widely published writer and an experienced editor. He has helped edit magazines about computers (PC World, MacWeek, InfoWorld); written about computers and technology (for Fortune, Good Housekeeping, Publish, Multimedia World, The Web, and The San Jose Mercury News); authored books about computers; edited books about computers; and contributed to books about computers. Outside the technical field his book credits include On the Edge (an unauthorized biography of filmmaker Francis Coppola, published by William Morrow) and several cookbooks. He sold a screenplay to Ghostbusters director Ivan Reitman (which was never shot), his script for Burden of Dreams helped filmmaker Les Blank win a British Academy Award for Best Feature-Length Documentary, he’s produced several calypso CDs for Rounder Records, and he’s taught magazine feature writing at the Journalism department at San Francisco State University. Michael used every bit of his considerable skills to keep me on the straight and narrow path to good writing, which is more than a full-time job. I appreciate his patience, his good humor, and the many valuable contributions he’s made to the book’s content, tone, and overall flow.
Jeff Webb, the technical editor, has written about computers and technology for 20 years. His books include Using Excel Visual Basic for Applications and Microsoft Visual Basic Developer’s Workshop. He also has written programming guides, articles, and sample applications for Microsoft and Digital Equipment Corporation, and he pitched in on some of the advanced material in this book on databases and VBA programming. Jeff is a terrific programmer, with an instinctive sense of what works and what doesn’t in Excel. If you have to use VBA in Excel 2003, look for his Excel 2003 Programming: A Developer’s Notebook from O’Reilly. Thanks, Jeff, for keeping me on my toes and suggesting such great fixes and improvements to my text and (especially) to my code.
Robert Luhn is the executive editor of O’Reilly’s consumer group. He’s been editing articles and books about spreadsheets since the days of VisiCalc, Microsoft Multiplan, VP-Planner, and 1-2-3. When he’s not writing macros for fun in his editing retreat high atop Mt. Zugspitze, he raises “yeast cultures, crocuses, and small, unattractive animals.” Robert arrived at O’Reilly in July 2003 and has been a joy to work with, first on the Excel Pocket Guide, and later, on this book. Some editors are like theatre directors, asking questions and inspiring the actor/writer to embark on a voyage of discovery. Robert can take that approach, but he’s not afraid to give direct notes on what’s working, what isn’t, and how to fix what needs fixing.
Finally, please take the time to read the Colophon at the back of the book and tip your hat to the designers, copyeditors, layout artists, production editors, illustrators, and myriad other conscientious and hard-working professionals who made this paper-based block party we call Excel Annoyances happen. Publishing a book is about as difficult as launching a kindergarten into earth orbit, but these folks pulled it off brilliantly.
It always comes back to family, doesn’t it? I’m no exception. My parents, David Frye and Jane Fulgham, have always been there for me whether I needed a hand up or a good kick in the tail. My twin brother Doug and I have spent the last 10 years taking turns sending one another money when one of us had work and the other didn’t. Doug just finished his doctorate in something really boring at George Mason University and got a job within a week, which means I can expect to be paid back within the year. Right, big guy?
My fiancée, Virginia Belt, is a retired ballerina and a working actor, director, choreographer, and university instructor. She is a wonderful addition to my life, has made me the happiest man in the world, and is looking over my left shoulder as I write this. Have I missed anything, honey? No? Good.
I’m represented in the computer book industry by Neil Salkind of Studio B. Neil has more jobs than my fiancée, and that’s saying a lot. Even so, he still finds the time to get me work, negotiate my deals, develop business for Studio B, and teach psychology at the University of Kansas. He specializes in child psychology, which probably explains why he’s so good with writers. David and Sherry Rogelberg run Studio B with a steady hand, and Stacey Barone and Jackie Coder help me out with my contracts and royalties. Studio B gave me a shot back in 1998 and I’ve never doubted my decision to go with them.
Finally, I’d like to thank Pat Short and Ruth Jenkins, the founders of ComedySportz Portland, for welcoming me into the group back in 1996 and giving me the chance (actually, several chances) to work through the baggage I brought with me from D.C. and to help people laugh in the process. Everyone in CSz is an important part of my family—you two most of all.
Get Excel Annoyances now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
