Excel Best Practices
for Business
Loren Abdulezer
Loren’s book provides a way for companies to quickly and efficiently use
Assistive Technology to make basic spreadsheets accessible. His approach is direct
and easy to follow, hands-on, and practical. His innovation, Assistive Portals,
opens the door to making graphical interfaces embedded in decision analysis
spreadsheets readily accessible to individuals with visual impairments. While
making technology accessible is a difficult challenge for the business world, this
book brings it well within reach. More importantly, making something accessible
doesn’t require watering it down. We think these ideas warrant serious attention
and represent a milestone in leveling the playing field for individuals with
disabilities.”
Crista Earl, Director of Web Operations/American Foundation for the Blind
“XML and spreadsheets are not usually thought of in the same breath. Loren’s
concepts and implementation of Spreadsheet Portals using the IBM WebSphere
tools combines these two in an elegant and natural manner, and makes best use
of what these technologies have to offer. It’s not everyday that you get a clear
and lucid explanation of best practices, new ideas and wealth of information all
wrapped up into a single book. In a fast paced world where on demand business
decisions are being made in Internet time, Excel Best Practice for Business is a
“must-read” for today’s busy professionals working with quantitative information.”
Tom Inman
Vice President, WebSphere Foundation & Tools
IBM Software Group
URL: www.ibm.com/websphere
Excel Best Practices for Business
Excel Best Practices
for Business
Loren Abdulezer
Excel Best Practices for Business
Published by
Wiley Publishing, Inc.
10475 Crosspoint Boulevard
Indianapolis, IN 46256
www.wiley.com
Copyright © 2004 by Wiley Publishing, Inc., Indianapolis, Indiana
Published simultaneously in Canada
Library of Congress Control Number: 2003105683
ISBN: 0-7645-4120-X
Manufactured in the United States of America
10 9 8 7 6 5 4 3 2 1
1B/SZ/RQ/QT/IN
No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or by
any means, electronic, mechanical, photocopying, recording, scanning or otherwise, except as permitted under
Sections 107 or 108 of the 1976 United States Copyright Act, without either the prior written permission of
the Publisher, or authorization through payment of the appropriate per-copy fee to the Copyright Clearance
Center, 222 Rosewood Drive, Danvers, MA 01923, (978) 750-8400, fax (978) 646-8600. Requests to the Publisher
for permission should be addressed to the Legal Department, Wiley Publishing, Inc., 10475 Crosspoint Blvd.,
Indianapolis, IN 46256, (317) 572-3447, fax (317) 572-4447, E-Mail: permcoordinator@wiley.com.
is a trademark of Wiley Publishing, Inc.
LIMIT OF LIABILITY/DISCLAIMER OF WARRANTY: WHILE THE PUBLISHER AND AUTHOR HAVE
USED THEIR BEST EFFORTS IN PREPARING THIS BOOK, THEY MAKE NO REPRESENTATIONS OR
WARRANTIES WITH RESPECT TO THE ACCURACY OR COMPLETENESS OF THE CONTENTS OF THIS
BOOK AND SPECIFICALLY DISCLAIM ANY IMPLIED WARRANTIES OF MERCHANTABILITY OR
FITNESS FOR A PARTICULAR PURPOSE. NO WARRANTY MAY BE CREATED OR EXTENDED BY
SALES REPRESENTATIVES OR WRITTEN SALES MATERIALS. THE ADVICE AND STRATEGIES
CONTAINED HEREIN MAY NOT BE SUITABLE FOR YOUR SITUATION. YOU SHOULD CONSULT WITH
A PROFESSIONAL WHERE APPROPRIATE. NEITHER THE PUBLISHER NOR AUTHOR SHALL BE
LIABLE FOR ANY LOSS OF PROFIT OR ANY OTHER COMMERCIAL DAMAGES, INCLUDING BUT NOT
LIMITED TO SPECIAL, INCIDENTAL, CONSEQUENTIAL, OR OTHER DAMAGES.
For general information on our other products and services or to obtain technical support, please contact
our Customer Care Department within the U.S. at (800) 762-2974, outside the U.S. at (317) 572-3993 or fax
(317) 572-4002.
Wiley also publishes its books in a variety of electronic formats. Some content that appears in print may not
be available in electronic books.
Trademarks: Wiley, the Wiley publishing logo and related trade dress are trademarks or registered trademarks
of Wiley Publishing, Inc., in the United States and other countries, and may not be used without written
permission. [Insert any third-party trademarks.] All other trademarks are the property of their respective
owners. Wiley Publishing, Inc., is not associated with any product or vendor mentioned in this book.
About the Author
Loren Abdulezer (Brooklyn, NY) President of Evolving Technologies Corporation, is
an experienced IT professional who has worked with several Fortune 500 companies,
such as JP Morgan Chase, IBM, Procter & Gamble Pharmaceuticals, and Pfizer. Over
a five-year period at JP Morgan Chase, he has consulted on MIS/Web reporting, Java
and object-oriented programming, Internet security, and business continuity plan-
ning. He has played an integral, hands-on role in the implementation and deploy-
ment teams for a variety of strategic technologies for the bank including the first
wireless Internet application and a Public Key Infrastructure/Smart Card initiative.
Credits
ACQUISITIONS EDITOR
Greg Croy
PROJECT EDITOR
Susan Christophersen
TECHNICAL EDITOR
William Good
COPY EDITOR
Susan Christophersen
EDITORIAL MANAGER
Carol Sheehan
VICE PRESIDENT AND
EXECUTIVE GROUP PUBLISHER
Richard Swadley
VICE PRESIDENT AND PUBLISHER
Andy Cummings
EDITORIAL DIRECTOR
Mary Corder
PROJECT COORDINATOR
Kristie Rees
GRAPHICS AND PRODUCTION
SPECIALISTS
Beth Brooks
Carrie Foster
Joyce Haughey
LeAndra Hosier
Michael Kruzil
Kristin McMullan
Barry Offringa
Lynsey Osborn
Heather Pope
QUALITY CONTROL TECHNICIANS
Laura Albert
Carl William Pierce
Dwight Ramsey
Brian Walls
PERMISSIONS EDITOR
Carmen Krikorian
MEDIA DEVELOPMENT SPECIALIST
Greg Stafford
PROOFREADING AND INDEXING
TECHBOOKS Production Services
To my wife, Susan, for being my inspiration, best friend, and partner in life.
Preface
There’s a very large community of business professionals who regularly work with
spreadsheets. They are not spreadsheet experts and don’t claim to be. They don’t
have enough time; they’re too busy meeting deadlines. The budget implications
take precedence over the details of preparing a well-designed budget spreadsheet.
However, this “business before technology” attitude comes at a price. All too
often, business professionals are caught short of time and find themselves design-
ing spreadsheets inefficiently. Many business people have told me that they know
they are not preparing spreadsheets as well as they could and should be doing. They
work hard (perhaps too hard) to meet deadlines. The process feels like, and often is,
an exercise in “electronic pencil pushing.” Once done, they move on to the next
task at hand and promptly forget their work until exactly one month rolls by. Then
they repeat the whole process, inefficiently resorting to “one-offs.”
This state of affairs would not be so bad were it not for the fact that the current
business and economic climate demands greater efficiency. Furthermore, business
decisions must now be performed according to “Internet time.” Crucial decision-
making in a shortened time frame, coupled with the critical consequences of those
decisions, increases risk exposure to businesses and thereby the likelihood of fallout
for individual business managers. No one can afford to be wrong in today’s warp-
speed and closely watched business environment.
How do we meet these challenges? Corporate practices relating to spreadsheets
often amount to leaving people to their own devices to work their way through the
task, picking up what they can from books and colleagues, repeating what worked
the last time, and if necessary, force parts of their spreadsheet to work in order to
complete their spreadsheet.
My starting point for addressing spreadsheet practices and features consists of
what you need to be able to do in the day-to-day business setting. In the process of
explaining techniques and practices, I introduce spreadsheet features where they
are needed and have a clear purpose. I point out the “gotchas” and stubbornly
refuse to sweep details under the rug. Providing techniques and guidance for real
business situations is what this book is all about.
Because this is a book about techniques, you’ll find plenty of “Take-Aways” on
the accompanying CD that you can immediately put to use. The examples illus-
trated throughout Excel Best Practices for Business all incorporate Excel 2003.
Fortunately, best practices are largely independent of which version of Excel you
happen to be using. You need to be aware that Excel 2003 introduces many new
XML-related features not found in the earlier versions of Excel. These XML- and
Web-related features play an important role in Chapter 12, “Spreadsheet Portals,
XML, and Web Services.” To gain full benefit of these capabilities, you need to work
with Excel 2003.
From a stylistic standpoint, I favor providing industrial-strength spreadsheet
examples and try to present concepts from a mature business perspective. I want
xi
you to be able to pinpoint where and when specific techniques and practices come
into play. An added benefit of these full-featured spreadsheets is that they are rich
in “mini-techniques” that often are unrelated to the main theme or purpose of the
spreadsheet. My hope is that you can harvest these components and use them
within your spreadsheets. Finally, these full-featured spreadsheets can be turned
into production-quality spreadsheets. Whenever possible, I outline features that
you may want to incorporate to ready them for a production environment.
Now, you’d better hold on to your hat, because Excel is getting a second wind.
Fanning the sails is XML. Microsoft has decided to embrace XML and integrate it
heavily with Excel 2003 and Office 2003. This is a smart move on Microsoft’s part.
In the coming years, XML will be the ubiquitous medium for virtually all electronic
data exchange for business. This development will catapult Excel to center stage,
and Excel Best Practices for Business readies you for this by introducing the topic
of Spreadsheet Portals, among other things.
Also important is the need to make the contents of spreadsheets accessible to
individuals with disabilities. For federal agencies, making electronic information
accessible has been mandated by law under Section 508 of the Rehabilitation Act.
To address this need, a significant amount of the text (Chapter 13) is devoted to the
topic of Assistive Portals, which provide an elegant means to grapple with the seri-
ous challenges faced by users and preparers of accessible-friendly spreadsheets. To
serve as a hands-on example, the basics of setting up screen reader software are
included, along with simple, practical methods for making spreadsheets accessible.
Almost every chapter compiles information that you may not easily find else-
where. I have prepared much of this information in a form intended for easy read-
ing and reference. Also, you’ll find a cross-reference listing (Appendix C) of many
of the specific best-practice techniques that the book highlights.
Chapter 8, “Analyzing Data,” is a good deal more mathematical than the rest of
the book. A second track that is largely non-mathematical in nature is also pro-
vided, allowing you to obtain beneficial information and useful tools for data
analysis. The technical rigor in selected portions of the chapter is needed to place
the validity of certain topics on firm ground. In particular, the section on the quan-
tification of uncertainty involves a methodology borrowed from mathematics and
physics called “Addition in Quadrature.” This method is applied to financial analy-
sis and is fully integrated with spreadsheets. Financial analysts, MBAs, and actuar-
ies will need the mathematical rules that formally spell out this body of knowledge.
For this reason, I felt it necessary to include these topics, even though some of you
will find it reaching beyond your needs or interests.
Throughout this book, you may encounter unfamiliar topics. My goal has been
to provide enough initial knowledge to bring you to the doorstep of a discipline
that you may then feel encouraged to explore on your own.
I purposely pose questions and prod you to look at things from a new perspec-
tive and think outside the box. I am confident that as you make your way through
the techniques presented here, you will select the styles, methodologies, and prac-
tices that work best for you.
Loren Abdulezer
September 2003

