Learn Excel 2011 for Mac (eBook)

Guy Hart-Davis is the author of more than 50 computer books, including How to Do Everything with Microsoft Excel 2007 and How to Do Everything with Microsoft Word 2007.

Title Page 2
Copyright Page 3
Contents at a Glance 5
Table of Contents 6
About the Author 14
About the Technical Reviewer 15
Acknowledgments 16
Introduction 17
Who Is This Book For? 17
What Does This Book Cover? 17
Conventions Used in This Book 19
Part I Becoming Proficient with Excel:Mac 21
Chapter 1 Learning the Secrets of the Excel:Mac Interface 22
Getting Ready to Learn Excel’s Secrets 22
Four Ways to Control Excel 23
Secrets of the Ribbon 24
Understanding How the Ribbon’s Tabs Work 24
Understanding How the Ribbon’s Groups and Controls Work 26
Collapsing the Ribbon 27
Secrets of the Toolbars 28
Choosing Which Toolbars to Display 28
Switching the Standard Toolbar Between Icons and Textand Icons Only 29
Undocking and Docking the Formatting Toolbar 29
Secrets of the Menu Bar 30
Driving Excel with Keyboard Shortcuts 31
Making the Toolbox Work Your Way 32
Navigating Quickly Through Worksheets andWorkbooks 34
Elements of the Excel User Interface 34
Navigating Among Worksheets 36
Changing the Active Cell 37
Selecting and Manipulating Cells 38
Tools for Entering Text and Formulas Quickly 40
Importing Data 40
Importing Data from a Comma-Separated Values File or a Text File 42
Importing Data from a FileMaker Pro Database 45
Importing Data from an HTML File 48
Connecting a Worksheet to External Data Sources 48
Entering Text Using AutoCorrect 48
Opening the AutoCorrect Preferences Pane 48
Choosing Options to Make AutoCorrect Work Your Way 49
Creating AutoCorrect Exceptions 50
Creating Replace-As-You-Type Entries 51
Creating AutoCorrect Entries 51
Using Your AutoCorrect Entries 52
Entering Text with AutoFill and Custom Lists 52
Using AutoFill’s Built-in Capabilities 52
Creating Your Own Custom AutoFill Lists 55
Entering Text Using Paste and Paste Options 56
Switching Data from Rows to Columns 57
Pasting in a Table from Word 58
Getting Comma-Separated Data into a Worksheet 59
Pasting in Multiple Items with the Scrapbook 59
Opening the Scrapbook 59
Adding an Item to the Scrapbook 60
Inserting an Item from the Scrapbook 61
Deleting an Item from the Scrapbook 61
Entering Text with Find and Replace 61
Inserting Symbols in a Document 63
Viewing Your Workbooks 64
Splitting the Window to View Separate Parts of aWorksheet 65
Opening Extra Windows to Show Other Parts of aWorkbook 67
Changing the Window and Arranging Open Windows 67
Zooming to Show the Data You Need to See 68
Freezing Rows and Columns So They Stay Onscreen 69
Using Custom Views 70
Summary 71
Chapter 2 Configuring Excel:Mac to Suit the Way You Work 72
Opening the Excel Preferences Dialog Box 72
Controlling How the Excel Window Appears 74
Choosing Options in the Settings Area of View Preferences 76
Choosing How to Display Comments 76
Choosing How to Display Objects 76
Choosing Window Options 77
Choosing Editing Options 78
Setting Preferences for Creating and Saving Your Workbooks 81
Creating Workbooks with the Number of Worksheets You Need 81
Choosing the Default Folder for Opening and Saving Workbooks 81
Choosing the Default Format for Saving Workbooks 82
Setting AutoRecover to Keep Backups of Your Workbooks for Safety 83
Making Excel Prompt You to Enter Workbook Properties 84
Creating Custom Keyboard Shortcuts 85
Customizing the Toolbars with the Commands You Need 87
Getting Ready to Customize the Toolbars 87
Creating a New Toolbar 89
Finding the Commands to Add to the Toolbars or Menus 89
Adding an Item to a Toolbar 90
Removing an Item from a Toolbar 90
Repositioning Items on a Toolbar 90
Changing How a Toolbar Button Appears 90
Adding a Built-in Menu to a Toolbar 92
Customizing the Menus and the Menu Bar 92
Customizing the Menus 93
Customizing the Menu Bar 94
Turning Off or Customizing the Ribbon 96
Turning the Ribbon Off So It Doesn’t Appear 96
Customizing the Ribbon 98
Customizing the Ribbon the Quick Way 98
Customizing the Ribbon Using the Ribbon Preferences Pane 98
Opening One or More Workbooks Automatically with Excel 99
Saving the Layout of Open Workbooks as a Workspace 100
Summary 100
Chapter 3 Creating Effective Workbooks and Templates 101
Creating Workbooks from Scratch or fromTemplates 101
Creating a New Blank Workbook 102
Creating a New Workbook Based on a Template 103
Creating a New Workbook Based on an Existing Workbook 103
Saving a Workbook 104
Protecting a Workbook with Automatic Backups andPasswords 105
Entering Workbook Properties 108
Entering Standard Properties on the Summary Tab 109
Entering Extra Data on the Custom Tab 109
Finding Your Workbooks by Using Properties 111
Saving Your Workbooks for Use with Older Versionsof Excel 113
Organizing the Worksheets in a Workbook 115
Inserting a New Worksheet 116
Naming a Worksheet 116
Changing a Worksheet’s Tab Color 116
Deleting a Worksheet 117
Rearranging the Worksheets in a Workbook 117
Four Easy Rules for Laying Out Your Worksheets 118
Entering Data on Multiple Worksheets at Once 119
Identifying Parts with Named Ranges 120
Assigning a Name to a Cell or Range 120
Assigning a Name to a Cell or Range Quickly 121
Assigning a Name to a Cell or Range with the Define Name Dialog Box 121
Creating Range Names Automatically 122
Using a Range Name in Your Formulas 123
Deleting a Range Name 124
Changing the Cell or Range a Name Refers To 124
Creating a Collapsible Worksheet by Outlining It 125
Having Excel Create an Outline Automatically 126
Changing the Settings for Outlining 128
Creating an Outline Manually 129
Grouping Rows or Columns 129
Ungrouping Rows or Columns 130
Expanding and Collapsing an Outline 130
Updating the Outline After Adding or Deleting Rows or Columns 130
Remove an Outline 131
Making the Most of Templates 131
Creating a Template Based on an Existing Workbook 132
Saving a Template 132
Summary 133
Chapter 4 Formatting Your Worksheets Quickly and Efficiently 134
Working with Rows and Columns 134
Inserting and Deleting Rows, Columns, and Cells 135
Inserting Columns and Rows 135
Inserting Some Cells 136
Deleting Columns or Rows 136
Deleting Some Cells 136
Setting Row Height 137
Setting Column Width 138
Hiding Rows and Columns 139
Formatting Cells and Ranges 139
Understanding the Three Main Tools for Applying Formatting 140
Controlling How Data Appears by Applying Number Formatting 142
Setting the Workbook’s Overall Look by Applying a Theme 150
Choosing How to Align Cell Contents 150
Choosing Font Formatting 152
Applying Borders and Fills 153
Applying Protection to Cells 154
Using Paste Special to Paste Formatting and Perform Actions 155
Identifying Unusual Values with Conditional Formatting 157
Understanding Excel’s Preset Types of Conditional Formatting 157
Applying a Preset Form of Conditional Formatting 159
Creating Custom Conditional Formatting 160
Changing the Order in Which Excel Applies Conditional Formatting Rules 161
Clearing Conditional Formatting from a Cell, Range, or Worksheet 162
Checking Input with Data Validation 163
Formatting Quickly with Table Formatting and Styles 167
Formatting with Table Formatting 167
Formatting with Styles 167
Meeting Excel’s Styles 168
Applying a Style 169
Creating Custom Styles 169
Copying Styles from One Workbook to Another 171
Deleting Styles You Don’t Need 172
Adding Headers and Footers to Your Worksheets 172
Adding Headers and Footers Using the Page Setup Dialog Box 172
Adding Headers and Footers Directly on the Worksheet 175
Summary 177
Part II Performing Calculations and Presenting Data 178
Chapter 5 Performing CustomCalculations with Formulas 180
Understanding the Difference Between Formulas and Functions 180
Referring to Cells and Ranges in Formulas and Functions 181
Referring to a Cell 181
Referring to a Range 183
Making One Row or Column Refer to Another Row or Column 183
Referring to Named Cells and Ranges 184
Understanding the Components of Formulas 184
Meeting Excel’s Calculation Operators 184
Creating Straightforward Formulas 186
Creating Complex Formulas 189
Understanding the Order in Which Excel Evaluates Operators 189
Nesting Parts of a Formula to Control Operator Precedence 191
Breaking Up a Complex Formula into Separate Steps 191
Entering Formulas Quickly by Copying and Using AutoFill 191
Choosing Preferences for Error Checking 192
Troubleshooting Common Problems with Formulas 195
Understanding Common Errors—and Resolving Them 195
Seeing the Details of an Error in a Formula 197
Tracing an Error Back to Its Source 197
Displaying All the Formulas in a Worksheet 198
Seeing Which Cells a Formula Uses 198
Removing Circular References 199
Summary 200
Chapter 6 Using Excel’s Built-In Functions 201
Understanding the Components of a Function 201
Entering Functions in Your Worksheets 202
Inserting Functions with the AutoSum Pop-up Menu 203
Inserting Functions with the Formula Builder 204
Inserting Functions with the Insert Pop-up Menu 208
Inserting Functions by Typing Them into a Worksheet 209
Nesting One Function Inside Another Function 210
Meeting Excel’s Built-in Functions 211
Database Functions 211
Date and Time Functions 213
Engineering Functions 215
Financial Functions 218
Logical Functions 225
Information Functions 226
Lookup and Reference Functions 228
Mathematical and Trigonometric Functions 229
Statistical Functions 233
Text Functions 234
Choosing the Right Calculation Preferences for Your Needs 236
Choosing When to Calculate Worksheets 237
Controlling Iteration of Calculations 238
Choosing Workbook Options 239
Summary 239
Chapter 7 Creating Clear and Persuasive Charts 240
Learning the Essentials of Charts in Excel 240
Understanding Embedded Charts and Chart Sheets 240
Understanding the Components of a Chart 242
Chart Area and Plot Area 244
Chart Axes 244
Categories and Data Series 244
Chart Title and Axis Titles 244
Data Markers, Gridlines, and Data Labels 245
Choosing the Best Chart Type for Your Data 245
Creating, Laying Out, and Formatting a Chart 246
Creating a Chart 246
Changing a Chart from an Embedded Chart to a Chart Sheet 248
Changing the Chart Type 249
Switching the Rows and Columns in a Chart 250
Changing the Source Data for a Chart 250
Choosing the Layout for the Chart 251
Adding a Separate Data Series to a Chart 252
Applying a Style to a Chart 253
Adding a Title to a Chart 254
Adding Axis Titles to the Chart 254
Changing the Scale or Numbering of an Axis 255
Adding a Legend to a Chart 258
Adding Axis Labels from a Range Separate from the Chart Data 259
Adding Data Labels to the Chart 259
Choosing Which Gridlines to Display 260
Formatting a Chart Wall and Chart Floor 261
Formatting Individual Chart Elements 264
Copying a Chart’s Formatting to Another Chart 265
Reusing Your Own Designs by Creating Custom Chart Types 266
Choosing Chart Preferences 267
Using Your Charts in Word Documents andPower Point Presentations 269
Understanding How You Can Add a Chart to a Document or Slide 269
Creating a Chart in a New Embedded Workbook 270
Pasting a Chart from Excel into a Word Document or PowerPoint Slide 272
Summary 274
Chapter 8 Using Data Bars, Color Scales, Icon Sets, and Sparklines 275
Using Data Bars 275
Creating Data Bars 276
Creating Data Bars in the Same Cells as Their Data 276
Creating Data Bars in Different Cells Than Their Data 277
Using Color Scales 279
Representing Data Graphically with Icon Sets 281
Showing Data Trends with Sparklines 284
Inserting Sparklines 285
Formatting Your Sparklines 285
Summary 288
Chapter 9 Illustrating Your Worksheets with Pictures, SmartArt, and More 289
Inserting Clip Art 290
Inserting a Clip Art Picture Using the Clip Art Browser 290
Inserting a Clip Art Picture Using the Clip Gallery 291
Managing Your Clip Art Items with the Clip Gallery 292
Adding Your Own Pictures to the Clip Gallery 292
Downloading Clip Art Items from the Microsoft Office Web Site 296
Organizing the Clip Gallery with Categories 297
Inserting Pictures in Your Workbooks 298
Inserting Pictures from iPhoto 298
Inserting Pictures from Your Mac’s File System 299
Adding and Formatting a Shape 300
Applying a Style to a Shape 302
Rotating a Graphical Object 304
Positioning a Graphical Object 304
Making a Picture Look the Way You Want It 305
Adjusting a Picture’s Sharpness, Brightness, Contrast, and Colors 305
Applying a Picture Style 306
Cropping a Picture 307
Saving Space by Compressing Pictures 309
Inserting SmartArt Diagrams 310
Adding Decorative Text with WordArt 312
Positioning Graphical Objects Relative to Cells 313
Arranging Graphical Objects to Control Which Is Visible 315
Summary 315
Part III Analyzing Data and Sharing and Automating Workbooks 316
Chapter 10 Creating Databases Using Tables 318
Creating Databases in Excel 318
Understanding What You Can and Can’t Do with Excel Tables 318
Creating a Table and Entering Data 319
Creating a Table 320
Customizing the Table’s Looks 321
Entering Data in a Table 323
Entering Data Directly in the Table 323
Entering Data Using a Data-Entry Form 324
Connecting a Table to an External Data Source 325
Getting and Installing an ODBC Driver 326
Establishing a Connection to a Database 326
Connecting to a Database 325
Getting and Installing an ODBC Driver 326
Establishing a Connection to a Database 326
Refreshing the Data from a Database 329
Importing Data from a FileMaker Pro Database 330
Resizing a Table 330
Sorting a Table by One or More Fields 331
Sorting Quickly by a Single Field 331
Sorting a Table by Multiple Fields 331
Identifying and Removing Duplicate Records in a Table 333
Filtering a Table 335
Using Database Functions with Tables 337
Summary 339
Chapter 11 Solving Business Questions with What-If Analysis, Goal Seek, and Solver 340
Assessing the Impact of Variables Using Data Tables 341
Creating a Data Table with One Variable 341
Creating a Data Table with Two Variables 344
Examining Different Scenarios in a Worksheet 345
Creating the Worksheet for Your Scenarios 345
Opening the Scenario Manager Dialog Box 347
Creating Scenarios 348
Applying Protection to Your Scenarios 351
Editing and Deleting Scenarios 351
Switching Among Your Scenarios 352
Merging Scenarios into a Single Worksheet 352
Creating Reports from Your Scenarios 354
Using Goal Seek 355
Solving Multiple-Variable Problems with Solver 357
Downloading and Installing Solver 357
Using Solver 358
Summary 362
Chapter 12 Analyzing Data with PivotTables 363
Understanding What PivotTables Are and What You Can Do with Them 363
Creating and Laying Out a PivotTable 365
Creating a PivotTable Automatically 365
Creating a PivotTable Manually 367
Understanding How the PivotTable Framework and PivotTable Builder Window Work 369
Adding the Fields to the PivotTable Framework 370
Changing the PivotTable to Show Different Data 374
Changing the Function Used to Summarize a Field 378
Controlling the Design of a PivotTable 379
Formatting a PivotTable 381
Applying a PivotTable Style 381
Choosing Options for a PivotTable Style 381
Naming a PivotTable and Setting Options for It 382
Renaming a PivotTable 383
Choosing Display Options for a PivotTable 383
Choosing Layout Options for a PivotTable 384
Choosing Data Options for a PivotTable 386
Refreshing the Data in a PivotTable 387
Changing the Source of a PivotTable 388
Sorting and Filtering a PivotTable 388
Summary 391
Chapter 13 Collaborating and Sharing with Macs and Windows PCs 392
Making Your Worksheets Print Correctly 392
Telling Excel Which Part of the Worksheet to Print 393
Checking the Page Layout and Where the Page Breaks Fall 393
Printing a Worksheet or Workbook 395
Sharing Your Worksheets as PDFs 397
Exporting Data to CSV Files 399
Documenting Your Workbooks 400
Adding Explanatory Text to Workbooks 401
Adding Comments to Cells 401
Adding a Comment 401
Viewing Comments 402
Deleting Comments 403
Adding Information with Data Validation 403
Sharing Your Workbooks with Your Colleagues 404
Protecting a Workbook or Some of Its Worksheets 404
Protecting a Workbook 404
Protecting a Worksheet 405
Tracking Changes to a Workbook 406
Sharing a Workbook So That Your Colleagues Can Edit It 408
Working in a Shared Workbook 410
Resolving Conflicts in a Shared Workbook 411
Reviewing Tracked Changes in a Shared Workbook 411
Merging Multiple Workbooks into a Single Workbook 413
Consolidating Multiple Worksheets into a Single Worksheet 413
Preparing to Consolidate Worksheets 414
Consolidating Worksheets by Their Position 414
Consolidating Worksheets by Category 416
Summary 418
Chapter 14 Automating Tasks with Macros and VBA 419
Understanding Your Options for Automating Tasks 420
Recording Macros 421
Recording an Example Macro 425
Running a Macro 426
Running a Macro from the Macro dialog Box 427
Running a Macro Using Its Keyboard Shortcut 428
Running a Macro from a Toolbar Button or Menu Item 429
Running a Macro from an Object in a Worksheet 432
Deleting a Macro 433
Editing Macros in the Visual Basic Editor 433
Opening the Sample Macro in the Visual Basic Editor 433
Meeting the Visual Basic Editor’s Interface 434
Examining the Sample Macro 435
Editing the Sample Macro 437
Testing the Macro 437
Saving the Changes 437
Returning from the Visual Basic Editor to Excel 437
Hiding the Personal Macro Workbook 438
Summary 438
Index 439