My Excel 2016 (includes Content Update Program) / Edition 1

by Tracy Syrstad

Overview

Book + Content Update Program

My Excel 2016 is your must-have companion for getting most out of Excel 2016. This friendly, quick, full-color, 100% practical tutorial walks you through every task you'll want to do with Excel 2016.

• Get productive fast with Excel 2016’s updated interface
• Save time and make your data easier to work with
• Efficiently enter, insert, move, and manage data
• Use templates to reuse work and get a jumpstart on new projects
• Format worksheets and charts to make them clearer and more useful
• Use intuitive sparkline charts that fit in a single cell
• Build custom formulas using powerful built-in functions
• Sort, filter, and consolidate data and eliminate duplication
• Instantly group data and generate subtotals
• Quickly summarize huge data sets with PivotTables
• Find, fix, and avoid errors that break spreadsheets or cause inaccuracies
• Communicate more powerfully using visual tools such as SmartArt and WordArt
• Precisely control what you print and how it looks
• Securely share and distribute your workbooks
• Use Excel on the Web wherever you have Internet access

Every task is presented step-by-step, using carefully annotated, colorful screenshots, all numbered so there's no chance of getting lost or confused. Everything's clearly organized in modular, self-contained chapters designed to help you get started quickly. Throughout, the book is packed with helpful tips, lists, and quick solutions to the problems you're most likely to encounter.

In addition, this book is part of Que’s exciting new Content Update Program. As Microsoft updates features of Excel 2016, sections of this book will be updated or new sections will be added to match the updates to the software. The updates will be delivered to you via a FREE Web Edition of this book, which can be accessed with any Internet connection. To learn more, visit www.quepublishing.com/CUP.

How to access the web edition: Follow the instructions in the book to learn how to register your book to access the FREE Web Edition.

Product Details

ISBN-13:	9780789755421
Publisher:	Que
Publication date:	10/23/2015
Series:	My... Series
Pages:	480
Sales rank:	421,230
Product dimensions:	6.00(w) x 8.90(h) x 0.90(d)

About the Author

Tracy Syrstad is a Microsoft Excel developer and author of eight Excel books. She has been helping people with Microsoft Office issues since 1997 when she discovered free online forums where anyone could ask and answer questions. Tracy discovered she enjoyed teaching others new skills, and when she began working as a developer, she was able to integrate the fun of teaching with one-on-one online desktop sharing sessions. Tracy lives on an acreage in eastern South Dakota with her husband, one dog, two cats, one horse (two soon), and a variety of wild foxes, squirrels, and rabbits.

Table of Contents

