Title Details

Chapter 1 Working with Excel

Get to Know Excel  4

Start Excel  6

Tour the Excel Window  8

Work with the Excel Ribbon  9

Work with the Excel Galleries  10

Customize the Quick Access Toolbar  12

Customize the Ribbon  14

Change the View  16

Configure Excel Options  18

Add Excel to the Windows Taskbar  20

Quit Excel  21

Chapter 1 Working with Excel

Learn the Layout of a Worksheet  24

Understanding the Types of Data You Can Use  25

Type Text in a Cell  26

Type a Number in a Cell  28

Type a Date or Time in a Cell  30

Insert a Symbol  32

Edit Cell Data  34

Delete Data from a Cell  36

Chapter 2 Inputting and Editing Data

Learn the Layout of a Worksheet  24

Understanding the Types of Data You Can Use  25

Type Text in a Cell  26

Type a Number in a Cell  28

Type a Date or Time in a Cell  30

Insert a Symbol  32

Edit Cell Data  34

Delete Data from a Cell  36

Chapter 3 Working with Ranges

Select a Range  40

Fill a Range with the Same Data  42

Fill a Range with a Series of Values  44

Flash Fill a Range  46

Move or Copy a Range  48

Insert a Row or Column  50

Insert a Cell or Range  52

Delete Data from a Range  54

Delete a Range  56

Hide a Row or Column  58

Freeze a Row or Column  60

Merge Two or More Cells  62

Transpose a Row or Column  64

Chapter 4 Working with Range Names

Understanding the Benefits of Range Names  68

Define a Range Name  70

Use Worksheet Text to Define a Range Name  72

Navigate a Workbook Using Range Names  74

Change a Range Name  76

Delete a Range Name  78

Paste a List of Range Names  80

Chapter 4 Working with Range Names

Understanding the Benefits of Range Names  68

Define a Range Name  70

Use Worksheet Text to Define a Range Name  72

Navigate a Workbook Using Range Names  74

Change a Range Name  76

Delete a Range Name  78

Paste a List of Range Names  80

Chapter 5 Formatting Excel Ranges

Change the Font or Font Size  84

Apply Font Effects  86

Change the Font Color  88

Align Text Within a Cell  90

Center Text Across Multiple Columns  92

Rotate Text Within a Cell  94

Add a Background Color to a Range  96

Apply a Number Format  98

Change the Number of Decimal Places Displayed 100

Apply an AutoFormat to a Range 102

Apply a Conditional Format to a Range 104

Apply a Style to a Range 106

Change the Column Width 108

Change the Row Height 110

Wrap Text Within a Cell 112

Add Borders to a Range 114

Copy Formatting from One Cell to Another 116

Chapter 6 Maximizing Excel Customization

Create Custom Numeric Formats 120

Create Custom Date and Time Formats 122

Set the Default Width for All Columns 124

Create a Custom Fill List 126

Maximize Work Space by Turning Off Window Elements 128

Create a Custom Cell Style 130

Build a Custom Table Style 132

Create a Custom Color Scheme 134

Create a Custom Font Scheme 136

Save a Custom Workbook Theme 138

Customize the Excel Status Bar 140

Unpin the Ribbon 141

Chapter 7 Making Excel More Efficient

Export Ribbon Customizations to a File 144

Configure Excel to Use the Mouse Wheel for Zooming 146

Move in a Different Direction When You Press Enter 148

Automatically Insert a Decimal Point 150

Configure When Excel Warns You About Long Operations 152

Make a Workbook Faster by Saving it as Binary 154

Open a New Window for a Workbook 156

Allow Only Certain Values in a Cell 158

Apply Text or Formatting to Multiple Worksheets 160

Use Dialog Box Controls to Input Data 162

Check for Accessibility Problems 166

Chapter 8 Building Formulas and Functions

Understanding Excel Formulas 170

Build a Formula 172

Understanding Excel Functions 174

Add a Function to a Formula 176

