Excel 2013 On Demand

Steve Johnson has written more than 80 books on a variety of computer software, including Adobe Edge Animate, Adobe Photoshop CS6, Adobe Dreamweaver CS6, Adobe InDesign CS6, Adobe Illustrator CS6, Adobe Flash Professional CS5, Microsoft Windows 8, Microsoft Office 2013 and 2010, Microsoft Office 2008 for the Macintosh, and Apple OS X Mountain Lion. 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 coaching baseball, playing golf, gardening, and spending time with his wife, Holly, and three children, JP, Brett, and Hannah. Steve and his family live in Northern California, but can also be found visiting family all over the western United States.

Introduction xvii

1  Getting Started with Excel 1

Starting Excel 2  New!

Viewing the Excel Window 3  New!

Using the Ribbon 4  New!

Choosing Commands 5  New!

Working with the Ribbon and Toolbars 6

Choosing Dialog Box Options 8

Using the Status Bar 9

Creating a Blank Workbook 10  New!

Creating a Workbook Using a Template 11  New!

Opening an Existing Workbook 12  New!

Converting an Existing Workbook 14

Using Task and Window Panes 15

Moving Around the Workbook 16  New!

Arranging Windows 18  New!

Getting Help While You Work 20  New!

Saving a Workbook 22  New!

Saving a Workbook with Different Formats 24  New!

Saving a Workbook to Online Services 26  New!

Working with Accounts 27  New!

Checking Compatibility 28

Checking Accessibility 29

Documenting Workbook Properties 30

Switching Views 31

Recovering a Workbook 32

Closing a Workbook and Exiting Excel 34  New!

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

Inserting Content with Flash Fill 46  New!

Editing Cell Contents 48

Clearing Cell Contents 49

Understanding How Excel Pastes Data 50

Storing Cell Contents 51

Copying Cell Contents 52

Moving Cell Contents 54

Inserting and Deleting Cell Contents 56

Finding and Replacing Cell Contents 58

Correcting Cell Contents with AutoCorrect 60

Inserting Information the Smart Way 62

Checking Spelling 64

Changing Proofing Options 65

Using Custom Dictionaries 66

Inserting Symbols 68

Finding the Right Words 69

Inserting Research Material 70

Translating Text to Another Language 71

Using Multiple Languages 72

Work with Touch Screens 73  New!

Undoing and Redoing an Action 74

3  Working with Formulas and Functions 75

Understanding Formulas 76

Creating a Simple Formula 78

Creating a Formula Using Formula AutoComplete 80

Editing a Formula 82

Understanding Cell Referencing 84

Using Absolute Cell References 85

Using Mixed Cell References 86

Using 3-D Cell References 87

Naming Cells and Ranges 88

Entering Named Cells and Ranges 90

Managing Names 92

Simplifying a Formula with Ranges 94

Displaying Calculations with the Status Bar 95

Calculating Totals with AutoSum 96

Calculating Totals with Quick Analysis 98  New!

Performing One Time Calculations 100

Converting Formulas and Values 102

Correcting Calculation Errors 103

Correcting Formulas 104

Auditing a Worksheet 106

Locating Circular References 107

Performing Calculations Using Functions 108

Creating Functions 109

Creating Functions Using the Library 110  New!

Calculating Multiple Results 111

Using Nested Functions 112

Using Constants and Functions in Names 114

4  Modifying Worksheets and Workbooks 115

Selecting and Naming a Worksheet 116

Inserting and Deleting a Worksheet 117

Moving and Copying a Worksheet 118

Hiding and Unhiding Worksheets and Workbooks 120

Selecting a Column or Row 122

Hiding and Unhiding a Column or Row 123

Inserting a Column or Row 124

Deleting a Column or Row 125

Adjusting Column Width and Row Height 126

Freezing and Unfreezing a Column or Row 128

Splitting a Worksheet into Panes 129

Showing and Hiding Workbook Elements 130

Zooming the View In and Out 131

Creating Custom Views 132

Setting Up for Personal Templates 133  New!

Creating a Personal Template 134  New!

Opening a Template 135  New!

Changing a Template 136  New!

5  Formatting a Worksheet 137

Formatting Numbers 138

Formatting Text 140

Applying Conditional Formatting 142  New!

Applying Specialized Conditional Formatting 144  New!

Creating Conditional Formatting 146

Clearing Conditional Formatting 147

Managing Conditional Formatting 148

Finding Conditional Formatting 149

Creating Sparkline Formatting 150  New!

Changing Data Alignment 152

Controlling Text Flow 154

Changing Data Color 155

Adding Color and Patterns to Cells 156

Adding Custom Colors 157

Adding Borders to Cells 158

Formatting Tabs and Background 160  New!

Copying Cell Formats 161

Understanding Color Themes 162

Viewing and Applying a Theme 163

Creating Theme Colors 164

Choosing Theme Fonts 166

Choosing Theme Effects 168

Creating a Custom Theme 169

