Unified Patents Inc. v. Vilox Technologies LLC
`Ex. 1009 / Page 1 of 44
`
`

`

`
`
`Ex. 1009 / Page 2 of 44
`
`Ex. 1009 / Page 2 of 44
`
`

`

`Microsoft®
`
`Excel 2000 Bible
`
`Ex. 1009 / Page 3 of 44
`
`Ex. 1009 / Page 3 of 44
`
`

`

`Microsoft®
`
`EXcel 2000 Bible
`
`John Walkenbach
`
`._—-'-o
`
`E I
`
`DG
`ms;
`
`IDG Books Worldwide, Inc.
`An International Data Group Company
`
`Foster City, CA + Chicago, IL + Indianapolis, IN + New York, NY
`
`Ex. 1009 / Page 4 of 44
`
`Ex. 1009 / Page 4 of 44
`
`

`

`Micronesia; by Grupo Editorial Norma S.A. for
`Guatemala; by Chips Computadoras SA. de CV. for
`Mexico; by Editorial Norma de Panama S.A. for
`Panama; by American Bookshops for Finland.
`' Authorized Sales Agent: Anthony Rudkin Associates
`for the Middle East and North Africa.
`
`For general information on IDG Books Worldwide’s
`books in the US, please call our Consumer Customer
`Service department at 800-762-2974. For reseller
`information, including discounts and premium sales,
`please call our Reseller Customer Service
`department at 800-434-3422.
`For information on where to purchase IDG Books
`Worldwide’s books outside the US, please contact
`our International Sales department at 317-596-5530 or
`fax 317-596-5692.
`For consumer information on foreign language
`translations, please contact our Customer Service
`department at 800-434-3422, fax 317-596-5692, or
`e-mail rights@idgb00ks.com.
`For information on licensing foreign or domestic
`rights, please phone +1-650-655-3109.
`For sales inquiries and special prices for bulk
`quantities, please contact our Sales department at
`650-655-3200 or write to the address above.
`For information on using IDG Books Worldwide's
`books in the classroom or for ordering examination
`copies, please contact our Educational Sales
`department at 800-434-2086 or fax 317-596-5499.
`For press review copies, author interviews, or other
`publicity information, please contact our Public
`Relations department at 650-655-3000 or fax
`650-655—3299.
`
`Microsoft' Excel 2000 Bible
`Published by
`IDG Books Worldwide, Inc.
`An International Data Group Company
`919 E. Hillsdale Blvd., Suite 400
`Foster City, CA 94404
`www.1'dg boo ks . corn (IDG Books Worldwide Web site)
`Copyright © 1999 IDG Books Worldwide, Inc. All
`rights reserved. No part of this book, including
`interior design, cover design, and icons, may be
`reproduced or transmitted in any form, by any means
`(electronic, photocopying. recording, or otherwise)
`without the prior written permission of the publisher.
`Library of Congress Catalog Card Number’; 98-75379 ‘3
`ISBN: 0-7645-3259-6
`" '
`Printed in the United States of America
`10 9 8 7 6 5 4 3 2 1
`lB/SU/QT/ZZ/FC
`Distributed in the United States by IDG Books
`Worldwide, Inc.
`Distributed by CDG Books Canada Inc. for Canada; by
`Transworld Publishers Limited in the United
`Kingdom; by IDG Norge Books for Norway; by IDG
`Sweden Books for Sweden; by Woodslane Pty. Ltd. for
`Australia; by Woodslane (NZ) Ltd. for New Zealand;
`by TransQuest Publishers Pte Ltd. for Singapore,
`Malaysia, Thailand, Indonesia, and Hong Kong; by
`ICC Muse, Inc. for Japan; by Norma Comunicaciones
`S.A. for Colombia; by lntersoft for South Africa; by Le
`Monde en Tique for France; by International
`Thomson Publishing for Germany, Austria and
`Switzerland; by Distribuidora Cuspide for Argentina;
`by Livraria Cultura for Brazil; by Ediciones ZETA
`S.C.R. Ltda. for Peru; by WS Computer Publishing
`Corporation, Inc., for the Philippines; by
`Contemporanea de Ediciones for Venezuela; by
`Express Computer Distributors for the Caribbean and
`Wesl Indies; by Micronesia Media Distributor, Inc. for
`
`: THE PUBLISHER AND AUTHOR HAVE USED THEIR
`IT F
`D
`FWA
`BEST EFFORTS IN PREPARING THIS BOOK. THE PUBLISHER AND AUTHOR MAKE NO
`REPRESENTATIONS OR WARRANTIES WITH RESPECT TO THE ACCURACY 0R COMPLETENESS OF THE
`CONTENTS OF THIS BOOK AND SPECIFICALLY DISCLAIM ANY IMPLIED WARRANTIES OF
`MERCHANTABILITY OR FITNESS FOR A PARTICULAR PURPOSE. THERE ARE NO WARRANTIES WHICH
`EXTEND BEYOND THE DESCRIPTIONS CONTAINED IN THIS PARAGRAPH. NO WARRANTY MAY BE
`CREATED 0R EXTENDED BY SALES REPRESENTATIVES OR WRITTEN SALES MATERIALS. THE
`ACCURACY AND COMPLETENESS OF THE INFORMATION PROVIDED HEREIN AND THE OPINIONS
`STATED HEREIN ARE NOT GUARANTEED 0R WARRANTED TO PRODUCE ANY PARTICULAR RESULTS,
`AND THE ADVICE AND STRATEGIES CONTAINED HEREIN MAY NOT BE SUITABLE FOR EVERY
`INDIVIDUAL. 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. FULFILLMENT OF EACH COUPON OFFER IS THE
`
`For authorization to photocopy items for corporate,
`personal, or educational use, please contact
`Copyright Clearance Center, 222 Rosewood Drive,
`Danvers, MA 01923, or fax 978-750-4470.
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
` RESPONSIBILITY OF THE OFFEROR.
`
`
`Trademarks: All brand names and product names used in this book are trade names, service marks.
`trademarks, or registered trademarks of their respective owners. IDG Books Worldwide is not associated with
`.1;-
`any product or vendor mentioned in this book.
`a
`III
`1 is registered trademark or trademark under exclusive license
`to lDG Books Worldwide, Inc., from International Data Group, Inc.,
`in the United States and/or other countries
`
`.
`
`gunl-Il$3
`
`Ex. 1009 / Page 5 of 44
`
`Ex. 1009 / Page 5 of 44
`
`

