Excel, Best Practices for Business: Covers Excel 2003, 2002, and 2000

Overview

Spreadsheets have become the de facto standard for communicating business information and the preferred tool for analyzing business data. In this current climate, the accuracy and clarity of spreadsheets are paramount. However, busy managers have little time to sift through heaps of reference books to extrapolate techniques for making polished spreadsheets. Even with finished spreadsheets in hand, managers and business professionals still need a book which holds up a mirror to their real world situations and ...
See more details below
Available through our Marketplace sellers.
Other sellers (Paperback)
  • All (9) from $1.99   
  • New (3) from $88.10   
  • Used (6) from $1.99   
Close
Sort by
Page 1 of 1
Showing All
Note: Marketplace items are not eligible for any BN.com coupons and promotions
$88.10
Seller since 2008

Feedback rating:

(193)

Condition:

New — never opened or used in original packaging.

Like New — packaging may have been opened. A "Like New" item is suitable to give as a gift.

Very Good — may have minor signs of wear on packaging but item works perfectly and has no damage.

Good — item is in good condition but packaging may have signs of shelf wear/aging or torn packaging. All specific defects should be noted in the Comments section associated with each item.

Acceptable — item is in working order but may show signs of wear such as scratches or torn packaging. All specific defects should be noted in the Comments section associated with each item.

Used — An item that has been opened and may show signs of wear. All specific defects should be noted in the Comments section associated with each item.

Refurbished — A used item that has been renewed or updated and verified to be in proper working condition. Not necessarily completed by the original manufacturer.

New
076454120X New. Looks like an interesting title!

Ships from: Naperville, IL

Usually ships in 1-2 business days

  • Standard, 48 States
  • Standard (AK, HI)
$173.68
Seller since 2014

Feedback rating:

(273)

Condition: New
Brand New Item.

Ships from: Chatham, NJ

Usually ships in 1-2 business days

  • Canadian
  • International
  • Standard, 48 States
  • Standard (AK, HI)
  • Express, 48 States
  • Express (AK, HI)
$175.00
Seller since 2014

Feedback rating:

(164)

Condition: New
Brand new.

Ships from: acton, MA

Usually ships in 1-2 business days

  • Standard, 48 States
  • Standard (AK, HI)
Page 1 of 1
Showing All
Close
Sort by
Sending request ...

Overview

Spreadsheets have become the de facto standard for communicating business information and the preferred tool for analyzing business data. In this current climate, the accuracy and clarity of spreadsheets are paramount. However, busy managers have little time to sift through heaps of reference books to extrapolate techniques for making polished spreadsheets. Even with finished spreadsheets in hand, managers and business professionals still need a book which holds up a mirror to their real world situations and reflects hidden flaws; and then takes the next step and guides the reader in specific ways to rework these critical documents.

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.

Book Highlights:

  • XML in Microsoft Office Excel 2003: Entirely new to Excel 2003 is major support for XML, making Excel truly web capable and Internet ready. This book provides extensive coverage of these new features from a hands-on perspective. It identifies subtleties, gotchas and problems, and shows you practical solutions and workarounds.
  • SPREADSHEET PORTALS: This book introduces the topic of Spreadsheet Portals, which elevates spreadsheet practices for the Internet-ready software to the next level. Aside from explaining the basic concepts and principles of Desktop Client Portals, best practice techniques for building your portal pages and reference implementations are provided. These reference implementations, sample spreadsheets, and online demos are provided on the book's CD.
  • SPREADSHEET MAKEOVERS: What do you do when your manager or boss asks you to take over a complex, spreadsheet-based application and send out reports every two weeks? The person who created the spreadsheet no longer works for the company. Aside from a few emails, there's no documentation. You look at the spreadsheet and you find it has flaws. Never mind about fixing the old reports; the new ones are going to go out with your name on it. This report is not your prime responsibility. You do not have the time or resources to turn this into a whole project, yet you can't afford to leave it the way it is. Excel Best Practices for Business provides a step-by-step approach to these "Mission Impossible" situations and walks you through the steps with fully worked out examples.
  • ASSISTIVE TECHNOLOGIES: For the first time in a mainstream book, the topic of preparing accessible spreadsheets for individuals with disabilities is addressed. Government agencies needing to make electronic information section 508 compliant and corporations choosing not to alienate communities with special needs will find the techniques presented invaluable. You will learn from a hands-on perspective how to organize and design accessible spreadsheets for the visually impaired that will work with Screen Reader software, how to set up Screen Reader software, and how to build graphical components that will work with Screen Readers. These practices are carried to the next level with the introduction of Assistive Portals. This allows you to make spreadsheets accessible and avoid having to alter your original spreadsheets. The Portal Page does all the work. Because it is table driven, there are no formulas or scripts to modify. Think of how this will change the economics of preparing accessible documents.

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!

