Excel 2010

896 Pages·2010·18.63 MB·English

Excel 2010
THE MISSING MANUAL
The book that
should have been
in the box®ˇ

Excel 2010
Matthew MacDonald
Beijing  •  Cambridge  •  Farnham  •  Köln  •  Sebastopol  •  Taipei  •  Tokyo
Excel 2010: The Missing Manual
Matthew MacDonald
Copyright © 2010 O’Reilly Media, Inc. All rights reserved.
Printed in the United States of America.
Published by O’Reilly Media, Inc., 1005 Gravenstein Highway North,
Sebastopol, CA 95472.
O’Reilly Media books may be purchased for educational, business, or sales promotional use.
Online editions are also available for most titles: http://my.safaribooksonline.com. For more
information, contact our corporate/institutional sales department: 800-998-9938 or corporate@
oreilly.com.
June 2010: First Edition.
The Missing Manual is a registered trademark of O’Reilly Media, Inc. The Missing Manual logo,
and “The book that should have been in the box” are trademarks of O’Reilly Media, Inc. Many
of the designations used by manufacturers and sellers to distinguish their products are claimed
as trademarks. Where those designations appear in this book, and O’Reilly Media is aware of a
trademark claim, the designations are capitalized.
While every precaution has been taken in the preparation of this book, the publisher assumes no
responsibility for errors or omissions, or for damages resulting from the use of the information
contained in it.
This book uses a durable and flexible lay-flat binding.
ISBN: 9781449382353
[CS]
Table of Contents
The Missing Credits . . . . . . . . . . . . . . . . . . . . . . . . . . . xix
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
Part One: Worksheet Basics
Chapter 1: Creating Your First Spreadsheet . . . . . . . . . . . . . . 19
Creating a Basic Worksheet . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
Starting a New Workbook . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21
Adding the Column Titles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22
Adding Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
Editing Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
Moving Around the Grid . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26
Shortcut Keys . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26
The Go To Feature . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28
A Tour of the Excel Window . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29
The Tabs of the Ribbon . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29
The Formula Bar . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31
The Status Bar . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32
Going Backstage . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36
Excel Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37
Saving Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37
The Excel File Format . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39
Sharing Your Spreadsheet with Excel 2007 . . . . . . . . . . . . . . . . . . . . . . . 41
Saving Your Spreadsheet for Excel 2003 . . . . . . . . . . . . . . . . . . . . . . . . . 43
Saving a Spreadsheet in Other Formats . . . . . . . . . . . . . . . . . . . . . . . . . 44
Saving Your Spreadsheet As a PDF . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44
Saving Your Spreadsheet As an HTML File . . . . . . . . . . . . . . . . . . . . . . . . 47
Saving Your Spreadsheet with a Password . . . . . . . . . . . . . . . . . . . . . . . . 50
Disaster Recovery . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53
v
Opening Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55
Opening Recent Documents . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56
Protected View . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57
Opening Files—with a Twist . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 58
Working with Multiple Open Spreadsheets . . . . . . . . . . . . . . . . . . . . . . . 60
Chapter 2: Adding Information to Worksheets . . . . . . . . . . . 63
Adding Different Types of Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63
How Excel Identifies Text . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 66
How Excel Identifies Numbers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67
How Excel Identifies Dates and Times . . . . . . . . . . . . . . . . . . . . . . . . . . 68
Regional Dating . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 70
Handy Timesavers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 71
AutoComplete . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 72
AutoCorrect . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 73
AutoFill . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75
AutoFit . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 78
Dealing with Change: Undo, Redo, and AutoRecover . . . . . . . . . . . . . . . . . . . . 79
Undo and Redo . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79
AutoRecover . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 82
Spell Check . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 85
Spell Checking Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 87
Adding Hyperlinks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 90
Creating a Link to a Web Page or Document . . . . . . . . . . . . . . . . . . . . . . 91
Creating a Link to a Worksheet Location . . . . . . . . . . . . . . . . . . . . . . . . 93
Chapter 3: Moving Data . . . . . . . . . . . . . . . . . . . . . . . . . 95
Selecting Cells . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 95
Moving Cells Around . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 102
Adding and Moving Columns or Rows . . . . . . . . . . . . . . . . . . . . . . . . . . . 113
Chapter 4: Managing Worksheets . . . . . . . . . . . . . . . . . . . 117
Worksheets and Workbooks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 118
Adding and Removing Worksheets . . . . . . . . . . . . . . . . . . . . . . . . . . 119
Hiding Worksheets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 121
Naming and Rearranging Worksheets . . . . . . . . . . . . . . . . . . . . . . . . . 121
Moving Worksheets from One Workbook to Another . . . . . . . . . . . . . . . . . 123
Grouping Worksheets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 125
Find and Replace . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 127
The Basic Find . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 128
Find All . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 129
More Advanced Searches . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 130
Finding Formatted Cells . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 133
Finding and Replacing Values . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 134
vi table of contents
Chapter 5: Formatting Cells . . . . . . . . . . . . . . . . . . . . . . 137
Formatting Cell Values . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 138
Changing the Cell Value Format . . . . . . . . . . . . . . . . . . . . . . . . . . . . 139
Formatting Numbers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 141
Formatting Dates and Times . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 146
Special Formats for Special Numbers . . . . . . . . . . . . . . . . . . . . . . . . . 149
Custom Formats . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 150
Formatting Cell Appearance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 158
Alignment and Orientation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 159
Fonts and Color . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 162
Borders and Fills . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 167
Drawing Borders by Hand . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 169
Chapter 6: Smart Formatting Tricks . . . . . . . . . . . . . . . . . . 173
The Format Painter . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 173
Styles and Themes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 174
Custom Styles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 176
Modifying Styles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 178
Transferring Styles Between Workbooks . . . . . . . . . . . . . . . . . . . . . . . . 179
Themes: A Package of Styles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 180
Modifying Themes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 183
Conditional Formatting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 186
The Basics of Conditional Formatting . . . . . . . . . . . . . . . . . . . . . . . . . 187
Highlighting Specific Values . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 187
Using Multiple Rules . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 192
Chapter 7: Viewing and Printing Worksheets . . . . . . . . . . . . 195
Controlling Your View . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 195
Zooming . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 196
Viewing Distant Parts of a Spreadsheet at Once . . . . . . . . . . . . . . . . . . . . 198
Freezing Columns or Rows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 201
Hiding Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 204
Saving View Settings . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 205
Viewing Multiple Workbooks at Once . . . . . . . . . . . . . . . . . . . . . . . . . 207
Printing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 210
How to Print an Excel File . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 211
Page Layout View: A Better Print Preview . . . . . . . . . . . . . . . . . . . . . . . 217
Creating Headers and Footers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 220
Controlling Pagination . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 222
Page Breaks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 222
Scaling . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 223
Page Break Preview: A Bird’s-Eye View of Your Worksheet . . . . . . . . . . . . . . 224
table of contents vii
Part Two: Formulas and Functions
Chapter 8: Building Basic Formulas . . . . . . . . . . . . . . . . . . 227
Creating a Basic Formula . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 227
Excel’s Order of Operations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 229
Cell References . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 231
How Excel Formats Cells That Contain Cell References . . . . . . . . . . . . . . . . 232
Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 234
Using a Function in a Formula . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 234
Using Cell References with a Function . . . . . . . . . . . . . . . . . . . . . . . . . 236
Using Cell Ranges with a Function . . . . . . . . . . . . . . . . . . . . . . . . . . . 237
Excel Compatibility Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 239
Formula Errors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 240
Logical Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 243
Formula Shortcuts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 245
Point-and-Click Formula Creation . . . . . . . . . . . . . . . . . . . . . . . . . . . 245
Point-and-Click Formula Editing . . . . . . . . . . . . . . . . . . . . . . . . . . . . 246
The Formulas Tab . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 247
Using the Insert Function Button . . . . . . . . . . . . . . . . . . . . . . . . . . . . 249
Copying Formulas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 252
Absolute Cell References . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 254
Partially Fixed References . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 256
Referring to Other Worksheets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 258
Referring to Other Workbooks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 258
Chapter 9: Math and Statistical Functions . . . . . . . . . . . . . . 263
Rounding Numbers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 263
ROUND(), ROUNDDOWN(), ROUNDUP(): Rounding Numbers . . . . . . . . . . . 264
MROUND(), CEILING(), and FLOOR(): More Rounding Functions . . . . . . . . . . 265
INT() and TRUNC(): Chopping Off Non-Whole Numbers . . . . . . . . . . . . . . . 266
EVEN() and ODD(): Rounding Up to Even or Odd Values . . . . . . . . . . . . . . . 268
Groups of Numbers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 268
SUM(): Summing Up Numbers . . . . . . . . . . . . . . . . . . . . . . . . . . . . 268
COUNT(), COUNTA(), and COUNTBLANK(): Counting Items in a List . . . . . . . . 269
MAX() and MIN(): Finding Maximum and Minimum Values . . . . . . . . . . . . . 271
LARGE(), SMALL(), and RANK(): Ranking Numbers . . . . . . . . . . . . . . . . . . 272
AVERAGE() and MEDIAN(): Finding Average or Median Values . . . . . . . . . . . 274
PERCENTILE() and PERCENTRANK(): Advanced Ranking Functions . . . . . . . . . 275
FREQUENCY(): Putting Numbers into Grouped Ranges . . . . . . . . . . . . . . . . 277
MODE(): Finding Numbers That Frequently Occur in a List . . . . . . . . . . . . . . 279
General Math Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 279
PRODUCT(), FACT(), POWER(), and SQRT(): Products, Factorials,
Powers, and Square Roots . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 280
QUOTIENT() and MOD(): Higher Division . . . . . . . . . . . . . . . . . . . . . . . 281
ABS() and SIGN(): Absolute Value and Determining a Number’s Sign . . . . . . . . 281
RAND() and RANDBETWEEN(): Generating Random Numbers . . . . . . . . . . . 282
viii table of contents