Excel 2007 Pocket Guide: A Quick Reference to Common Tasks

173 Pages·2007·1.682 MB·English

by  Curtis D. Frye

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: [phone] [e-mail].
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