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
`
`WORLDWIDE
`
`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 NormaS.A.for
`Guatemala; by Chips ComputadorasS.A. de C.V. for
`Mexico; by Editorial Norma de PanamaS.A.for
`Panama; by American Bookshopsfor Finland.
`‘ Authorized Sales Agent: Anthony Rudkin Associates
`for the Middle East and North Africa.
`For general information on IDG Books Worldwide’s
`booksin the U.S., please call our Consumer Customer
`Service departmentat 800-762-2974, Forreseller
`information, including discounts and premium sales,
`please call our Reseller Customer Service
`departmentat 800-434-3422.
`For information on where to purchase IDG Books
`Worldwide’s books outside the U.S., please contact
`ourInternational Sales departmentat 317-596-5530 or
`fax 317-596-5692.
`For consumerinformation on foreign language
`translations, please contact our Customer Service
`departmentat 800-434-3422,fax 317-596-5692, or
`email rights@idgbooks.com.
`For information onlicensing 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 orfor ordering examination
`copies, please contact our EducationalSales
`departmentat 800-434-2086or fax 317-596-5499.
`For press review copies, author interviews, or other
`publicity information, please contact our Public
`Relations departmentat 650-655-3000 or fax
`650-655-3299.
`
`For authorization to photocopy items for corporate,
`personal, or educationaluse, please contact
`Copyright Clearance Center, 222 RosewoodDrive,
`Danvers, MA 01923, or fax 978-750-4470.
`
`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. idgbooks.com (IDG Books Worldwide Website)
`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 ofthe publisher.
`Library of Congress Catalog Card Number: 98-75379 \
`ISBN:0-7645-3259-6
`~
`Printed in the United States of America
`10987654321
`1B/SU/QT/ZZ/FC
`Distributed in the United States by IDG Books
`Worldwide,Inc.
`Distributed by CDG Books CanadaInc. for Canada; by
`Transworld Publishers Limited in the United
`Kingdom;by IDG Norge Books for Norway; by IDG
`SwedenBooksfor Sweden; by WoodslanePty. Ltd. for
`Australia; by Woodslane (NZ) Ltd. for New Zealand;
`by TransQuest Publishers Pte Ltd. for Singapore,
`Malaysia, Thailand, Indonesia, and Hong Kong; by
`ICG Muse, Inc. for Japan; by Norma Comunicaciones
`S.A. for Colombia; by Intersoft for South Africa; by Le
`Monde en Tiquefor France; by International
`ThomsonPublishing 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
`Contemporaneade Ediciones for Venezuela; by
`Express Computer Distributors for the Caribbean and
`West Indies; by Micronesia Media Distributor,Inc. for
`
`: THE PUBLISHER AND AUTHOR HAVE USED THEIR
`IT OF
`D
`FWA
`BEST EFFORTS IN PREPARING THIS BOOK. THE PUBLISHER AND AUTHOR MAKE NO
`REPRESENTATIONS OR WARRANTIES WITH RESPECT TO THE ACCURACY OR COMPLETENESS OF THE
`CONTENTS OF THIS BOOK AND SPECIFICALLY DISCLAIM ANY IMPLIED WARRANTIES OF
`MERCHANTABILITY OR FITNESS FOR A PARTICULAR PURPOSE. THERE ARE NO WARRANTIES WHICH
`EXTEND BEYOND THE DESCRIPTIONS CONTAINED IN THIS PARAGRAPH. NO WARRANTY MAY BE
`CREATED OR 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 OR WARRANTED TO PRODUCE ANY PARTICULAR RESULTS,
`AND THE ADVICE AND STRATEGIES CONTAINED HEREIN MAY NOTBE SUITABLE FOR EVERY
`INDIVIDUAL. NEITHER THE PUBLISHER NOR AUTHORSHALL 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
`
` RESPONSIBILITY OF THE OFFEROR.
`
`Trademarks:All brand names and product namesusedin this book are trade names, service marks,
`trademarks, or registered trademarks oftheir respective owners. IDG Books Worldwideis not associated with
`Lp
`any product or vendor mentionedin this book.
`—ia— ©
`
`,
`
`|
`\V 1S‘)
`Pisa
`
`i B
`
`
`is registered trademark or trademark under exclusive license
`to IDG Books Worldwide,Inc., from International/Data Group,Inc.,
`IDG in the United States and/or other countries
`
`OOKS
`
`Ex. 1009 / Page 5 of 44
`
`Ex. 1009 / Page 5 of 44
`
`