Choosing a Custom Theme 170

Applying and Creating Cell Styles 172

Modifying a Cell Style 174

Finding and Replacing Cell Formatting 176

6  Viewing and Printing Worksheets and Workbooks 177

Setting Up the Page 178

Adjusting Page Margins 180

Adding Headers and Footers 182

Inserting Page Breaks 184

Customizing Worksheet Printing 186

Setting the Print Area 188

Previewing a Worksheet 189

Printing a Worksheet and Workbook 190

Creating a PDF Document 191

Creating an XPS Document 192

7  Inserting and Modifying Graphics 193

Locating and Inserting Online Pictures 194  New!

Inserting Pictures from an Online Service 196  New!

Inserting Pictures from a SkyDrive or SharePoint 198  New!

Inserting a Picture from a File 199

Inserting a Picture Screen Shot 200

Adding an Artistic Style to a Picture 201

Adding a Quick Style to a Picture 202

Applying a Shape to a Picture 203

Applying a Border to a Picture 204

Applying Picture Effects 205  New!

Modifying Picture Size 206  New!

Compressing a Picture 208

Correcting a Picture 209  New!

Recoloring a Picture 210

Cropping and Rotating a Picture 212

Removing a Picture Background 214

Creating WordArt Text 215

Formatting WordArt Text 216

Applying WordArt Text Effects 218

Modifying WordArt Text Position 219  New!

Creating SmartArt Graphics 220

Using the Text Pane with SmartArt Graphics 222

Modifying a SmartArt Graphic 224

Resizing a SmartArt Graphic 225

Formatting a SmartArt Graphic 226

Formatting a Shape in a SmartArt Graphic 228

Adding Pictures to a SmartArt Graphic 230

Creating an Organization Chart 231

Modifying an Organization Chart 232

8  Drawing and Modifying Shapes 233

Drawing and Resizing Shapes 234

Inserting Multiple Shapes 236

Adding Text to a Shape 237

Drawing Lines and Arrows 238  New!

Creating and Editing Freeforms 240

Copying and Moving Objects 241

Adding a Quick Style to a Shape 242  New!

Adding a Quick Style to Shape Text 243

Applying Color Fills 244  New!

Applying Picture Fills 246  New!

Applying Texture Fills 247  New!

Applying Gradient Fills 248  New!

Applying Shape Effects 250  New!

Adding 3-D Effects to a Shape 252  New!

Adding 3-D Rotation Effects to a Shape 254

Creating Shadows 255  New!

Aligning and Distributing Objects 256

Connecting Shapes 258

Selecting Objects Using the Selection Pane 259

Changing Stacking Order 260

Rotating and Flipping a Shape 261

Grouping and Ungrouping Shapes 262

9  Creating and Modifying Charts 265

Understanding Chart Terminology 266  New!

Choosing the Right Type of Chart 267

Creating a Chart 268  New!

Editing a Chart 270  New!

Moving and Resizing a Chart 272

Selecting Chart Elements 274

Changing Chart Elements 275  New!

Changing a Chart Type 276  New!

Changing a Chart Layout and Style 278  New!

Formatting Chart Elements 280  New!

Changing Chart Gridlines and Axes 282  New!

Changing Chart Titles 284  New!

Changing Chart Labels 286  New!

Pulling Out a Pie Slice 288

Formatting Chart Data Series 289  New!

Formatting Chart Text 290

Formatting Line and Bar Charts 291  New!

Changing the Chart Background 292

Enhancing a Chart 293

Editing Chart Data 294  New!

Adding and Deleting a Data Series 296  New!

Saving a Chart Template 298  New!

Managing Chart Templates 300

10  Analyzing Worksheet Data 301

Understanding Tables 302

Creating a Table 303  New!

Formatting a Table 304  New!

Creating or Modifying a Table Style 305

Formatting Table Elements 306  New!

Creating Calculations in a Table 307

Working with Tables 308

Removing Table Rows and Columns 310

Entering Data in a Table Using a Drop-Down List 311

Sorting Data in a Table 312

Displaying Parts of a Table with AutoFilter 314

Creating Custom Searches 315

Using Slicers to Filter a Table 316  New!

Exporting a Table to a SharePoint List 317  New!

Analyzing Data Using a PivotTable 318  New!

Analyzing Data Using a PivotChart 320  New!

Updating a PivotTable or PivotChart 321  New!

Modifying a PivotTable 322  New!

Formatting a PivotTable 323

Working with PivotTable Data 324  New!

Using Slicers to Filter a PivotTable 326

Displaying a Timeline to Filter a PivotTable 328  New!

Drilling into PivotTable Data 329  New!

Charting a PivotTable 330  New!

Presenting Data with Power View 332  New!

Working with PowerPivot 334  New!

Creating Groups and Outlines 335

Converting Text to Columns 336

Adding Data Validation to a Worksheet 337

Creating a Drop-Down List 338

11  Building More Powerful Worksheets 339

