Microsoft Excel 2010 On Demand

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