`

`Credits
`Acquisitions Editor
`David Mayhew
`
`DevelopmentEditors
`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 Walkenbachis 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.Hefinally found a job he’s
`good at: principal of JWalk and AssociatesInc., a one-person San Diego-based
`consulting firm that specializes in spreadsheet application development. John is
`also a shareware developer, and his most popular productis the award-winning
`PowerUtility Pak add-in for Excel—which is used by thousandsof people
`throughout the world, John started writing about spreadsheetsin 1984, and he
`has since written more than 250 articles 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, Excel Programming For Dummies, and Excel 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. John also maintains “The Spreadsheet Page” on the Web.Pay
`him a visit at http://www. j-walk.com.
`
`Ex. 1009 / Page 6 of 44
`
`Ex. 1009 / Page 6 of 44
`
`

`

`Preface
`
`
`
`I hanks for purchasing theMicrosoftExcel2000Bible—yourcomplete guideto 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 envelopea bit
`further —in somecases, a /ot further. My goalin writing this book is to share with
`you someof what I know about Excel, and in the process make you moreefficient
`on the job.
`‘
`
`The book contains everything that you need to knowto learn the basics of Excel
`and then move on to more advancedtopics at your own pace. You find many useful
`examples as well as someofthe tips and slick techniques that I’ve accumulated
`overthe years.
`
`Is This Book for You?
`
`The Bible series from IDG Books Worldwideis designed for beginning, intermediate,
`and advancedusers. This bookcovers 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 balancehere, focusing on the topics that are most useful to most
`users. The following answers whetherthis bookis for you:
`
`Yes—If you have no spreadsheet experience
`
`If you’re new to the world of spreadsheets, welcometo the fold. This book has
`everything that you need to get started with Excel and then advanceto othertopics
`as the needarises.
`
`Yes — If you have used previousversions 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 havelots to
`learn, because Microsoft has made many improvementsin 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
`
`

`

`xX
`
`Preface
`
`7
`
`Yes — If you have used Excel for the Macintosh
`
`The Macintoshversions of Excel are very similar to the Windowsversions. If you’re
`moving over from the Macplatform, you'll find some good background information
`as well as specific details to make yourtransition as smoothaspossible.
`
`Yes— If you have used DOSversionsof 1-2-3 or Quattro Pro
`
`If you’re abandoning a text-based spreadsheet suchas 1-2-3 or Corel’s Quattro Pro
`in favor of a more moderngraphical product, this book can serve you well. You
`have a head start, because you already know whatspreadsheetsareall about, and
`you'll discover somegreat new waysof doing things.
`
`Yes — If you have used Windowsversionsof 1-2-3 or Quattro Pro
`
`If you've tried the other versions and are convinced that Excelis the wayto go,
`this book quickly teaches you what Excelis all about and whyit has such a great
`reputation. Because you're already familiar with Windows and spreadsheets, you
`can breeze through manyintroductorytopics.
`
`Software Versions
`
`This bookis written for Excel 2000 (also known as Excel 9), but much of the
`information also applies to Excel 97, Excel 95, and Excel5. If you use any version of
`Excel prior to Version 5, be awarethat the earlier versionsare drastically different
`from the current version.
`
`Conventions Used in This Book
`
`Take a minute to scan this section to learn someof the typographical conventions
`that are used in this book.
`
`Named Rangesand Your Input
`Input that you type from the keyboard appears in bold. Named ranges may appear
`in a code font. Lengthy input usually appears on a separateline. For instance, I may
`instruct you to enter a formula suchasthefollowing:
`
`="Part Name:
`
`“ &VLOOKUP(PartNumber,PartList,2)
`
`Ex. 1009 / Page 8 of 44
`
`Ex. 1009 / Page 8 of 44
`
`

`