Acknowledgments
Fashioning a roughly written manuscript into a polished document ready for prime
time is no small undertaking. It takes more than technical skills and a mechanized
process to produce a quality book. I am impressed with the clarity of thought and
insight to the big picture that the Editorial team at Wiley brought to the table. I am
also impressed with the care and dedication they bring to each and every published
title. If I didn’t know that Wiley is one of the major publishers in the industry, I
could easily be convinced that my book is the only one they’re publishing. Major
kudos to Greg Croy and Susan Christophersen for having done an outstanding job.
I feel fortunate to have gotten Bill Good to serve as Technical Editor/Reviewer.
Jason Marcuson helped me to crystallize some essential topics. I also want to thank
Andy Cummings and Bob Ipsen. It has been a marvelous experience working with
the Wiley team.
I owe special gratitude to my wife, Susan, for immediately seeing before anybody
else the value of this rather substantial undertaking, and for her constant encour-
agement and support in every way possible. This book would not be a reality with-
out her involvement.
All the people listed here in some way or another, large or small, have contributed
in a substantive way to Excel Best Practices for Business. In all cases, however, each
of you pushed me to expand my horizons and further address topics particularly
germane to this book. Thank you Barry Wexler, Bill Good, Crista Earl, David Wong,
Don Shea, Howard Dammond, Iris Torres, Jamie McCarron, Jason Molesworth, Jim
Meyer, Jim Parker, Jim Rees, Jim Shields, Joe Marino, John Picard, Joseph
Rubenfeld, Karen Gorman, Karen Luxton-Gourgey, Kevin Gordon, Larry Gardner,
Larry Litowitz, Lenny Vayner, Leslie Wollen, Luis Guerrero, Lynette Tatum,
Madalaine Pugliese, Marilyn Silver, Mary Ellen Oliverio, Michael Tobin, Mike Ciulla,
Mike Mazza, Mike Wu, Nancy and Bob Stern, Neila Green, Noah Ravitz, Peggy
Groce, Ralph Chonchol, Russ Logar, Stanley Sandler, Vis Hariharan, Vita Zavoli,
Yatin Thakore, and in memoriam, Harry Picard.
Special thanks go to the American Foundation for the Blind, the Computer Center
for Visually Impaired People at Baruch College, and the NYC Department of
Education/Educational Vision Services, for their assistance and feedback on the
chapter on Assistive Technologies. I also want to thank the team at Freedom
Scientific and in particular Eric Damery and Bill Kilroy for their technical assistance.
xiii
Contents at a Glance
Preface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xi
Acknowledgments . . . . . . . . . . . . . . . . . . . . . . . . . xiii
Part I
Best Practice Essentials
Chapter 1
A Foundation for Developing Best Practices . . . . . . 3
Chapter 2
Mastering Spreadsheet Construction Techniques . . 45
Chapter 3
Your Handy Reference for Manipulating Data . . . . 69
Chapter 4
Compiling, Managing, and Viewing Your Data . . . 99
Part II
Spreadsheet Ergonomics
Chapter 5
Scaling the Peaks of Mt. Data . . . . . . . . . . . . . . . 133
Chapter 6
Let the Data Speak for Itself: Viewing and
Presenting Data . . . . . . . . . . . . . . . . . . . . . . . . . 159
Chapter 7
Creating and Using Smart Data . . . . . . . . . . . . . . 185
Chapter 8
Analyzing Data
Chapter 9
How Not to Get Stuck in the MUD
(Messed-Up Data) . . . . . . . . . . . . . . . . . . . . . . . . 249
Part III
Special Topics: Getting the Numbers Right
Chapter 10
Going for the Facelift: Spreadsheet Makeovers . . . 273
Chapter 11
Spreadsheet Auditing: Challenging the Numbers
of Others . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 315
Chapter 12
Spreadsheet Portals, XML, and Web Services . . . . 335
Chapter 13
Assistive Technologies and Assistive Portals . . . . 375
Appendix A: Excel Configuration and Setup . . . . . 433
Appendix B: Information for Macintosh Users . . . 453
Appendix C: Excel Best Practice Techniques
and Hip Pocket Tips . . . . . . . . . . . . . 457
Appendix D: What’s on the CD-ROM . . . . . . . . . . 471
Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 479
xv
Contents
Preface. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xi
Acknowledgments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xiii
Part I
Best Practice Essentials
Chapter 1
A Foundation for Developing Best Practices . . . . . . . . 3
Preliminaries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
Working with Different Ways To Compute a Number
in Excel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
Understanding Alternative Ways to Represent Cells . . . . . . . 5
The traditional approach: The A1 style . . . . . . . . . . . . . . . . . . . 5
Does the R1C1 approach scale well? . . . . . . . . . . . . . . . . . . . . . 7
Understanding how these two approaches differ . . . . . . . . . . . . . 7
What do you give up by using the Row and Column notation? . . . 8
What do you gain by using the Row and Column
notation style? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
Is there any happy medium between these choices? . . . . . . . . . . 11
Even better: Using names instead of coordinates for
cell referencing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
Computing a Number in Excel . . . . . . . . . . . . . . . . . . . . . . . 12
Best Practice Topic: Evolving a strategy toward Absolute vs.
Relative vs. Hybrid cell references . . . . . . . . . . . . . . . . . . . . 15
More useful information about working with formulas . . . . . . . 19
Keyboard and cursor navigation tips . . . . . . . . . . . . . . . . . . . . 26
User-defined names within Excel spreadsheets . . . . . . . . . . . . . 29
Excel Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31
Programming without programming . . . . . . . . . . . . . . . . . . . . 32
Types of Excel formulas you will encounter . . . . . . . . . . . . . . . 32
Some important functions and how they’re used . . . . . . . . . . . . 33
Closing Thoughts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42
Chapter 2
Mastering Spreadsheet Construction Techniques
. . . 45
One Size Does Not Fit All . . . . . . . . . . . . . . . . . . . . . . . . . . 45
Understanding Simple Spreadsheets . . . . . . . . . . . . . . . . . . 46
Building a Spreadsheet: A Simple Example . . . . . . . . . . . . . 49
Some closing remarks on simple spreadsheets . . . . . . . . . . . . . . 61
Complex Spreadsheets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 62
Determining what makes a spreadsheet complex . . . . . . . . . . . . 62
Creating a “blueprint” for large or complex spreadsheets . . . . . . 64
Closing Thoughts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 66
xvii
Chapter 3
Your Handy Reference for Manipulating Data . . . . 69
Excel String Manipulation Functions You Need to Know . . . 69
Sorting Techniques . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 70
First Steps to Tidying Up Your Data . . . . . . . . . . . . . . . . . . . 70
The Sentinel LookAhead Technique . . . . . . . . . . . . . . . . . . . 72
Other Functions for Effective Data Manipulation . . . . . . . . 74
The & joining operator and CONCATENATE . . . . . . . . . . . . . . . 75
Some more functions for data manipulation . . . . . . . . . . . . . . . 77
Useful Sorting Techniques . . . . . . . . . . . . . . . . . . . . . . . . . . 79
Sorting with more than three columns (or rows) . . . . . . . . . . . . 80
Block-sorting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 83
Data Surgery and Data Manipulation . . . . . . . . . . . . . . . . . 86
A scenario . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 87
The traditional approach . . . . . . . . . . . . . . . . . . . . . . . . . . . . 88
The alternative approach . . . . . . . . . . . . . . . . . . . . . . . . . . . . 89
Anatomy of the SQL Generator Spreadsheet Tool . . . . . . . . . . . 90
Things you might do to enhance this tool . . . . . . . . . . . . . . . . 96
Closing Thoughts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 96
Chapter 4
Compiling, Managing, and Viewing Your Data . . . . . 99
Preliminaries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99
The Number Line-Up . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 100
Copying and pasting columnar data . . . . . . . . . . . . . . . . . . . 100
How does this tool work, and is it super-automated? . . . . . . . . 102
Putting Data into Perspective with PivotTables . . . . . . . . . 106
Enter the PivotTable . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 106
Data grouping . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 110
More folds in the data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 114
Charting and interpreting data . . . . . . . . . . . . . . . . . . . . . . . 115
Pivot formulas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 120
Pivot data drill-down . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 122
Preparing Your Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 122
Data redundancy . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 123
Data substitution . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 123
Dataset pre-consolidation tool . . . . . . . . . . . . . . . . . . . . . . . 124
Saving PivotTables as Web pages . . . . . . . . . . . . . . . . . . . . . 127
Closing Thoughts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 130
Part II
Spreadsheet Ergonomics
Chapter 5
Scaling the Peaks of Mt. Data . . . . . . . . . . . . . . . . . . . 133
The Art of Data Slogging . . . . . . . . . . . . . . . . . . . . . . . . . . 134
Integrating old and new data . . . . . . . . . . . . . . . . . . . . . . . . 135
Importing data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 136
Cleaning up the spreadsheet . . . . . . . . . . . . . . . . . . . . . . . . . 137

