Learn Excel 2011 for Mac

Cover......Page 1
Contents at a Glance......Page 3
Contents......Page 466
About the Author......Page 474
About the Technical Reviewer......Page 475
Acknowledgments......Page 476
What Does This Book Cover?......Page 4
Conventions Used in This Book......Page 6
Part I: Becoming Proficient with Excel:Mac......Page 8
Getting Ready to Learn Excel’s Secrets......Page 9
Four Ways to Control Excel......Page 10
Understanding How the Ribbon’s Tabs Work......Page 11
Understanding How the Ribbon’s Groups and Controls Work......Page 13
Collapsing the Ribbon......Page 14
Choosing Which Toolbars to Display......Page 15
Undocking and Docking the Formatting Toolbar......Page 16
Secrets of the Menu Bar......Page 17
Driving Excel with Keyboard Shortcuts......Page 18
Making the Toolbox Work Your Way......Page 19
Elements of the Excel User Interface......Page 21
Navigating Among Worksheets......Page 23
Changing the Active Cell......Page 24
Selecting and Manipulating Cells......Page 25
Importing Data......Page 27
Entering Text Using AutoCorrect......Page 35
Entering Text with AutoFill and Custom Lists......Page 39
Entering Text Using Paste and Paste Options......Page 43
Pasting in Multiple Items with the Scrapbook......Page 46
Entering Text with Find and Replace......Page 48
Inserting Symbols in a Document......Page 50
Viewing Your Workbooks......Page 51
Splitting the Window to View Separate Parts of a Worksheet......Page 52
Changing the Window and Arranging Open Windows......Page 54
Zooming to Show the Data You Need to See......Page 55
Freezing Rows and Columns So They Stay Onscreen......Page 56
Using Custom Views......Page 57
Summary......Page 58
Opening the Excel Preferences Dialog Box......Page 59
Controlling How the Excel Window Appears......Page 61
Choosing How to Display Objects......Page 63
Choosing Window Options......Page 64
Choosing Editing Options......Page 65
Choosing the Default Folder for Opening and Saving Workbooks......Page 68
Choosing the Default Format for Saving Workbooks......Page 69
Setting AutoRecover to Keep Backups of Your Workbooks for Safety......Page 70
Making Excel Prompt You to Enter Workbook Properties......Page 71
Creating Custom Keyboard Shortcuts......Page 72
Getting Ready to Customize the Toolbars......Page 74
Finding the Commands to Add to the Toolbars or Menus......Page 76
Changing How a Toolbar Button Appears......Page 77
Customizing the Menus and the Menu Bar......Page 79
Customizing the Menus......Page 80
Customizing the Menu Bar......Page 81
Turning the Ribbon Off So It Doesn’t Appear......Page 83
Customizing the Ribbon......Page 85
Opening One or More Workbooks Automatically with Excel......Page 86
Summary......Page 87
Creating Workbooks from Scratch or from Templates......Page 88
Creating a New Blank Workbook......Page 89
Creating a New Workbook Based on an Existing Workbook......Page 90
Saving a Workbook......Page 91
Protecting a Workbook with Automatic Backups and Passwords......Page 92
Entering Workbook Properties......Page 95
Finding Your Workbooks by Using Properties......Page 98
Saving Your Workbooks for Use with Older Versions of Excel......Page 100
Organizing the Worksheets in a Workbook......Page 102
Changing a Worksheet’s Tab Color......Page 103
Rearranging the Worksheets in a Workbook......Page 104
Four Easy Rules for Laying Out Your Worksheets......Page 105
Entering Data on Multiple Worksheets at Once......Page 106
Assigning a Name to a Cell or Range......Page 107
Creating Range Names Automatically......Page 109
Using a Range Name in Your Formulas......Page 110
Changing the Cell or Range a Name Refers To......Page 111
Creating a Collapsible Worksheet by Outlining It......Page 112
Having Excel Create an Outline Automatically......Page 113
Changing the Settings for Outlining......Page 115
Creating an Outline Manually......Page 116
Updating the Outline After Adding or Deleting Rows or Columns......Page 117
Making the Most of Templates......Page 118
Saving a Template......Page 119
Summary......Page 120
Working with Rows and Columns......Page 121
Inserting and Deleting Rows, Columns, and Cells......Page 122
Setting Row Height......Page 124
Setting Column Width......Page 125
Formatting Cells and Ranges......Page 126
Understanding the Three Main Tools for Applying Formatting......Page 127
Controlling How Data Appears by Applying Number Formatting......Page 129
Choosing How to Align Cell Contents......Page 137
Choosing Font Formatting......Page 139
Applying Borders and Fills......Page 140
Applying Protection to Cells......Page 141
Using Paste Special to Paste Formatting and Perform Actions......Page 142
Understanding Excel’s Preset Types of Conditional Formatting......Page 144
Applying a Preset Form of Conditional Formatting......Page 146
Creating Custom Conditional Formatting......Page 147
Changing the Order in Which Excel Applies Conditional Formatting Rules......Page 148
Clearing Conditional Formatting from a Cell, Range, or Worksheet......Page 149
Checking Input with Data Validation......Page 150
Formatting with Styles......Page 154
Adding Headers and Footers Using the Page Setup Dialog Box......Page 159
Adding Headers and Footers Directly on the Worksheet......Page 162
Summary......Page 164
Part II: Performing Calculations and Presenting Data......Page 165
Understanding the Difference Between Formulas and Functions......Page 167
Referring to a Cell......Page 168
Making One Row or Column Refer to Another Row or Column......Page 170
Meeting Excel’s Calculation Operators......Page 171
Creating Straightforward Formulas......Page 173
Understanding the Order in Which Excel Evaluates Operators......Page 176
Entering Formulas Quickly by Copying and Using AutoFill......Page 178
Choosing Preferences for Error Checking......Page 179
Troubleshooting Common Problems with Formulas......Page 182
Summary......Page 187
Understanding the Components of a Function......Page 188
Entering Functions in Your Worksheets......Page 189
Inserting Functions with the AutoSum Pop-up Menu......Page 190
Inserting Functions with the Formula Builder......Page 191
Inserting Functions with the Insert Pop-up Menu......Page 195
Inserting Functions by Typing Them into a Worksheet......Page 196
Nesting One Function Inside Another Function......Page 197
Database Functions......Page 198
Date and Time Functions......Page 200
Engineering Functions......Page 202
Financial Functions......Page 205
Logical Functions......Page 212
Information Functions......Page 213
Lookup and Reference Functions......Page 215
Mathematical and Trigonometric Functions......Page 216
Statistical Functions......Page 220
Text Functions......Page 221
Choosing the Right Calculation Preferences for Your Needs......Page 223
Choosing When to Calculate Worksheets......Page 224
Controlling Iteration of Calculations......Page 225
Summary......Page 226
Understanding Embedded Charts and Chart Sheets......Page 227
Understanding the Components of a Chart......Page 229
Choosing the Best Chart Type for Your Data......Page 232
Creating a Chart......Page 233
Changing a Chart from an Embedded Chart to a Chart Sheet......Page 235
Changing the Chart Type......Page 236
Changing the Source Data for a Chart......Page 237
Choosing the Layout for the Chart......Page 238
Adding a Separate Data Series to a Chart......Page 239
Applying a Style to a Chart......Page 240
Adding Axis Titles to the Chart......Page 241
Changing the Scale or Numbering of an Axis......Page 242
Adding a Legend to a Chart......Page 245
Adding Data Labels to the Chart......Page 246
Choosing Which Gridlines to Display......Page 247
Formatting a Chart Wall and Chart Floor......Page 248
Formatting Individual Chart Elements......Page 251
Copying a Chart’s Formatting to Another Chart......Page 252
Reusing Your Own Designs by Creating Custom Chart Types......Page 253
Choosing Chart Preferences......Page 254
Understanding How You Can Add a Chart to a Document or Slide......Page 256
Summary......Page 261
Using Data Bars......Page 262
Creating Data Bars......Page 263
Using Color Scales......Page 266
Representing Data Graphically with Icon Sets......Page 268
Showing Data Trends with Sparklines......Page 271
Formatting Your Sparklines......Page 272
Summary......Page 275
Illustrating Your Worksheets with Pictures, SmartArt, and More......Page 276
Inserting a Clip Art Picture Using the Clip Art Browser......Page 277
Inserting a Clip Art Picture Using the Clip Gallery......Page 278
Managing Your Clip Art Items with the Clip Gallery......Page 279
Inserting Pictures from iPhoto......Page 285
Inserting Pictures from Your Mac’s File System......Page 286
Adding and Formatting a Shape......Page 287
Applying a Style to a Shape......Page 289
Positioning a Graphical Object......Page 291
Adjusting a Picture’s Sharpness, Brightness, Contrast, and Colors......Page 292
Applying a Picture Style......Page 293
Cropping a Picture......Page 294
Saving Space by Compressing Pictures......Page 296
Inserting SmartArt Diagrams......Page 297
Adding Decorative Text with WordArt......Page 299
Positioning Graphical Objects Relative to Cells......Page 300
Summary......Page 302
Part III: Analyzing Data and Sharing and Automating Workbooks......Page 303
Understanding What You Can and Can’t Do with Excel Tables......Page 305
Creating a Table and Entering Data......Page 306
Connecting to a Database......Page 312
Resizing a Table......Page 317
Sorting a Table by One or More Fields......Page 318
Identifying and Removing Duplicate Records in a Table......Page 320
Filtering a Table......Page 322
Using Database Functions with Tables......Page 324
Summary......Page 326
Solving Business Questions with What-If Analysis, Goal Seek, and Solver......Page 327
Creating a Data Table with One Variable......Page 328
Creating a Data Table with Two Variables......Page 331
Creating the Worksheet for Your Scenarios......Page 332
Opening the Scenario Manager Dialog Box......Page 334
Creating Scenarios......Page 335
Editing and Deleting Scenarios......Page 338
Merging Scenarios into a Single Worksheet......Page 339
Creating Reports from Your Scenarios......Page 341
Using Goal Seek......Page 342
Downloading and Installing Solver......Page 344
Using Solver......Page 345
Summary......Page 349
Understanding What PivotTables Are and What You Can Do with Them......Page 350
Creating a PivotTable Automatically......Page 352
Creating a PivotTable Manually......Page 354
Changing the PivotTable to Show Different Data......Page 361
Changing the Function Used to Summarize a Field......Page 365
Controlling the Design of a PivotTable......Page 366
Choosing Options for a PivotTable Style......Page 368
Naming a PivotTable and Setting Options for It......Page 369
Choosing Display Options for a PivotTable......Page 370
Choosing Layout Options for a PivotTable......Page 371
Choosing Data Options for a PivotTable......Page 373
Refreshing the Data in a PivotTable......Page 374
Sorting and Filtering a PivotTable......Page 375
Summary......Page 378
Making Your Worksheets Print Correctly......Page 379
Checking the Page Layout and Where the Page Breaks Fall......Page 380
Printing a Worksheet or Workbook......Page 382
Sharing Your Worksheets as PDFs......Page 384
Exporting Data to CSV Files......Page 386
Documenting Your Workbooks......Page 387
Adding Comments to Cells......Page 388
Adding Information with Data Validation......Page 390
Protecting a Workbook or Some of Its Worksheets......Page 391
Tracking Changes to a Workbook......Page 393
Sharing a Workbook So That Your Colleagues Can Edit It......Page 395
Working in a Shared Workbook......Page 397
Reviewing Tracked Changes in a Shared Workbook......Page 398
Consolidating Multiple Worksheets into a Single Worksheet......Page 400
Consolidating Worksheets by Their Position......Page 401
Consolidating Worksheets by Category......Page 403
Summary......Page 405
Automating Tasks with Macros and VBA......Page 406
Understanding Your Options for Automating Tasks......Page 407
Recording Macros......Page 408
Recording an Example Macro......Page 412
Running a Macro......Page 413
Running a Macro from the Macro dialog Box......Page 414
Running a Macro Using Its Keyboard Shortcut......Page 415
Running a Macro from a Toolbar Button or Menu Item......Page 416
Running a Macro from an Object in a Worksheet......Page 419
Opening the Sample Macro in the Visual Basic Editor......Page 420
Meeting the Visual Basic Editor’s Interface......Page 421
Examining the Sample Macro......Page 422
Returning from the Visual Basic Editor to Excel......Page 424
Summary......Page 425
A......Page 426
B......Page 428
C......Page 429
D......Page 434
E......Page 436
F......Page 437
H......Page 440
I......Page 441
K......Page 442
M......Page 443
N......Page 446
P......Page 447
Q......Page 450
R......Page 451
S......Page 452
T......Page 457
W......Page 459
Z......Page 462