New Perspectives on Microsoft Office Excel 2010, Introductory

Cover Page\n......Page 1
Title Page\n......Page 6
Copyright Page\n......Page 7
Preface......Page 8
BRIEF CONTENTS......Page 12
TABLE OF CONTENTS\n......Page 14
GETTING STARTED WITH MICROSOFT OFFICE 2010: Preparing a Meeting Agenda......Page 20
Exploring Microsoft Office 2010......Page 23
Starting Office Programs......Page 24
Switching Between Open Programs and Files......Page 26
Resizing the Program Window and Workspace......Page 27
Zooming and Scrolling......Page 28
Using the Ribbon......Page 30
Clicking Buttons......Page 31
Opening Dialog Boxes and Task Panes......Page 33
Accessing the Mini Toolbar......Page 35
Opening Shortcut Menus......Page 36
Working with Files......Page 37
Saving a File......Page 38
Opening a File......Page 41
Sharing Files Using Windows Live SkyDrive......Page 43
Viewing ScreenTips......Page 44
Using the Help Window......Page 45
Printing a File......Page 47
Review: Quick Check......Page 49
Review Assignments\n......Page 50
ProSkills: Teamwork......Page 51
Tutorial 1 Getting Started with Excel Creating an Order Report......Page 52
SESSION 1.1......Page 53
Exploring the Excel Window......Page 55
Navigating Between Worksheets......Page 56
Navigating Within a Worksheet......Page 57
Planning a Workbook.......Page 59
Entering Text......Page 60
Entering Multiple Lines of Text Within a Cell......Page 62
Entering Dates......Page 63
Entering Numbers......Page 64
Setting Column Widths......Page 66
Setting Row Heights......Page 68
Inserting a Column or Row......Page 69
Deleting and Clearing a Row or Column......Page 71
Session 1.1 Quick Check......Page 72
SESSION 1.2......Page 73
Selecting a Range......Page 75
Moving and Copying a Range......Page 77
Inserting and Deleting a Range......Page 79
Entering a Formula......Page 81
Copying and Pasting Formulas......Page 84
Entering a Function......Page 85
Entering Functions with the AutoSum Feature......Page 86
Inserting or Deleting a Worksheet......Page 88
Editing Worksheet Content......Page 89
Using Find and Replace......Page 91
Using the Spelling Checker......Page 92
Changing Worksheet Views......Page 94
Working with Portrait and Landscape Orientation......Page 95
Printing the Workbook......Page 96
Viewing and Printing Worksheet Formulas......Page 97
Session 1.2 Quick Check......Page 99
Review Assignments......Page 100
Case Problems......Page 101
Tutorial 2 Formatting a Workbook Designing a Financial Report\n......Page 108
SESSION 2.1......Page 109
Formatting Cell Text......Page 111
Applying Fonts and Font Styles......Page 112
Applying a Font Color......Page 113
Formatting Text Selections......Page 114
Changing a Fill Color......Page 115
Adding a Background Image......Page 116
Creating Formulas to Add, Subtract, and Divide Values......Page 117
Applying Number Formats......Page 119
Formatting Dates and Times......Page 122
Aligning Cell Content......Page 123
Indenting Cell Content......Page 124
Merging Cells......Page 125
Rotating Cell Contents......Page 126
Adding Cell Borders......Page 127
Exploring Options in the Format Cells Dialog Box......Page 128
Copying Formats with the Format Painter......Page 130
Copying Formats with the Paste Options Button......Page 131
Session 2.1 Quick Check......Page 132
SESSION 2.2......Page 133
Applying Cell Styles......Page 135
Working with Themes......Page 137
Creating Formulas to Calculate Totals......Page 139
Creating Formulas to Subtract Values......Page 140
Working with Table Styles......Page 142
Selecting Table Style Options......Page 144
Highlighting Cells with Conditional Formats......Page 146
Formatting the Worksheet for Printing......Page 152
Defining the Print Area......Page 153
Inserting Page Breaks......Page 154
Adding Print Titles......Page 155
Creating Page Headers and Footers......Page 156
Session 2.2 Quick Check......Page 158
Review Assignments......Page 159
Case Problems......Page 160
Tutorial 3 Working with Formulas and Functions Developing a Family Budget\n......Page 166
SESSION 3.1......Page 167
Understanding Cell References......Page 169
Using Relative References......Page 171
Using Absolute References......Page 172
Using Mixed References......Page 176
Understanding Function Syntax......Page 178
Inserting a Function......Page 182
Typing a Function......Page 185
Session 3.1 Quick Check......Page 188
SESSION 3.2......Page 189
Entering Data and Formulas with AutoFill......Page 191
Using the Fill Handle......Page 192
Filling a Series......Page 194
Developing a Savings Plan......Page 198
Using the IF Function......Page 202
Working with Date Functions......Page 206
Working with Financial Functions......Page 207
Using the PMT Function......Page 209
Session 3.2 Quick Check......Page 213
Review Assignments......Page 214
Case Problems......Page 215
Tutorial 4 Enhancing a Workbook with Charts and Graphs Charting Financial Data\n......Page 220
SESSION 4.1......Page 221
Creating an Excel Chart......Page 223
Selecting a Chart Type......Page 224
Moving a Chart to a Different Worksheet......Page 226
Moving and Resizing Charts......Page 227
Choosing a Chart Style......Page 228
Choosing a Chart Layout......Page 229
Formatting the Chart Title......Page 230
Formatting the Chart Legend......Page 231
Formatting Data Labels......Page 233
Changing Pie Slice Colors......Page 234
Creating a Column Chart......Page 235
Inserting a Column Chart......Page 236
Formatting a Column Chart Title and Legend......Page 237
Editing the Axis Scale and Text......Page 238
Formatting the Chart Columns......Page 240
Session 4.1 Quick Check......Page 242
SESSION 4.2......Page 243
Creating a Line Chart \n......Page 245
Formatting Date Labels......Page 246
Setting Label Units......Page 249
Overlaying a Chart Legend......Page 250
Adding an Axis Title......Page 252
Changing a Data Value......Page 253
Adding a Data Series to an Existing Chart......Page 254
Creating a Combination Chart......Page 256
Creating a 3-D Chart......Page 260
Creating Sparklines......Page 262
Adding and Formatting Sparkline Markers......Page 265
Creating Data Bars......Page 267
Modifying a Data Bar Rule......Page 268
Creating a Chart Sheet......Page 270
Session 4.2 Quick Check......Page 273
Review Assignments......Page 274
Case Problems......Page 275
ProSkills Exercise: Decision Making......Page 280
Tutorial 5 Working with Excel Tables, PivotTables, and PivotCharts Tracking Museum Art Objects\n......Page 284
SESSION 5.1......Page 285
Planning a Structured Range of Data......Page 287
Freezing Rows and Columns......Page 289
Creating an Excel Table......Page 290
Renaming an Excel Table......Page 291
Maintaining Data in an Excel Table......Page 292
Adding Records......Page 293
Finding and Editing Records......Page 294
Sorting One Column Using the Sort Buttons......Page 295
Sorting Multiple Columns Using the Sort Dialog Box......Page 296
Sorting Using a Custom List......Page 298
Session 5.1 Quick Check......Page 300
SESSION 5.2......Page 301
Filtering Using One Column......Page 303
Filtering Using Multiple Columns......Page 305
Selecting Multiple Filter Items......Page 306
Creating Criteria Filters to Specify More Complex Criteria......Page 307
Using the Total Row to Calculate Summary Statistics......Page 310
Splitting the Worksheet Window into Panes......Page 312
Inserting Subtotals......Page 314
Using the Subtotal Outline View......Page 317
Session 5.2 Quick Check......Page 318
SESSION 5.3......Page 319
Analyzing Data with PivotTables......Page 321
Creating a PivotTable......Page 323
Adding Fields to a PivotTable......Page 325
Formatting PivotTable Values Fields......Page 327
Rearranging a PivotTable......Page 328
Changing the PivotTable Report Layout Options......Page 329
Adding a Report Filter to a PivotTable......Page 330
Filtering PivotTable Fields......Page 332
Collapsing and Expanding Items......Page 333
Adding a Second Values Field to a PivotTable......Page 334
Removing a Field, Field Headers, and Buttons from a PivotTable......Page 335
Using a Slicer to Filter a PivotTable......Page 336
Refreshing a PivotTable......Page 338
Grouping PivotTable Items......Page 339
Grouping Date Fields......Page 341
Creating a PivotChart......Page 342
Session 5.3 Quick Check......Page 344
Case Problems......Page 345
Tutorial 6 Managing Multiple Worksheets and Workbooks Summarizing Ticket Sales\n......Page 352
SESSION 6.1......Page 353
Grouping Worksheets......Page 355
Entering Formulas in a Worksheet Group......Page 357
Ungrouping Worksheets......Page 359
Copying Worksheets......Page 360
Referencing Cells and Ranges in Other Worksheets......Page 361
Using 3-D References to Add Values Across Worksheets......Page 363
Printing a Worksheet Group......Page 368
Session 6.1 Quick Check......Page 370
SESSION 6.2......Page 371
Linking Workbooks......Page 373
Navigating Multiple Workbooks......Page 374
Arranging Multiple Workbooks......Page 375
Creating Formulas with External References......Page 376
Updating a Destination Workbook with Source Workbooks Open......Page 380
Updating a Destination Workbook with Source Workbooks Closed......Page 381
Managing Links......Page 383
Creating an Excel Workspace......Page 384
Session 6.2 Quick Check......Page 386
SESSION 6.3......Page 387
Inserting a Hyperlink......Page 389
Creating Templates......Page 391
Creating a Workbook Based on an Existing Template......Page 392
Creating a Custom Workbook Template......Page 395
Creating a New Workbook from a Template......Page 397
Using the Excel Web App and SkyDrive......Page 399
Saving a Workbook to SkyDrive......Page 400
Editing a Workbook with the Excel Web App......Page 401
Session 6.3 Quick Check......Page 403
Review Assignments......Page 404
Case Problems......Page 405
Tutorial 7 Using Advanced Functions and Conditional Formatting Calculating Employee Benefits\n......Page 414
SESSION 7.1......Page 415
Inserting Calculated Columns in an Excel Table......Page 417
Using the IF Function......Page 418
Using the AND Function......Page 421
Using the OR Function......Page 425
Using Structured References to Create Formulas in Excel Tables......Page 427
Section 7.1 Quick Check.......Page 430
SESSION 7.2......Page 431
Creating Nested IFs......Page 433
Using Lookup Tables and Functions......Page 438
Using the VLOOKUP Function to Find an Exact Match......Page 439
Using the VLOOKUP Function to Find an Approximate Match......Page 441
Using the IFERROR Function......Page 445
Session 7.2 Quick Check......Page 448
SESSION 7.3......Page 449
Highlighting Duplicate Values......Page 451
Using the Conditional Formatting Rules Manager......Page 453
Summarizing Data Conditionally......Page 455
Using the COUNTIF Function......Page 456
Using the SUMIF Function......Page 458
Using the AVERAGEIF Function......Page 459
Session 7.3 Quick Check......Page 462
Review Assignments......Page 463
Case Problems......Page 464
Tutorial 8 Developing an Excel Application Creating an Invoice\n......Page 470
SESSION 8.1......Page 471
Planning an Excel Application\n......Page 473
Naming Cells and Ranges......Page 474
Creating Defined Names......Page 475
Using the Paste Names Command......Page 479
Using Defined Names in Formulas......Page 480
Adding Defined Names to Existing Formulas......Page 484
Session 8.1 Quick Check......Page 486
SESSION 8.2......Page 487
Specifying Validation Criteria......Page 489
Creating an Error Alert Style and Message......Page 491
Creating a List Validation Rule......Page 493
Protecting a Worksheet......Page 496
Protecting a Workbook......Page 498
Unprotecting a Worksheet and a Workbook......Page 499
Inserting Comments......Page 500
Session 8.2 Quick Check......Page 502
SESSION 8.3......Page 503
Automating Tasks with Macros......Page 505
Macro Security Settings......Page 506
Recording a Macro......Page 508
Running a Macro......Page 511
Creating the TransferData Macro......Page 513
Working with the Visual Basic Editor......Page 515
Editing a Macro Using the Visual Basic Editor......Page 517
Creating Macro Buttons......Page 519
Saving Workbooks with Macros......Page 522
Customizing the Ribbon......Page 523
Expanding the Ribbon......Page 524
Session 8.3 Quick Check......Page 525
Review Assignments......Page 526
Case Problems......Page 528
ProSkills Exercise: Problem Solving......Page 537
Appendix A Working with Text Functions and Creating Custom Formats Cleaning Data in a Spreadsheet\n......Page 540
Opening and Saving Workbooks Created in Earlier Versions of Excel......Page 541
Using Text Functions......Page 542
Using the LEN and LEFT Functions......Page 543
Using the Paste Values Command......Page 544
Using the PROPER Function and the & Operator......Page 545
Using the Text to Columns Command......Page 546
Using the UPPER Function to Convert Case......Page 547
Using the SUBSTITUTE Function......Page 548
Using Special Formats......Page 549
Creating a Custom Number Format......Page 550
Creating a Custom Date Format......Page 552
Using the Compatibility Checker......Page 553
Review Assignments......Page 554
Case Problems......Page 555
Appendix B Advanced Filters, Database, and Summary IFS Functions Generating a List of Eligible Employee Candidates\n......Page 556
Understanding the Criteria Range......Page 557
Creating a Criteria Range......Page 558
Using Database Functions to Summarize Data......Page 560
Summarizing Data Using the COUNTIFS, SUMIFS, and AVERAGEIFS Functions......Page 564
Case Problems......Page 570
GLOSSARY/INDEX......Page 572
TASK REFERENCE......Page 580