®
Excel 2007
FOR
DUMmIES
‰
QUICK REFERENCE
by John Walkenbach and Colin Banfield
®
Excel 2007
FOR
DUMmIES
‰
QUICK REFERENCE
by John Walkenbach and Colin Banfield
Excel® 2007 For Dummies® Quick Reference
Published by
Wiley Publishing, Inc.
111 River Street
Hoboken, NJ 07030-5774
www.wiley.com
Copyright © 2007 by Wiley Publishing, Inc., Indianapolis, Indiana
Published by Wiley Publishing, Inc., Indianapolis, Indiana
Published simultaneously in Canada
No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or by any means,
electronic, mechanical, photocopying, recording, scanning or otherwise, except as permitted under Sections 107 or 108 of
the 1976 United States Copyright Act, without either the prior written permission of the Publisher, or authorization through
payment of the appropriate per-copy fee to the Copyright Clearance Center, 222 Rosewood Drive, Danvers, MA 01923, (978)
750-8400, fax (978) 646-8600. Requests to the Publisher for permission should be addressed to the Legal Department, Wiley
Publishing, Inc., 10475 Crosspoint Blvd., Indianapolis, IN 46256, (317) 572-3447, fax (317) 572-4355, or online at http://
www.wiley.com/go/permissions.
Trademarks: Wiley, the Wiley Publishing logo, For Dummies, the Dummies Man logo, A Reference for the Rest of Us!, The
Dummies Way, Dummies Daily, The Fun and Easy Way, Dummies.com, and related trade dress are trademarks or registered
trademarks of John Wiley & Sons, Inc. and/or its affiliates in the United States and other countries, and may not be used
without written permission. Excel is a registered trademark of Microsoft Corporation in the United States and/or other
countries. All other trademarks are the property of their respective owners. Wiley Publishing, Inc., is not associated with
any product or vendor mentioned in this book.
LIMIT OF LIABILITY/DISCLAIMER OF WARRANTY: THE PUBLISHER AND THE AUTHOR MAKE NO REPRESENTATIONS
OR WARRANTIES WITH RESPECT TO THE ACCURACY OR COMPLETENESS OF THE CONTENTS OF THIS WORK AND
SPECIFICALLY DISCLAIM ALL WARRANTIES, INCLUDING WITHOUT LIMITATION WARRANTIES OF FITNESS FOR A
PARTICULAR PURPOSE. NO WARRANTY MAY BE CREATED OR EXTENDED BY SALES OR PROMOTIONAL MATERIALS.
THE ADVICE AND STRATEGIES CONTAINED HEREIN MAY NOT BE SUITABLE FOR EVERY SITUATION. THIS WORK IS
SOLD WITH THE UNDERSTANDING THAT THE PUBLISHER IS NOT ENGAGED IN RENDERING LEGAL, ACCOUNTING,
OR OTHER PROFESSIONAL SERVICES. IF PROFESSIONAL ASSISTANCE IS REQUIRED, THE SERVICES OF A COMPE-
TENT PROFESSIONAL PERSON SHOULD BE SOUGHT. NEITHER THE PUBLISHER NOR THE AUTHOR SHALL BE LIABLE
FOR DAMAGES ARISING HEREFROM. THE FACT THAT AN ORGANIZATION OR WEBSITE IS REFERRED TO IN THIS
WORK AS A CITATION AND/OR A POTENTIAL SOURCE OF FURTHER INFORMATION DOES NOT MEAN THAT THE
AUTHOR OR THE PUBLISHER ENDORSES THE INFORMATION THE ORGANIZATION OR WEBSITE MAY PROVIDE OR
RECOMMENDATIONS IT MAY MAKE. FURTHER, READERS SHOULD BE AWARE THAT INTERNET WEBSITES LISTED IN
THIS WORK MAY HAVE CHANGED OR DISAPPEARED BETWEEN WHEN THIS WORK WAS WRITTEN AND WHEN IT IS
READ.
For general information on our other products and services, please contact our Customer Care Department within the U.S.
at 800-762-2974, outside the U.S. at 317-572-3993, or fax 317-572-4002.
For technical support, please visit www.wiley.com/techsupport.
Wiley also publishes its books in a variety of electronic formats. Some content that appears in print may not be available in
electronic books.
Library of Congress Control Number: 2006934839
ISBN-13: 978-0-470-04671-5
ISBN-10: 0-470-04671-6
Manufactured in the United States of America
10 9 8 7 6 5 4 3 2 1
1O/RT/RS/QW/IN
About the Authors
John Walkenbach is a leading authority on spreadsheet software and is principal of
JWalk and Associates Inc., a small San Diego–based consulting firm that specializes
in spreadsheet application development. He is the author of approximately three
dozen spreadsheet books and has written hundreds of articles and reviews for a
variety of publications, including PC World, InfoWorld, Windows magazine, and
PC/Computing. John graduated from the University of Missouri and earned a master’s
and a PhD from the University of Montana. Visit his Web site at www.j-walk.com.
Colin Banfield has been working in the telecommunications field for more than
twenty-five years and has been using spreadsheet programs for more than two
decades. Colin’s largest Excel project to date is a comprehensive tool that is
designed for telephone central office equipment configuration, pricing, and manufac-
turing and used by sales, engineering, and manufacturing personnel. In between his
telecom consulting duties, Colin works with organizations to extract information
from corporate databases for business intelligence reporting in Excel. In his spare
time, Colin writes and reviews books for Wiley and enjoys photography, astronomy,
and expanding his musical repertoire. Colin holds a BSc (Honors) in Electrical
Engineering from the University of the West Indies and has received extensive
training in many advanced technologies.
Dedication
To Dorothy, my most loving and patient better half, and one of the nicest people on
the planet. —C. B.
Author’s Acknowledgments
Thanks to the folks at Wiley who helped with the update of this book. In particular,
my project editor, Susan Pink (Pinkie to me), did her usual wonderful editing and
coordinating job and kept me on my toes. I’m indebted to Allen Wyatt, who provided
a thorough technical review and made some very good suggestions for improve-
ment. Finally, I’d like to thank David Gainer, Lead Program Manager for Excel at
Microsoft, and his staff for clarifying some issues and for delivering this amazing
update to a superb piece of software. —C. B.
Publisher’s Acknowledgments
We’re proud of this book; please send us your comments through our online registration form located at
www.dummies.com/register/.
Some of the people who helped bring this book to market include the following:
Acquisitions, Editorial, and Media Development Composition Services
Project Editor: Susan Pink Project Coordinator: Heather Kolter, Ryan Steffen
(Previous Edition: Christine Berman) Layout and Graphics: Stephanie D. Jumper,
Acquisitions Editor: Greg Croy Barry Offringa, Lynsey Osborn, Heather Ryan
Copy Editor: Susan Pink Proofreaders: Dwight Ramsey, Evelyn Still,
Brian H. Walls
(Previous Edition: John Edwards)
Indexer: Techbooks
Technical Editor: Allen Wyatt
Editorial Manager: Jodi Jensen
Media Development Manager: Laura VanWinkle
Editorial Assistant: Amanda Foxworth
Publishing and Editorial for Technology Dummies
Richard Swadley, Vice President and Executive Group Publisher
Andy Cummings, Vice President and Publisher
Mary Bednarek, Executive Acquisitions Director
Mary C. Corder, Editorial Director
Publishing for Consumer Dummies
Diane Graves Steele, Vice President and Publisher
Joyce Pepple, Acquisitions Director
Composition Services
Gerry Fahey, Vice President of Production Services
Debbie Stailey, Director of Composition Services
Contents at a Glance
Part 1: Getting to Know Excel 2007 ................................................................1
Part 2: Managing Workbooks ........................................................................21
Part 3: Working with Worksheets ................................................................37
Part 4: Entering and Editing Worksheet Data ..............................................49
Part 5: Using Formulas and Functions..........................................................77
Part 6: Creating and Using Names ................................................................87
Part 7: Auditing Your Work ..........................................................................97
Part 8: Formatting Your Data ......................................................................113
Part 9: Printing Your Work..........................................................................135
Part 10: Charting Your Data ........................................................................151
Part 11: Working with Tables ......................................................................169
Part 12: Goal Seeking and What-If Analysis ..............................................185
Part 13: Analyzing Data with PivotTables..................................................193
Glossary: Tech Talk......................................................................................207
Index ............................................................................................................215
Table of Contents
Part 1: Getting to Know Excel 2007 ........................1
Excel Basics ........................................................................................................2
Formulas ........................................................................................................2
Active cell and ranges ..................................................................................3
Familiarizing Yourself with the Excel 2007 Window ......................................3
Moving, resizing, and closing windows......................................................3
Exiting Excel ..................................................................................................3
Navigating with the Mouse and Keyboard......................................................5
Mousing around ............................................................................................5
Using the keyboard ......................................................................................6
Introducing the Ribbon ....................................................................................8
Dissecting the parts of the Ribbon ............................................................8
Sizing up the Ribbon ..................................................................................11
Tipping off your keyboard ........................................................................12
Hiding the Ribbon commands ..................................................................12
Introducing the Quick Access Toolbar..........................................................13
Introducing the Office Menu ..........................................................................14
Previewing Your Formatting Live ..................................................................15
Formatting with Themes ................................................................................15
Soliciting Help ..................................................................................................17
Part 2: Managing Workbooks ................................21
Activating a Workbook ....................................................................................22
Arranging Windows Automatically ................................................................22
Changing the Default File Location................................................................22
Closing a Workbook ........................................................................................23
Comparing Two Workbooks Side by Side ....................................................24
Creating an Empty Workbook ........................................................................24
Creating Multiple Windows ( Views) for a Workbook ..................................25
Opening Nonstandard Files ............................................................................25
Opening a Workbook ......................................................................................26
Protecting and Unprotecting a Workbook ....................................................27
Safeguarding your workbook from unauthorized users ........................27
Protecting and unprotecting a workbook structure or window ..........28
Saving Files ......................................................................................................29
Saving a workbook......................................................................................30
Saving a workbook under a different name ............................................30
Saving a workbook in a different or earlier file format ..........................31
Switching among Open Workbooks ..............................................................32
Using a Workspace File....................................................................................32
Opening a workspace file ..........................................................................33
Saving a workspace file ..............................................................................33
x Excel 2007 For Dummies Quick Reference
Working with Workbook Templates ..............................................................33
Creating a workbook template ..................................................................33
Creating a workbook from a template......................................................34
Creating a default workbook template ....................................................36
Part 3: Working with Worksheets ..........................37
Activating a Worksheet ..................................................................................38
Adding a New Worksheet ................................................................................38
Changing a Worksheet’s Name ......................................................................38
Coloring a Worksheet Tab ..............................................................................39
Copying a Worksheet ......................................................................................39
Deleting a Worksheet ......................................................................................40
Freezing Row or Column Titles ......................................................................40
Grouping and Ungrouping Worksheets ........................................................41
Grouping worksheets ................................................................................42
Ungrouping worksheets ............................................................................42
Hiding and Unhiding a Worksheet ................................................................42
Moving a Worksheet ........................................................................................43
Protecting a Worksheet ..................................................................................44
Publishing Your Worksheet Data to the Web................................................45
Splitting Panes..................................................................................................46
Turning Off Gridlines ......................................................................................47
Using Full-Screen View ....................................................................................48
Zooming a Worksheet......................................................................................48
Part 4: Entering and Editing Worksheet Data ..........49
Copying Cells and Ranges ..............................................................................50
Copying a cell to another cell or a range ................................................50
Copying a range to another range ............................................................51
Copying data to another worksheet or workbook ................................52
Copying multiple cells or ranges to another range ................................53
Deleting Entire Rows and Columns................................................................54
Editing the Contents of a Cell ........................................................................55
Entering Data into a Range ............................................................................57
Entering data into a specific range ..........................................................57
Entering the same data into a range of cells ..........................................57
Entering Dates and Times ..............................................................................57
Entering specific dates and times ............................................................58
Entering the current date or time ............................................................59
Entering Formulas............................................................................................59
Entering formulas manually ......................................................................59
Entering formulas by pointing ..................................................................61
Entering Text ....................................................................................................61
Entering text into cells ..............................................................................62
Completing text entries by using AutoComplete....................................63
Entering Values ................................................................................................63
Entering values into cells ..........................................................................63
Entering fractions ......................................................................................64
Table of Contents xi
Erasing Data in Cells and Ranges ..................................................................64
Filling a Series ..................................................................................................65
Entering a series of incremental values or dates....................................65
Entering a series of text ............................................................................66
Inserting Entire Rows and Columns ..............................................................66
Moving Cells and Ranges ................................................................................67
Moving data to a new location in the same worksheet..........................67
Moving data to a different worksheet or workbook ..............................68
Replacing the Contents of a Cell ....................................................................69
Searching for Data to Replace ........................................................................69
Selecting Cells and Ranges ............................................................................70
Selecting a cell ............................................................................................71
Selecting a range ........................................................................................71
Selecting noncontiguous ranges ..............................................................71
Selecting entire rows and columns ..........................................................72
Selecting a multisheet (3-D) range............................................................72
Transposing a Range ......................................................................................73
Undoing Changes and Mistakes ....................................................................74
Validating Data Entry ......................................................................................74
Part 5: Using Formulas and Functions ....................77
Absolute, Relative, and Mixed References....................................................78
Basic Formula Essentials ................................................................................78
Changing When Formulas Are Calculated ....................................................79
Converting Formulas to Values ......................................................................80
Editing Functions in Formulas........................................................................80
Entering Functions in Formulas ....................................................................81
Entering functions manually......................................................................82
Using the AutoSum tool ............................................................................82
Using the Function Library........................................................................83
Modifying a Range Reference Used in a Function........................................84
Referencing Cells or Ranges in Other Worksheets ......................................85
Part 6: Creating and Using Names ........................87
Advantages to Naming Cells and Ranges ......................................................88
Applying Names to Existing Cell References ................................................88
Creating Names ................................................................................................89
Creating a name using the New Name dialog box ..................................89
Creating a name using the name box ......................................................90
Creating names from row and column labels..........................................91
Creating sheet-level names........................................................................92
Creating multisheet names ........................................................................93
Deleting Names ................................................................................................93
Editing Names ..................................................................................................94
Managing Names ..............................................................................................94
Naming Constants and Formulas ..................................................................95
Pasting Names into a Formula........................................................................96
xii Excel 2007 For Dummies Quick Reference
Part 7: Auditing Your Work....................................97
Checking for Specific Worksheet Errors ......................................................98
Checking for errors in the background ....................................................98
Checking for errors manually....................................................................99
Checking Your Spelling..................................................................................100
Creating a Table of Names ............................................................................100
Displaying Formulas in a Worksheet ..........................................................101
Evaluating Parts of a Formula ......................................................................101
Finding Specific Data ....................................................................................102
Handling Circular References ......................................................................104
Locating Errors by Selecting Special Cells..................................................106
Tracing Cell Relationships ............................................................................107
Tracing precedents and dependents......................................................107
Tracing formula error values ..................................................................108
Understanding Formula Error Values ..........................................................109
Using Cell Comments ....................................................................................110
Adding a cell comment ............................................................................110
Editing a cell comment ............................................................................111
Viewing cell comments ............................................................................111
Using the Watch Window ..............................................................................111
Displaying and adding cells to the Watch window ..............................111
Removing cells from the Watch window................................................112
Part 8: Formatting Your Data ..............................113
Adding Borders to a Cell or a Range............................................................114
Aligning Cell Contents ..................................................................................114
Changing the Appearance of Cell Contents ................................................116
Copying Formats ............................................................................................117
Formatting Based on the Contents of a Cell or Range ..............................117
Formatting based on individual cell values ..........................................118
Formatting based on values in a range ..................................................119
Applying multiple conditional formats to a cell or range....................121
Editing or deleting a conditional format ................................................122
Formatting a Number ....................................................................................123
Using Excel’s built-in number formats ..................................................123
Creating custom number formats ..........................................................124
Formatting a Range Using Visualizations....................................................125
Using a visualization to compare values in a range ............................126
Modifying the visualization for a range ................................................126
Hiding Cell Contents ......................................................................................128
Hiding and Unhiding Columns and Rows....................................................128
Hiding columns and rows ........................................................................128
Unhiding columns and rows....................................................................129
Modifying Cell Size ........................................................................................129
Changing column width ..........................................................................130
Changing row height ................................................................................130
Table of Contents xiii
Using Cell Styles ............................................................................................131
Applying a predefined cell style..............................................................132
Modifying an existing style ......................................................................133
Creating a custom cell style ....................................................................133
Copying (merging) cell styles from another workbook ......................134
Part 9: Printing Your Work ..................................135
Adjusting Margins and Centering Printed Output ....................................136
Changing Default Print Settings Using a Template ....................................137
Changing the Worksheet Display Mode ......................................................137
Controlling Page Settings ..............................................................................138
Changing page orientation ......................................................................139
Selecting paper size ..................................................................................139
Scaling your printed output ....................................................................139
Specifying the beginning page number..................................................139
Handling Page Breaks ....................................................................................140
Inserting manual page breaks ................................................................140
Removing manual page breaks ..............................................................140
Previewing and adjusting page breaks ..................................................141
Inserting a Header or Footer ........................................................................142
Selecting a predefined header or footer ................................................143
Creating a custom header or footer ......................................................143
Using multiple headers and footers in your reports ............................145
Previewing Your Work ..................................................................................146
Printing with Quick Print ..............................................................................146
Selecting a Printer..........................................................................................147
Setting Sheet Printing Options ....................................................................147
Printing gridlines or row and column headings....................................147
Printing row or column data labels on each page ................................148
Selecting miscellaneous sheet printing options ..................................148
Setting the Print Area ....................................................................................149
Specifying What You Want to Print ..............................................................150
Part 10: Charting Your Data ................................151
Anatomy of a Chart........................................................................................152
Activating a Chart ..........................................................................................153
Adding a New Data Series to a Chart ..........................................................154
Adding a Title to a Chart ..............................................................................155
Changing the Axes for a Data Series............................................................155
Changing the Chart Type for an Existing Chart or Data Series ................156
Creating a Chart ............................................................................................156
Creating and Using a Chart Template..........................................................158
Displaying a Data Table in a Chart ..............................................................159
Displaying Data Labels in a Chart ................................................................160
Formatting a Chart Element..........................................................................160
Handling Missing Data or Charting Hidden Data ......................................161
xiv Excel 2007 For Dummies Quick Reference
Inserting and Modifying Chart Legends......................................................162
Adding a legend to a chart ......................................................................162
Changing the names (titles) on a chart legend ....................................162
Modifying a Chart Axis ..................................................................................163
Resizing, Moving, Copying, and Deleting an Embedded Chart ................164
Resizing, Moving, and Deleting a Chart Element ......................................165
Selecting a Chart Element ............................................................................165
Updating the Source Data Range for a Chart or a Data Series ................166
Using and Changing the Default Chart Type ..............................................167
Part 11: Working with Tables ..............................169
Anatomy of a Table ........................................................................................170
Converting a Table to a Range......................................................................171
Creating a Table..............................................................................................171
Filtering Data in a Table ................................................................................172
Filtering based on individual column entries........................................172
Filtering based on column data type......................................................173
Filtering based on cell color ....................................................................174
Formatting a Table ........................................................................................174
Applying a new style to a table ..............................................................174
Formatting based on cell or column content ........................................175
Inserting a Totals Row in a Table ................................................................175
Inserting and Deleting Table Rows and Columns ......................................175
Adding and deleting table rows ..............................................................176
Adding and deleting table columns........................................................176
Referencing Table Data in a Formula ..........................................................177
Referencing table data outside the table ..............................................177
Referencing table data using a calculated column ..............................178
Removing Duplicate Values from a Table....................................................179
Selecting Table Areas ....................................................................................180
Sorting Data in a Table ..................................................................................180
Sorting a single column based on column values ................................181
Sorting multiple columns based on column values ............................181
Sorting based on a custom sort order ..................................................182
Sorting based on color or conditional formatting icons......................183
Part 12: Goal Seeking and What-If Analysis ........185
Creating Data Input Tables ..........................................................................186
Creating a one-input table........................................................................186
Creating a two-input table ......................................................................187
Finding Input Values that Produce the Desired Output ............................189
Using Scenario Manager................................................................................190
Creating a named scenario ......................................................................190
Creating a scenario summary report ....................................................191
Displaying a named scenario ..................................................................192
Table of Contents xv
Part 13: Analyzing Data with PivotTables ............193
Anatomy of a PivotTable ..............................................................................194
Changing the Summarization of a Value Field ............................................195
Creating a PivotTable Report ......................................................................195
Filtering a PivotTable Report........................................................................197
Filtering a PivotTable based on individual field items ........................197
Filtering a PivotTable based on field data type ....................................198
Pre-filtering a PivotTable..........................................................................199
Clearing PivotTable filters ......................................................................199
Formatting a PivotTable Report ..................................................................199
Applying a new style to a PivotTable ....................................................199
Formatting based on data values............................................................200
Changing the number format of a PivotTable field ..............................201
Grouping PivotTable Items ..........................................................................202
Creating a date group ..............................................................................202
Creating a number group ........................................................................203
Creating a text group ................................................................................204
Modifying a PivotTable Report ....................................................................204
Modifying a report layout ........................................................................204
Modifying a report structure ..................................................................205
Displaying and hiding subtotals and grand totals................................205
Expanding and collapsing field item levels ..........................................206
Refreshing a PivotTable Report ..................................................................206
Glossary: Tech Talk..............................................207
Index ..................................................................215
xvi Excel 2007 For Dummies Quick Reference
Part 1
Getting to Know
Excel 2007
With Microsoft’s popular Excel 2007 spreadsheet program, you can enter, manip-
ulate, and analyze data in ways that would be impossible, cumbersome, or error
prone for you to do manually. This part gives you the basics you need to get up
and running quickly in Excel.
In this part . . .
Familiarizing Yourself with the Excel 2007 Window
Navigating with the Mouse and Keyboard
Introducing the Ribbon, Quick Access Toolbar, and Office Menu
Formatting with Themes and Previewing Your Formatting Live
Copyright by webtailieu.net