`\a
`
`Preface
`
`xi
`
`Key Names
`Namesof the keys on your keyboard appear in normal type. When two keys should
`be pressed simultaneously, they are connected with a plussign, like this: Press
`AIt+E to select the Edit menu. Here are the key namesas | refer to them in the book:
`
`Alt
`
`down arrow Num Lock
`
`right arrow
`
`Backspace
`
`End
`
`Pause
`
`Scroll Lock
`
`Caps Lock
`Ctrl
`Delete
`
`Home
`Insert
`left arrow
`
`PgDn
`PgUp
`Print Screen
`
`Shift
`Tab
`up arrow
`
`Functions
`Excel's built-in worksheet functions appear in uppercase,like this: Enter a SUM
`formula in cell C20.
`
`Mouse Conventions
`
`I assume that you’re using a mouse or someother pointing device. You comeacross
`someof the following mouse-related terms:
`
`+ Mousepointer: The small graphic figure that moves onscreen when you
`move your mouse. The mousepointeris usually an arrow,but it changes
`shape when you moveto certain areas of the screen or when you're
`performing certain actions.
`
`+ Point: Move the mousesothat the mousepointeris 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 andreleaseit immediately.
`The right-mousebuttonis used in Excel to pop up shortcut menusthat are
`appropriate for whateveris currently selected.
`‘
`wos
`.
`.
`+ Double-click: Press the left-mouse button twice in rapid succession.If your
`double-clicking doesn’t seem to be working, you can adjust the double-click
`sensitivity by using the Windows Control Panelicon.
`
`+ 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 changethe size
`of an object.
`
`f you're
`mation
`
`ro Pro
`You
`ut, and
`
`go
`great
`s, you
`
`e a
`
`rsion of
`lifferent
`
`ventions
`
`y appear
`nce,
`I may
`
`Ex. 1009 / Page 9 of 44
`
`Ex. 1009 / Page 9 of 44
`
`

`

`Xii
`
`Preface
`
`What the Icons Mean
`Throughoutthe book, you see special graphic symbols,oricons, in the left margin.
`Thesecall your attention to points that are particularly important or relevant to a
`specific group of readers. The icons in this book are as follows:
`FOOD This symbol denotesfeatures that are new to Excel 2000.
`
`This icon signals the fact that something is important or worth noting. This may
`alert you to a concept that helps you to master the task at hand, or it may denote
`somethingthatis fundamental to understanding subsequent material.
`This icon marks a moreefficient way of doing something that may not be obvious.
`
`“Note
`~~
`
`Tip
`
`aon,,
`
`On the
`This indicates that the material uses an examplefile located on this book's com-
`cae panion CD-ROM.
`
`Caution
`
`| use this symbol whenthere is a possibility that the operation I'm describing
`could cause problemsif you're not careful.
`
`ca This icon indicates thata related topic is discussed elsewherein this book.
`Reference
`
`How This Book Is Organized
`Notice that the book is divided into six main parts, followed by four appendixes.
`
`Part I: 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.
`PartIl: Introductory Concepts — The chaptersin Part Il cover the basic concepts
`with whichall Excel users should be familiar.
`
`Part Ill: Advanced Features— This part consists of six chapters dealing with
`topics that are sometimes considered advanced. Many beginning and intermediate
`users mayfind this information useful, as well.
`Part IV: Analyzing Data— The broadtopic of data analysis is the focus of the
`chaptersin PartIV. Usersofall levels will find someof these chaptersofinterest.
`
`Ex. 1009 / Page 10 of 44
`
`Ex. 1009 / Page 10 of 44
`
`

`