Introduction 1
What’s in This Book 2
Guidance for Beginners 2
Chapter 1: Understanding the Microsoft Excel Interface 5
Identifying Parts of the Excel Window 6
Using the Built-in Help 8
Perform a Search 9
Making Selections from the Ribbon 10
Customizing the Ribbon 10
Minimize the Ribbon Size 11
Add More Commands to the Ribbon 12
Customizing the QAT 15
Move the QAT to a New Location 15
Add More Commands to the QAT 15
Viewing Multiple Sheets at the Same Time 16
Arrange Multiple Sheets 17
Scroll Two Sheets Side by Side 18
Changing the Zoom on a Sheet 19
Use Excel’s Zoom Controls 19
Moving Around on a Sheet 20
Keyboard Shortcuts for Quicker Navigation 20
Selecting a Range of Cells 21
Select a Range Using the Mouse 21
Chapter 2: Working with Workbooks and Templates 25
Managing Workbooks 25
Create a New Workbook 26
Open an Existing Workbook 26
Use the Recent Workbooks List 27
Save a Workbook 28
Close a Workbook 30
Using Templates to Quickly Create New Workbooks 30
Use Microsoft’s Online Templates 31
Save a Template 32
Open a Locally Saved Template to Enter Data 33
Edit the Design of a Locally Saved Template 34
Change Personal Templates Location 35
Chapter 3: Working with Sheets 37
Adding and Deleting Sheets 37
Add a New Sheet 38
Delete a Sheet 38
Navigating and Selecting Sheets 39
Activate Another Sheet 39
Select Multiple Sheets 40
Moving or Copying Sheets 40
Move or Copy a Sheet in the Same Workbook 41
Move or Copy a Sheet Between Workbooks 42
Renaming a Sheet 43
Change a Sheet’s Name 43
Chapter 4: Getting Data onto a Sheet 45
Entering Different Types of Data into a Cell 46
Type Numbers or Text into a Cell 46
Enter Numbers as Text 47
Type Dates and Times into a Cell 48
Undo an Entry 49
Using Lists to Quickly Fill a Range 50
Extend a Series Containing Text 50
Extend a Numerical Series 51
Create Your Own List 53
Using Paste Special 55
Paste Values Only 55
Combine Multiple Paste Special Options 57
Multiply the Range by a Specific Value 60
Use Paste to Merge a Noncontiguous Selection 63
Using Text to Columns to Separate Data in a Single Column 64
Work with Delimited Text 64
Using Data Validation to Limit Data Entry in a Cell 67
Limit User Entry to a Selection from a List 67
Using Web Queries to Get Data onto a Sheet 70
Insert a Web Query 70
Editing Data 72
Modify Cell Data 72
Clearing the Contents of a Cell 73
Clear Only Data from a Cell 73
Clearing an Entire Sheet 74
Clear an Entire Sheet 74
Working with Tables 75
Define a Table 75
Add a Total Row to a Table 77
Change the Total Row Function 77
Expand a Table 78
Fixing Numbers Stored as Text 79
Use Convert to Number on Multiple Cells 80
Use Paste Special to Force a Number 81
Spell Checking a Sheet 83
Finding Data on a Sheet 83
Perform a Search 84
Perform a Wildcard Search 86
Replace Data on a Sheet 87
Chapter 5: Selecting and Moving Data on a Sheet 91
Working with Rows and Columns 91
Select a Row or Column 92
Insert a New Row or Column 93
Delete a Row or Column 95
Move Rows or Columns by Dragging 96
Move Rows or Columns by Cutting 97
Copy Rows or Columns 99
Working with Cells 101
Select a Cell Using the Name Box 101
Select Noncontiguous Cells and Ranges 102
Insert Cells 103
Delete Cells 104
Move Cells 105
Chapter 6: Formatting Sheets and Cells 107
Changing the Font Settings of a Cell 108
Select a New Font Typeface 109
Increase and Decrease the Font Size 110
Apply Bold, Italic, and Underline to Text 111
Apply Strikethrough, Superscript, and Subscript 111
Change the Font Color 113
Format a Character or Word in a Cell 114
Format Quickly with the Format Painter 114
Adjusting the Row Height 115
Modify the Row Height by Dragging 116
Modify the Row Height by Entering a Value 117
Use Font Size to Automatically Adjust the Row Height 118
Adjusting the Column Width 119
Modify the Column Width by Dragging 119
Modify the Column Width by Entering a Value 120
Aligning Text in a Cell 121
Change Text Alignment 121
Merging Two or More Cells 121
Merge and Center Data 122
Merge Across Columns 123
Unmerge Cells 123
Centering Text Across Multiple Cells 124
Center Text Without Merging 124
Wrapping Text in a Cell to the Next Line 125
Wrap Text in a Cell 125
Reflowing Text in a Paragraph 126
Fit Text to a Specific Range 126
Indenting Cell Contents 127
Indent Data 127
Applying Number Formats 128
Modify the Number Format 128
Change the Format of Negative Numbers 129
Apply a Currency Symbol 130
Format Dates and Times 131
Format as Percentage 132
Format as Text 133
Apply the Special Number Format 134
Adding a Border Around a Range 135
Format a Range with a Thick Outer Border and Thin Inner Lines 136
Add a Colored Border 137
Coloring the Inside of a Cell 138
Apply a Two-Color Gradient to a Cell 139
Chapter 7: Advanced Formatting 143
Creating Custom Number Formats 143
The Four Sections of a Custom Number Format 144
Optional Versus Required Digits 145
Use the Thousands Separator, Color Codes, and Text 146
Line Up Decimals 148
Fill Leading and Trailing Spaces 149
Show More Than 24 Hours in a Time Format 150
Creating Hyperlinks 152
Create a Hyperlink to Another Sheet 152
Link to a Web Page 154
Dynamic Cell Formatting with Conditional Formatting 154
Use Icons to Mark Data 155
Highlight the Top 10 157
Highlight Duplicate or Unique Values 159
Create a Custom Rule 161
Clear Conditional Formatting 163
Edit Conditional Formatting 164
Using Cell Styles to Apply Cell Formatting 166
Apply a Style 166
Create a Custom Style 167
Using Themes to Ensure Uniformity in Design 169
Apply a New Theme 169
Create a New Theme 170
Share a Theme 173
Chapter 8: Using Formulas 175
Entering a Formula into a Cell 175
Calculate a Formula 176
View All Formulas on a Sheet 177
Relative Versus Absolute Referencing 178
Lock the Row When Copying a Formula Down 179
Copying Formulas 180
Copy and Paste Formulas 180
Copy by Dragging the Fill Handle 181
Copy Rapidly Down a Column 182
Copy Between Workbooks Without Creating a Link 183
Converting Formulas to Values 184
Paste as Values 184
Select and Drag 185
Using Names to Simplify References 186
Create a Named Cell 186
Use a Name in a Formula 187
Inserting Formulas into Tables 189
Write a Formula in a Table 189
Write Table Formulas Outside the Table 191
Using Array Formulas 193
Enter an Array Formula 193
Delete a Multicell Array Formula 194
Working with Links 195
Control the Prompt 196
Refresh Data 196
Change the Source Workbook 197
Break the Link 198
Troubleshooting Formulas 198
Fix ###### in a Cell 199
Understand a Formula Error 199
Use Trace Precedents and Dependents 201
Track Formulas on Other Sheets with Watch Window 203
Use the Evaluate Formula Dialog Box 204
Evaluate with F9 206
Adjusting Calculation Settings 207
Set Calculations to Manual 207
Chapter 9: Using Functions 209
Understanding Functions 209
Look Up Functions 210
Use the Function Arguments Dialog Box 211
Enter Functions Using Formula Tips 214
Using the AutoSum Button 214
Calculate a Single Range 215
Sum Rows and Columns at the Same Time 216
Quick Calculations 217
Calculate Results Quickly 217
Using Quick Analysis Functions 219
Using Lookup Functions 219
Use CHOOSE to Return the nth Value from a List 219
Use VLOOKUP to Return a Value from a Table 221
Use INDEX and MATCH to Return a Value from the Left 223
Using SUMIFS to Sum Based on Multiple Criteria 225
Sum a Column Based on Two Criteria 225
Using IF Statements 228
Compare Two Values 228
Hiding Errors with IFERROR 229
Hide a #DIV/0! Error 230
Understanding Dates and Times 231
Return a New Date X Workdays from Date 231
Calculate the Number of Days Between Dates 233
Using Goal Seek 234
Calculate the Best Payment 234
Using the Function Arguments Dialog Box to Troubleshoot Formulas 235
Narrow Down a Formula Error 236
Chapter 10: Sorting Data 239
Using the Sort Dialog Box 239
Sort by Values 240
Sort by Color or Icon 242
Doing Quick Sorts 244
Quick Sort a Single Column 244
Quick Sort Multiple Columns 245
Performing Custom Sorts 246
Perform a Random Sort 246
Sort with a Custom Sequence 247
Rearranging Columns 249
Sort Columns with the Sort Dialog Box 249
Fixing Sort Problems 251
Chapter 11: Filtering and Consolidating Data 253
Using the Filter Tool 253
Apply a Filter 254
Clear a Filter 256
Reapply a Filter 257
Turn the Filter On for One Column 257
Filtering Grouped Dates 258
Turn On Grouped Dates 258
Filter by Date 259
Using Special Filters 260
Filter for Items that Include a Specific Term 260
Filter for Values Within a Range 261
Filter for the Top 25 Items 262
Filter Dates by Quarter 263
Filtering by Color or Icon 263
Filtering by Selection 264
Allowing Users to Filter a Protected Sheet 264
Filter a Protected Sheet 265
Using the Advanced Filter 266
Reorganize Columns 266
Create a List of Unique Items 268
Filter Records Using Criteria 269
Use Formulas as Criteria 272
Removing Duplicates 273
Delete Duplicate Rows 273
Consolidating Data 274
Merge Values from Two Datasets 274
Merge Data Based on Matching Labels 276
Chapter 12: Distributing and Printing a Workbook 279
Using Cell Comments to Add Notes to Cells 280
Insert a New Cell Comment 280
Edit a Cell Comment 281
Format a Cell Comment 282
Insert an Image into a Cell Comment 284
Resize a Cell Comment 287
Show and Hide Cell Comments 287
Delete a Cell Comment 288
Allowing Multiple Users to Edit a Workbook at the Same Time 289
Share a Workbook 289
Hiding and Unhiding Sheets 291
Hide a Sheet 291
Unhide a Sheet 292
Using Freeze Panes 292
Lock the Top Row 293
Lock Multiple Rows and Columns 294
Configuring the Page Setup 295
Set Paper Size, Margins, and Orientation 295
Set the Print Area 296
Set Page Breaks .297
Scale the Data to Fit a Printed Page 299
Repeat Specific Rows on Each Printed Page 300
Creating a Custom Header or Footer 300
Add an Image to the Header or Footer 301
Add Page Numbering to the Header and Footer 303
Printing Sheets 304
Configure Print Options 304
Protecting a Workbook from Unwanted Changes 306
Set File-Level Protection 306
Set Workbook-Level Protection 307
Protecting the Data on a Sheet 308
Protect a Sheet 308
Unlock Cells 309
Allow Users to Edit Specific Ranges 310
Preventing Changes by Marking a File as Final 312
Mark a Workbook as Final 312
Sharing Files Between Excel Versions 313
Check Version Compatibility 313
Recovering Lost Changes 314
Configure Backups 314
Recover a Backup 315
Recover Unsaved Files 316
Sending an Excel File as an Attachment 316
Email a Workbook 317
Sharing a File Online 318
Save to OneDrive 318
Chapter 13: Inserting Subtotals and Grouping Data 321
Using the SUBTOTAL Function 321
Calculate Visible Rows 322
Summarizing Data Using the Subtotal Tool 323
Apply a Subtotal 323
Expand and Collapse Subtotals 325
Remove Subtotals or Groups 325
Sort Subtotals 326
Copying the Subtotals to a New Location 327
Copy Subtotals 328
Applying Different Subtotal Function Types 329
Create Multiple Subtotal Results on Multiple Rows 329
Combine Multiple Subtotal Results to One Row 331
Adding Space Between Subtotaled Groups 332
Separate Subtotaled Groups for Print 333
Separate Subtotaled Groups for Distributed Files 334
Grouping and Outlining Rows and Columns 336
Apply Auto Outline 336
Group Data Manually 337
Chapter 14 :Creating Charts and Sparklines 341
Adding a Chart 341
Add a Chart with the Quick Analysis Tool 342
Preview All Charts 343
Switch Rows and Columns 344
Apply Chart Styles or Colors 345
Apply Chart Layouts 346
Resizing or Moving a Chart 346
Resize a Chart 346
Move to a New Location on the Same Sheet 347
Relocate to Another Sheet 347
Editing Chart Elements 348
Use the Format Task Pane 348
Edit the Chart or Axis Titles 349
Change the Display Units in an Axis 351
Customize a Series Color 352
Changing an Existing Chart’s Type 353
Change the Chart Type 353
Creating a Chart with Multiple Chart Types 354
Insert a Multiple Type Chart 354
Add a Secondary Axis 356
Updating Chart Data 356
Change the Data Source 357
Adding Special Charts 358
Create a Stock Chart 358
Create a Bubble Chart 359
Pie Chart Issue: Small Slices 360
Rotate the Pie 360
Create a Bar of Pie Chart 361
Using a User-Created Template 363
Save a Chart Template 363
Use a Chart Template 364
Adding Sparklines to Data 364
Insert a Sparkline 365
Emphasize Points on a Sparkline 365
Space Markers by Date 366
Delete Sparklines 367
Chapter 15: Summarizing Data with PivotTables 369
Creating a PivotTable 370
Use the Quick Analysis Tool 371
Create a PivotTable from Scratch 372
Change the Calculation Type of a Field Value 375
Format Values 376
Changing the PivotTable Layout 377
Choose a New Layout 378
PivotTable Sorting 378
Click and Drag 378
Use Quick Sort 379
Expanding and Collapsing Fields 380
Expand and Collapse a Field 380
Grouping Dates 381
Group by Week 381
Group by Month and Year 382
Filtering Data in a PivotTable 383
Filter for Listed Items 383
Clear a Filter 384
Creating a Calculated Field 384
Add a Calculated Field 384
Hiding Totals 385
Hide Totals 386
Hide Subtotals 386
Viewing the Records Used to Calculate a Value 387
Unlinking PivotTables 388
Unlink a PivotTable Report 388
Refreshing the PivotTable 389
Refresh on Open 390
Refresh After Adding New Data 390
Refresh After Editing the Data Source 391
Working with Slicers 392
Create a Slicer 392
Use a Slicer 93
Chapter 16: Inserting SmartArt, WordArt, and Pictures 395
Working with SmartArt 395
Insert a SmartArt Graphic 396
Insert Images into SmartArt 398
Move and Resize SmartArt 399
Reorder Placeholders 401
Change the Layout 402
Change an Individual Shape 402
Working with WordArt 403
Insert WordArt 403
Inserting Pictures 404
Insert a Picture 404
Resize and Crop a Picture 405
Apply Corrections, Color, and Artistic Effects 407
Reduce a File’s Size 409
Chapter 17: Introducing the Excel Web App 411
Acquiring a Microsoft Account 411
Create an Account 412
Uploading a Workbook 413
Upload Through OneDrive 413
Save from Excel 414
Delete a File from OneDrive 415
Opening a Workbook Online or Locally 416
Open a Workbook 417
Download a Workbook 417
Creating a New Workbook Online 418
Create a Workbook 419
Rename the New Workbook 419
Sharing a Folder or Workbook 420
Create a View-Only Folder 420
Remove Sharing 422
Edit Simultaneously 424
Configuring Browser View Options 425
Create an Online Form 425
Designing a Survey Through the Web App 428
Create a Survey 428
Index 430