`

`Credits
`
`Acquisitions Editor
`David Mayhew
`
`Development Editors
`Tracy Brown
`Valerie Perry
`Kenyon Brown
`
`Technical Editor
`Bill Karow
`
`Copy Editors
`Anne Friedman
`Bill McManus
`
`Cover Design
`Murder By Design
`
`Production
`Foster City Production Department
`
`Proofreading and Indexing
`York Production Services
`
`About the Author
`
`John Walkenbach is one of the country's leading authorities on spreadsheet
`software. He holds a Ph.D. from the University of Montana and has worked as an
`instructor, programmer, and market research manager. He finally found a job he’s
`good at: principal of JWalk and Associates Inc., a one-person San Diegoabased
`consulting firm that specializes in spreadsheet application development. John is
`also a shareware deveioper, and his most popular product is the award-winning
`Power Utility Pak add-in for Excel —which is used by thousands of people
`throughout the world. John started writing about spreadsheets in 1984, and he
`has since written more than .250 articies and reviews for publications such as
`PC World, InfoWorld, Windows magazine, and PC/Computing. In addition, he's
`the author of a dozen other spreadsheet books, including Excel 2000 Power
`Programming Techniques, Exce! Programming For Dummies, and Excei 2000 For
`Windows For Dummies Quick Reference (all from IDG Books Worldwide). In his
`spare time, John enjoys digital photography and composing and playing music in
`a variety of styles. .lohn also maintains “The Spreadsheet Page“ on the Web. Pay
`him avisit at http : //www . j -wai k. com.
`
`Ex. 1009 / Page 6 of 44
`
`Ex. 1009 / Page 6 of 44
`
`

`