`
`
`y Preface—Xi
`
`nargin.
`at toa
`
`.
`k Note
`
`enote
`
`bvious.
`
`s com-
`
`scribing
`
`idixes.
`
`00.
`portunity
`
`oncepts
`
`vith .
`rmediate
`
`f the
`nterest.
`
`Part V: Other Topics — This part consists of three chapters that didn’t quite fit into
`any otherpart. 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 VI 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 usefulto you.
`
`How to Use This Book
`
`This bookis not intended to be read coverto 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 chaptertypically focuses on a single
`broad topic. If you’re just starting out with Excel, I recommendthat 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 becomefamiliar with Excel’s environment,
`you can refer to the chapters that interest you the most. Some users, however, may
`prefer to follow the chaptersin order. Part Il is designed with these users in mind.
`
`Don't be discouragedif some of the material is over your head. Mostusers 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 percentofits features. However,
`using only 20 percentof Excel's featuresstill gives you /ots of power at your
`fingertips.
`
`About the CD-ROM
`
`You'll find that my writing style emphasizes examples. I knowthat I learn more
`from a well-thought-out example than from reading a dozen pages.I’ve 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
`
`\
`
`PowerUtility Pak Coupon
`Toward the backof the book, you'll find a coupon that you can redeem for a copy
`of my PowerUtility Pak software—a collection of useful Excel utilities and new
`worksheet functions. This product normally sells for $39.95, but I'm makingit
`available to readersof this book for only $9.95, plus shipping and handling.I
`developed this package by using VBAexclusively, and the complete source files
`are also available for those who wantto learnslick 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 happyto hearfrom readers of my books. The best way to contact meis
`by e-mailat the following Internet address:
`
`john@j-walk.com
`
`I get lots of e-mail, so I can’t promise a personalreply.
`
`
`
`
`
`
`
`
`
`
`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-walk.com/ss/
`
`Ex. 1009 / Page 12 of 44
`
`Ex. 1009 / Page 12 of 44
`
`

`

`Contents at a Glance
`
`eoeovueucescecoeosvococgzposescogcoeoeeooeceeaecseeoaceaeseseeeoeoeoseoseoseeesae
`
`Preface..
`asiawasuwisstegheGaavevuvesueis aueateeiaevestseuiandavacavecesVbuniekonus svaveavastaiscsabevsivbidsanseensier ives
`AcknowledgmentsDoisssesveesuuesuvesvussueusesveuuusevesvetecuvevunseessesserevevsvuessvessssuressnnseevucseneessesyievyexiii
`
`Part 1: Getting Started ..esccssssssssssssserssscsssnsssnssssssonssnsesssssssetsesserseanesrseesensestneesnnts 1
`Chapter 1: A Bit of Background...
`sesecesaeeaeeseuaceceseeceesaeencessceseeeeeseaseacensersenersteaseneraneeee
`Chapter 2: What’s New in Excel 2000?gaiaHeHorscdmayeeeyayStatorsmagesseeeratmereetarieelbertaery“15
`Chapter3: Getting Acquainted with Excel.. NRE wd]
`
`Part Il: Introductory CONCEPES.caescssssessssesssversnnessneensosersnuerssssersssnnersanersessnessaeGl L
`Chapter 4: Navigating Through Excel ......cccccssssesssseseeseceesssessesesseseseesesecsesesseseeeessaeens43
`Chapter 5: Working with Files and WorkbDOOKkS ........cccccssssessessesessesessecsscsscecsecsseeeserened L
`Chapter6: Entering and Editing Worksheet Data oo... ieccesessesseteeeesreteeteesnereeteneeee Do
`Chapter 7: Essential Spreadsheet Operations 00... ccccccccsceetsessetsesettessesetesseneeeee Lol
`Chapter 8: Working with Cell Ranges .o...c.ccccsscsssssesssesssssesssesessecscscsessesnectecsseessecens 139
`Chapter 9: Creating and Using Formulas w...ccccccccccccccesesceecsesscesseecssesccseceecseseesnecaes 169
`Chapter 10: Using Worksheet Functions ....ccccccccccecsseseceseesecsseeeseneesssaessecsessennesies 199
`Chapter 11: Worksheet Formatting .0....... ccc csecseesceseeceseseeeeeeesecsesenetecesseeteneserens235
`Chapter 12: Printing YOUr WOrk wocceccecccseesesseceeceescesesessssesecesneessasssssusssesessessetssseeeneeDOO
`Chapter 13: Chart-Making Basics w.cccssssescsesscssscesseesesssssesssesseesesseeeseeaeersssseneeneeess283
`Chapter 14: Enhancing Your Work with Pictures and Drawings ......ccceceesseseeeeees 317
`Chapter 15: Putting It All Together .,.........cccccessesseestessecescceeceeseesscceessseesessasearenseanseeees343
`
`Part Il: Advanced Features ...ssccsosssssssesesssssseereersersseresnnessentessnntesneesnssestnness361
`
`Chapter 16: Advanced Charting ..
`ss suyinkd a eaawansscenevanyenss aayententiis
`303
`Chapter 17: Creating Maps with MicrosoftMap.tesesenseesacssessesasenestsacaesancuacsrenseseeeteners405
`Chapter 18: Creating and Using Worksheet Outlines ............csecsscseesaceeseeserenenseeenees429
`Chapter 19: Linking and Consolidating Worksheets.........::.::ssssssssrssesersesseneeeestreenenss441
`Chapter 20: Creating and Using Array Formulas.
`...........:c0:ce:essecsessseseseseeesessesestereeress457
`Chapter 21: Using Excel in a Workgroup .........::scs-ecseseessseesreseeceeesseneneseessentarestiseeesere419
`
`Part IV: Amalyzing Datel ......:sssssssssssssssessmessessuseenseesesneesnneesenuneesssnnessnuneesanees489
`Chapter 22: Importing Data from Other SOUrCES u....ccesesesssssenssecssseeeneccsssceeseacecaeeess491
`Chapter 23: Working With Lists ..........ccccccssssesseseecscsccessseseesencaceesseseasssseneesenceceessennenes511
`Chapter 24: Using External Database Files ....c.cc.cceccccscssssseessnesenencseneseneecenens
`.-D39
`Chapter 25: Analyzing Data with Pivot Tables o.....c..ccsccssscsscscsesssesesessessenees
`.-DO9
`Chapter 26: Performing Spreadsheet What-lf Analysis sceeinetieaecsiandieileEne.595
`Chapter 27: Analyzing Data Using Goal Seeking and Solver
`....cccccscscssssesseessssseeeerere617
`Chapter 28: Analyzing Data with Analysis ToOlPak .........ccccccccssssssescsesescssseseseecsesenens639
`
`Ex. 1009 / Page 13 of 44
`
`Ex. 1009 / Page 13 of 44
`
`

`

