Course objectives

• Quickly set up workbooks, enter data, and format it for easier viewing
• Perform calculations and find and correct errors
• Filter, sort, summarize, and combine data
• Analyze data by using PivotTables, PivotCharts, scenarios, data tables, and Solver
• Visualize data with charts and graphs, including new sunbursts, waterfalls, and treemaps
• Build data models and use them in business intelligence analyzes
• Create timelines, forecasts, and visualizations, including KPIs and PowerMap data maps
• Look up just the tasks and lessons you need

Course content

Introduction

Who this book is for
The Step by Step approach
Download the practice files
Sidebar: Adapt exercise steps
Ebook edition
Get support and give feedback
Errata and support
We want to hear from you
Stay in touch

Part 1: Create and format workbooks

• Chapter 1: Set up a workbook
  Explore the editions of Excel 2016
  Excel 2016
  Excel Online
  Excel Mobile Apps
  Become familiar with new features in Excel 2016
  Create workbooks
  Modify workbooks
  Modify worksheets
  Merge and unmerge cells
  Customize the Excel 2016 app window
  Zoom in on a worksheet
  Arrange multiple workbook windows
  Add buttons to the Quick Access Toolbar
  Customize the ribbon
  Skills review
  Practice tasks
• Chapter 2: Work with data and Excel tables
  Enter and review data
  Manage data by using Flash Fill
  Move data within a workbook
  Find and replace data
  Correct and expand upon data
  Define Excel tables
  Skills review
  Practice tasks
• Chapter 3: Perform calculations on data
  Name groups of data
  Create formulas to calculate values
  Sidebar: Operators and Precedence
  Summarize data that meets specific conditions
  Set iterative calculation options and enable or disable automatic calculation
  Use array formulas
  Find and correct errors in calculations
  Skills review
  Practice tasks
• Chapter 4: Change workbook appearance
  Format cells
  Define styles
  Apply workbook themes and Excel table styles
  Make numbers easier to read
  Change the appearance of data based on its value
  Add images to worksheets
  Skills review
  Practice tasks

Part 2: Analyze and present data

• Chapter 5: Manage worksheet data
  Limit data that appears on your screen
  Manipulate worksheet data
  Sidebar: Select list rows at random
  Summarize data in worksheets that have hidden and filtered rows
  Find unique values ​​within a data set
  Defines valid sets of values ​​for ranges of cells
  Skills review
  Practice tasks
• Chapter 6: Reorder and summarize data
  Sort worksheet data
  Sort data by using custom lists
  Organize data into levels
  Look up information in a worksheet
  Skills review
  Practice tasks
• Chapter 7: Combine data from multiple sources
  Use workbooks as templates for other workbooks
  Link to data in other worksheets and workbooks
  Consolidate multiple sets of data into a single workbook
  Skills review
  Practice tasks
• Chapter 8: Analyze alternative data sets
  Examine data by using the Quick Analysis Lens
  Define an alternative data set
  Defines multiple alternative data sets
  Analyze data by using data tables
  Vary your data to get a specific result by using Goal Seek
  Find optimal solutions by using Solver
  Analyze data by using descriptive statistics
  Skills review
  Practice tasks
• Chapter 9: Create charts and graphics
  Create charts
  Create new types of charts
  Customize chart appearance
  Find trends in your data
  Create dual-axis charts
  Summarize your data by using sparklines
  Create diagrams by using SmartArt
  Create shapes and mathematical equations
  Skills review
  Practice tasks
• Chapter 10: Create dynamic worksheets by using PivotTables
  Analyze data dynamically by using PivotTables
  Filter, show, and hide PivotTable data
  Edit PivotTables
  Format PivotTables
  Create PivotTables from external data
  Create dynamic charts by using PivotCharts
  Skills review
  Practice tasks

Part 3: Collaborate and share in Excel

• Chapter 11: Print worksheets and charts
  Add headers and footers to printed pages
  Prepare worksheets for printing
  Fit your worksheet contents to the printed page
  Change page breaks in a worksheet
  Change the page printing order for worksheets
  Print worksheets
  Print parts of worksheets
  Print charts
  Skills review
  Practice tasks
• Chapter 12: Automate repetitive tasks by using macros
  Enable and examine macros
  Set macro security levels in Excel 2016
  Browse macros
  Create and modify macros
  Run macros when you click a button
  Run a macro when you open a workbook
  Insert form controls into a worksheet
  Skills review
  Practice tasks
• Chapter 13: Work with other Microsoft Office apps
  Include Office documents in workbooks and other files
  Link Office documents to Excel workbooks
  Embed files in Excel and other Office apps
  Create hyperlinks
  Paste charts into documents
  Skills review
  Practice tasks
• Chapter 14: Collaborate with colleagues
  Share workbooks
  Save workbooks for electronic distribution
  Manage comments
  Track and manage colleagues' changes
  Add protection to workbooks and worksheets
  Finalize workbooks
  Authenticate workbooks
  Save workbooks for the web
  Import and export XML data
  Work with OneDrive and Excel Online
  Skills review
  Practice tasks

Part 4: Perform advanced analysis

• Chapter 15: Perform business intelligence analysis
  Enable the Data Analysis add-ins
  Define relationships between tables
  Analyze data by using Power Pivot
  View data by using timelines
  Bring in external data by using Power Query
  Skills review
  Practice tasks
• Chapter 16: Create forecasts and visualizations
  Create Forecast Worksheets
  Define and manage measures
  Define and display Key Performance Indicators
  Create 3D maps
  Skills review
  Practice tasks