Microsoft Excel 2016 Step by Step

Curtis Frye is the author of more than 30 books, including  Microsoft Excel 2013 Step by Step for Microsoft Press and  Brilliant Excel VBA Programming for Pearson, UK. He has also created and recorded more than three dozen courses for lynda.com, including  Excel for Mac 2016 Essential Training and  Excel 2013: PivotTables in Depth. In addition to his work as a writer, Curt is a popular conference speaker and performer, both as a solo presenter and as part of the Portland, Oregon ComedySportz improvisational comedy troupe. He lives in Portland with his wife and three cats.

Introduction     xiii
Who this book is for     xiii
The Step by Step approach     xiii
Download the practice files     xiv
Sidebar: Adapt exercise steps     xviii
Ebook edition     xx
Get support and give feedback     xx
Errata and support     xx
We want to hear from you     xx
Stay in touch     xx
Part 1: Create and format workbooks
Chapter 1: Set up a workbook     3
Explore the editions of Excel 2016     4
Excel 2016     4
Excel Online     4
Excel Mobile Apps     5
Become familiar with new features in Excel 2016     6
Create workbooks     7
Modify workbooks     13
Modify worksheets     18
Merge and unmerge cells     23
Customize the Excel 2016 app window     26
Zoom in on a worksheet     26
Arrange multiple workbook windows     28
Add buttons to the Quick Access Toolbar     30
Customize the ribbon     33
Skills review     39
Practice tasks     40
Chapter 2: Work with data and Excel tables     43
Enter and revise data     44
Manage data by using Flash Fill     48
Move data within a workbook     51
Find and replace data     56
Correct and expand upon data     60
Define Excel tables     65
Skills review     69
Practice tasks     70
Chapter 3: Perform calculations on data     73
Name groups of data     74
Create formulas to calculate values     77
Sidebar: Operators and Precedence     88
Summarize data that meets specific conditions     90
Set iterative calculation options and enable or disable automatic calculation     96
Use array formulas     98
Find and correct errors in calculations     100
Skills review     106
Practice tasks     107
Chapter 4: Change workbook appearance     111
Format cells     112
Define styles     118
Apply workbook themes and Excel table styles     121
Make numbers easier to read     127
Change the appearance of data based on its value     130
Add images to worksheets     138
Skills review     142
Practice tasks     143
Part 2: Analyze and present data
Chapter 5: Manage worksheet data     147
Limit data that appears on your screen     148
Manipulate worksheet data     153
Sidebar: Select list rows at random     154
Summarize data in worksheets that have hidden and filtered rows     155
Find unique values within a data set     161
Define valid sets of values for ranges of cells     163
Skills review     165
Practice tasks     166
Chapter 6: Reorder and summarize data     169
Sort worksheet data     170
Sort data by using custom lists     176
Organize data into levels     179
Look up information in a worksheet     184
Skills review     187
Practice tasks     188
Chapter 7: Combine data from multiple sources     191
Use workbooks as templates for other workbooks     192
Link to data in other worksheets and workbooks     197
Consolidate multiple sets of data into a single workbook     202
Skills review     205
Practice tasks     206
Chapter 8: Analyze alternative data sets     209
Examine data by using the Quick Analysis Lens     210
Define an alternative data set     212
Define multiple alternative data sets     216
Analyze data by using data tables     218
Vary your data to get a specific result by using Goal Seek     221
Find optimal solutions by using Solver     223
Analyze data by using descriptive statistics     229
Skills review     231
Practice tasks     232
Chapter 9: Create charts and graphics     237
Create charts     238
Create new types of charts     248
Customize chart appearance     252
Find trends in your data     260
Create dual-axis charts     263
Summarize your data by using sparklines     264
Create diagrams by using SmartArt     266
Create shapes and mathematical equations     271
Skills review     277
Practice tasks     278
Chapter 10: Create dynamic worksheets by using PivotTables     281
Analyze data dynamically by using PivotTables     282
Filter, show, and hide PivotTable data     290
Edit PivotTables     301
Format PivotTables     305
Create PivotTables from external data     309
Create dynamic charts by using PivotCharts     313
Skills review     316
Practice tasks     317
Part 3: Collaborate and share in Excel
Chapter 11: Print worksheets and charts     323
Add headers and footers to printed pages     324
Prepare worksheets for printing     330
Fit your worksheet contents to the printed page     331
Change page breaks in a worksheet     334
Change the page printing order for worksheets     335
Print worksheets     337
Print parts of worksheets     339
Print charts     343
Skills review     345
Practice tasks     346
Chapter 12: Automate repetitive tasks by using macros     349
Enable and examine macros     350
Set macro security levels in Excel 2016     350
Examine macros     353
Create and modify macros     356
Run macros when you click a button     357
Run a macro when you open a workbook     361
Insert form controls into a worksheet     362
Skills review     369
Practice tasks     370
Chapter 13: Work with other Microsoft Office apps     373
Include Office documents in workbooks and other files     374
Link Office documents to Excel workbooks     375
Embed files in Excel and other Office apps     377
Create hyperlinks     379
Paste charts into documents     384
Skills review     385
Practice tasks     386
Chapter 14: Collaborate with colleagues     389
Share workbooks     390
Save workbooks for electronic distribution     394
Manage comments     395
Track and manage colleagues’ changes     398
Add protection to workbooks and worksheets     401
Finalize workbooks     408
Authenticate workbooks     410
Save workbooks for the web     412
Import and export XML data     414
Work with OneDrive and Excel Online     418
Skills review     424
Practice tasks     425
Part 4: Perform advanced analysis
Chapter 15: Perform business intelligence analysis     431
Enable the Data Analysis add-ins     432
Define relationships between tables     434
Analyze data by using Power Pivot     438
View data by using timelines     442
Bring in external data by using Power Query     446
Skills review     451
Practice tasks     452
Chapter 16: Create forecasts and visualizations     455
Create Forecast Worksheets     456
Define and manage measures     461
Define and display Key Performance Indicators     464
Create 3D maps     468
Skills review     475
Practice tasks     476
Keyboard shortcuts     479
Glossary     89
Index     95
About the author     521