- Shopping Bag ( 0 items )
Excel Best Practices for Business enables readers to examine their work and ask critical questions. And once asked, this book also answers with dynamic, practical approaches and provides Take-Aways extrapolated from real situations across a managerial spectrum, making this book more mentor than reference. In this book, a critical need is met.
There are many more topics in Excel Best Practices For Business including: practical techniques for visualizing hard-to-present data, incorporating "Smart Data" into your spreadsheets, how to build a Data Overpass, quantification of uncertainty, conversion of mountains of legacy data into manageable and useful form, spreadsheet auditing to validate the work of others, a hands-on approach to working with the Excel Solver tool, spreadsheet construction techniques through both simple design and large, complex applications. If you want to find about these and many other techniques covered, then Excel Best Practices for Business is the perfect guide!
|Pt. I||Best Practice Essentials|
|Ch. 1||A Foundation for Developing Best Practices||3|
|Ch. 2||Mastering Spreadsheet Construction Techniques||45|
|Ch. 3||Your Handy Reference for Manipulating Data||69|
|Ch. 4||Compiling, Managing, and Viewing Your Data||99|
|Pt. II||Spreadsheet Ergonomics|
|Ch. 5||Scaling the Peaks of Mt. Data||133|
|Ch. 6||Let the Data Speak for Itself: Viewing and Presenting Data||159|
|Ch. 7||Creating and Using Smart Data||185|
|Ch. 8||Analyzing Data||203|
|Ch. 9||How Not to Get Stuck in the MUD (Messed-Up Data)||249|
|Pt. III||Special Topics: Getting the Numbers Right|
|Ch. 10||Going for the Facelift: Spreadsheet Makeovers||273|
|Ch. 11||Spreadsheet Auditing: Challenging the Numbers of Others||315|
|Ch. 12||Spreadsheet Portals, XML, and Web Services||335|
|Ch. 13||Assistive Technologies and Assistive Portals||375|
|App. A||Excel Configuration and Setup||433|
|App. B||Information for Macintosh Users||453|
|App. C||Excel Best Practice Techniques and Hip Pocket Tips||457|
|App. D: What's on the CD-ROM||471|
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.
This is a book about best practices. In my mind, the notion of best practices conjures up an image of working smartly and efficiently when using spreadsheets. To be sure, there isno shortage of "esoterica" or sophistication in this book, but there is plenty of time for that later. Sophisticated techniques that are effective and useful are not generally built upon more esoteric concepts. Rather, they take hold most easily when built on solid foundations.
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 backwards 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 denominator, just enough to squeak by. These skills can be readily acquired through corporate 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 formulas absolute, relative and hybrid expressions. You will also be shown the practical benefits of using one approach over another.
Also, you will find out that the built-in Excel functions are not only about performing 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 functions perform computations specifying precision and fuzziness (and this has nothing 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 straightforward 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 locations 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 translating column letters and row numbers in your head.
Suppose you see two formulas. The one in cell DC91 is
There's another cell, EG62, which contains the formula:
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
and the second formula is
Although the first expression (RC[-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 expression in the formulas doesn't line up in terms of the number of row offsets or column 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 arithmetic 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 compared 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 correspond 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 computed. 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 relative references, each new formula, although similar in structure, is dependent 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 ITLITL 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 column 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" number. As an example, if you want to specify the cell to your immediate right, you use RC. It doesn't matter which cell you are in. If you copy and paste cells that use RC, the expression RC 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 RC 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) 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.
Excerpted from Excel Best Practices for Business by Loren Abdulezer Excerpted by permission.
All rights reserved. No part of this excerpt may be reproduced or reprinted without permission in writing from the publisher.
Excerpts are provided by Dial-A-Book Inc. solely for the personal use of visitors to this web site.
Posted January 25, 2004
I'm a very lucky guy because I was paid to read Excel Best Practices for Business. I teach introductory Excel courses as well as more advanced Excel applications. As a result, I have reviewed many guides on how to use spreadsheets dating all the way back to VisiCalc, MultiPlan, and Lotus 1-2-3. For these and other reasons, I was offered and accepted the opportunity to be the Technical Editor on this book. Aside from having all the latest and greatest spreadsheet technology (like spreadsheet portals, XML, and web services), the author writes about really practical techniques. Even if you think that you know all that there is to know about spreadsheets, you will still learn more from this book. Where else can you learn, for instance, how to evolve a strategy for Absolute vs. Relative vs. Hybrid cell referencing in Excel? While this may seem mundane to some, having the best strategy for your particular situation can make a serious difference in long-term productivity and the resulting value of the spreadsheet. Just as there are best practices for construction in the home building industry, there are best practices for spreadsheet construction that can make a long-term difference in customer or client satisfaction. In addition to providing a better understanding of simple spreadsheets, you will learn how to create blueprints for large or complex spreadsheets. The author's excellent formal training in applied mathematics is readily apparent in the sections on manipulating, compiling, managing, viewing, and presenting data. You will learn about how to create and use smart data and how to analyze data without getting stuck in the MUD (messed-up-data). How you ever managed before learning the fine art of data slogging will become a mystery to you. There are whole chapters devoted to special topics and themes. Some of these include using spreadsheet portals with XML, spreadsheet auditing, spreadsheet makeovers, developing spreadsheets to accommodate special needs of individuals with disabilities, and learning how to manage mountains of data. By now it should be clear that this is not your ordinary spreadsheet book written just for the computer phobic or mathematically challenged. It's well worth reading by anyone with an interest in using best practices for spreadsheets even if you've only had eight hours of previous Excel training. The CD-ROM alone is worth the price of the book because it's not just files that serve as input for exercises in a computer lab. The CD-ROM contains a selection of components and spreadsheets that can be quickly and easily modified to meet various predictable business needs, including making spreadsheets accessible to the motor impaired, hearing impaired and visually impaired using the latest assistive technologies. In addition to the CD-ROM, there is an author's website that is kept updated and takes the reader beyond the book.Was this review helpful? Yes NoThank you for your feedback. Report this reviewThank you, this review has been flagged.