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.

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