Climbing Past the Foothills . . . . . . . . . . . . . . . . . . . . . . . . 148
Search enable your source data . . . . . . . . . . . . . . . . . . . . . . 149
The Data Inspector . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 149
The Region Inspector . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 152
The Regional Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . 153
Sometimes a picture is worth a thousand formulas . . . . . . . . . 155
Closing Thoughts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 156
Chapter 6
Let the Data Speak for Itself: Viewing and
Presenting Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 159
They Threw In the Kitchen Sink . . . . . . . . . . . . . . . . . . . . . 160
Start by looking at your data . . . . . . . . . . . . . . . . . . . . . . . . 160
Edit how you want your data to appear . . . . . . . . . . . . . . . . . 161
The ReportSheet . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 165
Using Excel Filters . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 167
Additional features you need to know about filters . . . . . . . . . 171
Replicating content with filtered data . . . . . . . . . . . . . . . . . . 172
Presentation Tear Sheets . . . . . . . . . . . . . . . . . . . . . . . . . . 174
Advanced Filters . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 176
Advanced Filters setup . . . . . . . . . . . . . . . . . . . . . . . . . . . . 176
Multiple criteria for a single filter . . . . . . . . . . . . . . . . . . . . . 177
You can use formulas in your Advanced Filters . . . . . . . . . . . 178
Tips for Spreadsheet Comments . . . . . . . . . . . . . . . . . . . . . 180
Use the Comments Catalog . . . . . . . . . . . . . . . . . . . . . . . . . 180
Print settings . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 181
Good practices . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 181
Coloration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 182
Formatting comments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 183
Closing Thoughts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 183
Chapter 7
Creating and Using Smart Data . . . . . . . . . . . . . . . . . 185
What Is Smart Data, Anyhow? . . . . . . . . . . . . . . . . . . . . . 186
Smart Data used with anchor cells . . . . . . . . . . . . . . . . . . . . 186
Conditional Formatting . . . . . . . . . . . . . . . . . . . . . . . . . . . 188
Constructing a “digital dashboard” . . . . . . . . . . . . . . . . . . . . 188
Rules of the road . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 189
The Phantom Formatting technique and Four-Color tables . . . . 189
Smart formatting for overextended text . . . . . . . . . . . . . . . . . 192
From RAGs to Riches: An Interactive Array of Colors . . . . 193
Preparing your status report . . . . . . . . . . . . . . . . . . . . . . . . . 194
Having the best of both worlds . . . . . . . . . . . . . . . . . . . . . . . 195
Peeking under the hood . . . . . . . . . . . . . . . . . . . . . . . . . . . . 196
If you have more time (and the inclination) . . . . . . . . . . . . . . 198
Perimeter Surveillance: Smart Borders . . . . . . . . . . . . . . . . 198

Miscellaneous Topics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 200
Helper cells . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 200
Scalability issues . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 201
Closing Thoughts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 202
Chapter 8
Analyzing Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 203
Charting Your Course in a Sea of Data . . . . . . . . . . . . . . . 204
Seasonal data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 205
The Data Viewer tool . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 205
Stochastic and Markov processes . . . . . . . . . . . . . . . . . . . . . 209
Fourier Transforms and Fourier Analysis . . . . . . . . . . . . . . . . 210
Quantifying Uncertainty: Techniques and Rules . . . . . . . . 214
Quantifying uncertainty: The technique of Adding
in Quadrature
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 215
Adding in Quadrature: A real-world example . . . . . . . . . . . . . 217
Uncertainty rules . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 220
Data Sculpting: Crafting Data with the Excel Goal
Seek and Solver Tools . . . . . . . . . . . . . . . . . . . . . . . . . . . 229
The Goal Seek tool . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 230
Optimization and the art of data sculpting . . . . . . . . . . . . . . . 233
A last word on the Solver . . . . . . . . . . . . . . . . . . . . . . . . . . 245
Suggestions for Further Reading . . . . . . . . . . . . . . . . . . . . 245
Closing Thoughts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 246
Chapter 9
How Not to Get Stuck in the MUD
(Messed-Up Data) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 249
Ambiguous and Incomplete Data . . . . . . . . . . . . . . . . . . . . 250
An example scenario . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 250
Devise a plan . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 252
The lesson to be learned . . . . . . . . . . . . . . . . . . . . . . . . . . . 260
Inconsistent Data and Computations . . . . . . . . . . . . . . . . . 261
Data presented might be contradictory . . . . . . . . . . . . . . . . . . 261
What to do when you find an exception . . . . . . . . . . . . . . . . 263
Identify differences in almost identical data . . . . . . . . . . . . . . 263
Square Peg/Round Hole Scenario . . . . . . . . . . . . . . . . . . . . 266
Using a data overpass . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 267
Analyses/Reports Built on Too Much White Noise
or Static . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 269
Understanding “data mirages” . . . . . . . . . . . . . . . . . . . . . . . 269
Strategies for assessing whether you have a data mirage . . . . . 269
Closing Thoughts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 270

Part III
Special Topics: Getting the Numbers Right
Chapter 10
Going for the Facelift: Spreadsheet Makeovers . . . . 273
Spreadsheet Makeover Techniques . . . . . . . . . . . . . . . . . . . 274
Some preliminaries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 275
Review the existing spreadsheets while taking into account
complexities . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 276
First steps in the makeover . . . . . . . . . . . . . . . . . . . . . . . . . 277
A Hands-On Example of a Spreadsheet Makeover . . . . . . . 281
The scenario . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 281
Now for some of the wrinkles... . . . . . . . . . . . . . . . . . . . . . . 282
Review of the prior year’s budget . . . . . . . . . . . . . . . . . . . . . 282
Performing the actual makeover: Part One . . . . . . . . . . . . . . . 293
Performing the actual makeover: Part Two . . . . . . . . . . . . . . . 300
Alternative Approaches . . . . . . . . . . . . . . . . . . . . . . . . . . . 307
Further optimizations: Some things you could do if you
have the time or the need . . . . . . . . . . . . . . . . . . . . . . . . . 307
The alternative one-off approach . . . . . . . . . . . . . . . . . . . . . 310
Closing Thoughts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 312
Chapter 11
Spreadsheet Auditing: Challenging the Numbers
of Others . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 315
Structural Analysis of Spreadsheets . . . . . . . . . . . . . . . . . . 317
Test your skills . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 317
Some observations for beginning your assessment . . . . . . . . . 320
Formula evaluation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 323
A partial checklist for discerning mistakes . . . . . . . . . . . . . . . 324
Off-Spreadsheet Analysis . . . . . . . . . . . . . . . . . . . . . . . . . . 326
We hold these truths to be self-evident . . . . . . . . . . . . . . . . . . 327
The mind-reading game . . . . . . . . . . . . . . . . . . . . . . . . . . . 327
State Transition Analysis . . . . . . . . . . . . . . . . . . . . . . . . . . 328
Testing the Reasonableness of Spreadsheets . . . . . . . . . . . 332
Sometimes it’s a matter of semantics . . . . . . . . . . . . . . . . . . . 332
Closing Thoughts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 334
Chapter 12
Spreadsheet Portals, XML, and Web Services . . . . . 335
Spreadsheet Portals and Desktop Client Portals . . . . . . . . . 336
Simple Client Portals . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 337
Complex Spreadsheet Portals . . . . . . . . . . . . . . . . . . . . . . . 339
Interactive participation in an evolving survey . . . . . . . . . . . . 340
Use a Spreadsheet Portal to braid information . . . . . . . . . . . . 341

XML in Excel 2003 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 346
Step 1: Communicate with the server . . . . . . . . . . . . . . . . . . 347
Step 2: Tell the spreadsheet application how to structure
the received data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 348
Step 3: Interaction between Excel and the remote server . . . . . 354
Step 4: Interaction with the other parts of your spreadsheet
application . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 358
The XML “Staircase” Problem in Excel 2003
(and Other Things to Keep in Sight) . . . . . . . . . . . . . . . . 361
By the way . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 365
Web Services — with a Twist . . . . . . . . . . . . . . . . . . . . . . . 366
Accessing the UDDI Registry from your Spreadsheet . . . . . . . . 368
Closing Thoughts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 373
Chapter 13
Assistive Technologies and Assistive Portals . . . . . . 375
Intended audience and basic goals . . . . . . . . . . . . . . . . . . . . 376
Chapter organization . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 377
Background . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 378
Disabilities . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 378
Legislation you should know about . . . . . . . . . . . . . . . . . . . . 382
The Economics of Making Spreadsheets Accessible . . . . . . 383
The Assistive Portal approach . . . . . . . . . . . . . . . . . . . . . . . . 384
Setting Up a Screen Reader . . . . . . . . . . . . . . . . . . . . . . . . 385
Getting acclimated to a screen reader . . . . . . . . . . . . . . . . . . 386
Preliminary JAWS concepts: A training-wheel approach . . . . . 387
Basic JAWS configuration . . . . . . . . . . . . . . . . . . . . . . . . . . 390
Spreadsheets with Screen Readers . . . . . . . . . . . . . . . . . . 394
The design features of an accessible spreadsheet . . . . . . . . . . . 394
Spreadsheet Structure . . . . . . . . . . . . . . . . . . . . . . . . . . . . 400
Defining spreadsheet regions . . . . . . . . . . . . . . . . . . . . . . . . 400
Graphical Components . . . . . . . . . . . . . . . . . . . . . . . . . . . . 405
Road Map for Creating the Assistive Portal . . . . . . . . . . . . 407
Creating an accessible UserForm in a spreadsheet . . . . . . . . . . 408
Interface for a List Box and a button . . . . . . . . . . . . . . . . . . . 409
An Important Design Strategy: Remove Hardwired
Dependencies . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 411
Abstraction Layer approach to coding . . . . . . . . . . . . . . . . . . 411
Compound interfaces . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 412
Implementing the screen reader–accessible two-level List Box . . . 414
Assistive Portals . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 420
Basic ingredients needed for an Assistive Portal . . . . . . . . . . . 420
An Assistive Portal implementation . . . . . . . . . . . . . . . . . . . . 421
Time to shift into high gear . . . . . . . . . . . . . . . . . . . . . . . . . 428
Closing Thoughts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 429

Appendix A: Excel Configuration and Setup . . . . . 433
Appendix B: Information for Macintosh Users . . . 453
Appendix C: Excel Best Practice Techniques
and Hip Pocket Tips . . . . . . . . . . . . . 457
Appendix D: What’s on the CD-ROM . . . . . . . . . . 471
Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 479

