### About Global Documents

**Global Documents provides you with documents from around the globe on a variety of topics for your enjoyment. **

**Global Documents utilizes edocr for all its document needs due to edocr's wonderful content features. Thousands of professionals and businesses around the globe publish marketing, sales, operations, customer service and financial documents making it easier for prospects and customers to find content. **

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

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

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