Add a Row or Column of Numbers 178

Build an AutoSum Formula 180

Add a Range Name to a Formula 182

Reference Another Worksheet Range in a Formula 184

Move or Copy a Formula 186

Switch to Absolute Cell References 188

Hide the Formula Bar or Ribbon 190

Chapter 9 Getting More Out of Formulas

Paste a Formula Result 194

Show Formulas Instead of Results 196

Use a Watch Window to Monitor a Cell Value 198

Create an Array Formula 200

Combine Two Ranges Arithmetically 202

Skip Data Tables When Calculating Workbooks 204

Turn On Iterative Calculations 206

Troubleshoot a Formula by Stepping Through Each Part 208

Display Text Instead of Error Values 210

Check for Formula Errors in a Worksheet 212

Audit a Formula to Locate Errors 214

Chapter 10 Manipulating Worksheets

Navigate a Worksheet 218

Rename a Worksheet 219

Create a New Worksheet 220

Move a Worksheet 222

Copy a Worksheet 224

Delete a Worksheet 226

Change the Gridline Color 228

Toggle Worksheet Gridlines On and Off 230

Toggle Worksheet Headings On and Off 231

Set the Worksheet Tab Color 232

Set the Worksheet Background 234

Zoom In on or Out of a Worksheet 236

Split a Worksheet into Two Panes 238

Hide or Unhide a Worksheet 240

Chapter 11 Dealing with Workbooks

Create a New Blank Workbook 244

Create a New Workbook from a Template 246

Save a Workbook 248

Open a Workbook 249

Specify Workbook Properties 250

Find Text in a Workbook 252

Replace Text in a Workbook 254

Check Spelling and Grammar 256

Close a Workbook 258

Chapter 12 Managing Workbooks

Increase the Number of Recent Documents 262

Open Workbooks Automatically at Start-up 264

Create a Workspace of Workbooks 266

Specify a New Default File Location 268

Set the Default Font and Font Size for New Workbooks 270

Set the Default Number of Worksheets for New Workbooks 272

Repair a Corrupted Workbook File 274

Convert a Workbook to a PDF File 276

Create a Workbook Template 278

Create a New Workbook from an Existing File 280

Compare Two Workbooks Side by Side 282

Check for Features Not Supported by Earlier Versions of Excel 284

Chapter 13 Formatting Workbooks

Modify Workbook Colors 288

Set Workbook Fonts 290

Choose Workbook Effects 292

Apply a Workbook Theme 294

Add a Workbook Header 296

Add a Workbook Footer 298

Chapter 14 Printing Workbooks

Adjust Workbook Margins 302

Change the Page Orientation 304

Insert a Page Break 305

Choose a Paper Size 306

Set the Print Area 308

Configure Titles to Print on Each Page 310

Preview a Printout 312

Print a Workbook 314

Chapter 15 Working with Tables

Understanding Tables 318

Get to Know Table Features 319

Convert a Range to a Table 320

Select Table Data 322

Insert a Table Row 324

Insert a Table Column 325

Delete a Table Row 326

Delete a Table Column 327

Add a Column Subtotal 328

Convert a Table to a Range 330

Apply a Table Style 331

Resize a Table 332

Rename a Table 333

Chapter 16 Analyzing Data

Sort a Range or Table 336

Filter a Range or Table 338

Calculate a Conditional Sum 340

Calculate a Conditional Count 341

Create a Data Table 342

Summarize Data with Subtotals 344

Group Related Data 346

Analyze Data with Goal Seek 348

Analyze Data with Scenarios 350

Remove Duplicate Values from a Range or Table 354

Highlight Cells That Meet Some Criteria 356

Highlight the Top or Bottom Values in a Range 358

Analyze Cell Values with Data Bars 360

Analyze Cell Values with Color Scales 362

Analyze Cell Values with Icon Sets 364

Create a Custom Conditional Formatting Rule 366

Consolidate Data from Multiple Worksheets 368

