Table of Contents

Chapter 1 Working with Ranges

Select a Range 4

Fill a Range with the Same Data 6

Fill a Range with a Series of Values 8

Flash Fill a Range 10

Move or Copy a Range 12

Insert a Row or Column 14

Insert a Cell or Range 16

Delete Data from a Range 18

Delete a Range 20

Hide a Row or Column 22

Freeze Rows or Columns 24

Merge Two or More Cells 26

Transpose Rows and Columns 28

Select and Enter Data Using Touch Gestures 30

Chapter 2 Working with Range Names

Understanding the Benefits of Using Range Names 34

Define a Range Name 36

Using Worksheet Text to Define a Range Name 38

Navigate a Workbook Using Range Names 40

Change a Range Name 42

Delete a Range Name 44

Paste a List of Range Names 46

Chapter 3 Formatting Excel Ranges

Change the Font and Font Size 50

Apply Font Effects 52

Change the Font Color 54

Align Text Within a Cell 56

Center Text Across Multiple Columns 58

Rotate Text Within a Cell 60

Add a Background Color to a Range 62

Apply a Number Format 64

Change the Number of Decimal Places Displayed 66

Apply an AutoFormat to a Range 68

Apply a Conditional Format to a Range 70

Apply a Style to a Range 72

Change the Column Width 74

Change the Row Height 76

Wrap Text Within a Cell 78

Add Borders to a Range 80

Copy Formatting from One Cell to Another 82

Chapter 4 Building Formulas

Understanding Excel Formulas 86

Build a Formula 88

Understanding Excel Functions 90

Add a Function to a Formula 92

Add a Row or Column of Numbers 94

Build an AutoSum Formula 96

Add a Range Name to a Formula 98

Reference Another Worksheet Range in a Formula 100

Move or Copy a Formula 102

Switch to Absolute Cell References 104

Hide the Formula Bar or Ribbon 106

Troubleshoot Formula Errors 108

Chapter 5 Manipulating Worksheets

Navigate a Worksheet 112

Rename a Worksheet 113

Create a New Worksheet 114

Move a Worksheet 116

Copy a Worksheet 118

Delete a Worksheet 120

Change the Gridline Color 122

Toggle Worksheet Gridlines On and Off 124

Toggle Worksheet Headings On and Off 125

Set the Worksheet Tab Color 126

Set the Worksheet Background 128

Zoom In on or Out of a Worksheet 130

Split a Worksheet into Two Panes 132

Hide and Unhide a Worksheet 134

Chapter 6 Dealing with Workbooks

Create a New Blank Workbook 138

Create a New Workbook from a Template 140

Save a Workbook 142

Open a Workbook 143

Arrange Workbook Windows 144

Find Text in a Workbook 146

Replace Text in a Workbook 148

Check Spelling and Grammar 150

Chapter 7 Formatting Workbooks

Modify the Workbook Colors 154

Set the Workbook Fonts 156

Choose Workbook Effects 158

Apply a Workbook Theme 160

Add a Workbook Header 162

Add a Workbook Footer 164

Chapter 8 Importing Data into Excel

Understanding External Data 168

Import Data from a Data Source 170

Import Data from an Access Table 172

Import Data from a Word Table 174

Import Data from a Text File 176

Import Data from a Web Page 180

Import Data from an XML File 182

Refresh Imported Data 184

Separate Cell Text into Columns 186

Chapter 9 Printing Workbooks

Adjust the Workbook Margins 190

Change the Page Orientation 192

Insert a Page Break 193

Choose a Paper Size 194

Set the Print Area 196

Configure Titles to Print on Each Page 198

Preview the Printout 200

Print a Workbook 202

Chapter 10 Working with Tables

Understanding Tables 206

Get to Know Table Features 207

Convert a Range to a Table 208

Select Table Data 210

Insert a Table Row 212

Insert a Table Column 213

Delete a Table Row 214

Delete a Table Column 215

Add a Column Subtotal 216

Convert a Table to a Range 218

Apply a Table Style 219

Build a Custom Table Style 220

Create a PivotTable 222

Chapter 11 Analyzing Data

Sort a Range or Table 226

Filter a Range or Table 228

Set Data Validation Rules 230

Create a Data Table 232

Summarize Data with Subtotals 234

Group Related Data 236

Analyze Data with Goal Seek 238

Analyze Data with Scenarios 240

Remove Duplicate Values from a Range or Table 244

Highlight Cells That Meet Some Criteria 246

Highlight the Top or Bottom Values in a Range 248

Analyze Cell Values with Data Bars 250

Analyze Cell Values with Color Scales 252

Analyze Cell Values with Icon Sets 254

Create a Custom Conditional Formatting Rule 256

Consolidate Data from Multiple Worksheets 258

Load the Excel Analysis ToolPak 262

Chapter 12 Visualizing Data with Charts

Examine Chart Elements 266

Understanding Chart Types 267

Create a Chart 268

Create a Recommended Chart 270

Add Chart Titles 272

Add Data Labels 273

Position the Chart Legend 274

Display Chart Gridlines 275

Display a Data Table 276

Change the Chart Layout and Style 277

Select a Different Chart Type 278

Change the Chart Source Data 280

Move or Resize a Chart 282

Add a Sparkline to a Cell 284

Chapter 13 Adding Worksheet Graphics

Draw a Shape 288

Insert a Clip Art Image 290

Insert a Photo 292

Insert a WordArt Image 294

Insert a SmartArt Graphic 296

Move or Resize a Graphic 298

Crop a Picture 300

Format a Picture 302

Chapter 14 Collaborating with Others

Add a Comment to a Cell 306

Protect a Worksheet’s Data 308

Protect a Workbook’s Structure 310

Share a Workbook with Other Users 312

Track Workbook Changes 314

Accept or Reject Workbook Changes 316

Save a Workbook to Your OneDrive 318

Send a Workbook as an E‐Mail Attachment 320

Save Excel Data as a Web Page 322

Make a Workbook Compatible with Earlier Versions of Excel 324

Mark Up a Worksheet with a Digital Pen 326

Collaborate on a Workbook Online 328

Index

About the Author

Paul McFedries (Toronto, Ontario) is the president of Logophilia Limited, a technical writing company. He has been programming since he was a teenager in the mid-1970s, has programmed everything from mainframes to desktops to bar code scanners, and has worked with many different languages, including Fortran, assembly language, C++, and, of course, JavaScript. Paul has written more than four dozen books that have sold more than two million copies worldwide. These books include Windows 8.1 Simplified, Teach Yourself VISUALLY Windows 8.1, Excel Data Analysis Visual Blueprint, and previous editions of Teach Yourself VISUALLY Excel. Paul encourages all readers to drop by his Web site, mcfedries.com.