Excel best practices for business

531 Pages·8.959 MB·English

by  Loren Abdulezer

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, [phone], fax [phone]. Requests to the Publisher
for permission should be addressed to the Legal Department, Wiley Publishing, Inc., 10475 Crosspoint Blvd.,
Indianapolis, IN 46256, [phone], fax [phone], E-Mail: [email protected].
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 [phone], outside the U.S. at [phone] or fax
[phone].
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.
is a trademark of Wiley Publishing, Inc.
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 PROJECT COORDINATOR
Greg Croy Kristie Rees
PROJECT EDITOR GRAPHICS AND PRODUCTION
Susan Christophersen SPECIALISTS
Beth Brooks
TECHNICAL EDITOR Carrie Foster
William Good Joyce Haughey
LeAndra Hosier
COPY EDITOR Michael Kruzil
Susan Christophersen Kristin McMullan
Barry Offringa
EDITORIAL MANAGER Lynsey Osborn
Carol Sheehan Heather Pope
VICE PRESIDENT AND QUALITY CONTROL TECHNICIANS
EXECUTIVE GROUP PUBLISHER Laura Albert
Richard Swadley Carl William Pierce
Dwight Ramsey
VICE PRESIDENT AND PUBLISHER Brian Walls
Andy Cummings
PERMISSIONS EDITOR
EDITORIAL DIRECTOR Carmen Krikorian
Mary Corder
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
xi
examples and try to present concepts from a mature business perspective. I want
xii Preface
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 bethe 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
xvii
Closing Thoughts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 66