`Preface
`
`
`
`I hanks for purchasing theMicrosof!Excel 2000Bible—your complete guide to a
`
`powerful and easy—to—use spreadsheet product.
`
`I think that Excel 2000 is the best spreadsheet program on the market (trust me—
`I’ve used them all). Excel has been around in various incarnations for almost a
`decade, and each subsequent release pushes the spreadsheet envelope a bit
`further—in some cases, a lot further. My goal in writing this book is to share with
`you some of what I know about Excel, and in the process make you more efficient
`on the job.
`‘
`
`The book contains everything that you need to know to learn the basics of Excel
`and then move on to more advanced topics at your own pace. You find many useful
`examples as well as some of the tips and slick techniques that I've accumulated
`over the years.
`
`Is This Book for You?
`
`The Bible series from lDG Books Worldwide is designed for beginning, intermediate,
`and advanced users. This book covers all the essential components of Excel and
`provides clear and practical examples that you can adapt to your own needs.
`
`Excel can be used at many levels —-—from the simple to the extremely complex. I’ve
`drawn a good balance here, focusing on the topics that are most useful to most
`users. The following answers whether this book is for you:
`
`Yes—If you have no spreadsheet experience
`
`If you’re new to the world of spreadsheets, welcome to the fold. This book has
`everything that you need to get started with Excel and then advance to other topics
`as the need arises.
`
`Yes — If you have used previous versions of Excel
`
`If you’ve used Excel 97, you’re going to feel right at home with Excel 2000. If you’re
`skipping a few upgrades and moving up from Excel 5 or Excel 95, you have lots to
`learn, because Microsoft has made many improvements in the past few years. In
`any case, this book can get you up to speed quickly.
`
`Ex. 1009 / Page 7 of 44
`
`Ex. 1009 / Page 7 of 44
`
`

`

`X
`
`Preface
`
`]
`
`Yes—If you have used Excel for the Macintosh
`
`The Macintosh versions of Excel are very similar to the Windows versions. If you’re
`moving over from the Mac platform, you’ll find some good background information
`as well as specific details to make your transition as smooth as possible.
`
`Yes—If you have used DOS versions of 1-2-3 or Quattro Pro
`
`lf you're abandoning a text-based spreadsheet such as 1-2-3 or Corel’s Quattro Pro
`in favor of a more modern graphical product, this book can serve you well. You
`have a head start, because you already know what spreadsheets are all about, and
`you’ll discover some great new ways of doing things.
`
`Yes—If you have used Windows versions of 1-2-3 or Quattro Pro
`
`If you’ve tried the other versions and are convinced that Excel is the way to go,
`this book quickly teaches you what Excel is all about and why it has such a great
`reputation. Because you're already familiar with Windows and spreadsheets, you
`can breeze through many introductory topics.
`‘
`
`Software Versions
`
`This book is written for Excel 2000 (also known as Excel 9), but much of the
`information also applies to Excel 97, Excel 95, and Excel 5. If you use any version of
`Excel prior to Version 5, be aware that the earlier versions are drastically different
`from the current version.
`
`Conventions Used in This Book
`
`Take a minute to scan this section to learn some of the typographical conventions
`that are used in this book.
`
`Named Ranges and Your Input
`lnput that you type from the keyboard appears in bold. Named ranges may appear
`in a code font. Lengthy input usually appears on a separate line. For instance, I may
`instruct you to enter a formula such as the following:
`
`=“Part Name:
`
`" &VLO0KUP(PartNumber.PartList,2)
`
`Ex. 1009 / Page 8 of 44
`
`Ex. 1009 / Page 8 of 44
`
`

`

