Welcome to ONLC Training Centers

Excel 2016 On-Demand: Comprehensive (Basic, Intermediate & Advanced) Course Outline

 (3 days)

**Note: This is an On-Demand Self Study Course, 3 days of content, 6-month unlimited access, $495**
You may take this course at any time; there are no set dates. The course covers Microsoft Excel 2016 Basic, Intermediate and Advanced topics. Customers must call us directly to purchase this class at 800-288-8221.

About This Course
The Online On-Demand training course includes expert lectures, PowerPoint visuals, and demonstrations. You will be able to follow along and successfully complete the course by completing the quizzes and exams. You do not need to own the software for the course and will be able to access the course from any device, provided you have internet access.

When you purchase your On-Demand course from ONLC you will receive the printed courseware at no charge!

Course Outline

Microsoft Office Excel 2016 - Basic:

Chapter 01 - Getting Started with Microsoft Office Excel 2016

Navigate the Excel User Interface
Microsoft Office Excel 2016
Office 365 Portal
Spreadsheets, Worksheets, and Workbooks
Cells and Ranges
Cell and Range References
The Excel User Interface Outer Elements
The Excel User Interface Inner Elements
Excel Window Commands
The Backstage View
Mouse Cursor Icons
Basic Data Entry

Use Excel Commands
The Ribbon
Tell Me
ScreenTips and KeyTips
The Quick Access Toolbar
The Mini Toolbar and Context Menus

Create and Save a Basic Workbook
The New Tab
The Save and Save As Commands
The Save As Screen
Compatibility Mode
The Convert Option
The Compatibility Checker
Microsoft OneDrive for Business
Excel Online

Enter Cell Data
Data Types
The Cut, Copy, and Paste Commands
The Undo and Redo Commands
The AutoFill Feature
AutoFill Options
Flash Fill
Demo - Entering Cell Data
Topic E: Use Excel Help
The Excel 2016 Help Window

Chapter 02 - Performing Calculations

Create Worksheet Formulas
Excel Formulas
The Formula Bar
A Basic Mathematical Formula
Elements of Excel Formulas
The Order of Operations
Intersection Operator

Insert Functions
The Function Library Group
The Insert Function Dialog Box
The Function Arguments Dialog Box
Graphical Cell and Range Reference Entry
The AutoSum Feature
Other Commonly Used Functions
Basic Function Syntax
The Formula AutoComplete Feature
The Arguments Tooltip

Reuse Formulas and Functions
The Paste Options
The Paste Special Dialog Box
Relative References
Absolute References
Mixed References
AutoFill and Formulas
Worksheet References
Excel Errors
Error Indicators

Chapter 03 - Modifying a Worksheet

Insert, Delete, and Adjust Cells, Columns, and Rows
The Insert and Delete Options
Manual Width and Height Adjustments
Manual Fit
The AutoFit Feature
The Row Height and Column Width Dialog Boxes
The Hide and Unhide Commands

Search for and Replace Data
The Find Command
The Replace Command
The Go To Dialog Box
The Go To Special Dialog Box

Use Proofing and Research Tools
The Spelling Dialog Box
The Thesaurus Task Pane
The Insights Task Pane

Chapter 04 - Formatting a Worksheet

Apply Text Formats
The Font Group
Live Preview
The Format Cells Dialog Box
The Colors Dialog Box
The Insert/Edit Hyperlink Dialog Box
The Format Painter

Apply Number Formats
Number Formats
Number Format Tab
Custom Number Formats

Align Cell Contents
Alignment Options
The Indent Commands
The Wrap Text Command
Orientation Options
The Merge & Center Options

Apply Styles and Themes
Cell Styles
The Style Dialog Box
The Merge Styles Dialog Box
Guidelines for Using Themes

Apply Basic Conditional Formatting
Conditional Formatting
The Conditional Formatting Dialog Boxes
The Highlight Cells Rules
Data Bars
Color Scales
Icon Sets

Create and Use Templates
Templates and the Backstage View

Chapter 05 - Printing Workbooks

Preview and Print a Workbook
The Print Tab
Print Settings
Page Orientation
Print Preview

Set Up the Page Layout
The Page Setup Dialog Box
The Print Area
The Print Titles Command
Page Breaks
Workbook Views
Page Break Preview View

Configure Headers and Footers
Headers and Footers
Page Layout View
Contextual Tabs
The Header & Footer Tools Contextual Tab
The Header and Footer Dialog Boxes

Chapter 06 - Managing Workbooks

Manage Worksheets
Tab Formatting Options
Drag to Reposition Worksheets
The Move or Copy Dialog Box
Methods of Inserting and Deleting Worksheets
The Hide and Unhide Worksheet Options

Manage Workbook and Worksheet Views
Custom Views
The Add View Dialog Box
The Split Command
The Freeze Panes Options
The Arrange All Command
The Arrange Windows Dialog Box
The View Side by Side Command
The Switch Windows Command
The New Window Command

Manage Workbook Properties
Workbook Properties in the Backstage View
The Properties Dialog Box
Custom Workbook Properties

This portion of the course takes one day to complete and includes 4h 17m of video presentation.

Microsoft Office Excel 2016 - Intermediate:

Chapter 01 - Working with Functions

Work with Ranges
Cell and Range Names
Names and the Name Box
The New Name Dialog Box
The Create from Selection Command
The Name Manager Dialog Box
Cell and Range Names in Formulas
The Use in Formula Command Method
The Formula AutoComplete Method

Use Specialized Functions
The Excel Function Reference
Function Syntax
Date Calculations

Work with Logical Functions
Comparison Operators
The IF Function
The AND Function
The OR Function
The NOT Function
Nested Function Syntax

