My Excel 2016

Tracy Syrstad  
QUE Publishing
Total pages
October 2015
Related Titles

Product detail

Product Price CHF Available  
My Excel 2016
31.10 approx. 7-9 days


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


• The perfect book for beginners who want to learn about Excel 2016
• Includes more than 100 hands-on tasks to get students up and running with Microsoft Excel 2016
• Each task is designed to teach the easiest, fastest or most direct way to accomplish common Excel tasks
• Full-color images to clearly demonstrate the tasks at hand
• Updates will be delivered to via a FREE Web Edition of this book, which can be accessed with any Internet connection

New to this Edition

New author and coverage of features new to Excel 2016.

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


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.