Introduction
How to Use This Book
Although the cover lists this book as being intended is for intermediate to advanced
users of Excel, rest assured that even if you consider yourself a beginner, the book
can serve as a wonderful learning tool for you as well. You will probably want to
have some additional source of information at your disposal, however. Because this
book focuses on spreadsheet construction and best practices within the business
setting, there are bound to be gaps on basic spreadsheet concepts that a more sea-
soned Excel user could fill in without resorting to supplementary material.
Regardless of your level of Excel expertise, use this book to get you on the road
to practicing techniques that will regularly prove useful. Packed within it is valu-
able information that is otherwise hard to gather in one place. Just make sure you
have additional sources of information. There are plenty of books, including Excel
2003 Bible by John Walkenbach, Wiley Publications. You’ll also find many “Take-
Aways” and spreadsheet examples included in the book and on the CD-ROM. So,
even if you are not a seasoned Excel user, you can make good use of the material
provided.
For those of you who are in the intermediate to advanced range, you should take
what you already know and find ways to extend or improve it. To that end, I’ve
taken a three-pronged approach by providing the following:
◆ Tidbits, facts, and techniques that are helpful in promoting best practices.
◆ Ways to approach things differently from what might be conventional
wisdom or common practices.
◆ New material that has not generally appeared in published form. Starting
with Excel 2003, a whole host of new features have become available,
resulting in potential new uses of spreadsheet technology. You will see
some of these in action when you come across Part III of this book.
This book can be profitably read with and without a computer at hand. At some
point, I assume you are going to be working out examples on your computer.
It would be ideal if you could read the book from cover to cover. Of course, I
know this is not going to happen. I have tried to prepare the book so you could get
the most out of it regardless of how many chapters you read. In my mind, this
means there’s got to be something useful in every chapter. It also means that if you
go to one of the later chapters, you should not feel suddenly stranded. I like to
think I provided enough road signs and a clear enough map. To help you along I’ve
xxv
included in Appendix C a list of techniques covered in Excel Best Practices for
Business and where they can be found in the book.
Though you are free to lunge into any chapter or topic the moment you take the
book home, I would suggest that you thoroughly read Chapter 1 and Appendix A
first. The examples throughout the book use the settings and conventions described
in the setup and foundation material.
I encourage you to innovate and develop your own practices and techniques.
What You Need to Use This Book
Excel 2003 and, for that matter, Office 2003, for the first time introduces a compre-
hensive suite of features related to XML not found in the earlier versions of Excel.
To make use of XML, the Web-savvy capabilities, or Spreadsheet Portals, you
absolutely will need Excel 2003 and not an earlier version. Most of the online
examples detailed in Chapter 12 will not work with earlier versions of Excel.
Beyond these restrictions, essentially all other book examples will work perfectly
well with Excel 2002 (or Office XP). Thankfully, most of the practices and techniques
described in this book are not dependent on the specific version of Excel used. As
you progress to earlier versions of Excel, increasingly fewer of the examples will
work as presented. Table A-1 in Appendix A summarizes what general features work
with various versions.
What This Book Covers
Let me state flat out: This book is about spreadsheet techniques and best practices
in business. It is not meant to be an encyclopedic reference on Excel.
Concerning XML
In many regards, Excel 2003 is like its predecessors. In one regard there stands a
clear exception. Substantial XML support is entirely new to Excel 2003 and is
tightly integrated with the product. This new XML capability has generated a lot of
excitement and is a principal focus of Chapter 12 (Spreadsheet Portals, XML, and
Web Services).
Thankfully, there’s very little formal knowledge of XML that you need to know
in order to profitably use the XML features of Excel 2003. While such knowledge of
XML is “nice to have”, it is definitely NOT a “must have” prerequisite. If you are
familiar with HTML and can understand that XML is similar, except that XML
allows you to define your own vocabulary rather than restricting yourself to a lex-
icon of hardwired tags, then you’ll have enough of an understanding to put XML to
use in Excel 2003.

Concerning macros
It is not until Chapters 12 and 13 that Excel macros play any significant role. Other
than these last two chapters, there is no need to acquire knowledge of macros. In
presenting macros in Chapter 12, I introduce what you need to know about macros
as if you are learning it for the first time. Should you feel you need more extensive
information on macros and VBA, go to John Walkenbach’s Excel 2003 Power
Programming with VBA (Wiley Publishing, Inc.).
You should be aware that if you have your Excel security settings set to High, you
will not be able to run the macros. Setting the security level to Medium will allow
you to run macros but prompt you with a dialog box to ask your permission to use
them for the duration of your session. This is the setting that I recommend that you
use for the book.
Conventions Used in this Book
Listed below are the various conventions used in the book.
Spreadsheet functions and cells
The built-in Excel worksheet functions (such as SUM or RAND), as well as standard
Excel Add-In functions (such as RANDBETWEEN), all appear in UPPERCASE format.
User-defined names assigned to cell ranges appear in a mixed case (for example,
SomeValueDefinedForACell).
Using keystroke sequences and
menu command sequences
Isolated keystrokes are identified by the name as it appears on the keyboard: Alt, Ctrl,
and so on. Keystroke combinations are signified by a plus sign, as in Ctrl+Alt+Del
(the DOS reboot sequence).
Menu command sequences, such as clicking File to open that menu and then
clicking Save to save a document, are signified as follows: File → Save.
Macintosh users should consult Appendix B to better map actual experiences
with the book description.
Using the R1C1 convention
This book reintroduces the use of the R1C1 style for representing Excel formulas.
Excel spreadsheets and their formulas can be rendered using either the A1 Style or
R1C1 Style. Although you may be used to using the A1 Style, you will find some
compelling reasons, discussed in Chapter 1, for adopting the R1C1 Style. To give
you the benefit of easily switching back and forth, I have provided on the CD-ROM
a SwitchTool that will allow you to go back and forth between notation styles at

the click of a button. Additionally, where appropriate, named ranges and references
are used instead of cell coordinates, thus rendering the A1-vs.-R1C1 debate essen-
tially moot.
Icons Used in the Book
Following are descriptions of some visual cues used throughout this book to draw
your attention to specific issues.
Practical techniques to get in the habit of using regularly to promote effec-
tive and efficient spreadsheet preparation and maintenance.
Ideas or issues that require some special awareness or workaround.
When you see this icon, read carefully. Some actions you might be about to
take could be disastrous. Some things you may not know could hurt you. In
cases such as these, ignorance is definitely not bliss.
Occasionally you’ll see a reminder in this fashion to make use of tools or
examples that you’ll find on the CD-ROM accompanying this book.
How This Book Is Organized
This book is organized in three parts. Part One, “Best Practice Essentials,” discusses
the process of managing the Excel environment. The basic Excel worksheet func-
tions, along with explanations of their intended purpose and typical usage, are
explained, and basic spreadsheet construction practices for both simple and com-
plex spreadsheets are introduced.

Part Two, “Spreadsheet Ergonomics,” builds upon the background material
introduced in the first few chapters, with emphasis on best practice techniques. This
portion of the book arms the reader with potent techniques and methodologies for
doing anything and everything that has to do with their spreadsheet data.
The chapters in Part Three are all single topic chapters and can be read in any
order. The focus within these chapters is all on technique. They put to use and
extend all the material presented in Parts One and Two.
The last part of the book contains the appendixes. Appendix A presents infor-
mation concerning the suitability of various versions of Excel you might be using,
along with some setup guides. Of particular importance is the use of monospace
fonts. You’ll also find information and tools concerning the notation style of refer-
encing cells using row and column numbers (Chapter 1 describes this as well).
Appendix B contains information relevant to Excel users working with the
Macintosh platform.

Best Practice Essentials
CHAPTER 1
A Foundation for Developing Best Practices
CHAPTER 2
Mastering Spreadsheet Construction Techniques
CHAPTER 3
Your Handy Reference for Manipulating Data
CHAPTER 4
Compiling, Managing, and Viewing Your Data
Part I
Chapter 1
A Foundation for
Developing Best Practices
IN THIS CHAPTER
◆ Understanding alternative ways to represent cells (R1C1 notation style
compared to the traditional A1 style)
◆ Alternative approaches to computing numbers
◆ Getting a firm grounding on absolute, relative and hybrid cell references
and when to use each
◆ Specifying cell ranges and incorporating those within functions such
as SUM
◆ Understanding rapid and efficient navigation techniques in large
spreadsheets
◆ Defining user names (in rapid fire succession)
THIS CHAPTER IS INTENDED TO ARM you with the foundation for best practices including
basic components and spreadsheet functions you need to know. The chapter begins
with a discussion of important concepts and spreadsheet functions that you will be
working with regularly and presents the best way to use these. Included are the
gotchas, the tips, cautionary tales, and revelations that uncover new avenues for
developing your spreadsheets.
Some important and useful Excel functions are introduced, but focus is on their
usage rather than giving a formal treatment.
Preliminaries
This is a book about best practices. In my mind, the notion of best practices con-
jures up an image of working smartly and efficiently when using spreadsheets. To
be sure, there is no shortage of “esoterica” or sophistication in this book, but there
is plenty of time for that later. Sophisticated techniques that are effective and use-
ful are not generally built upon more esoteric concepts. Rather, they take hold most
easily when built on solid foundations.
3
Moreover, a surprising amount of sophistication comes from simple and basic
stuff. This will become evident as you explore the later chapters. If you’re curious,
thumb ahead and glance at the Chapter 7, “Creating and Using Smart Data,” to see
what I mean. You’ll better appreciate the things you can do, though, if you give me
a chance to mold the way you think about spreadsheets and their use. I therefore
focus on simple and basic spreadsheet concepts in the early chapters.
Many, if not all of the topics covered in the opening portions of this book may
already be well known to you. My purpose for presenting these “foundation” topics
is to get you to characterize and think about things a certain way. So, if you’re
already familiar with the topics, this should be an easy and quick read.
Indulge me. Read through the early chapters even if you know spreadsheets back-
wards and forwards. Getting into the deeper and more involved discussions, later in
the book, will occur more naturally if you do. I’m also willing to bet you’ll acquire
a thing or two in the early chapters that you’ll be happy to carry in your hip pocket.
Enough of the pep talk. Let’s get on with Excel best practices.
Working with Different Ways To
Compute a Number in Excel
Often, what people are taught about spreadsheets is the lowest common denomina-
tor, just enough to squeak by. These skills can be readily acquired through corpo-
rate and continuing education courses. Most people who regularly work with
spreadsheets tend to adopt, learn and share common practices — which are not
always synonymous with best practices. My goal in writing this book is to get you
to elevate the bar, to go beyond the common denominator so that your ability to
manipulate spreadsheets is on par with your natural abilities. Spreadsheets are
intimidating, so many of us are afraid to go beyond what others have taught us. I
would like to reconnect your good business instincts with your ability to handle
spreadsheets, and I have no doubt that you’ll be able to achieve and exceed your
expectations.
This chapter introduces you to the various ways you can put to use in your for-
mulas absolute, relative and hybrid expressions. You will also be shown the practi-
cal benefits of using one approach over another.
Also, you will find out that the built-in Excel functions are not only about per-
forming arithmetic and mathematical calculations. You will see that Excel introduces
specific functions of a spatial nature. Other Excel functions have a temporal nature
to them. As I go through this list, it should not surprise you that some Excel func-
tions perform computations specifying precision and fuzziness (and this has noth-
ing to do with “Fuzzy Math”). Did you think I was going to end there? Would it
interest you to know that there are Excel functions that can edit text? I’m not talking
about functionality, as in features of the Excel product; I am specifically talking
about Excel functions that utilize these features in their computations.