`.t .—
`
`Preface
`
`X|
`
`Key Names
`Names of the keys on your keyboard appear in normal type. When two keys should
`be pressed simultaneously, they are connected with a plus sign, like this: Press
`Alt+E to select the Edit menu. Here are the key names as I refer to them in the book:
`
`Alt
`
`down arrow Num Lock
`
`right arrow
`
`Backspace
`
`End
`
`Caps Lock
`
`Home
`
`Ctrl
`
`Insert
`
`Pause
`
`PgDn
`
`PgUp
`
`Scroll Lock
`
`Shift
`
`Tab
`
`Delete
`
`left arrow
`
`Print Screen
`
`up arrow
`
`Functions
`Excel's built-in worksheet functions appear in uppercase, like this: Enter a SUM
`formula in cell C20.
`
`Mouse Conventlons
`
`I assume that you’re using a mouse or some other pointing device. You come across
`some of the following mouse-related terms:
`
`+ Mouse pointer: The small graphic figure that moves onscreen when you
`move your mouse. The mouse pointer is usually an arrow, but it changes
`shape when you move to certain areas of the screen or when you’re
`performing certain actions.
`
`+ Point: Move the mouse so that the mouse pointer is on a specific item. For
`example, “Point to the Save button on the toolbar.”
`
`+ Press: Press the left-mouse button once and keep it pressed. Normally, this is
`used when dragging.
`
`+ Click: Press the left mouse button once and release it immediately.
`
`+ Right-click: Press the right-mouse button once and release it immediately.
`The right-mouse button is used in Excel to pop up shortcut menus that are
`appropriate for whatever is currently selected.
`.
`.
`.
`.
`.
`+ Double-click: Press the left-mouse button tw1ce in rapid successmn. If your
`double-clicking doesn’t seem to be working, you can adjust the double-click
`sensitivity by using the Windows Control Panel icon.
`
`+ Drag: Press the left-mouse button and keep it pressed while you move the
`mouse. Dragging is often used to select a range of cells or to change the size
`of an object.
`
`fyou’re
`mation
`
`:ro Pro
`You
`
`”t! and
`
`, g0
`great
`3, you
`
`e e
`
`rsion of
`lifferent
`
`rentions
`
`'
`
`Y appear
`nce I may
`
`Ex. 1009 / Page 9 of 44
`
`Ex. 1009 / Page 9 of 44
`
`

`

`Preface
`
`What the Icons Mean
`
`Throughout the book. you see special graphic symbols. or icons, in the left margin.
`These call your attention to points that are particularly important or relevant to a
`specific group of readers. The icons in this book are as follows:
`
`56%;
`
`\
`
`This symbol denotes features that are new to Excel 2000.
`
`This icon signals the fact that something is important or worth noting. This may
`7 Note
`\ alert you to a concept that helps you to master the task at hand, or it may denote
`something that is fundamental to understanding subsequent material.
`
`Tip
`
`This icon marks a more efficient way of doing something that may not be obvious.
`
`21?I
`
`On tho:
`Cflfl
`
`This indicates that the material uses an example file located on this book’s com-
`panion CD-ROM.
`
`Caution
`
`I use this symbol when there is a possibility that the operation I’m describing
`could cause problems if you're not careful.
`
`-
`, Cross-
`'\ Heterenceh
`
`This icon indicates that a related topic is discussed elsewhere in this book.
`
`How This Book Is Organized
`Notice that the book is divided into six main parts, followed by four appendixes.
`
`Part E: Getting Started—This part consists of three chapters that provide
`background about Excel. Chapter 2 describes the new features in Excel 2000.
`Chapter 3 is a hands-on guided tour of Excel, which gives new users an opportunity
`to get their feet wet immediately.
`
`Part II: Introductory Concepts—The chapters in Part 11 cover the basic concepts
`with which all Excel users should be familiar.
`
`Part 111: Advanced Features—This part consists of six chapters dealing with
`topics that are sometimes considered advanced. Many beginning and intermediate
`users may find this information useful, as well.
`
`Part IV: Analyzing Data—The broad topic of data analysis is the focus of the
`chapters in Part IV. Users of all levels will find some of these chapters of interest.
`
` Ex. 1009 / Page 10 of 44
`
`Ex. 1009 / Page 10 of 44
`
`

