Table of Contents
1 Introduction 7
The Spreadsheet Concept 8
Microsoft Excel 10
Microsoft Office 2016 11
System Requirements 12
Getting Office 2016 13
Excel 2016 and Windows 10 14
The Office 2016 Ribbon 16
Exploring Excel 2016 18
Excel Online App 20
2 Begin with Excel 21
The Excel Window 22
Create a Workbook 24
Add Data to the Worksheet 25
Build the Worksheet 26
Fill Cells 28
Complete the Worksheet 29
Format the Text 30
Format the Numbers 31
Print the Worksheet 32
Insert, Copy and Paste 34
Excel Help 35
Contextual Help 36
Excel File Formats 37
3 Manage Data 39
Use Existing Data 40
Import Data 42
Navigate the Worksheet 44
Scroll with the Wheel Mouse 46
Keystrokes and Touch 47
Sort Rows 48
Find Entries 49
Filter Information 50
Remove Duplicate Entries 52
Check Spelling 53
Freeze Headers and Labels 54
Hide Columns or Rows 55
Protect a Worksheet 56
4 Formulas and Functions 57
Number Formats 58
Text Formats 60
Relative References 61
Absolute References 62
Name References 63
Operators 64
Calculation Sequence 65
Functions 66
AutoSum 68
Formula Errors 69
Add Comments 70
5 Excel Tables 71
Create an Excel Table 72
Edit Tables 74
Table Styles 75
Table Totals 76
Count Unique Entries 77
Structured References 78
Calculated Columns 79
Insert Rows 80
Custom Sort 82
Print a Table 83
Summarize a Table 84
Convert to a Range 86
6 Advanced Functions 87
Function Library 88
Logical Functions 89
Lookup/Reference Functions 90
Financial Functions 92
Date & Time Functions 94
Text Functions 96
Math SL Trig Functions 98
Random Numbers 100
Other Functions: Statistical 101
Other Functions: Engineering 102
Excel Add-ins 103
Evaluate Formula 104
7 Control Excel 105
Audit Formulas 106
Protect Formulas 108
Check for Errors 109
Backup 111
AutoSave and AutoRecover 112
Startup Switches 113
Create a Shortcut 114
Ribbon Key Tips 115
Using Key Tips 116
Collapse the Ribbon 118
Quick Access Toolbar 119
Mini Toolbar 120
Print Worksheets 121
8 Charts 123
Create a Chart 124
Default Chart Type 125
Change Chart Layout 127
Legend and Data Table 128
Change Chart Type 129
Pie Chart 130
3-D Pie Chart 132
3-D Column Chart 133
Share Price Data 134
Line Chart 135
Stock Chart 136
Mixed Types 137
Print Charts 138
9 Macros in Excel 139
Macros 140
Create Macros 141
Record a Macro 142
Apply the Macro 144
View the Macro 145
Macro to Make a Table 146
Edit the Macro 148
Use the Macro 149
Create Macros with VBA 150
Add Macros to the Toolbar 152
Debug Macros 154
10 Templates and Scenarios 155
Templates 156
Online Templates 158
More Excel Resources 160
What-If Analysis 162
Summary Reports 164
Goal Seek 166
Optimization 167
Project Worksheet 168
Solver 169
11 Links and Connections 171
Link to Workbooks 172
Create External References 174
Styles of Reference 176
Source Workbook Changes 177
Apply the Updates 178
Turn Off the Prompt 179
Save Workbook Online 180
Using the Excel Online App 182
Excel in Word 184
Publish as PDF (or XPS) 186
Index 187