The point is, more computational facilities are at your disposal than just straight-
forward arithmetic. You should be thinking about Excel functions in this purposeful
and tantalizing manner, rather than as a boring laundry list of computing functions.
Understanding Alternative
Ways to Represent Cells
Spreadsheets organize information into rows and columns like the one contained
on your CD-ROM (open the sample spreadsheet, ch01-01.xls).
The intersection of a row and column is referred to as a spreadsheet cell. In
Figure 1-1, a cell has been selected on row 13 and column 2. A shorthand notation
for designating this cell position is R13C2. Excel understands this notation, so you
can use it directly in your formulas. Excel also understands a different way of
expressing formulas, which involves having columns that use letters instead of
numbers. This section explains that approach and discusses the implications and
benefits of both types of notation.
Excel provides two basic ways to display formulas appearing in spreadsheet
cells. One of them is referred to as an “A1” style and the other as “R1C1.” In much
the same way as a fashion statement, these styles affect only the outer appearance
of a spreadsheet. The underlying content remains unaffected no matter how often
you switch back and forth between the two modes (yes, you can switch back and
forth). What follows is information about these two approaches, what they offer,
and how they differ.
Appendix A discusses Excel Options settings that you can adjust, including
telling Excel whether you want your fashion garb to be R1C1 or A1. Actually,
the CD-ROM with this book contains a spreadsheet tool that will make
switching back and forth as often as you like very easy.
The traditional approach: The A1 style
Most people are already familiar with the A1 style of representing spreadsheet cells.
In this scheme, columns appear as letters and rows as numbers. Out of the box,
Excel is loaded with the default setting switched to A1.
There are some consequences associated with using columns as letters and rows
as numbers. When referring to a relative reference (say, two columns over to the
right and two rows down), you must specify fixed absolute position in space (the
actual column letter and row number). If you copy a cell and paste it to other loca-
tions on the spreadsheet, the formula, as it is written, changes. The exact formula is
dependent upon the cell you paste to. You’re giving yourself the burden of trans-
lating column letters and row numbers in your head.

Figure 1-1: A sample spreadsheet
Suppose you see two formulas. The one in cell DC91 is
=CR98+CX66
There’s another cell, EG62, which contains the formula:
=DV69+EA36
Now for the pop quiz. Giving these just a quick visual inspection, do you see them
as equivalent formulas (that is, could you have done a simple copy-and-paste to get
the second formula from the first)? I am willing to bet that most spreadsheet users
wouldn’t want to be caught thinking about such a question.
If you do give it a little thought, you will see that in the first formula, CR98 is
7 rows below DC91 as well as 11 columns to the left of DC91. In the second expression
of the formula, CX66 is 25 rows above DC91 and also 5 columns to the left of it.
In the second formula, DV69 is 7 rows below EG22, as well as 11 columns to the
left of EG62. So, the first expression lines up. The second expression, EA36, is 26 rows
above EG62 as well as 6 columns to the left of EG62.
Whew! That was a bit of mind twisting. Not only did I have to think about this
twice, I had the editors breaking their heads when proofing the cell references by
hand.
Oh, after all that, you do know whether the formulas were equivalent, don’t you?
If you instead used row and column number referencing (that is, the R1C1 style
in the Excel Options), you find would it immediately evident that these formulas are
not the same. In terms of row and column references, you would see that the first
formula is
=R[7]C[-11]+R[-25]C[-5]
and the second formula is
=R[7]C[-11]+R[-26]C[-6]

Although the first expression (R[7]C[-11]) matches perfectly, the second
expression doesn’t. Just looking at the formulas is all you need to do. There is no
mental translation.
In this example, using the R1C1 style, it was easy to see that the second expres-
sion in the formulas doesn’t line up in terms of the number of row offsets or col-
umn offsets.
Does the R1C1 approach scale well?
What if the formulas were more complex, having more than two expressions as
well as incorporating mathematical functions going well beyond the simple arith-
metic of addition and subtraction? The answer is that you would still be fine using
the R1C1 style of cell referencing.
Understanding how these two approaches differ
So, why is the alphabetic notation for columns potentially unwieldy when com-
pared to the numeric referencing of columns? There are several reasons:
◆
Imagine giving someone driving directions using east/west directions in
miles and north/south directions in kilometers. Do you think it’s a good
way to give directions? Why use numbers for rows and letters for columns?
Complicate this by the fact that rows are labeled in Base 10 notation and
columns in Base 26 (twenty-six letters in the alphabet). This situation
forces you to use two different numbering or labeling systems.
◆ Try taking any of your typical spreadsheets and swapping the rows for
columns and columns for rows. Compare the formulas between the two
spreadsheets. It may not be immediately evident which formulas corre-
spond to which. If Excel didn’t provide a “Transpose” capability when
copying and pasting cells, you could quickly get lost in comparing and
rewriting formulas.
◆
In the A1 style of cell referencing, relative references in formulas must
specify the actual position of a cell relative to the current cell being com-
puted. Does that sound convoluted enough? Other than the fact that you’re
just used to it; why would you want to use an absolute cell position for a
relative reference? Doing so only introduces an artificial artifact.
Take it one step further. When you copy and paste a cell involving rela-
tive references, each new formula, although similar in structure, is depen-
dent upon the location it is copied to whenever relative cell references are
involved in the formula. Wouldn’t it seem preferable and logical to have
the replicated formulas remain the same as the original formula they were
copied from?

What do you give up by using
the Row and Column notation?
Making the switch to using row and column numbers does come with a price, albeit
a small one. For example, the R1C1 style forces you to put an R in front of the row
number and a C in front of the column number. There are some other mental
adjustments I want you to think about:
◆
In the “matrix style” of rows and columns, cells are written with the row
followed by the column. The A1 style, by comparison, displays the col-
umn first as a letter. If you’re already used to the A1 style, there a definite
readjustment in thinking “row, column” rather than “column letter, row.”
◆ There is another adjustment, though I wouldn’t call it giving up something,
but rather an even trade. Absolute references in the A1 style are designated
by a $ symbol— for example, $B$23. In R1C1 parlance, this refers to row 23,
column 2, hence R23C2. As the example shows, in the R1C1 style you drop
the $ notation, because you don’t need it for absolute references. When a
relative reference is involved, you place brackets around an “offset” num-
ber. As an example, if you want to specify the cell to your immediate right,
you use RC[1]. It doesn’t matter which cell you are in. If you copy and
paste cells that use RC[1], the expression RC[1] remains unaltered in the
pasted cell regardless of where you paste it.
This way of referencing forces you to start thinking about the position of
spreadsheet cells visually. For example, think of R[3]C[1] as one cell over
to the right, three cells below.
◆ Excel allows you to compute the sum of a whole row or column. Using
the A1 style, if you want to take the sum of all cells in row 5, you write
SUM($5:$5) in absolute coordinates or SUM(5:5) in relative coordinates
for the cell you happen to be in. If you were to switch this to the R1C1
style, you would write SUM(R5) in absolute coordinates and SUM(R[2])
in relative coordinates if you happen to be anywhere in the third row.
Understandably, the usage of the R1C1 style for representing a whole
row using absolute coordinates can be confusing if you are used to the
A1 notation, because R5 looks like an A1 notation. Make no mistake
about it. R5 in the R1C1 style refers to all of row 5. How hard can that
be to get used to?
In summary, you’re not really giving up anything. You’re just making some
mental adjustments to think about things a little differently.
What do you gain by using the Row and
Column notation style?
Here’s what you gain by switching to the Row and Column notation:

◆
It buys you mental brevity. When you copy and paste formulas, the pasted
formulas remain unchanged regardless of where you paste them. This eases
the level of spreadsheet complexity. The one thing you don’t want to have
to be doing is thinking about many different versions of the same formula.
You have to think about only one formula; there’s no mental translation
involved.
You can quickly spot altered formulas, however slight the variations may
be. Just use your arrow keys on the keyboard to quickly pass over cells
that should be identical. If one of them is different, the formula will not
appear the same as the others.
◆ You begin to think about the relationship between cells visually. After all,
one of the major appeals of using a spreadsheet is to visually spread out
your numbers onto rows and columns, in much the same way as you
would think about laying out cards and arranging them on a table.
◆ You think more clearly about your formulas. Imagine that you are prepar-
ing a report that summarizes projected financial data for a ten-year period.
Different groups of data exist for each of the ten years. As an example,
open the file ch01-02formulacompare.xls (or just look at Figure 1-2). If
the categories are arranged in groups of ten columns (one column for
each of ten years), your formula could appear as:
=L7+V7+AF7+AP7+AZ7
when using the A1 Style. If instead, you choose to use the R1C1 Style (if
you have the spreadsheet open, try clicking the R1C1 Style button), the
formula becomes:
=RC[10]+RC[20]+RC[30]+RC[40]+RC[50]
Figure 1-2: Formulas using the “A1 style”

As you can see, each successive term in the formula points to a cell 10
columns further to the right (Figure 1-3). This formula is a lot easier to
understand and visualize than is the A1 style of the same formula.
This is the formula for R7C2. What about the formula for row 7, column 3?
The formula is
=RC[10]+RC[20]+RC[30]+RC[40]+RC[50]
Notice any similarity? Why not check the formula in the cell immediately
below R7C3? The formula is
=RC[10]+RC[20]+RC[30]+RC[40]+RC[50]
In fact, all the formulas that compute the aggregate sales for any prod-
uct during any given year are identical. They are also easy to read and
understand.
Figure 1-3: Formulas using the “R1C1 style”
Try pressing the A1 style button. Inspect the formulas in the different
cells. Do the formulas appear identical? Is it clear that you’re shifting over
in columns of 10 with each additional expression in the formula? Would
you know what to type in for the next expression (that is, what column
letter to use) if you were to add yet another category?
If you never used spreadsheets before in your life, and you looked at this
example, which approach do you think would make more sense and enable
you to better manage increasingly complex spreadsheets? What would the
kid who just graduated from college, and is not wedded to any particular
style, think? If he or she would pick the R1C1 style, would that gained
simplicity give that person a competitive edge?

