Excel 2013 On Demand Book

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