Using Data Analysis Tools 340

Using the Euro Conversion 341

Looking at Alternatives with Data Tables 342

Asking “What If” with Goal Seek 343

Creating Scenarios 344

Using Solver 346

Using Inquire Tools 348  New!

Using Lookup and Reference Functions 350  New!

Using Text Functions 352  New!

Summarizing Data Using Subtotals 353

Summarizing Data Using Functions 354

Using Date & Time Functions 356  New!

Using Logical Functions 358  New!

Using Financial Functions 359  New!

Using Math Functions 360  New!

Using Statistical Functions 361  New!

Using Information Functions 362  New!

Using Compatibility Functions 363

Using Web Functions 364  New!

12  Protecting and Securing a Workbook 365

Inspecting Workbooks 366

Protecting a Worksheet 368

Locking and Unlocking Worksheet Cells 370

Adding Security Encryption to a Workbook 371

Adding Password Protection to a Workbook 372

Adding a Digital Signature 374

Adding a Signature Line 376

Avoiding Harmful Attacks 378

Using the Trust Center 380

Selecting Trusted Publishers and Locations 381

Setting Document Related Security Options 382

Setting App Catalog Security Options 384  New!

Setting Add-in Security Options 385

Setting ActiveX Security Options 386

Setting Macro Security Options 387

Changing Message Bar Security Options 388

Setting Privacy Options 389

Setting External Content Security Options 390

Working with Office Safe Modes 392

Marking a Workbook as Read-Only 394

13  Reviewing and Sharing Workbook Data 395

Sharing Workbooks 396

Creating and Reading a Cell Comment 398

Editing and Deleting a Cell Comment 399

Tracking Workbook Changes 400

Sending a Workbook Using E-Mail 402

Sending a Workbook by Internet Fax 403  New!

Sending a Workbook by Instant Message 404  New!

Presenting a Workbook Online 405  New!

Sharing Information Between Programs 406

Linking Data 407

Linking and Embedding Files 408

Exporting and Importing Data 410

Consolidating Data 412

Getting External Data 414  New!

Getting Text Data 415

Connecting to External Data 416

Getting Query Data from a Database 418

Getting Data from Microsoft Access 420

Working with XML 422

Creating an XML Data Map 424

Exporting and Saving Data in XML 426

14  Publishing Workbook Data as Web Pages 427

Opening a Workbook as a Web Page 428

Previewing a Web Page 429

Creating a Hyperlink 430

Formatting a Hyperlink 432

Changing Web Page Options 433

Saving a Worksheet as a Web Page 434

Publishing a Web Page 436

Copying a Web Table to a Worksheet 437

Creating Refreshable Web Queries 438

Getting Data from Web Queries 439

Getting Documents from the Web 440

15  Tools for Working More Efficiently 441

Getting General Excel Options 442  New!

Setting New Workbook and Start Up Options 443  New!

Setting Editing Options 444  New!

Setting Image Options 446

Setting Chart Options 447  New!

Setting Workbook and Worksheet Display Options 448

Setting Display View Options 450  New!

Setting Advanced Options 451

Setting Formula Options 452

Changing Advanced Document Properties 453

Customizing the Way You Create Objects 454

Accessing Commands Not in the Ribbon 455

Working with Office Tools 456  New!

Maintaining and Repairing Office 458

16  Expanding Excel Functionality 459

Adding and Inserting Apps for Office 460  New!

Viewing and Managing Add-ins 462

Loading and Unloading Add-ins 464  New!

Enhancing a Workbook with VBA 466

Viewing the Visual Basic Editor 468

Setting Developer Options 469

Understanding How Macros Automate Your Work 470

Recording a Macro 471

Creating a Macro 472

Running a Macro 473

Controlling a Macro 474

Adding a Digital Signature to a Macro Project 476

Assigning a Macro to a Toolbar or Ribbon 477

Saving a Workbook with Macros 478

Opening a Workbook with Macros 479

Inserting ActiveX Controls 480

Using ActiveX Controls 482

Setting ActiveX Control Properties 483

Adding VBA Code to an ActiveX Control 484

17  Working Online with Office Documents 485

Working Online with SharePoint and SkyDrive 486  New!

Signing in to SharePoint or SkyDrive 488  New!

Saving and Opening on SharePoint or SkyDrive 490  New!

Accessing Documents on SharePoint 492  New!

Syncing Documents on SharePoint 494  New!

Sharing Documents on SkyDrive 496  New!

Accessing Documents on SkyDrive 498  New!

Managing Documents on SkyDrive 500  New!

Downloading or Uploading Documents on SkyDrive 502

Creating Office Documents on SkyDrive 503

Sending Links to Documents on SkyDrive 504  New!

Comparing the Office Desktop App to the Web App 506

Working with Office Web Apps 508  New!

Saving or Printing in Office Web Apps 510

Co-authoring Documents with Office Web Apps 512  New!

New Features 515  New!

Microsoft Office Specialist 521

Index 529