Load the Analysis ToolPak 372

Chapter 17 Analyzing Data with PivotTables

Understanding PivotTables 376

Explore PivotTable Features 377

Build a PivotTable from an Excel Range or Table 378

Create a PivotTable from External Data 380

Refresh PivotTable Data 384

Add Multiple Fields to the Row or Column Area 386

Add Multiple Fields to the Data Area 388

Move a Field to a Different Area 390

Group PivotTable Values 392

Apply a PivotTable Filter 394

Filter a PivotTable with a Slicer 396

Apply a PivotTable Style 398

Change the PivotTable Summary Calculation 400

Customize the PivotTable Fields Pane 402

Create a PivotChart from a PivotTable 403

Chapter 18 Visualizing Data with Charts

Examine Chart Elements 406

Understanding Chart Types 407

Create a Chart 408

Create a Recommended Chart 410

Add Chart Titles 412

Add Data Labels 413

Position the Chart Legend 414

Display Chart Gridlines 415

Display a Data Table 416

Change the Chart Layout and Style 417

Select a Different Chart Type 418

Change the Chart Source Data 420

Move or Resize a Chart 422

Add a Sparkline to a Cell 424

Chapter 19 Working with Worksheet Graphics

Draw a Shape 428

Insert Clip Art 430

Insert a Photo 432

Insert WordArt 434

Insert SmartArt 436

Move or Resize a Graphic 438

Crop a Picture 440

Format a Picture 442

Chapter 20 Importing Data into Excel

Understanding External Data 446

Import Data from a Data Source 448

Import Data from an Access Table 450

Import Data from a Word Table 452

Import Data from a Text File 454

Import Data from a Web Page 458

Import Data from an XML File 460

Refresh Imported Data 462

Separate Cell Text into Columns 464

Chapter 21 Querying Data Sources

Understanding Microsoft Query 468

Define a Data Source 470

Start Microsoft Query 474

Tour the Microsoft Query Window 475

Add a Table to a Query 476

Add Fields to a Query 478

Filter the Records with Query Criteria 480

Sort Query Records 482

Return the Query Results 484

Chapter 22 Collaborating with Others

Add a Comment to a Cell 488

Protect a Worksheet’s Data 490

Protect a Workbook’s Structure 492

Share a Workbook with Other Users 494

Track Workbook Changes 496

Accept or Reject Workbook Changes 498

Save a Workbook to Your SkyDrive 500

Send a Workbook as an E-Mail Attachment 502

Save Excel Data as a Web Page 504

Make a Workbook Compatible with

Earlier Versions of Excel 506

Mark Up a Worksheet with a Digital Pen 508

Collaborate on a Workbook Online 510

Chapter 23 Protecting Excel Data

Open a Read-Only Version of a Workbook 514

Mark a Workbook as Final to Avoid Accidental Editing 516

Protect Workbooks by Shortening the AutoRecover Interval 518

Specify Cells that Users Can Edit 520

Hide a Formula 522

Protect a Range with a Password 524

Protect Worksheet Data 526

Protect the Structure of a Workbook 528

Restore a Previous Version of a Workbook 530

Chapter 24 Maximizing Security

Open a Workbook in Protected View 534

Block Dangerous Excel File Types 536

Set the Macro Security Level 538

Digitally Sign Your Excel Macros 540

Create a Trusted Location for Opening Files 542

Inspect a Workbook for Private Data 544

Assign a Password to a Workbook 546

Turn On Excel Parental Control 548

Disable External Data Connections and Links 550

Apply a Digital Signature to a Workbook 552

Chapter 25 Learning VBA Basics

Record a Macro 556

Open the VBA Editor 558

Explore the Excel Object Model 560

Add a Macro to a Module 562

Run a Macro 564

Assign a Shortcut Key to a Macro 566

Assign a Macro to the Quick Access Toolbar 568

Assign a Macro to the Ribbon 570

Index  572