`XVII
`
`Contents at a Glance
`
`Part V: Other Topics...aeesueeaneanecusensesnsesneesnsatsaneeraneconeranseensannesarsOO
`Chapter 29: Sharing Data with Other Applications .........::ssssssssesrssseeseeersereeee e+ 665
`Chapter 30: Excel and the Internet .....c.c.cscscssessssssesseseeesreeeeesseeseetesesnenenetenerseneenassasecens685
`Chapter 31: Making Your Worksheets Error-Free.
`......:.ccsceeeeeseeeeeteeeeeneneseeeaesteneeees699
`Chapter 32: Fun Stuff
`..ccecceecccescsescsecsesecseseneserseenteessrensseeeseeneseeneseenseneeeeneneenesensy 715
`
`Part VI: Customizing Excel ...ssssssssesssncseseanneemermveenennnnettinalasereeverseeserss129)
`Chapter 33: Customizing Toolbars and MeMUuS ........ccssssssssssssssersestieesnsestesneeateeenee lo]
`Chapter 34: Using and Creating Templates .........cessscsscssesssesseessesneesssessesseessesstesneanenne FAT
`Chapter35: Using Visual Basic for Applications
`.o.cccccseeseeeeteetteceseseeesesesaetenens 755
`Chapter 36: Creating Custom Worksheet FUNCtIONS wien teeeeeeneeeeneeeentes 785
`Chapter 37: Creating Custom Dialog BOXES .i...ceccccscseesssensensneesereeretstseecarsenneeneeneenes801
`Chapter 38: Using Dialog Box Controls in Your Worksheet .....ccssssesssenreeenerenrrseees823
`Chapter 39: VBA Programming Examples ........scssssssseseneneseerersesrsnenensersstenecenssnseerans839
`Chapter 40: Creating Custom Excel AdC-INS ........:sessseseneseneeeneneenenensaereersnsnnnenenensens857
`
`Appendix A: Using Online Help: A Primer ..........ecssessesesessereretereeseneneeserenseetesenssennnees871
`Appendix B: WorksheetFunction Reference .........:ccsssscssecsenseceeessrneeeeseeeseneereneeres881
`Appendix C: Excel’s Shortcut KeyS ......ccccsesseesessseseeeeessensseneanensteneneessensesnenaneansseneres897
`Appendix D: What’s on the CD-ROM .........ccccssseseseenetssseesenerenseeteneeneneaseenssenseecseees905
`INCOX voeeeeeceeessccssesscsscssceseesteseceesessscsesscsscesevscsesseseesenessneoesssssseaesesssssseeaaseessesseneeceeenennenseees915
`End-User License Agreement.....ccceccsssseerseeesseresseeesesseesessnesetessaevsgeenenseessteeereeeeeys937
`CD-ROM Installation IMstructions ......ccccesesesesseesseececessussreeensesseesesseesterseeetanessageneeees944
`
`Ex. 1009 / Page 14 of 44
`
`Ex. 1009 / Page 14 of 44
`
`