`

`‘_ -
`
`Preface
`
`xiii
`
`nargin
`nt to a.
`
`is may
`jenote
`
`bvious.
`
`5 com-
`
`scribing
`
`1dixes.
`
`00'
`portunity
`
`:oncepts
`
`Vith _
`rmedlate
`
`f the
`nterest.
`
`Part V: Other Topics—This part consists of three chapters that didn’t quite fit into
`any other part. The chapters deal with using Excel with other applications, auditing
`and proofing your work, and exploring the fun side of Excel (yes, Excel does have a
`fun side).
`
`Part VI: Customizing Excel—— Part V1 is for those who want to customize Excel for
`their own use or who are designing workbooks or add-ins that are to be used by
`others. It’s a good introduction to Visual Basic for Applications (VBA).
`
`Appendixes—The appendixes consist of supplemental and reference material that
`may be useful to you.
`
`How to Use This Book
`
`This book is not intended to be read cover to cover. Rather, it's a reference book
`that you can consult when:
`
`+ You’re stuck while trying to do something.
`
`+ You need to do something that you’ve never done before.
`
`+ You have some time on your hands, and you’re interested in learning
`something new.
`
`The index is quite comprehensive, and each chapter typically focuses on a single
`broad topic. If you’re just starting out with Excel, I recommend that you read the
`first three chapters to gain a basic understanding of the product, and then do some
`experimenting on your own. After you've become familiar with Excel’s environment,
`you can refer to the chapters that interest you the most. Some users, however, may
`prefer to follow the chapters in order. Part II is designed with these users in mind.
`
`Don’t be discouraged if some of the material is over your head. Most users get by
`just fine using only a small subset of Excel’s total capabilities. In fact, the 80/20 rule
`applies here: 80 percent of Excel users use only 20 percent of its features. However,
`using only 20 percent of Excel's features still gives you lots of power at your
`fingertips.
`
`About the CD'ROM
`
`You’ll find that my writing style emphasizes examples. I know that I learn more
`from a well-thought-out example than from reading a dozen pages. We found that
`this is true for many other people. Consequently, I spent a lot of time developing the
`examples in this book. These example files are available on the companion CD—ROM.
`
`Appendix D describes the material on the CD-ROM.
`
`Ex. 1009 /Page 11 of 44
`
`Ex. 1009 / Page 11 of 44
`
`

`

`
`
`
`
`
`
`
`
`
`
`
`
`
`XiV
`
`Preface
`
`.
`
`Power Utility Pak Coupon
`Toward the back of the book. you‘ll find a coupon that you can redeem for a copy
`of my Power Utiiity Pak software—a collection of useful Excel utilities and new
`worksheet functions. This product normally sells for $39.95, but I'm making it
`available to readers of this book for only $9.95, plus shipping and handling. l
`developed this package by using VBA exclusively, and the complete source files
`are also available tor those who want to learn slick VBA techniques.
`
`I think that the Power Utility Pak is extremely useful in your day-to-day work with
`Excel, and I urge you to take advantage of this offer.
`
`Contacting the Author
`I’m always happy to hear from readers of my books. The best way to contact me is
`by e-mail at the following Internet address:
`
`john@j*walk.com
`
`I get lots of e—mail, so I can't promise a personal reply.
`
`Visit "The Spreadsheet Page"
`For even more information on Excel, be sure to check out “The Spreadsheet Page”
`on the World Wide Web. The URL is:
`
`http://www.j-wal k.com/ss/
`
`
`
`Ex. 1009 / Page 12 of 44
`
`Ex. 1009 / Page 12 of 44
`
`

`