The correct answer in any scenario is that you decide what’s best for you. All I
can do is lay the cards on the table and present options and ways to think about
these things.
Is there any happy medium
between these choices?
Yes, there is a happy medium. There is no reason for you to deny yourself the avail-
ability of both approaches. You have seen that you can switch between one mode
and the other at a press of a button (see Figures 1-2 and 1-3).
I’ve provided a SwitchTool spreadsheet (ch00-02switchtool.xls on the
CD-ROM) for your use. You can switch back and forth at the click of a
button.
You should be aware that Excel really doesn’t care which way you represent
cells. The underlying representation of cells has nothing to do with the way you
represent and label the data. So, you can switch the mode or context from the R1C1
to A1 (and vice versa) as often as you like, and save your spreadsheet files in either
mode.
Even better: Using names instead of
coordinates for cell referencing
To make matters better, there is yet a cleaner and more elegant approach to com-
plex spreadsheets that definitely promotes best practices. The approach is to define
descriptive names and use the names instead of cell references directly in your for-
mula. To see for yourself, try this: Which of the following three formulas would you
prefer to use to compute current assets?
=B$24+B$35+B$40+B$45+B$50
=R24C-R35C+R40C+R45C+R50C
=Cash+MktbleSecurities+Receivables+Inventory+PrepaidExpenses
Although an extra step is required in terms of defining named ranges, the pay-
off is definitely there. To begin with, your formulas incorporate meaningful words
rather than use cryptic cell references. The formula not only is readable to you and
third parties but also is equally well understood by the computer. Getting everyone
(the computer included) to be using the same referencing scheme is a definite plus.
As if this is not enough justification, you have the added benefit of getting the
computer to validate that you are using properly defined expressions. If you acci-
dentally misspell a word such as Inventory by spelling it, for example, Inventorx,

Excel will evaluate the cell to be #Name?. You’ll know right away that it’s wrong.
This wouldn’t happen if you had used a cell reference such as R39$C instead of
R40$C or R39C instead of R40C.
The use of named references does not involve any context mode switching, and
no Excel setting is required. It’s just there for you to use.
Using named references is definitely the way to go!
Computing a Number in Excel
Spreadsheet cells have a variety of properties. In terms of plain-vanilla spreadsheet
cells, one of three basic options is allowed for any spreadsheet cell:
◆ Option 1: The cell can be blank.
◆ Option 2: The cell can contain a fixed value. This fixed value can be a
number, a date, or some text label. If this value is fixed, the cell has no
formula. Also, the value of the cell will be visually displayed on the
spreadsheet.
◆ Option 3: A cell can possess a formula that is used to dynamically com-
pute a value that will be displayed in the cell. Formulas always start with
an equal (=) symbol.
Arbitrarily pick any cell, say row 14, column 1. If this cell holds the formula
=2+3
it maintains an internal formula but displays the value 5. There is a fundamental
distinction between fixed values (for example, 5) and equivalent formulas (such as
=2+3 or =5). In a formula, Excel will try to compute formulas and then display the
value rather than just simply retrieve the value. You will see that formulas can and
do get more complicated.
Excel formulas can use mathematical functions. For instance, you can obtain the
square root of 200 using the following formula:
=SQRT(200)
The value that Excel displays will be 14.14213562. Certainly, you can combine
mathematical expressions to build more complicated formulas. As an example, you
can add 10 to the square root of 200 with the following formula:
=SQRT(200)+10

The value displayed will be 24.14213562. Clearly, this is neither a very compli-
cated nor interesting formula. Probably, some of you are thinking “I’m not interested
in square roots. That’s what mathematicians use. I don’t have the time to get involved
with stuff like that.” Basically, you’re right. Throughout most of this book you will
hardly touch upon square roots and other similar mathematical constructs except
where it’s really appropriate. When you do, you’ll be supplied with all the informa-
tion you need.
In addition to conventional mathematical expressions, Excel formulas can incor-
porate values already computed in other spreadsheet cells. Here, things start to get
interesting. The real goal is to get you to understand the options available to you
when entering Excel formulas and the subtleties involved with cell references.
Cell references in Excel are generally not well understood. This is why people are
easily stymied when it comes to understanding and using formulas in Excel. It’s
worth the time to really nail down this topic. It will pay off in handsome dividends.
As you build formulas, you can refer to the contents of other spreadsheet cells.
You can do so by identifying which cell you want based on its specific row and col-
umn. This is known as an absolute cell reference.
You can also do so by identifying, in terms of the number of rows and columns,
how far away the cell is. This is known as a Relative cell reference.
You can also make use of a hybrid cell reference, in which the row is fixed but
the column offset is relative (or vice versa).
For an illustration of the contrast among these three types, look at Figure 1-4.
Figure 1-4: Absolute vs. relative vs. hybrid cell reference
usage in Excel formulas
Rows six through nine of column 2 show alternative ways to compute the value
of ten plus the square root of 200. They all produce the identical results, but each
one is computed in a slightly different manner. Column 3 displays the formulas
used for column 2 so that you can see what they look like; column 4 provides some
explanation.
The value of 10 is occupied in the cell R4C2. It is a plain value.
In R5C2, the square root of 200 is computed using this formula:
=SQRT(200)
The cell displays the value that results from the computation.

The number in row 6 is computed using the sum of pure absolute cell references
R5C2 and R4C2:
=R5C2+R4C2
Row 7 contains a slightly different kind of formula, which employs “relative”
references. The formula used is
=R[-2]C+R[-3]C
Basically, the formula in row 7 column 2 is saying: “Give me the value of the
cell two rows directly above me and in the same column (this happens to be
14.14213562) and add to this the value of the cell three rows directly above me and
in the same column as I am (which happens to be the value 10).”
The bracketed numbers immediately after the row reference indicates a “row off-
set.” So, R[-1]C would refer to the neighboring cell to the immediate north. By
comparison, R[1]C refers to the neighboring cell directly below or to the south. By
the same token, R[2]C refers to the cell two rows directly below (to the south).
The relative reference in row 7 column 2 could explicitly state the “column off-
set” of zero in addition to the row offsets. This would make the formula more ver-
bose than it needs to be. In this case, the formula would appear as
=R[-2]C[0]+R[-3]C[0]
The column offset happens to be zero, since the cells being referred to by the for-
mulas are in the same column as the formula for R7C2. Excel is happy to drop off
the bracketed zeros, because doing so will make formulas easier to read. Had the
formula been referring to a cell to the immediate left (west), the column offset
would be a negative number. Had the formula been referring to a cell to the imme-
diate right (east), the column offset would be a positive number.
Thus, the way to refer to a cell directly above and to the immediate right (that is,
northeast) would be referenced by R[-1]C[1]. Had there been a greater separation
between the cells, the offsets of rows and columns would have had to be increased
accordingly.
This way of thinking about cell references takes a little getting used to. When
you get comfortable using offsets, you’ll find yourself thinking about formulas
visually rather than in terms of abstract formulas.
At this point you might be asking, “Can I mix relative and absolute cell refer-
ences?” The answer is a resounding yes. When a formula is composed of a com-
pound formula involving multiple references, it uses the cell value and doesn’t care
how the cell was referenced.
As an example, take a look at the formula in row 8.
=R5C+R4C

Here you have such a mixture. The rows are listed in absolute references (notice
that the numbers 5 and 4 do not have brackets around them, so they must refer to
rows 5 and 4, respectively). The columns are relative references. In relative refer-
ences, you don’t have to show the brackets if your offsets are zero. Had you decided
to provide the column offsets using bracketed expressions, you would have:
=R5C[0]+R4C[0]
Another possible formula appears in row 9. In this case, the row references are
kept relative and the columns are absolute. It takes the form:
=R[-4]C2+R[-5]C2
Most of the time, you will be working with pure relative references. There are a
couple of reasons for this.
1. Excel will let you build spreadsheet formulas by “clicking” the cells you
want to reference. As you click cells, Excel inserts them into the edit line
of your formula as relative cell references. So unless you want to change
these references, the formulas you build will tend to be based on relative
references.
2. Relative cell references are mostly a good thing because they make repli-
cating formulas to other portions of your spreadsheet easy.
When entering and editing an Excel formula, you can switch selected por-
tions of your formula from relative to absolute to hybrid by pressing the
key.
Best Practice Topic: Evolving a strategy toward
Absolute vs. Relative vs. Hybrid cell references
Rather than just state a pre-established set of guidelines regarding the use of alter-
native types of cell references, I want to present some scenarios and have you for-
mulate your own judgments concerning the merits of the various choices among
absolute, relative and hybrid cell references.
Consider the following: You have a variety of items. For argument’s sake, let’s
say there are ten such items and you may be purchasing these items. Although each
of these has a listed cost, you are entitled to a 20% discount. The items are arranged
(see Figure 1-5) in a vertical column.

Figure 1-5: Preparing a schedule to compute
discounted amounts
A natural step in the cost analysis is to compute the discount amount for each of
the ten items. In the cells to the immediate right of each of the ten items, you can
write a formula in column 3 that looks like:
=RC[-1]*0.2
or
=RC[-1]*20%
or
RC[-1]*20/100
They’ll all work. However, they are hard-wired to 20%. This may be correct for
your discount percentage today. Next week you might need to adjust your discount
percentage. That would mean that you would have to rework your formulas every
time the discount changes. You don’t want to have to change this by hand, for two
good reasons:
1. In the first place, it is time consuming to hunt everywhere to change the
20% discount factor to something else.
2. Even if you have the time to make manual changes, chances are that as
the complexity and size of your spreadsheet increases, the likelihood of
missing a number you need to change, or making a mistake when hand
editing, increases.
So a better way to approach this is to reference the item cost using a relative ref-
erence, and reference the discount rate using an absolute cell reference. The dis-
count rate could be parked into a place like R6C3 (see Figure 1-6).

Where possible, try to use global references. Single global numbers in iso-
lated cells are easily identified and maintained. It will serve your purpose
even better if you assign a name to use in place of cell coordinates.
Then you could use a formula like:
Formula 1-1: First Attempt at Computing Discounted Amounts
=RC[-1]*R6C3
Figure 1-6: Combined relative and absolute references
The formula for computing discounted amounts is more easily maintainable. The
discount percentage is now:
◆ A single global number. (If you were to update/alter the number, every
reference to the discount percentage would be immediately updated.)
◆ The number is visible and accessible for easy updating.
This way has a decisive advantage. When the number has changed, you know it
has changed and you know what the new value is.
So far, so good. It’s time to throw in a little more complexity. The likelihood is
that you might need to evaluate the financial impact of more than one discount
rate. You have two strategies available:
1. Compute your values using the first rate (and if necessary, print it). Compute
your values again with the alternative rate. After all, the formulas are set
up so that the discount rate only appears in one (globally referenced) cell.
Changing it in this one location is easy. Spreadsheets are great for this
purpose. You can interactively try different values and see what their
impact would be. This is often referred to as a “what if?” scenario.

