Microsoft Excel 2010 On Demand
Published on 15. July 2010
Book
Paperback/Softback
576 pages
978-0-7897-4277-3 (ISBN)
Description
What you need, when you need it!
Need answers quickly? Microsoft Excel 2010 on Demand provides those answers in a visual step-by-step format. We will show you exactly what to do through lots of full color illustrations and easy-to-follow instructions.
Includes
Workshops
MCAS Exam Prep
More than 500 Essential Excel Tasks
Inside the Book
* Create workbooks more efficiently using the improved Ribbon interface
* Use data-analysis tools and techniques for better decision making
* Use organizing, processing, and presenting tools to create and analyze data
* Integrate data from external sources and add hyperlinks
* Use conditional formatting and Sparklines to visualize results
* Add impact to your data with PivotTable and PivotChart reports
* Organize information and add impact with clip art, SmartArt diagrams, tables, and charts
* Use SharePoint to collaborate and share documents
* Use the Excel Web App to view and edit documents in a browser
* Prepare for the Microsoft Certified Application Specialist (MCAS) exam
Bonus Online Content
Register your book at queondemand.com to gain access to:
* Workshops and related files
* Keyboard shortcuts
Need answers quickly? Microsoft Excel 2010 on Demand provides those answers in a visual step-by-step format. We will show you exactly what to do through lots of full color illustrations and easy-to-follow instructions.
Includes
Workshops
MCAS Exam Prep
More than 500 Essential Excel Tasks
Inside the Book
* Create workbooks more efficiently using the improved Ribbon interface
* Use data-analysis tools and techniques for better decision making
* Use organizing, processing, and presenting tools to create and analyze data
* Integrate data from external sources and add hyperlinks
* Use conditional formatting and Sparklines to visualize results
* Add impact to your data with PivotTable and PivotChart reports
* Organize information and add impact with clip art, SmartArt diagrams, tables, and charts
* Use SharePoint to collaborate and share documents
* Use the Excel Web App to view and edit documents in a browser
* Prepare for the Microsoft Certified Application Specialist (MCAS) exam
Bonus Online Content
Register your book at queondemand.com to gain access to:
* Workshops and related files
* Keyboard shortcuts
More details
Language
English
Place of publication
United States
Publishing group
Pearson Education (US)
Target group
Professional and scholarly
Dimensions
Height: 231 mm
Width: 191 mm
Thickness: 24 mm
Weight
964 gr
ISBN-13
978-0-7897-4277-3 (9780789742773)
Copyright in bibliographic data and cover images is held by Nielsen Book Services Limited or by the publishers or by their respective licensors: all rights reserved.
Schweitzer Classification
Other editions
New editions
.. Perspection Inc. | Steve Johnson
Excel 2013 On Demand
Book
03/2013
Que Corporation,U.S.
€47.22
Article is exhausted; no reprint
Persons
Steve Johnson has written more than 50 books on a variety of computer software, including Adobe Photoshop CS4, Adobe Flash CS4, Adobe Dreamweaver CS4, Adobe InDesign CS4, Adobe Illustrator CS4, Microsoft Windows 7, Microsoft Office 2007, Microsoft Office 2008 for the Macintosh, and Apple Mac OS X Snow Leopard. In 1991, after working for Apple Computer and Microsoft, Steve founded Perspection, Inc., which writes and produces software training. When he is not staying up late writing, he enjoys playing golf, gardening, and spending time with his wife, Holly, and three children, JP, Brett, and Hannah. Steve and his family live in Pleasanton, California, but can also be found visiting family all over the western United States.
Content
Introduction xvii
Chapter 1 Getting Started with Excel 1
Starting Excel 2 New!
Viewing the Excel Window 3
Using the Ribbon 4 New!
Choosing Commands 5 New!
Working with Toolbars 6 New!
Choosing Dialog Box Options 8
Using the Status Bar 9
Creating a Blank Workbook 10
Creating a Workbook Using a Template 11
Opening an Existing Workbook 12
Converting an Existing Workbook 14
Using Task and Window Panes 15
Moving Around the Workbook 16
Arranging Windows 18
Getting Help While You Work 20
Saving a Workbook 22
Saving a Workbook with Different Formats 24
Checking Compatibility 26
Checking Accessibility 27 New!
Documenting Workbook Properties 28
Switching Views 29
Recovering a Workbook 30 New!
Maintaining and Repairing Office 32
Getting Updates on the Web 33
Closing a Workbook and Quitting Excel 34
Chapter 2 Basic Workbook Skills 35
Making Label Entries 36
Selecting Cells 37
Selecting Rows, Columns, and Special Ranges 38
Entering Labels on a Worksheet 40
Entering Values on a Worksheet 42
Entering Values Quickly with AutoFill 44
Editing Cell Contents 46
Clearing Cell Contents 47
Understanding How Excel Pastes Data 48
Storing Cell Contents 49
Copying Cell Contents 50 New!
Moving Cell Contents 52 New!
Inserting and Deleting Cell Contents 54
Finding and Replacing Cell Contents 56
Correcting Cell Contents with AutoCorrect 58 New!
Inserting Information the Smart Way 60 New!
Checking Spelling 62
Changing Proofing Options 63
Using Custom Dictionaries 64
Inserting Symbols 66
Finding the Right Words 67
Inserting Research Material 68
Translating Text to Another Language 69
Undoing and Redoing an Action 70
Chapter 3 Working with Formulas and Functions 71
Understanding Formulas 72
Creating a Simple Formula 74
Creating a Formula Using Formula AutoComplete 76
Editing a Formula 78New!
Understanding Cell Referencing 80
Using Absolute Cell References 81
Using Mixed Cell References 82
Using 3-D Cell References 83
Naming Cells and Ranges 84
Entering Named Cells and Ranges 86
Managing Names 88
Simplifying a Formula with Ranges 90
Displaying Calculations with the Status Bar 91
Calculating Totals with AutoSum 92
Performing One Time Calculations 94
Converting Formulas and Values 96
Correcting Calculation Errors 97
Correcting Formulas 98
Auditing a Worksheet 100
Locating Circular References 101
Performing Calculations Using Functions 102
Creating Functions 103
Creating Functions Using the Library 104 New!
Calculating Multiple Results 105
Using Nested Functions 106
Using Constants and Functions in Names 108
Chapter 4 Modifying Worksheets and Workbooks 109
Selecting and Naming a Worksheet 110
Inserting and Deleting a Worksheet 111
Moving and Copying a Worksheet 112
Hiding and Unhiding Worksheets and Workbooks 114
Selecting a Column or Row 116
Hiding and Unhiding a Column or Row 117
Inserting a Column or Row 118
Deleting a Column or Row 119
Adjusting Column Width and Row Height 120
Freezing and Unfreezing a Column or Row 122
Splitting a Worksheet into Panes 123
Showing and Hiding Workbook Elements 124
Zooming the View In and Out 125
Creating Custom Views 126
Saving a Workspace Layout 127
Creating a Template 128
Opening a Template 129
Changing a Template 130
Chapter 5 Formatting a Worksheet 131
Formatting Numbers 132
Formatting Text 134
Applying Conditional Formatting 136
Applying Specialized Conditional Formatting 138
Creating Conditional Formatting 140
Clearing Conditional Formatting 141
Managing Conditional Formatting 142
Finding Conditional Formatting 143
Creating Sparkline Formatting 144 New!
Changing Data Alignment 146
Controlling Text Flow 148
Changing Data Color 149
Adding Color and Patterns to Cells 150
Adding Custom Colors 151
Adding Borders to Cells 152
Formatting Tabs and Background 154
Copying Cell Formats 155
Understanding Color Themes 156
Viewing and Applying a Theme 157 New!
Creating Theme Colors 158
Choosing Theme Fonts 160
Choosing Theme Effects 162
Creating a Custom Theme 163
Choosing a Custom Theme 164
Applying and Creating Cell Styles 166
Modifying a Cell Style 168
Finding and Replacing Cell Formatting 170
Chapter 6 Viewing and Printing Worksheets and Workbooks 171
Setting Up the Page 172
Adjusting Page Margins 174
Adding Headers and Footers 176
Inserting Page Breaks 178
Customizing Worksheet Printing 180
Setting the Print Area 182
Previewing a Worksheet 183 New!
Printing a Worksheet and Workbook 184 New!
Creating a PDF Document 185
Creating an XPS Document 186
Chapter 7 Inserting and Modifying Graphics 187
Locating and Inserting Clip Art 188 New!
Inserting Media Clips 189
Accessing Clip Art on the Web 190
Organizing Clips into Categories 192
Adding and Removing Clips 194
Inserting a Picture 195
Inserting a Picture Screen Shot 196 New!
Adding an Artistic Style to a Picture 197 New!
Adding a Quick Style to a Picture 198
Applying a Shape to a Picture 199 New!
Applying a Border to a Picture 200
Applying Picture Effects 201
Modifying Picture Size 202
Compressing a Picture 204 New!
Correcting a Picture 205 New!
Recoloring a Picture 206 New!
Cropping and Rotating a Picture 208 New!
Removing a Picture Background 210 New!
Creating WordArt Text 211
Formatting WordArt Text 212
Applying WordArt Text Effects 214
Modifying WordArt Text Position 215
Creating SmartArt Graphics 216 New!
Using the Text Pane with SmartArt Graphics 218
Modifying a SmartArt Graphic 220
Resizing a SmartArt Graphic 221
Formatting a SmartArt Graphic 222
Formatting a Shape in a SmartArt Graphic 224
Adding Pictures to a SmartArt Graphic 226 New!
Creating an Organization Chart 227
Modifying an Organization Chart 228
Chapter 8 Drawing and Modifying Shapes 229
Drawing and Resizing Shapes 230
Inserting Multiple Shapes 232
Adding Text to a Shape 233
Drawing Lines and Arrows 234
Creating and Editing Freeforms 236
Copying and Moving Objects 237 New!
Adding a Quick Style to a Shape 238
Adding a Quick Style to Shape Text 239
Applying Color Fills 240
Applying Picture Fills 242
Applying Texture Fills 243
Applying Gradient Fills 244 New!
Applying Shape Effects 246
Adding 3-D Effects to a Shape 248
Adding 3-D Rotation Effects to a Shape 250
Creating Shadows 251
Aligning and Distributing Objects 252
Connecting Shapes 254
Selecting Objects Using the Selection Pane 255
Changing Stacking Order 256
Rotating and Flipping a Shape 257
Grouping and Ungrouping Shapes 258
Adding a Shape to the Clip Organizer 260
Chapter 9 Creating and Modifying Charts 261
Understanding Chart Terminology 262
Choosing the Right Type of Chart 263
Creating a Chart 264
Editing a Chart 266
Moving and Resizing a Chart 268
Selecting Chart Elements 269
Changing a Chart Type 270
Changing a Chart Layout and Style 272 New!
Formatting Chart Elements 274 New!
Changing Chart Gridlines and Axes 276
Changing Chart Titles 278
Changing Chart Labels 280
Pulling Out a Pie Slice 282
Formatting Chart Data Series 283
Formatting Chart Text 284
Formatting Line and Bar Charts 285
Changing the Chart Background 286
Enhancing a Chart 287
Editing Chart Data 288 New!
Adding and Deleting a Data Series 290
Saving a Chart Template 292
Managing Chart Templates 294
Chapter 10 Analyzing Worksheet Data 295
Understanding Tables 296
Creating a Table 297
Formatting a Table 298
Creating or Modifying a Table Style 299
Formatting Table Elements 300
Creating Calculations in a Table 301
Working with Tables 302
Removing Table Rows and Columns 304
Entering Data in a Table Using a Drop-Down List 305
Sorting Data in a Table 306
Displaying Parts of a Table with AutoFilter 308 New!
Creating Custom Searches 309
Analyzing Data Using a PivotTable or PivotChart 310
Updating a PivotTable or PivotChart 311
Modifying a PivotTable 312 New!
Formatting a PivotTable 313
Working with PivotTable Data 314 New!
Using Slicers to Filter a PivotTable 316 New!
Charting a PivotTable 318 New!
Creating Groups and Outlines 320
Converting Text to Columns 321
Adding Data Validation to a Worksheet 322
Creating a Drop-Down List 323
Exporting a Table to a SharePoint List 324
Chapter 11 Building More Powerful Worksheets 325
Using Data Analysis Tools 326
Using the Euro Conversion 327
Looking at Alternatives with Data Tables 328
Asking "What If" with Goal Seek 329
Creating Scenarios 330
Using Solver 332 New!
Using Lookup and Reference Functions 334
Using Text Functions 336
Summarizing Data Using Subtotals 337
Summarizing Data Using Functions 338
Using Date & Time Functions 340
Using Logical Functions 342
Using Financial Functions 343
Using Math Functions 344
Using Statistical Functions 345 New!
Using Information Functions 346
Using Compatibility Functions 347 New!
Chapter 12 Protecting and Securing a Workbook 349
Inspecting Workbooks 350
Protecting a Worksheet 352
Locking and Unlocking Worksheet Cells 354
Adding Security Encryption to a Workbook 355
Adding Password Protection to a Workbook 356
Adding a Digital Signature 358
Adding a Signature Line 360
Avoiding Harmful Attacks 362
Using the Trust Center 364 New!
Selecting Trusted Publishers and Locations 365
Setting Document Related Security Options 366 New!
Setting Add-in Security Options 368
Setting ActiveX Security Options 369
Setting Macro Security Options 370
Changing Message Bar Security Options 371
Setting Privacy Options 372
Setting External Content Security Options 374
Working with Office Safe Modes 376
Marking a Workbook as Read-Only 378
Chapter 13 Reviewing and Sharing Workbook Data 379
Sharing Workbooks 380
Creating and Reading a Cell Comment 382
Editing and Deleting a Cell Comment 383
Tracking Changes 384
Sending a Workbook for Review Using E-Mail 386
Sending a Workbook by Internet Fax 387
Working with XML 388 New!
Creating an XML Data Map 390
Exporting and Saving Data in XML 392
Sharing Information Between Programs 394
Exporting and Importing Data 396 New!
Linking and Embedding Files 398 New!
Consolidating Data 400
Linking Data 402
Getting External Data 403
Managing Connections 404
Getting Query Data from a Database 406
Getting Data from Microsoft Access 408
Getting Text Data 410
Chapter 14 Publishing Data on the Web 411
Opening a Workbook as a Web Page 412
Previewing a Web Page 413
Creating a Hyperlink 414
Formatting a Hyperlink 416
Changing Web Page Options 41714
Saving a Worksheet as a Web Page 418
Publishing a Web Page 420
Copying a Web Table to a Worksheet 421
Creating Refreshable Web Queries 422
Getting Data from Web Queries 423
Saving Web Queries 424
Accessing Office Information on the Web 425
Getting Documents from the Web 426
Chapter 15 Tools for Working More Efficiently 427
Getting General Excel Options 428
Setting New Workbook Options 429
Setting Editing Options 430 New!
Setting Image and Chart Options 432 New!
Setting Display View Options 433 New!
Setting Workbook and Worksheet Display Options 434 New!
Setting Advanced Options 436 New!
Setting Formula Options 437
Changing Advanced Document Properties 438
Changing Research Options 439
Customizing the Way You Create Objects 440
Accessing Commands Not in the Ribbon 441
Managing Pictures 442
Using Multiple Languages 444 New!
Chapter 16 Expanding Excel Functionality 445
Viewing and Managing Add-ins 446
Loading and Unloading Add-ins 448
Enhancing a Workbook with VBA 450
Viewing the Visual Basic Editor 452
Setting Developer Options 453
Understanding How Macros Automate Your Work 454
Recording a Macro 455 New!
Creating a Macro 456
Running a Macro 457
Controlling a Macro 458
Adding a Digital Signature to a Macro Project 460
Assigning a Macro to a Toolbar 461
Saving a Workbook with Macros 462
Opening a Workbook with Macros 463
Inserting ActiveX Controls 464
Using ActiveX Controls 466
Setting ActiveX Control Properties 467
Adding VBA Code to an ActiveX Control 468
Playing a Movie Using an ActiveX Control 469
Changing the Document Information Panel 470
Chapter 17 Working Together on Office Documents 471
Getting Started with Office Web Apps 472 New!
Setting Up to Use Office Web Apps 474 New!
Saving and Opening Documents with Windows Live 476 New!
Comparing the Desktop App to Web App 478 New!
Accessing Documents on Windows Live 480 New!
Working with Folders on Windows Live 482
Setting Folder Permissions on Windows Live 484
Creating Office Documents on Windows Live 485 New!
Working with Documents on Windows Live 486
Downloading Documents from Windows Live 488
Downloading or Saving Documents in Office Web Apps 490 New!
Collaborating with Documents on Windows Live 492 New!
Working with SharePoint Workspaces 494 New!
Sharing Documents in a Groove Workspace 496 New!
Inviting Others to a Groove Workspace 497 New!
Saving a Document to a SharePoint Server 498 New!
Using Office Mobile 2010 500 New!
Workshops: Putting It All Together 501
Project 1: Creating a Drop-Down List 501
Project 2: Adding a Form Control 503
Project 3: Adding Conditional Formatting 504
Project 4: Creating a VBA Script 506
Project 5: Creating a VBA Interface 508
Want More Projects 513
New Features 515 New!
Microsoft Certified Applications Specialist 521
Index 529
Chapter 1 Getting Started with Excel 1
Starting Excel 2 New!
Viewing the Excel Window 3
Using the Ribbon 4 New!
Choosing Commands 5 New!
Working with Toolbars 6 New!
Choosing Dialog Box Options 8
Using the Status Bar 9
Creating a Blank Workbook 10
Creating a Workbook Using a Template 11
Opening an Existing Workbook 12
Converting an Existing Workbook 14
Using Task and Window Panes 15
Moving Around the Workbook 16
Arranging Windows 18
Getting Help While You Work 20
Saving a Workbook 22
Saving a Workbook with Different Formats 24
Checking Compatibility 26
Checking Accessibility 27 New!
Documenting Workbook Properties 28
Switching Views 29
Recovering a Workbook 30 New!
Maintaining and Repairing Office 32
Getting Updates on the Web 33
Closing a Workbook and Quitting Excel 34
Chapter 2 Basic Workbook Skills 35
Making Label Entries 36
Selecting Cells 37
Selecting Rows, Columns, and Special Ranges 38
Entering Labels on a Worksheet 40
Entering Values on a Worksheet 42
Entering Values Quickly with AutoFill 44
Editing Cell Contents 46
Clearing Cell Contents 47
Understanding How Excel Pastes Data 48
Storing Cell Contents 49
Copying Cell Contents 50 New!
Moving Cell Contents 52 New!
Inserting and Deleting Cell Contents 54
Finding and Replacing Cell Contents 56
Correcting Cell Contents with AutoCorrect 58 New!
Inserting Information the Smart Way 60 New!
Checking Spelling 62
Changing Proofing Options 63
Using Custom Dictionaries 64
Inserting Symbols 66
Finding the Right Words 67
Inserting Research Material 68
Translating Text to Another Language 69
Undoing and Redoing an Action 70
Chapter 3 Working with Formulas and Functions 71
Understanding Formulas 72
Creating a Simple Formula 74
Creating a Formula Using Formula AutoComplete 76
Editing a Formula 78New!
Understanding Cell Referencing 80
Using Absolute Cell References 81
Using Mixed Cell References 82
Using 3-D Cell References 83
Naming Cells and Ranges 84
Entering Named Cells and Ranges 86
Managing Names 88
Simplifying a Formula with Ranges 90
Displaying Calculations with the Status Bar 91
Calculating Totals with AutoSum 92
Performing One Time Calculations 94
Converting Formulas and Values 96
Correcting Calculation Errors 97
Correcting Formulas 98
Auditing a Worksheet 100
Locating Circular References 101
Performing Calculations Using Functions 102
Creating Functions 103
Creating Functions Using the Library 104 New!
Calculating Multiple Results 105
Using Nested Functions 106
Using Constants and Functions in Names 108
Chapter 4 Modifying Worksheets and Workbooks 109
Selecting and Naming a Worksheet 110
Inserting and Deleting a Worksheet 111
Moving and Copying a Worksheet 112
Hiding and Unhiding Worksheets and Workbooks 114
Selecting a Column or Row 116
Hiding and Unhiding a Column or Row 117
Inserting a Column or Row 118
Deleting a Column or Row 119
Adjusting Column Width and Row Height 120
Freezing and Unfreezing a Column or Row 122
Splitting a Worksheet into Panes 123
Showing and Hiding Workbook Elements 124
Zooming the View In and Out 125
Creating Custom Views 126
Saving a Workspace Layout 127
Creating a Template 128
Opening a Template 129
Changing a Template 130
Chapter 5 Formatting a Worksheet 131
Formatting Numbers 132
Formatting Text 134
Applying Conditional Formatting 136
Applying Specialized Conditional Formatting 138
Creating Conditional Formatting 140
Clearing Conditional Formatting 141
Managing Conditional Formatting 142
Finding Conditional Formatting 143
Creating Sparkline Formatting 144 New!
Changing Data Alignment 146
Controlling Text Flow 148
Changing Data Color 149
Adding Color and Patterns to Cells 150
Adding Custom Colors 151
Adding Borders to Cells 152
Formatting Tabs and Background 154
Copying Cell Formats 155
Understanding Color Themes 156
Viewing and Applying a Theme 157 New!
Creating Theme Colors 158
Choosing Theme Fonts 160
Choosing Theme Effects 162
Creating a Custom Theme 163
Choosing a Custom Theme 164
Applying and Creating Cell Styles 166
Modifying a Cell Style 168
Finding and Replacing Cell Formatting 170
Chapter 6 Viewing and Printing Worksheets and Workbooks 171
Setting Up the Page 172
Adjusting Page Margins 174
Adding Headers and Footers 176
Inserting Page Breaks 178
Customizing Worksheet Printing 180
Setting the Print Area 182
Previewing a Worksheet 183 New!
Printing a Worksheet and Workbook 184 New!
Creating a PDF Document 185
Creating an XPS Document 186
Chapter 7 Inserting and Modifying Graphics 187
Locating and Inserting Clip Art 188 New!
Inserting Media Clips 189
Accessing Clip Art on the Web 190
Organizing Clips into Categories 192
Adding and Removing Clips 194
Inserting a Picture 195
Inserting a Picture Screen Shot 196 New!
Adding an Artistic Style to a Picture 197 New!
Adding a Quick Style to a Picture 198
Applying a Shape to a Picture 199 New!
Applying a Border to a Picture 200
Applying Picture Effects 201
Modifying Picture Size 202
Compressing a Picture 204 New!
Correcting a Picture 205 New!
Recoloring a Picture 206 New!
Cropping and Rotating a Picture 208 New!
Removing a Picture Background 210 New!
Creating WordArt Text 211
Formatting WordArt Text 212
Applying WordArt Text Effects 214
Modifying WordArt Text Position 215
Creating SmartArt Graphics 216 New!
Using the Text Pane with SmartArt Graphics 218
Modifying a SmartArt Graphic 220
Resizing a SmartArt Graphic 221
Formatting a SmartArt Graphic 222
Formatting a Shape in a SmartArt Graphic 224
Adding Pictures to a SmartArt Graphic 226 New!
Creating an Organization Chart 227
Modifying an Organization Chart 228
Chapter 8 Drawing and Modifying Shapes 229
Drawing and Resizing Shapes 230
Inserting Multiple Shapes 232
Adding Text to a Shape 233
Drawing Lines and Arrows 234
Creating and Editing Freeforms 236
Copying and Moving Objects 237 New!
Adding a Quick Style to a Shape 238
Adding a Quick Style to Shape Text 239
Applying Color Fills 240
Applying Picture Fills 242
Applying Texture Fills 243
Applying Gradient Fills 244 New!
Applying Shape Effects 246
Adding 3-D Effects to a Shape 248
Adding 3-D Rotation Effects to a Shape 250
Creating Shadows 251
Aligning and Distributing Objects 252
Connecting Shapes 254
Selecting Objects Using the Selection Pane 255
Changing Stacking Order 256
Rotating and Flipping a Shape 257
Grouping and Ungrouping Shapes 258
Adding a Shape to the Clip Organizer 260
Chapter 9 Creating and Modifying Charts 261
Understanding Chart Terminology 262
Choosing the Right Type of Chart 263
Creating a Chart 264
Editing a Chart 266
Moving and Resizing a Chart 268
Selecting Chart Elements 269
Changing a Chart Type 270
Changing a Chart Layout and Style 272 New!
Formatting Chart Elements 274 New!
Changing Chart Gridlines and Axes 276
Changing Chart Titles 278
Changing Chart Labels 280
Pulling Out a Pie Slice 282
Formatting Chart Data Series 283
Formatting Chart Text 284
Formatting Line and Bar Charts 285
Changing the Chart Background 286
Enhancing a Chart 287
Editing Chart Data 288 New!
Adding and Deleting a Data Series 290
Saving a Chart Template 292
Managing Chart Templates 294
Chapter 10 Analyzing Worksheet Data 295
Understanding Tables 296
Creating a Table 297
Formatting a Table 298
Creating or Modifying a Table Style 299
Formatting Table Elements 300
Creating Calculations in a Table 301
Working with Tables 302
Removing Table Rows and Columns 304
Entering Data in a Table Using a Drop-Down List 305
Sorting Data in a Table 306
Displaying Parts of a Table with AutoFilter 308 New!
Creating Custom Searches 309
Analyzing Data Using a PivotTable or PivotChart 310
Updating a PivotTable or PivotChart 311
Modifying a PivotTable 312 New!
Formatting a PivotTable 313
Working with PivotTable Data 314 New!
Using Slicers to Filter a PivotTable 316 New!
Charting a PivotTable 318 New!
Creating Groups and Outlines 320
Converting Text to Columns 321
Adding Data Validation to a Worksheet 322
Creating a Drop-Down List 323
Exporting a Table to a SharePoint List 324
Chapter 11 Building More Powerful Worksheets 325
Using Data Analysis Tools 326
Using the Euro Conversion 327
Looking at Alternatives with Data Tables 328
Asking "What If" with Goal Seek 329
Creating Scenarios 330
Using Solver 332 New!
Using Lookup and Reference Functions 334
Using Text Functions 336
Summarizing Data Using Subtotals 337
Summarizing Data Using Functions 338
Using Date & Time Functions 340
Using Logical Functions 342
Using Financial Functions 343
Using Math Functions 344
Using Statistical Functions 345 New!
Using Information Functions 346
Using Compatibility Functions 347 New!
Chapter 12 Protecting and Securing a Workbook 349
Inspecting Workbooks 350
Protecting a Worksheet 352
Locking and Unlocking Worksheet Cells 354
Adding Security Encryption to a Workbook 355
Adding Password Protection to a Workbook 356
Adding a Digital Signature 358
Adding a Signature Line 360
Avoiding Harmful Attacks 362
Using the Trust Center 364 New!
Selecting Trusted Publishers and Locations 365
Setting Document Related Security Options 366 New!
Setting Add-in Security Options 368
Setting ActiveX Security Options 369
Setting Macro Security Options 370
Changing Message Bar Security Options 371
Setting Privacy Options 372
Setting External Content Security Options 374
Working with Office Safe Modes 376
Marking a Workbook as Read-Only 378
Chapter 13 Reviewing and Sharing Workbook Data 379
Sharing Workbooks 380
Creating and Reading a Cell Comment 382
Editing and Deleting a Cell Comment 383
Tracking Changes 384
Sending a Workbook for Review Using E-Mail 386
Sending a Workbook by Internet Fax 387
Working with XML 388 New!
Creating an XML Data Map 390
Exporting and Saving Data in XML 392
Sharing Information Between Programs 394
Exporting and Importing Data 396 New!
Linking and Embedding Files 398 New!
Consolidating Data 400
Linking Data 402
Getting External Data 403
Managing Connections 404
Getting Query Data from a Database 406
Getting Data from Microsoft Access 408
Getting Text Data 410
Chapter 14 Publishing Data on the Web 411
Opening a Workbook as a Web Page 412
Previewing a Web Page 413
Creating a Hyperlink 414
Formatting a Hyperlink 416
Changing Web Page Options 41714
Saving a Worksheet as a Web Page 418
Publishing a Web Page 420
Copying a Web Table to a Worksheet 421
Creating Refreshable Web Queries 422
Getting Data from Web Queries 423
Saving Web Queries 424
Accessing Office Information on the Web 425
Getting Documents from the Web 426
Chapter 15 Tools for Working More Efficiently 427
Getting General Excel Options 428
Setting New Workbook Options 429
Setting Editing Options 430 New!
Setting Image and Chart Options 432 New!
Setting Display View Options 433 New!
Setting Workbook and Worksheet Display Options 434 New!
Setting Advanced Options 436 New!
Setting Formula Options 437
Changing Advanced Document Properties 438
Changing Research Options 439
Customizing the Way You Create Objects 440
Accessing Commands Not in the Ribbon 441
Managing Pictures 442
Using Multiple Languages 444 New!
Chapter 16 Expanding Excel Functionality 445
Viewing and Managing Add-ins 446
Loading and Unloading Add-ins 448
Enhancing a Workbook with VBA 450
Viewing the Visual Basic Editor 452
Setting Developer Options 453
Understanding How Macros Automate Your Work 454
Recording a Macro 455 New!
Creating a Macro 456
Running a Macro 457
Controlling a Macro 458
Adding a Digital Signature to a Macro Project 460
Assigning a Macro to a Toolbar 461
Saving a Workbook with Macros 462
Opening a Workbook with Macros 463
Inserting ActiveX Controls 464
Using ActiveX Controls 466
Setting ActiveX Control Properties 467
Adding VBA Code to an ActiveX Control 468
Playing a Movie Using an ActiveX Control 469
Changing the Document Information Panel 470
Chapter 17 Working Together on Office Documents 471
Getting Started with Office Web Apps 472 New!
Setting Up to Use Office Web Apps 474 New!
Saving and Opening Documents with Windows Live 476 New!
Comparing the Desktop App to Web App 478 New!
Accessing Documents on Windows Live 480 New!
Working with Folders on Windows Live 482
Setting Folder Permissions on Windows Live 484
Creating Office Documents on Windows Live 485 New!
Working with Documents on Windows Live 486
Downloading Documents from Windows Live 488
Downloading or Saving Documents in Office Web Apps 490 New!
Collaborating with Documents on Windows Live 492 New!
Working with SharePoint Workspaces 494 New!
Sharing Documents in a Groove Workspace 496 New!
Inviting Others to a Groove Workspace 497 New!
Saving a Document to a SharePoint Server 498 New!
Using Office Mobile 2010 500 New!
Workshops: Putting It All Together 501
Project 1: Creating a Drop-Down List 501
Project 2: Adding a Form Control 503
Project 3: Adding Conditional Formatting 504
Project 4: Creating a VBA Script 506
Project 5: Creating a VBA Interface 508
Want More Projects 513
New Features 515 New!
Microsoft Certified Applications Specialist 521
Index 529