`Contents at a Glance
`
`....300......IOOOOOOOOOOOOOI.......OOOOIOOOOIOOUQ
`
`Preface.1x
`Acknowledgments ..........................................................................................................xiii
`
`Part 1: Getting Started ........................................................................................ l
`Chapterl: ABit of Background...
`...3
`Chapter 2: What’5 New'1n Excel 2000?" ..........................................................................15
`Chapter3: Getting Acquainted with Excel"
`..21
`
`Part II: Introductory Concepts4|
`Chapter 4: Navigating Through Excel ............................................................................ 43
`Chapter 5: Working with Files and Workbooks71
`Chapter 6: Entering and Editing Worksheet Data93
`Chapter 7: Essential Spreadsheet Operations121
`Chapter 8: Working with Cell Ranges .......................................................................... 139
`Chapter 9: Creating and Using Formulas .................................................................... 169
`Chapter 10: Using Worksheet Functions .................................................................... 199
`Chapter 11: Worksheet Formatting .............................................................................. 235
`Chapter 12: Printing Your Work263
`Chapter 13: Chart—Making Basics ................................................................................ 283
`Chapter 14: Enhancing Your Work with Pictures and Drawings .............................. 317
`Chapter 15: Putting It All Together .............................................................................. 343
`
`Part III: Advanced Features .......................................................................... 361
`
`.363
`Chapter 16: Advanced Charting"
`Chapter 17: Creating Maps with Microsoft Map".........................................................405
`Chapter 18: Creating and Using Worksheet Outlines ................................................429
`Chapter 19: Linking and Consolidating Worksheets .................................................. 441
`Chapter 20: Creating and Using Array Formulas
`...................................................... 457
`Chapter 21: Using Excel in a Workgroup 479
`
`...............................................489
`Part IV: Analyzing Data
`Chapter 22: Importing Data from Other Sources ......................................................491
`Chapter 23: Working With Lists .................................................................................... 511
`Chapter 24. Using External Database Files
`...539
`Chapter 25: Analyzing Data with Pivot Tables
`...559
`Chapter 26. Performing Spreadsheet What-1f Analysis ............................................ 595
`Chapter 27. Analyzing Data Using Goal Seeking and Solver
`.................................... 617
`Chapter 28: Analyzing Data with Analysis ToolPak .................................................. 639
`
`Ex. 1009 / Page 13 of 44
`
`Ex. 1009 / Page 13 of 44
`
`

`

`XVIII
`
`Contents at a Glance
`
`........663
`.....
`.........
`.......
`..........
`.....
`Part V: Other Topics
`Chapter 29: Sharing Data with Other Applications 665
`Chapter 30: Excel and the Internet ..............................................................................685
`Chapter 31: Making Your Worksheets Error-Free ......................................................699
`Chapter 32: Fun Stuff
`.................................................................................................... 715
`
`.........729
`......................
`................
`Part VI: Customiling Excel
`Chapter 33: Customizing Toolbars and Menus 731
`Chapter 34: Using and Creating Templates 747
`Chapter 35: Using Visual Basic for Applications
`...................................................... 755
`Chapter 36: Creating Custom Worksheet Functions ................................................ 785
`Chapter 37: Creating Custom Dialog Boxes ................................................................ 801
`Chapter 38: Using Dialog Box Controls in Your Worksheet ...................................... 823
`Chapter 39: VBA Programming Examples ..................................................................839
`Chapter 40: Creating Custom Excel Add-Ins ..............................................................857
`
`Appendix A: Using Online Help: A Primer .................................................................. 871
`Appendix B: Worksheet Function Reference .............................................................. 881
`Appendix C: Excel’s Shortcut Keys .............................................................................. 897
`Appendix D: What‘s on the CD—ROM ............................................................................905
`Index ................................................................................................................................ 915
`End-User License Agreement ........................................................................................ 937
`CD-ROM Installation Instructions ................................................................................ 944
`
`Ex. 1009 / Page 14 of 44
`
`Ex. 1009 / Page 14 of 44
`
`

`

`A Bit of
`Background
`
` + 4+ §
`
`
`
`
`
`
`
`CHAP
`
`ER
`
`=
`
`I
`
`I
`
`I
`
`In This Chapter
`
`What Is E cel?
`
`x
`
`The Evolution of Excel
`
`_
`,
`Excel 5 Competitors
`
`What Excel Has to
`one.
`
`§
`
`§
`
`4
`
`§
`
`Every book has to start somewhere. This chapter starts
`
`from square one by introducing you to the concept of a
`spreadsheet. Also included is a lot of interesting background
`information about Excel and Windows.
`
`What Is Excel?
`
`Excel is a software product that falls into the general
`category of spreadsheets. Excel is one of several spreadsheet
`products that you can run on your PC. Others include 1-2-3
`and Quattro Pro.
`
`A spreadsheet (including Excel) is a highly interactive
`computer program that consists of a collection of rows and
`columns displayed onscreen in a scrollable window. The
`intersection of each row and column is called a cell, which
`can hold a number, a text string, or a formula that performs
`a calculation by using one or more other cells. Copying and
`moving cells and modifying formulas is easy with a
`spreadsheet.
`
`A spreadsheet can be saved in a file for later use or discarded
`after it has served its intended purpose. The cells in a
`spreadsheet can be formatted in various ways and printed for
`hard-copy reference. In addition, groups of numerical cells can
`be used to generate charts and maps.
`
`The most significant advantage of an electronic spreadsheet is
`that the formulas recalculate their results if you change any of
`the cells that they use. As a result, after you set up your
`spreadsheet by defining formulas, you can use this “model” to
`explore different possibilities, with very little additional effort.
`Excel is currently the best-selling Windows spreadsheet — and
`I hope to explain why in this book.
`
`Ex. 1009 / Page 15 of 44
`
`Ex. 1009 / Page 15 of 44
`
`