What I describe is a kind of manual approach to a what-if scenario. Excel
sports a special-purpose facility called a Scenario Manager. To get an idea
of what you can do with Scenario Manager, look at Figure 1-7.
Figure 1-7: Scenario Summary produced by the Excel Scenario Manager
There are too many caveats on the usage of this facility. My chief goal
right now is to keep you focused on the use of absolute, relative, and
hybrid cell references in Excel formulas.
2., The Scenario Summary, shown in Figure 1-7, is actually food for thought.
Instead of trying to experiment with different values for the discount per-
centage one at a time (which is what you would do when you generate
each scenario anyway), think about how you would write the formulas to
compute discount amounts for multiple discount rates placed side by side.
To make this accommodation for the second strategy, you need to make some
alterations to Formula 1-1. Think of doing a side-by-side comparison of discounted
amounts based on differing percentages. The one that already incorporates the 20%
baseline is a series of numbers running down column 3 from rows 7 through 16.
Imagine placing an alternate discount percentage on row 6 column 4 and having
the computed numbers running down column 4 from rows 7 through 16.
These sets of computations share two features:
1. The discounted amount resides on the same row as the item to be dis-
counted. Specifically, the item to be discounted resides in column 2.
You’ll want to think of the item to be discount in terms of its absolute
column reference.

2. The discounted amount resides on the same column as the discount per-
centage. Specifically, the discount percentage in this layout (see Figure
1-8) is always placed on row 6; this is an absolute.
The resulting formula to compute the discounted amount should be
Formula 1-2: Hybrid Formula
=RC2*R6C
Figure 1-8 shows how the spreadsheet with the revised formulas should appear.
Note that the formulas (appearing in column 4) used for computing the discounted
amount based on the alternate discount percentage are completely identical to the
formulas (appearing in column 3) based on the original discount percentage.
Additionally, if you want to do a side-by-side comparison with three or more dis-
count rates, you need only to keep putting your discount rates side by side across
row 6. Then replicate your formula (=RC2*R6C) across all your discounted amounts.
Figure 1-8: Correct use of replicating hybrid formulas
Clearly, if the spreadsheet you build is arranged differently than the one in this
chapter, the formulas will be different. However, the logic remains identical.
More useful information about
working with formulas
Shortly I discuss the functions SUM, SUMPRODUCT, and others. First, I explain some
details on entering formulas, changing cell references, and designating any range
of cell names of your choosing so that formulas can be easier to write, read, and
validate.

ENTERING FORMULAS
You may have noticed that the mathematical functions such as SQRT and cell refer-
ences have been appearing in uppercase format. This is no accident. Excel likes to
convert and store its worksheet functions and cell references that way. What hap-
pens if you enter them in lowercase? Go ahead and try it. Enter something like the
following set of formulas, each in separate rows, one below the other:
=now()
=row()
=row(r[1]c)
=sqrt(18)
=power(2,3)
=rand()
=sum(r[-5]c:r[-1]c)
=max(r[-6]c:r[-2]c)
=average(r[-7]c:r[-3]c)
=average(10,20,30,70,80,90)
After you’ve entered these expressions, go back and click on each of these cells
to see whether these spreadsheet functions and cell references appear in uppercase
format.
Okay, Excel doesn’t really care how you enter formulas; it converts them to
uppercase regardless. What’s the big deal? You can use this to your advantage.
Consider typing in the formula:
=average(1,2,3,4,5)+random()
The result you get is #NAME?. Obviously, something is wrong with this formula;
otherwise you would get a computed number, not a quizzical error message. You
have two functions in your formula: average and random. Which of these two is
the offending function? Maybe it’s both.
If you select the cell where you just entered this formula, you will see the text
shown in the Excel Formula Bar in Figure 1-9.
Figure 1-9: The function with the error is not
automatically converted to uppercase.
Notice that Excel converted the AVERAGE function to uppercase. However, it didn’t
do anything with “random.” This is because Excel does not have a “random” func-
tion. Had you written the function as RANDOM, Excel would have kept it that way, in
which case you’d be staring at two expressions in uppercase form, not knowing
which one is the offender.

Enter the Excel functions in your formula in lowercase form. If the functions
are understood by Excel, they will be converted to uppercase. Those func-
tions not understood by Excel will remain in lowercase.This practice enables
you to spot naming errors in your formulas when they occur.
If you want to go ahead and fix the formula, you can edit the formula to
=AVERAGE(1,2,3,4,5)+rand()
ENTERING AND EDITING FORMULAS
Perhaps it’s time to begin discussing easier ways to enter formulas. Open the sam-
ple spreadsheet, ch01-01.xls (Figure 1-10).
Figure 1-10: Sample spreadsheet with Web log data
If you want, you can enter a formula into R13C2 such as the total Page Views
over the seven-day interval. You could enter the following (see Figure 1-11):
The following formula is included in your Take-Away text file takeaway.txt:
=r6c2+r7c2+r8c2+r9c2+r10c2+r11c2+r12c2
This formula produces a count of 345,710 for Page Views over the seven-day
period.

Figure 1-11: Cells in the formula line are visually highlighted.
You should notice that as you type in the formulas, the cells or ranges of cells
you specified in your formula become highlighted in colored borders and the colors
are matched with the cell references as you are editing or typing in the formulas.
This is a useful aid for two reasons:
◆
It can give you easy visual feedback of what you’ve just typed in.
◆
If you have a typographic error in your formula, strange things will hap-
pen to the highlighting (Figure 1-12); or what you expect to be high-
lighted will not be highlighted at all.
Figure 1-12: A formula entered with an error is
highlighted differently.

This technique of clicking in the edit line and seeing the corresponding high-
lighted cells within the worksheet will prove helpful when you prepare and analyze
more complex spreadsheets.
The technique of visually highlighting cell references in a formula (as shown
in Figures 1-11 and 1-12) need not be applied only to new formulas you are
entering. You can also click the Excel Formula Bar for any cell. This is espe-
cially useful when you receive a spreadsheet from a third party and want to
quickly inspect where it is gathering its numbers.
While you’re at it, you’re likely to have the occasion to write some nested for-
mulas. As you type away in the edit line, when you enter a closing parenthesis,
Excel momentarily highlights the corresponding opening one (see Figure 1-13).
This feature helps you both to see what your expressions contain as well as to bal-
ance the parentheses (that is, ensure that the number of opening parentheses
matches the number of closing ones).
Figure 1-13: As you enter the closing parenthesis on the right, the
corresponding opening parenthesis is momentarily highlighted
(in this case, between IF and RC[1]).
Unfortunately, the corresponding balanced parenthesis is highlighted for only a
brief moment. Sometimes Excel formulas can contain heavily nested expressions. A
technique I often use is to go to the edit line, backspace or delete one of the closing
parentheses, and immediately re-enter it. This way, I can see where the opening
parenthesis is located.
Excel sports another feature to aid in the editing of formulas. The logical for-
mula structure is highlighted in a hovering text message (see Figure 1-14).
Be sure to enable the Function tooltips feature on your Excel Options...
General tab. For further information, consult Appendix A.
Figure 1-14: Hovering text helps to identify formula structure.

This use of the hovering text is definitely helpful in keeping your bearings when
entering formulas. It zeroes in on the selected portion of the Excel formula, freeing
you from being distracted by the rest of the line.
SELECTING CELLS AND SPECIFYING
CELL RANGES IN EXCEL FORMULAS
Many Excel functions are capable of performing computations on ranges of cells.
Most prevalent of these is the familiar SUM worksheet function. Ranges of cells in
such formulas are separated by a colon (:) symbol. Examples of this usage include:
=SUM(R1C1:R10C4)
=SUM($A$1:$D$10)
The preceding examples include equivalent formulas in both the R1C1 and A1
notation (see the section “Understanding Alternative Ways to Represent Cells,” ear-
lier in this chapter). Where possible, I present both these notations or else use
named ranges, as appropriate. For cases in which the context is clear and has little
or no dependency on the cell-referencing notation, I may use only one notation style.
With regard to screen shots, I use only one of these notations. The choice is
based on whatever makes most sense. Quite often, the screen shot illustrates some-
thing independent of the notation.
You have the SwitchTool spreadsheet to change modes on your spread-
sheets at the press of a button.Therefore, don’t worry about the format.
I discuss the Excel SUM function in other places and in different contexts
throughout this book. Here, I’m just using it as a representative function that
accepts multiple cell ranges for its input.
Take a look at the three examples in Listing 1-1.
Listing 1-1: The same formula shown three different ways
=SUM(R1C1:R10C4,R4C6:R14C6)
=SUM($A$1:$D$10,$F$4:$F$14)
=SUM(OverHeadAndAdminExpenses,OperatingExpenses)
As you can see, Excel functions such as SUM can accommodate multiple cell
ranges, each separated by a comma. The following representative formulas (see
Formulas 1-3 and 1-4) show that the SUM function can accommodate a variety of
input arguments. I provide the same examples in both the R1C1 notation and A1
notation:

Formula 1-3: Example of Input Arguments for the SUM function in R1C1 Notation
=SUM(R21C1:R30C1)
=SUM(R[-10]C:R[-1]C)
=SUM(R21C1:R30C1,R[-10]C:R[-1]C)
=SUM(R[-10]C:R[-1]C,C[1],R4,R5,R6)
=SUM(OneUserDefinedRange,AnotherDefinedRange,StillAnotherUserDefined
Range)
=SUM(OneUserDefinedRange,R[1]C[1],3,4,5,R1C1:R10C3,3.14159)
Formula 1-4: Same Formulas as that in Formula 1-3, represented here in A1 Notation
=SUM($A$21:$A$30)
=SUM(A11:A20)
=SUM($A$21:$A$30,A12:A21)
=SUM(A13:A22,B:B,$4:$4,$5:$5,$6:$6)
=SUM(OneUserDefinedRange,AnotherDefinedRange,StillAnotherUserDefined
Range)
=SUM(OneUserDefinedRange,B25,3,4,5,$A$1:$C$10,3.14159)
Notice that valid cell ranges can include literal values (such as a number), user
defined named ranges, cell references (whether relative, absolute or hybrid). Note
that a cell range can reference a rectangular swatch; not just only a single row or
column.
Keep in mind that named values in Excel need not be restricted to individual
cells, but can be indicative of a range of cells for any individual name. As indicated
in Listing 1-1 (appearing earlier in the chapter), OverHeadAndAdminExpenses could
be defined to range over the cells A1 through D10. OperatingExpenses might cor-
relate to F4:F14; making the third formula equivalent to the first two. There are
certainly other possible combinations of valid formulas equivalent to the formulas
shown in Listing 1-1. Consider:
=SUM(R1C1:R10C4, OperatingExpenses)
=SUM($A$1:$D$10,OperatingExpenses)
OVERLAPPING RANGES
What happens if the ranges separated by commas happen to overlap? Would those
overlapping regions be counted more than once? While you to ponder this ques-
tion, consider another one, which may help: What amount would be computed for
the following?
=SUM(1,3,1,2)
The computed sum in this case doesn’t involve cell positions; it just involves lit-
eral values. There is no confusion. The result is 7. Ask yourself what SUM is doing.
The answer: It is separately evaluating each of the expressions separated by com-
mas and adding up the numeric value of each of the results.

