®
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
2 Part 1: Getting to Know Excel 2007
Excel Basics
Excel documents are known as workbooks. A single workbook can store as many
sheets as will fit into memory, and these sheets are stacked like the pages in a
notebook. Sheets can be either worksheets (a normal spreadsheet-type sheet with
rows and columns) or chart sheets (a special sheet that holds a single chart).
Most of the time, you perform tasks in worksheets. In older versions of Excel
(well, except for really old versions), each worksheet used a grid with 65,536
rows and 256 columns. Excel numbers rows starting with 1 and assigns letters to
columns starting with A. After Excel exhausts the letters of the alphabet, column
lettering continues with AA, AB, and so on. So column 1 is A, column 26 is Z,
column 27 is AA, column 52 is AZ, column 53 is BA, and so on. Prior to Excel
2007, row numbers ranged from 1 to 65,536 and column labels ranged from A
(column 1) to IV (column 256).
Excel 2007 increases the number of rows and columns in a single worksheet sig-
nificantly. A worksheet now has 1,048,576 rows (no, that’s not a typo) and 16,384
columns (no, that’s not a typo either). Rows are numbered from 1 to 1048576
and columns are labeled from A to XFD.
The intersection of a row and a column is called a cell. A quick calculation using
Excel tells me that this works out to 17,179,869,184 cells — more than enough
for just about any use. Cells have addresses, which are based on their row and
column. The upper-left cell in a worksheet is called A1, and the cell down at
the bottom right is called XFD1048576. Cell K9 (also known as the dog cell) is the
intersection of the eleventh column and the ninth row.
You might be wondering about the amount of system memory (known as random
access memory, or RAM) you need to accommodate all those rows and columns.
The actual memory you need depends on the amount of data you store in the
workbook and the number of open workbooks. The good news is that Excel 2007
allows you to work with more memory than previous versions. Excel 2003, for
example, will utilize up to only 1 GB (gigabyte) of memory, even if your system has
more memory available. In Excel 2007, the memory available is limited by the max-
imum amount of memory that your version of Windows (XP or Vista) can use.
Formulas
A cell in Excel can hold a number, some text, a formula, or nothing at all. You
already know what numbers and text are, but you may be a bit fuzzy on the con-
cept of a formula. A formula tells Excel to perform a calculation using informa-
tion stored in other cells. For example, you can insert a formula that tells Excel
to add the values in the first 10 cells in column A and to display the result in the
cell that contains the formula.
Excel Basics — Familiarizing Yourself with the Excel 2007 Window 3
Formulas can use normal arithmetic operators such as + (plus), — (minus),
* (multiply), and / (divide). They can also use special built-in functions that
let you do powerful things without much effort on your part. For example,
Excel has functions that add a range of values, calculate square roots, compute
loan payments, and even tell you the time of day. Part 5 covers how to use the
various functions in Excel.
Active cell and ranges
In Excel, one of the cells in a worksheet is always the active cell. The active cell
is the one that’s selected, and it’s displayed with a thicker border than the
others. Its contents appear in the formula bar. You can select a group, or range,
of cells by clicking and dragging the mouse pointer over them. You can then
issue a command that does something to the active cell or to the range.
The selected range is usually a group of contiguous cells, but it doesn’t have to
be. To select a noncontiguous group of cells, select the first cell or group of
cells, hold down the Ctrl key while you drag the mouse, and select the next cell
or group of cells.
Familiarizing Yourself with the Excel 2007 Window
Figure 1-1 shows a typical Excel 2007 window, with the important parts labeled.
This terminology rears its ugly head throughout the book, so look at the figure
carefully.
Moving, resizing, and closing windows
When Excel and workbook windows are in a restored state (between a maximized
and minimized state, that is) you can use the resize handles to adjust the window
size to your liking. Move the mouse pointer to the area of the resize handle until
the pointer changes to a double-headed arrow, and then drag with the mouse.
You can move the window around the screen by dragging the title bars. See also
“Using the Mouse and Keyboard,” later in this part.
When the active workbook window is maximized, it shares a single Close button
with the Excel window. After you click the shared Close button, Excel closes the
active workbook.
Exiting Excel
Use any one of the following methods to close the Excel application:
Click the Close button on the Excel title bar if one or no workbook is open.
Click the Office button and then click the Exit Excel button.
4 Part 1: Getting to Know Excel 2007
Double-click the Office button. See “Introducing the Office Menu,” later in
this part.
Press the Alt key, then press F, and then press X.
Select all button
Control button
Office button Mouse pointer Split box
Name box Formula bar Help
Quick Access toolbar Excel title bar Close
Active Workbook title bar Maximize/Restore
cell Column
pointer header Workbook window Minimize
Figure 1-1
Sheet tabs Status bar Tab split Zoom controls
Row header New sheet tab Scroll bar Split box
Tab scrolling controls Normal view Page break preview
Page layout view
Window resize handles
Familiarizing Yourself with the Excel 2007 Window — Navigating with the Mouse and Keyboard 5
Navigating with the Mouse and Keyboard
The mouse is the primary tool that you use in Excel for executing commands,
making selections, and navigating in the worksheet. Following are the mouse
conventions that we use in this book:
Click: Click the left mouse button once.
Double-click: Click the left mouse button twice in quick succession. It may
take you some time to get the hang of this action.
Right-click: Click the right mouse button once.
Drag: Hold down the left mouse button and move the mouse. Release the
mouse button to complete the drag operation.
Hover: Place the mouse pointer over an element without clicking a mouse
button.
Select: Place the mouse pointer over an element and click the left mouse
button.
Mousing around
Every mouse action is associated with some element in the Excel window. An
element can be a slider, button, cell, chart object, and so on. You select or hover
over the element using the mouse pointer.
Navigating through a worksheet with a mouse works just as you’d expect. Just
click a cell, and it becomes the active cell. If the cell that you want to activate
isn’t visible in the workbook window, you can use the scroll bars to scroll the
window in any direction, as follows:
To scroll one cell, click one of the arrows on the scroll bar.
To scroll by a complete screen, click either side of the scroll bar’s slider
button (the large center button).
To scroll faster, drag the slider.
To scroll a long distance vertically, press and hold the Shift key while drag-
ging the slider button.
Note that only the active workbook window displays scroll bars. If you activate a
different window, its scroll bars appear.
After you right-click a cell, a range of cells, or another object in the worksheet
area, Excel displays a contextual menu, so-called because the menu includes
commands specific to working with the cell, range, or object.
6 Part 1: Getting to Know Excel 2007
For your convenience, Excel 2007 adds a mini-toolbar above the contextual
menu with useful commands drawn from the Ribbon, as shown in Figure 1-2. See
also “Introducing the Ribbon,” later in this part.
Figure 1-2
Using the keyboard
Most users will be comfortable using the mouse to do all their work in Excel. For
users who prefer to use the keyboard exclusively when working in Windows
applications or for users who prefer to split the use of the mouse and keyboard
among various tasks, Excel provides the following solutions.
Keyboard shortcuts
Keyboard navigation
KeyTips
The first two functions are described next. For more on the last function,
KeyTips, see “Tipping off your keyboard,” later in this part.
You can access commands in Excel using keyboard shortcuts, which are individ-
ual keystrokes or a combination of keys pressed simultaneously. To access the
Print command using a shortcut, for example, you press and hold down the Ctrl
key and press the P key, represented in this book as Ctrl+P. The following table
lists some common keyboard shortcuts in Excel.
Shortcut Action
Ctrl+A Select all
Ctrl+B Apply or remove bold formatting
Ctrl+C Copy selection