`

`
`
`
`ll‘lllllll
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`96
`
`Part II + Introductory Concepts
`
`Entering Values
`
`1
`
`Entering values into a cell is quite easy. Just move the cell pointer to the appropriate
`cell to make it the active cell, enter the value, and then press Enter. The value is dis—
`played in the cell and also appears in Excel’s formula bar. You can, of course, include
`decimal points and dollar signs when entering values and dollar signs, along with
`plus signs, minus signs, and commas. If you precede a value with a minus sign or
`enclose it in parentheses, Excel considers it to be a negative number.
`
`Note
`
`/.
`
`Sometimes, the value that you enter won’t be displayed exactly as you enter it.
`More specifically,
`if you enter a large number, it may be converted to scientific
`notation. Notice, however, that the formula bar displays the value that you entered
`originally. Excel simply reformatted the value so that it would fit into the cell. If you
`make the column wider, the number is displayed as you entered it.
`
`The section “Formatting values," later in this chapter, discusses the various ways to
`format values so that they appear differently.
`
`Entering Text
`
`Entering text into a cell is just as easy as entering a value: activate the cell, type the
`text, and then press Enter. A cell can contain a maximum of about 32,000 characters.
`To give you an idea of how much text can fit into a single cell, consider the fact that
`this entire chapter has approximately 32,000 characters.
`
`Caution
`
`Even though a cell can hold a huge number of characters, you'll find that it's not
`possible to actually display all of them.
`
`If you type an exceptionally long text entry into a cell, the characters appear to
`wrap around when they reach the right edge of the window, and the formula bar
`expands so that the text wraps around.
`
`What happens when you enter text that’s longer than its column’s current width?
`If the cells to the immediate right are blank, Excel displays the text in its entirety,
`appearing to spill the entry into adjacent cells. If an adjacent cell is not blank, Excel
`displays as much of the text as possible (the full text is contained in the cell; it's
`just not displayed). If you need to display a long text string in a cell that’s adjacent
`to a nonblank cell, you can take one of several actions:
`
`+ Edit your text to make it shorter
`+ Increase the width of the column
`
`§ Use a smaller font
`
`+ Wrap the text within the cell so that it occupies more than one line
`
`§ Use Excel’s “shrink to fit” option (see Chapter 11 for details)
`
`
`
`Ex. 1009 / Page 16 of 44
`
`Ex. 1009 / Page 16 of 44
`
`

`

`-E
`
`Chapter 10 + UsingWorksheet Functions
`
`23 '|
`
`56%;?
`
`\
`
`MAXA and MINA are new to Excel 2000. These functions work like MAX and MIN,
`respectively, but MAXA and MINA don’t ignore logical values and text.
`
`LARGE and SMALL
`
`The LARGE function returns the nth-largest value in a range. For example, to display
`the second-largest value in a range named Data, use the following formula:
`
`=LARGE(Data,2)
`
`The SMALL function works just as you would expect; it returns the nth-smallest
`value in a range.
`
`Database Functions
`
`Excel’s Database function category consists of a dozen functions that you use
`when working with database tables (also known as lists) stored in a worksheet.
`These functions all begin with the letter D, and they all have non—database equiva-
`lents. For example, the DSUM function is a special version of the SUM function
`that returns the sum of values in a database that meet a specified criterion. A
`database table is a rectangular range with field names in the top row. Each subse-
`quent row is considered a record in the database.
`
`To use a database function, you must specify a special criteria range in the
`worksheet. This type of criteria range is the same one that you use with Excel’s
`Data -:> Filter ED Advanced Filter command.
`
`.D This topic is discussed in Chapter 23.
`\ fiatawnca',‘
`The DSUM function calculates the sum of the values in a specified field, filtered
`by the criteria table. For example, to calculate the total sales for the North region,
`enter North under the Region field in the criteria range. Then, enter the following
`formula into any cell (this assumes that the database table is named Data and that
`the criteria range is named Criteria):
`
`=DSUM(Data,"Sales" ,Criteria)
`
`The formula returns the sum of the Sales field, but only for the records that meet
`the criteria in the range named Criteria. You can change the criteria, and the
`formula displays the new result. For example, to calculate the sales for January,
`enter Jan under the Month field in the Criteria range (and delete any other entries).
`
`If you want to use several DSUM formulas, you can have each of them refer to a
`different criteria range (you can use as many criteria ranges as you need).
`
`Excel’s other database functions work exactly like the DSUM function.
`
`ee
`
`ction
`1
`
`nu a
`
`ts
`
`ion
`33
`ange
`a
`
`.
`:
`
`-
`
`_
`
`,
`I
`'
`I
`
`!
`
`i
`
`Ex. 1009 / Page 17 of 44
`
`Ex. 1009 / Page 17 of 44
`
`