If you’re summing regions that overlap, the overlapped regions can get
counted more than once.
The situation is no different when cell references are brought into the picture.
Look at Figure 1-15. The formula:
=SUM(B3:E12,D9:H20)
would first evaluate the sum of B3:E12 (which is 10500), and then evaluate the sum
of D9:H20 (which is 10003), and then add these together to get a total of 20503. So,
if the numbers are represented more than once, they will be counted more than
once.
Figure 1-15: Overlapped regions can be double counted.
Keyboard and cursor navigation tips
Although the use of spreadsheets and the windowing environment is intuitive, peo-
ple sometimes find it not so easy to manage large spreadsheets that may have many
thousands of rows of data. There are some things you can do to facilitate moving
about on a large spreadsheet and manage formulas. These are essentially trivial, but
practical nonetheless.
Open the file ch01-05navigation.xls (see Figure 1-16).
The screen shot shows the beginning and end portions of data that spans 1,000
rows. When you open the file on your CD that accompanies the book, it appears
without the horizontal split pane shown in the figure. The file contains data that’s
actually used in a later chapter (Chapter 9, “How Not to Get Stuck in the M.U.D.”),
where you’ll be addressing some very thorny issues on data ambiguity. The data, as
you can see, is a string of numbers. The numbers actually represent dates without a

delimiter to separate days, months, and years. Take a look at the first two entries. If
you knew the dates follow the sequence of day-month-year, you could unambigu-
ously interpret 122952 as 12/29/1952. Likewise, you could see that 3643 would be
3/6/1943. The way you reason this out is dependent, in part, upon the length of the
string of characters that make up the date string. Does this problem seem intrigu-
ing? If you want, you can jump ahead to Chapter 9 to get a sneak peak.
Figure 1-16: Spreadsheet with continuous stream of data for 1,000 rows
Right now, I want you to deal with the much more mundane task of quickly fill-
ing in the needed first steps, the first of which is to compute the length of each of
the 1,000 strings. The focus here is in nailing down a rudimentary mechanical tech-
nique so that when you turn to large, complex, and challenging problems, you’ll be
slicing through the data as easily as a hot knife cuts through butter.
The worksheet function used to compute the length of a string of characters is
called LEN. With the string data in the first column, the formula to compute the
string length from the second column is
=LEN(RC[-1])
This is obviously a simple formula. The key is to replicate it a thousand rows
down. One way of doing so is to select the cell in the second row containing your
length formula and scroll all the way down until you reach the last piece of data,
which happens to be on row 1002. Press Shift and click Select and Fill Down
(Ctrl+D). Not particularly hard, right? But ask yourself some questions:
◆ How many mouse clicks or key presses did you need to scroll down?
◆ What if the number of lines of data were closer to fifteen thousand than
one thousand?

Think of some of the shorter steps you could have done. You could grab the ver-
tical scroll bar and pull it all the way down to get to the last row of data. The “last
row” happens to be the last row of the worksheet. In any given situation, what
guarantee do you have that the specific data you are working on extends all the
way down to the bottom of the spreadsheet?
THE PROBLEM: TRYING TO KEEP TRACK OF YOUR DATA
The real spreadsheets that you work with, as opposed to the simplified example
from this book, might be chock full of data spread over many columns. And when
you scroll down to the bottom the last piece of data you’re looking for, you might
not find it there. You think it’s very near the last row of the spreadsheet, so you
press PgUp several times. To no avail; it’s not there. Now you go back to manually
scanning by moving the vertical scroll bar. You spot the last line of data. You’ve
wasted 15–20 seconds searching for that last cell.
To avoid that delay the next time around, you make a mental note of what row
it appears in. Say that row happens to be 3874. Chances are, if it’s that far down,
your spreadsheet is complex and you’re working under pressure.
The last thing you want to do is to tax your memory with information that has
nothing to do with the underlying spreadsheet and interpretive analysis of its data.
Also, when you put down your spreadsheet and reopen it next month to add or
update data, do you really think you’re going to have row 3874 at the forefront of
your thoughts? So there you go chasing after that row of data again, probably
along with others as well.
I’m sure you’ve guessed that I have a simple solution in mind. I do. If it’s that
simple, why go through all the coy disguises? Why not flat-out state it and be done
with it? The answer is that the simple solution is not the moral of the story. The ten-
dency and common practice among many people in business is to apply a “one-off”
approach to everything. Nowhere is this more true than in how people use spread-
sheets in the business environment.
THE SOLUTION
Here’s the simple solution: I previously said, the string of dates in column 1 was
contiguous. Select the first cell containing data in column 1, press Ctrl+Down
Arrow and you’re instantly brought down to row 1002 (or whatever row the last
contiguous piece of data happens to reside in). Now press the right arrow key once.
You’re now 1,000 rows directly below R2C2, which contains the length formula you
want to replicate. Now press Shift+Ctrl and, keeping those keys down, also press the
Up Arrow key. The addition of the Shift key in this action allows you to select all
the intervening cells you just navigated. With all the intervening cells selected (the
top cell of which having the formula you want to replicate), click Fill Down (or
press Ctrl+D) and you’re done.
A REAL-WORLD COMPLICATION ... AND WHAT TO DO ABOUT IT
Let me add a little complication. Suppose that you want to do the same and select
all the cells in the range, but the string of data in column 1 is not contiguous. You

might have two or three cells that are empty. Well, you could quickly “arrow over”
to the last piece of data before hitting the empty cell. This could be useful to iden-
tify where the breaks are in an otherwise continuous chain of data. Suppose there
are significantly more than two or three breaks? What if 10 percent of the many
hundreds or thousands of lines have empty cells and for the most part are randomly
placed? The technique to moving across the cells using the arrow key as presented
wouldn’t be very useful. So, you need to improvise. After going down a screenful or
two and seeing that this method is too slow, just select the cell one column over to
the right (that is, press the right arrow key once to place yourself on to column 2,
which is now essentially empty). Press Ctrl+Down Arrow. This action brings you
down to row 65536, the bottom of the spreadsheet. Move one column to the left.
Press Ctrl+Up Arrow to move to the very last piece of data in column 1. This is
where you were trying to go. Move one column over to the right. Press Ctrl+Shift+
Up Arrow and click Fill Down (or press Ctrl+D).
What advantages have you gained by this?
◆ You virtually eliminate any guesswork in finding, selecting, moving data
for copying, pasting, moving, or otherwise manipulating large blocks of
data.
◆ The overhead for managing large blocks of data remains constant regard-
less of how large your spreadsheet grows.
◆ By traversing the spreadsheet with lightning speed and removing the bur-
den of thinking about cell locations and placing artificial visual markers,
you are freer to focus on the important things in your spreadsheets.
The keystroke-based cell navigation technique will be useful while you are
in the process of entering formulas that use cell references spanning many
rows and columns. From the Excel Formula Bar, you can click in cells and
navigate your spreadsheet using these keystroke techniques. As you move
about, the Formula Bar will be populated with the corresponding cell
ranges.They will be a definite time saver and reduce errors.
User-defined names within Excel spreadsheets
The feature of user-defined names is an important part of Excel. You likely already
know how to use this feature, but I go over the bare-bone basics just in case.
There are two techniques for defining a batch of names.
USING KEYSTROKES TO DEFINE A SEQUENCE OF NAMES
Sometimes it is easier and quicker to define user names by navigating the user
menu through keystroke sequences than by using the mouse. If your named ranges

are single cells, you might want to try the following steps. (I am assuming that you
are using the default Excel Edit Options, which involves moving your selection
down after pressing Enter. Appendix A has more on setting Excel Options).
1. Group the cells to be named together, generally in a column.
2. Pre-label the cells. The labels can be to the immediate left of the cells to
be defined.
3. For each cell to be defined, press the following keystroke sequence:
Alt+i+n+ENTER+ENTER+ENTER
As you type in the keystrokes given in Step 3, the Excel menu will automatically
pull down. Excel will “guess” and insert a suggested name in the edit line of the
Define Name window (see Figure 1-17).
Note that in column A, a list of labels starts in row 2. These are the names you’ll
be giving to the cells you define. Obviously, the choice of names is up to you. As
you can see in Figure 1-17, the name being defined for the cell B5 is TaxRate4,
which matches the label to its immediate left. This is no accident. Excel picked up
the name from the label, sparing you the drudgery of entering it. For the nine or ten
names you have pre-listed, press the repetitive keystrokes in Step 3 of the preced-
ing list and you’ll quickly be done with defining user names.
This technique of rapid name definition does not apply to named ranges
spanning multiple cells for a given name.
Figure 1-17: Using labels to generate user-defined names

Is there a limit to how many names you can define this way? There really isn’t;
however, if you’ll be defining a great many names, such as hundreds, you may
want to consider using an Excel Macro/VBA facility to automate this process. This
lies beyond the scope of this book. Applying a visual marker to identify named cells
is a good practice. You can apply cell shading, borders or even sticky notes.
USING CTRL+SHIFT+F3 TO DEFINE A BATCH OF NAMES
There’s a more rapid and efficient way to define a group of names. You can select
the labels and the group of cells to be given names and press Ctrl+Shift+F3. You
will then be presented with a dialog box like the one shown in Figure 1-18.
This technique is much easier to perform than the one shown in the previous
section, but it does not afford as much flexibility. In the previous method, as you
create new names, you can pause momentarily to decide whether you want to
tweak the name that Excel suggests.
Both of these methods are effective. Use whatever works for you.
Figure 1-18: Batch creation of names
Excel Functions
Now that you’re armed with a little bit about how to express formulas, I want to
spend some time discussing Excel functions.
Excel has two very powerful features in it. It’s got a terrifically efficient comput-
ing engine and is also a powerful tool for visually communicating numerical infor-
mation. This combination makes Excel particularly potent and vital in business.
Obviously, there’s a lot more to Excel than just these two facets. But without these,
Excel would be just another faceless application that we would all tire of quickly.
When I speak about the computational engine, I am referring to both the raw
number crunching capacity and the range of special-purpose and general functions
it possesses.
Number crunching is not always about benchmarks such as how many multipli-
cations can be performed each second. To be sure, Excel on a desktop computer is
not going to outperform a supercomputer in sheer number-crunching capacity. One
thing it does “excel” at, though, is its unwavering ability to take complex relation-
ships (with multitudes of formulas, each of which can be dependent upon other
computations) and combine them with large quantities of data to immediately