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