`

`A Bit of
`Background
`
`
`
` + ++ +
`
`CHAP
`
`ER
`
`
`
`
`
`E..bookhas to start somewhere. This chapterstarts
`
`from square one by introducing you to the conceptof a
`spreadsheet.Also includedisa lot of interesting background
`information about Excel and Windows.
`
`In This Chapter
`
`a
`
`WhatIs Excel?
`
`The Evolution of Excel
`;
`.
`Excel’s Competitors
`
`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 yourPC. Others include 1-2-3
`and Quattro Pro.
`
`WhatExcel Has to
`Offer
`tee?
`
`+
`
`+
`
`:
`
`A spreadsheet(including Excel) is a highly interactive
`computer program thatconsists of a collection of rows and
`columnsdisplayed onscreenin a scrollable window. The
`intersection of each row and columnis called a cell, which
`can hold a number,a text string, or a formula that performs
`a calculation by using one or more othercells. Copying and
`moving cells and modifying formulas is easy with a
`spreadsheet.
`
`A spreadsheet can besavedin a file for later use or discarded
`after it has served its intended purpose. Thecells in a
`spreadsheet can be formatted in various ways and printed for
`hard-copyreference. In addition, groups of numerical cells can
`be used to generate charts and maps.
`
`The mostsignificant advantage of an electronic spreadsheetis
`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 usethis “model” to
`explore different possibilities, with very little additionaleffort.
`Excel is currently the best-selling Windows spreadsheet — and
`I hope to explain whyin this book.
`
`Ex. 1009 / Page 15 of 44
`
`Ex. 1009 / Page 15 of 44
`
`