`

`Using External
`Database Files
`
`+
`
`c H
`
`P tails R._'
`
`.4
`
`In This Chapter
`
`Why Use External
`
`Database Files?
`Usin Quer .
`An Eiam ley.
`p
`Working with an
`External Data Range
`
`UsingMicrosofi
`Query Without
`Query Wizard
`
`0
`
`+
`
`§
`
`+
`
`I he preceding chapter described how to work with lists
`that are stored in a worksheet. Many users find that
`worksheet lists are sufficient for their data tracking. Others,
`however, choose to take advantage of Excel’s capability to
`access data that is stored in external database files. That's
`th t
`'
`fth'
`h
`.
`e oplco
`18C apter
`
`Why Use External Database Files?
`Accessing external database files from Excel is useful when you
`have the following situations:
`
`+ You need to work with a very large database.
`
`+ You share the database with others; that is, other users
`have access to the database and may need to work with
`the data at the same time.
`
`§ You want to work with only a subset of the data—data
`that meets certain criteria that you specify.
`
`+ The database is in a format that Excel can’t read.
`
`If you need to work with external databases, you may prefer
`Excel to other database programs. The advantage? After you
`bring the data into Excel, you can manipulate and format it by
`using familiar tools.
`
`As you may know, Excel can read some database files directly—
`specifically, those produced by various versions of dBASE (with
`a DBF extension). If the database has fewer than 65,535 records
`and no more than 255 fields, you can load the entire file into a
`worksheet, memory permitting. Even if you have enough mem-
`ory to load such a large file, however, Excel’s performance
`would likely be poor.
`
`In many cases, you may not be interested in all the records or
`fields in the file. Instead, you may want to bring in just the
`
`I on a
`d
`m
`y and
`1.
`'e com—
`n of
`
`_
`
`r
`
`Ex. 1009 / Page 18 of 44
`
`Ex. 1009 / Page 18 of 44
`
`

`

`
`
`
`
`'
`i
`‘
`
`i
`
`
`
`
`
`
`i
`
`I
`
`l
`
`|
`
`|
`
`i
`i
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`540
`
`Part IV + Analyzing Data
`
`data that meets certain criteria. In other words, you want to query the database and
`load into your worksheet a subset of the external database that meets the criteria.
`
`Excel makes this type of operation relatively easy.
`To perform queries using external databases, Microsoft Query must be installed on
`your system. If Query is not installed, you will be prompted to install it when you
`select the DataEDGet External DatacDCreate New Query command. You must
`rerun the Excel (or Microsoft Office) setup program and install Query.
`
`Note
`x-\
`
`In previous versions of Excel, using Microsoft Query required that you load an add-
`