Excel 2007 Workbook For Dummies

Excel 2007 Workbook For Dummies......Page 5
About the Author......Page 7
Dedication......Page 9
Author’s Acknowledgments......Page 11
Contents at a Glance......Page 13
Table of Contents......Page 15
Conventions Used in This Book......Page 21
How This Book Is Organized......Page 22
Using the Practice Material on the CD-ROM......Page 24
Icons Used in This Book......Page 25
Where to Go from Here......Page 26
Part I: Creating Spreadsheets......Page 27
Identifying the Parts of the Excel Display Screen......Page 29
Selecting Commands on the Office Menu......Page 31
Selecting Commands from the Ribbon......Page 32
Selecting Commands on the Quick Access Toolbar......Page 34
Launching Excel......Page 39
Opening a New Workbook......Page 40
Moving Around the Workbook......Page 43
Selecting Cell Ranges......Page 47
Making Cell Entries......Page 48
Saving the Spreadsheet Data in a Workbook File......Page 54
Resizing Columns and Rows......Page 57
Cell Formatting Techniques......Page 60
Hiding Columns and Rows......Page 75
Previewing Pages in the Worksheet Area......Page 77
Adjusting Page Breaks......Page 79
Adding Headers and Footers......Page 81
Adding Print Titles to a Report......Page 84
Modifying the Print Setting for a Report......Page 86
Printing All or Part of the Workbook......Page 89
Finding and Identifying the Region That Needs Editing......Page 97
Selecting the Ranges to Edit......Page 100
Editing Data Entries......Page 102
Catching Errors with Text to Speech......Page 103
Deleting and Inserting Data and Cells......Page 105
Moving and Copying Data and Cells......Page 107
Using Notes in the Spreadsheet......Page 112
Using Find and Replace and Spell Checking......Page 113
Group Editing......Page 117
Part II: Using Formulas and Functions......Page 121
Building Formulas......Page 123
Using External Reference Links......Page 138
Controlling When Formulas Are Recalculated......Page 140
Copying Formulas with Relative References......Page 143
Copying Formulas with Absolute References......Page 146
Copying Formulas with Mixed References......Page 147
Using Range Names in Formulas......Page 152
Building Array Formulas......Page 156
Tracing and Eliminating Formula Errors......Page 159
Dealing with Circular References......Page 161
Constructing Date and Time Formulas......Page 165
Working with Simple Date Functions......Page 167
Working with Excel’s More Sophisticated Date Functions......Page 169
Working with the Time Functions......Page 171
Working with Financial Functions......Page 173
Using the Basic Investment Functions......Page 174
Figuring the Depreciation of an Asset......Page 179
Rounding Off Values......Page 183
Finding Products, Powers, and Square Roots......Page 186
Doing Fancier Sums......Page 188
Computing Averages......Page 195
Finding the Highest and Lowest Values......Page 197
Counting Cells......Page 198
Using the Statistical Functions in Analysis ToolPak Add-in......Page 201
Returning Single Values from a Lookup Table......Page 203
Using the Lookup Wizard......Page 209
Working with the Logical Functions......Page 213
Constructing Decision-Making Formulas......Page 214
Constructing Error-Trapping Formulas......Page 221
Constructing Text Formulas......Page 225
Using Text Functions......Page 228
Part III: Working with Graphics......Page 233
Understanding Excel Charts......Page 235
Creating Charts......Page 240
Formatting Charts......Page 243
Editing Charts......Page 246
Understanding Graphic Objects......Page 249
Adding Various Types of Graphic Objects......Page 254
Part IV: Managing and Securing Data......Page 271
Creating a Data List......Page 273
Sorting Lists......Page 279
Subtotaling a List......Page 284
Filtering a List......Page 286
Querying External Database Tables......Page 290
Password-Protecting the Workbook......Page 295
Protecting the Worksheet......Page 298
Doing Data Entry in a Protected Worksheet......Page 302
Protecting the Entire Workbook......Page 304
Part V: Doing Data Analysis......Page 307
Using Data Tables......Page 309
Exploring Various Scenarios......Page 316
Performing Goal Seeking......Page 319
Creating Complex Models with Solver......Page 320
Understanding Pivot Tables......Page 323
Creating Pivot Tables......Page 326
Modifying the Pivot Table......Page 328
Creating Pivot Charts......Page 334
Part VI: Macros and Visual Basic for Applications......Page 339
Creating Macros......Page 341
Assigning Macros to the Quick Access Toolbar......Page 346
Using the Visual Basic Editor......Page 349
Creating User-Defined Functions......Page 356
Part VII: The Part of Tens......Page 363
Conditional Formatting......Page 365
Format As Table......Page 366
Style Galleries......Page 367
Live Preview......Page 368
Generating New Workbooks from Templates......Page 369
Creating Data Series with AutoFill......Page 370
Using Range Names......Page 371
Preventing Data Entry Errors with Data Validation......Page 372
Trapping Error Values in Their Original Formulas......Page 373
Controlling the Display of Data in Tables through Outlines......Page 374
Using View Side by Side to Work with Two Workbooks......Page 375
System Requirements......Page 377
What You’ll Find on the CD......Page 378
Troubleshooting......Page 382
Customer Care......Page 383
Index......Page 385