My Excel 2016

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.

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