Microsoft Excel 2016 Step by Step

Microsoft Press
Curtis Frye  
Microsoft Press
Total pages
October 2015
Related Titles

Product detail

Product Price CHF Available  
Microsoft Excel 2016 Step by Step

Alternative title

Product Edition Date Price CHF Available
Microsoft Excel 2019 Step by Step
1 November 2018 45.20


This is learning made easy. Students will get productive fast with Excel 2016 and jump in wherever they need answers--brisk lessons and colorful screen shots show them exactly what to do, step by step.


  • Step-by-step, full-color guide to learning Microsoft Excel
  • Easy lessons for essential tasks
  • Big full-color visuals
  • Skill-building practice files

Table of Contents

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


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, 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.