Read More Show Less

Product Details

  • ISBN-13: 9780764541209
  • Publisher: Wiley
  • Publication date: 11/3/2003
  • Edition description: Book & CD
  • Edition number: 1
  • Pages: 540
  • Product dimensions: 7.46 (w) x 9.20 (h) x 1.20 (d)

Meet the Author


Loren Abdulezer is CEO of Evolving Technologies Corporation and an IT professional who has assisted several Fortune 500 companies. He has worked with MIS/Web reporting and Internet security for a major financial firm, where he played a significant role in implementing a public key infrastructure/smart card initiative.
Read More Show Less

Table of Contents

Preface
Acknowledgments
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
Index 479
Read More Show Less

First Chapter

Excel Best Practices for Business

Covers Excel 2003, 2002, and 2000
By Loren Abdulezer

John Wiley & Sons

ISBN: 0-7645-4120-X


Chapter One

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

=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 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[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.

Continues...


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.

Read More Show Less

Customer Reviews

Be the first to write a review
( 0 )
Rating Distribution

5 Star

(0)

4 Star

(0)

3 Star

(0)

2 Star

(0)

1 Star

(0)

Your Rating:

Your Name: Create a Pen Name or

Barnes & Noble.com Review Rules

Our reader reviews allow you to share your comments on titles you liked, or didn't, with others. By submitting an online review, you are representing to Barnes & Noble.com that all information contained in your review is original and accurate in all respects, and that the submission of such content by you and the posting of such content by Barnes & Noble.com does not and will not violate the rights of any third party. Please follow the rules below to help ensure that your review can be posted.

Reviews by Our Customers Under the Age of 13

We highly value and respect everyone's opinion concerning the titles we offer. However, we cannot allow persons under the age of 13 to have accounts at BN.com or to post customer reviews. Please see our Terms of Use for more details.

What to exclude from your review:

Please do not write about reviews, commentary, or information posted on the product page. If you see any errors in the information on the product page, please send us an email.

Reviews should not contain any of the following:

  • - HTML tags, profanity, obscenities, vulgarities, or comments that defame anyone
  • - Time-sensitive information such as tour dates, signings, lectures, etc.
  • - Single-word reviews. Other people will read your review to discover why you liked or didn't like the title. Be descriptive.
  • - Comments focusing on the author or that may ruin the ending for others
  • - Phone numbers, addresses, URLs
  • - Pricing and availability information or alternative ordering information
  • - Advertisements or commercial solicitation

Reminder:

  • - By submitting a review, you grant to Barnes & Noble.com and its sublicensees the royalty-free, perpetual, irrevocable right and license to use the review in accordance with the Barnes & Noble.com Terms of Use.
  • - Barnes & Noble.com reserves the right not to post any review -- particularly those that do not follow the terms and conditions of these Rules. Barnes & Noble.com also reserves the right to remove any review at any time without notice.
  • - See Terms of Use for other conditions and disclaimers.
Search for Products You'd Like to Recommend

Recommend other products that relate to your review. Just search for them below and share!

Create a Pen Name

Your Pen Name is your unique identity on BN.com. It will appear on the reviews you write and other website activities. Your Pen Name cannot be edited, changed or deleted once submitted.

 
Your Pen Name can be any combination of alphanumeric characters (plus - and _), and must be at least two characters long.

Continue Anonymously
Sort by: Showing 1 Customer Reviews
  • Anonymous

    Posted January 25, 2004

    Most Practical Spreadsheet Guide Ever Written

    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  No   Report this review
Sort by: Showing 1 Customer Reviews

If you find inappropriate content, please report it to Barnes & Noble
Why is this product inappropriate?
Comments (optional)