10820i by CRBD-Biblioteca Digital

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 . . . . . . . . . . . . . . . . . More useful information about working with formulas . . . . Keyboard and cursor navigation tips . . . . . . . . . . . . . . . . . User-defined names within Excel spreadsheets . . . . . . . . . .

Chapter 2

. . . 15 . . . 19 . . . 26 . . . 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 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

xviii

Contents Chapter 3

Your Handy Reference for Manipulating Data . . . . 69

Chapter 4

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 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

Contents 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

Chapter 7

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 Creating and Using Smart Data . . . . . . . . . . . . . . . . . 185 What Is Smart Data, Anyhow? . . . . . . . . . . . . . . . . . . . . . 186 Smart Data used with anchor cells . . . . . . . . . . . . . . . . . . . . 186 Conditional Formatting . . . . . . . . . . . . . . . . . . . . . . . . . . . 188 Constructing a â&#x20AC;&#x153;digital dashboardâ&#x20AC;? . . . . . . . . . . . . . . . . . . . . 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

xix

Contents

Chapter 8

Miscellaneous Topics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 200 Helper cells . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 200 Scalability issues . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 201 Closing Thoughts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 202 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 â&#x20AC;&#x153;data miragesâ&#x20AC;? . . . . . . . . . . . . . . . . . . . . . . . 269 Strategies for assessing whether you have a data mirage . . . . . 269 Closing Thoughts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 270

Contents 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â&#x20AC;&#x2122;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

Chapter 12

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â&#x20AC;&#x2122;s a matter of semantics . . . . . . . . . . . . . . . . . . . 332 Closing Thoughts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 334 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

xxi

xxii

Contents 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

Chapter 13

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 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

Contents 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â&#x20AC;&#x2122;s on the CD-ROM . . . . . . . . . . 471 Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 479

xxiii