Work with Date & Time Functions
The TODAY Function
The NOW Function
The DATE Function
The WEEKDAY Function
The WORKDAY Function

Work with Text Functions
The LEFT Function
The RIGHT Function
Convert Text to Columns Wizard
The MID Function
Text Concatenation with the Ampersand
The UPPER Function
The LOWER Function
The PROPER Function

Chapter 02 - Working with Lists
Sort Data
Multiple Column/Row Sorting
Quick Sorts
The Sort Dialog Box
The Sort Options Dialog Box

Filter Data
The Custom AutoFilter Dialog Box
Advanced Filtering
The Criteria Range

Query Data with Database Functions
Database Functions
Database Function Syntax

Outline and Subtotal Data
The SUBTOTAL Function
The Subtotals Feature
The Subtotal Dialog Box

Chapter 03 - Analyzing Data

Create and Modify Tables
Table Components
The Create Table Dialog Box
The Table Tools Design Contextual Tab
Table Styles and Quick Styles
The New Table Style Dialog Box
Quick Analysis

Apply Intermediate Conditional Formatting
The New Formatting Rule Dialog Box
The Conditional Formatting Rules Manager Dialog Box
Rule Precedence

Apply Advanced Conditional Formatting
The Use a Formula to Determine Which Cells to Format Rule

Chapter 04 - Visualizing Data with Charts

Create Charts

Chart Basics
Chart Types
Recommended Charts
The Insert Chart Dialog Box

Modify and Format Charts
Chart Modifications
Chart Formatting
Chart Elements
The Design Tab
The Format Tab
The Format Task Pane
The Chart Tools Buttons
The Select Data Source Dialog Box

Use Advanced Chart Features
Dual-Axis Charts
The Format Trendline Task Pane
Chart Templates

Chapter 05 - Using PivotTables and PivotCharts

Create a PivotTable
Transactional Data
The Create PivotTable Dialog Box
The PivotTable Fields Task Pane

Analyze PivotTable Data
The Analyze Tab
The Design Tab
The Value Field Settings Dialog Box
SUM Function Only
Percentage of Total
Difference From Option

Present Data with PivotCharts
The PivotChart Fields Task Pane
PivotChart Filters

Filter Data by Using Timelines and Slicers
The Insert Slicers Dialog Box
The Slicer Tools Contextual Tab
The Report Connections Dialog Box
The Insert Timelines Dialog Box
The Timeline Tools Contextual Tab

Chapter 05 Review

This portion of the course takes one day to complete and includes 3h 45m of video presentation.

Microsoft Office Excel 2016 - Advanced:

Chapter 01 - Working with Multiple Worksheets and Workbooks

Use Links and External References
Linked Cells
The Edit Links Dialog Box
External References in Formulas and Functions

Use 3-D References
Grouped Worksheets
3-D References
3-D References in Summary Functions

Consolidate Data
Data Consolidation
The Consolidate Dialog Box

Chapter 02 - Using Lookup Functions and Formula Auditing

Use Lookup Functions
The VLOOKUP Function

Trace Cells
Precedent and Dependent Cells
The Trace Precedents and Trace Dependents Commands
Trace Arrows
The Go To Dialog Box and Trace Arrows
The Go To Special Dialog Box

Watch and Evaluate Formulas
The Watch Window
The Evaluate Formula Dialog Box

Chapter 03 - Sharing and Protecting Workbooks

Collaborate on a Workbook
Shared Workbooks
Change Tracking
The Highlight Changes Dialog Box
The Select Changes to Accept or Reject Dialog Box
The Accept or Reject Changes Dialog Box
The Compare and Merge Workbooks Command
The Share Options
Microsoft Accounts and OneDrive
Excel Online
The Accessibility Checker

Protect Worksheets and Workbooks
Worksheet and Workbook Element Protection
The Protect Sheet Command
The Protect Workbook Command
The Protect Workbook Options
The Document Inspector

Chapter 04 - Automating Workbook Functionality

Apply Data Validation
The Data Validation Dialog Box
Data Validation Criteria
The Input Message Tab
The Error Alert Tab

Search for Invalid Data and Formulas with Errors
The Circle Invalid Data Command
The Error Checking Dialog Box

Work with Macros
Macro Security Settings
Microsoft Visual Basic for Applications
The Record Macro Dialog Box
The Macro Dialog Box
The Personal Workbook

Creating Sparklines and Mapping Data

Create Sparklines
The Create Sparklines Dialog Box
The Sparkline Tools Contextual Tab
Demo - Creating Sparklines

Map Data
3D Maps
3D Map Layer Pane
Launch 3D Maps Dialog Box

Chapter 06 - Forecasting Data

Determine Potential Outcomes Using Data Tables
What-If Analysis
One-Variable Data Tables
Two-Variable Data Tables
The Data Table Dialog Box

Determine Potential Outcomes Using Scenarios
The Scenario Manager Dialog Box
The Add Scenario Dialog Box
The Scenario Values Dialog Box
The Scenario Command

Use the Goal Seek Feature
The Goal Seek Dialog Box
The Create Forecast Worksheet Dialog Box

This portion of the course takes one day to complete and includes 2h 45m of video presentation.

View outline in Word


Attend hands-on, instructor-led Excel 2016 On-Demand: Comprehensive (Basic, Intermediate & Advanced) training classes at ONLC's more than 300 locations. Not near one of our locations? Attend these same live classes from your home/office PC via our Remote Classroom Instruction (RCI) technology.

For additional training options, check out our list of Courses and select the one that's right for you.

Microsoft Gold Partner
Need a price quote?

Follow the link to our self-service price quote form to generate an email with a price quote.

Attend computer classes from ONLC Training Centers Request a copy via mail


Class Format
Class Policies
Student Reviews

Bookmark and Share

First Name

Last Name