Teach Yourself VISUALLY Complete Excel

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