Excel 2007 Pocket Guide: A Quick Reference to Common Tasks PDF
Preview Excel 2007 Pocket Guide: A Quick Reference to Common Tasks
Excel 2007 Pocket Guide SECOND EDITION Excel 2007 Pocket Guide Curt Frye Beijing • Cambridge • Farnham • Köln • Paris • Sebastopol • Taipei • Tokyo Excel 2007 Pocket Guide, Second Edition by Curt Frye Copyright © 2008 Curt Frye. All rights reserved. Printed in Canada. Published by O’Reilly Media, Inc., 1005 Gravenstein Highway North, Sebastopol, CA 95472. O’Reilly books may be purchased for educational, business, or sales promotional use. Online editions are also available for most titles (safari.oreilly.com). For more information, contact our corporate/ institutional sales department: (800) 998-9938 [email protected]. Editor: Simon St.Laurent Indexer: Ellen Troutman Zaig Production Editor: Cover Designer: Karen Montgomery Marlowe Shaeffer Interior Designer: David Futato Proofreader: Marlowe Shaeffer Illustrator: Jessamyn Read Printing History: September 2003: First Edition. October 2007: Second Edition. NutshellHandbook,theNutshellHandbooklogo,andtheO’Reillylogoare registered trademarks of O’Reilly Media, Inc. ThePocket Guide series designations,Excel2007PocketGuide,theimageofanortherngannet,and related trade dress are trademarks of O’Reilly Media, Inc. Many of the designations used by manufacturers and sellers to distinguish theirproductsareclaimedastrademarks.Wherethosedesignationsappear in this book, and O’Reilly Media, Inc. was aware of a trademark claim, the designations have been printed in caps or initial caps. While every precaution has been taken in the preparation of this book, the publisherandauthorassumenoresponsibilityforerrorsoromissions,orfor damages resulting from the use of the information contained herein. ISBN-10: 0-596-51452-2 ISBN-13: 978-0-596-51452-5 [TM] Contents Chapter1: UnderstandingExcel 1 What’s New in Excel 2007 1 The Excel 2007 Interface 2 The Anatomy of an Excel File 14 Formatting 17 Shortcut Menus and the Mini Toolbar 25 How Excel Tries to Help 28 Chapter2: ExcelTasks 31 Working with Files 32 Printing 35 Moving Around in a Workbook or Worksheet 40 Manipulating Workbooks and Worksheets 43 Manipulating Rows, Columns, and Cells 45 Entering and Editing Data 48 Formatting 61 Working with Hyperlinks 70 Working with Headers and Footers 72 Summarizing Data 75 Using Named Ranges 79 Defining Alternative Data Sets 80 Controlling How Data Is Displayed 82 Protecting All or Part of a Workbook 87 v Spelling and Other Tools 88 Customizing Excel 91 Collaborating 94 Working with the Web 98 Summarizing Data with Charts 99 Analyzing Data with PivotTables and PivotCharts 101 Chapter3: FormulaReference 105 New Formulas in Excel 2007 105 Math Formulas 106 Date and Time Formulas 114 Financial Formulas 116 Lookup and Reference Formulas 121 Logical Formulas 125 Text Formulas 126 Statistical Formulas 128 Chapter4: Excel Reference 138 Command Reference 139 Native Formats 140 Startup Switches 141 Wildcards in Filters and Searches 142 Default File Locations 143 Keyboard Shortcuts 143 Chapter5: Excel Resources 152 Internet Sites 152 Books 153 Excel Utilities 154 Index 157 vi | Contents CCHHAAPPTTEERR 11 Understanding Excel Thefirstchapterofthisbookisdesignedtogivethoseread- ers with relatively little experience using Excel 2007 enough information to dive right in and start creating rich Excel workbooksimmediately,andtofillinsomeofthedetailsfor moreexperiencedExceluserswhohaven’thadenoughtime to examine the program and its new user interface in depth. This chapter covers: • What’s new in Excel 2007 • The Excel interface • Workbook, template, and workspace files • The anatomy of an Excel file • Formatting • Shortcut Menus and the Mini Toolbar (a.k.a. the “floatie”) What’s New in Excel 2007 AfterthesignificantleapforwardfromExcel95toExcel97, Excel versions 2000, 2002, and 2003 were incremental improvementsonthesamebasicapplicationdesign.Bycon- trast,Excel2007isasubstantialdeparturefromExcel2003. Excel2007comeswithanewuserinterface(detailedlaterin this chapter), a much larger worksheet, and new formatting capabilities, among many other changes. Table1-1 summa- rizes the most important changes in Excel 2007. 1 Table1-1.Excel2007greatlyexpandsthesizeoftheworksheetand extends the possible number of sorting levels, characters in a cell, and colors in a workbook. Capability Old limit New limit Worksheet size 65,536 rows by 256 1,048,576 rows by 16,384 columns columns Total characters in a cell 1,024 characters 32,767 characters Total characters printed in 1,024 characters 32,767 characters a cell Colors in a workbook 56 16 million colors Undo levels 16 100 Sort levels 3 64 Conditional format 3 Limited by available conditions memory Maximum length of a 1,024 characters 8,192 characters formula Maximum number of 30 255 arguments to a function Number of nested levels 7 64 allowed in a formula NOTE Thenewlimitsonlyapplywhenyousaveaworkbookus- ingthenewOffice2007fileformat.Ifyousaveawork- book using the old Excel 97–2003 file format, the old limits apply. The Excel 2007 Interface Excel’s interface stayed more or less constant from Excel 97 to Excel 2003. During that time, the program’s developers added some new features and moved items around in the menu and toolbar system, but the basic structure remained the same. All that changed in Office 2007. 2 | Chapter 1: Understanding Excel
The list of books you might like