`

`
`
`
`yin
`
`96
`
`Part 1! + IntroductoryConcepts
`
`Entering Values
`
`]
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`Entering values into a cell is quite easy. Just move thecell pointer to the appropriate
`cell to makeit the active cell, enter the value, and then press Enter. The valueis 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, minussigns, and commas. If you precede a value with a minus sign or
`encloseit in parentheses, Excel considers it to be a negative number.
`
`Note
`
`/
`
`Sometimes, the value that you enter won't be displayed exactly as you enterit.
`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 thatit would fit into the cell. If you
`make the column wider, the numberis displayed as you enteredit.
`
`The section “Formatting values,” later in this chapter, discusses the various ways to
`format values so that they appeardifferently.
`
`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 about32,000 characters.
`To give you an idea of how muchtext canfit into a single cell, consider the fact that
`this entire chapter has approximately 32,000 characters.
`
`Caution
`
`Even thougha cell can hold a huge numberof 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 whentheyreach the right edge of the window,and the formula bar
`expandsso that the text wraps around.
`
`What happens whenyou entertext 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,
`appearingto spill the entry into adjacentcells. If an adjacentcell is not blank, Excel
`displays as muchof the text as possible (the full text is contained in thecell; it’s
`just not displayed). If you need to display a long text string in a cell that’s adjacent
`to anonblankcell, you can take one of several actions:
`
`
`
`
`
`
`
`
`
`
`
`
`# Wrapthetext within the cell so that it occupies more than oneline
`
`
`+ Edit your text to makeit shorter
`+ Increase the width of the column
`
`+ Use a smaller font
`
`+ Use Excel’s “shrinkto fit” option (see Chapter 11 for details) Ex. 1009 / Page 16 of 44
`
`Ex. 1009 / Page 16 of 44
`
`

`

`w
`
`Chapter 10 # UsingWorksheetFunctions
`
`2%]
`
`nae \\”
`
`MAXA and MINAare new to Excel 2000. These functions work like MAX and MIN,
`
`respectively, but MAXA and MINA don't ignore logical values andtext.
`
`LARGE and SMALL
`The LARGEfunction returns the nth-largest value in a range. For example,to display
`the second-largest value in a range namedData, use the following formula:
`
`=LARGE( Data, 2)
`
`The SMALLfunction worksjust 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 knownaslists) 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 meeta specified criterion. A
`database table is a rectangular range with field names in the top row. Each subse-
`quentrow 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 > Advanced Filter command.
`
`‘in Thistopic is discussed in Chapter 23.
`| Reference\\
`
`The DSUM function calculates the sum of the valuesin a specified field, filtered
`by the criteria table. For example, to calculate the total sales for the North region,
`enter North under the Regionfield in the criteria range. Then, enter the following
`formula into any cell (this assumes that the databasetable is named Data and that
`the criteria range is namedCriteria):
`
`|
`
`=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 changethecriteria, and the
`formula displays the newresult. For example, to calculate the sales for January,
`enter Jan under the Monthfield in the Criteria range (and delete any other entries).
`
`If you want to use several DSUM formulas, you can have eachof them refer to a
`different criteria range (you can use as manycriteria ranges as you need).
`
`Excel’s other database functions workexactly like the DSUM function.
`
`ee
`
`ction
`
`lnula
`
`ts
`
`ion
`4g
`ange
`a
`
`Ex. 1009 / Page 17 of 44
`
`Ex. 1009 / Page 17 of 44
`
`

`

`Using External
`DatabaseFiles
`
`Cu
`
`P WE RU
`
`Pim
`
`i
`
`
`
`
`
`
`
` + ++ +
`
`In This Chapter
`
`WhyUse External
`
`Database Files?
`
`Using Query:
`An Example
`
`Working with an
`External Data Range
`
`Using Microsof
`
`Query Without
`Query Wizard
`
`+
`
`+
`
`+
`
`+
`
`I
`CL a
`in
`y and
`.
`‘e com-
`nof
`
`|
`
`:
`
`(
`
`Hy he preceding chapter described how to workwithlists
`
`that are stored in a worksheet. Manyusersfind that
`worksheetlists are sufficient for their data tracking. Others,
`however, chooseto take advantage of Excel’s capability to
`access data that is stored in external database files. That’s
`the topic of this chapter.
`
`Why Use External DatabaseFiles?
`
`Accessing external databasefiles from Excel is useful when you
`havethe following situations:
`
`+ You need to work with a very large database.
`+ You share the database with others; that is, other users
`have accessto the database and may need to work with
`the data at the sametime.
`
`+ You want to workwith only a subset of the data— data
`that meets certain criteria that you specify.
`+ The databaseis in a format that Excel can’t read.
`
`If you need to workwith external databases, you may prefer
`Excel to other database programs. The advantage? After you
`bring the data into Excel, you can manipulate and formatit by
`using familiar tools.
`
`As you may know, Excel can read somedatabasefiles directly —
`specifically, those producedby various versions of dBASE (with
`a DBFextension). If the database has fewer than 65,535 records
`and no more than 255fields, you can load the entire file into a
`worksheet, memory permitting. Even if you have enough mem-
`ory to load sucha largefile, however, Excel’s performance
`wouldlikely be poor.
`
`In many cases, you maynotbeinterestedin all the records or
`fields in thefile. Instead, you may wantto bring in just the
`
`Ex. 1009 / Page 18 of 44
`
`Ex. 1009 / Page 18 of 44
`
`

`

`
`
`
`
`
`
`
`
`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 thecriteria.
`Excel makesthis type of operation relatively easy.
`
`Note
`
`SS
`
`To perform queries using external databases, Microsoft Query mustbeinstalled on
`your system. If Query is notinstalled, you will be prompted toinstall it when y