Online Class: Advanced Excel 2016

no certificate
- OR -
with CEU Certificate*
Start Right Now!
Taking multiple courses? Save with our platinum program.
  • 22
  • 33
    Exams &
  • 2,945
    have taken this course
  • 17
    average time
  • 1.7

Course Description

Excel 2016 Advanced Skills Course

With most software programs on the market today, you can thrive with just a basic knowledge of the tools and options built into the individual program.  However, that is not necessarily true when it comes to Excel 2016.  

Excel is a complex, yet easy-to-use spreadsheet program that contains a multitude of much-needed tools with very advanced options.   While it's true that you can create an attractive spreadsheet and use simple formulas in Excel with very little experience, that is just the tip of the iceberg when it comes to what Excel can truly do.  In order to be able to successfully use the program to create spreadsheets, enter data, and maintain that data, you must have more than basic knowledge.   This course gives you the advanced skills you need to be able to fully use the program.

This course is for the intermediate student who has already mastered the basic skills needed to use Excel 2016 and wants to gain more advanced skills to put to work in a business environment or for personal use.  This course goes in-depth, using step-by-step instructions to teach advanced techniques to broaden all students' knowledge and use of the program.

In this course, you will learn how to:

·    Create outlines in Excel

·    Create your own templates

·    Work with sparklines

·    Create and manage scenarios

·    Use Goal Seek and Solver

·    Create, manage, and format pivot tables and pivot charts

·    Use the Excel mathematical functions

·    Create and write complex formulas

·    Perform data lookups

·    Create and use IF statements

·    Use Excel's data functions

·    Create and use data validation rules

·    Apply custom and prebuilt conditional formatting

·    Work with functions to manipulate strings of text and data

·    Create and use macros

·    Troubleshoot and audit formulas

·    Create pivot tables and pivot charts

·    Use queries to import external data

·    Import and clean data

·    Link and consolidate worksheets and workbooks

·    Troubleshoot errors

·    And more

This advanced Excel course is designed for the intermediate Excel user who desires to learn more advanced skills.   The topics covered in this course will give each student the knowledge and practice to be able to use Excel in a business setting or for advanced personal use. 

This course makes the assumption that all students have at least a basic working knowledge of Excel and have mastered the basic skills required to:

  • Navigate Excel 2016

  • Create, name, save, and work with worksheets and workbooks

  • Work with, edit, and format cells, rows, and columns

  • Create and format tables

  • Enter data into Excel

  • Create basic formulas and calculations

  • Understand the role of functions in Excel, as well as mathematical operators

  • Understand absolute vs. relative cell references

  • Have basic knowledge of the What-If Analysis

  • Create, sort, and filter lists

If you are not a regular Excel user, it is important that you take the time to brush up on your basic Excel skills before continuing with this course.   Although our Excel 2016 basic course is not a mandatory prerequisite, it is recommended that you complete that course prior to starting advanced Excel 2016 training if you do not have a working knowledge of the topics listed above.


  • Completely Online
  • Self-Paced
  • Printable Lessons
  • Full HD Video  
  • 6 Months to Complete
  • 24/7 Availability
  • Start Anytime
  • PC & Mac Compatible
  • Android & iOS Friendly
  • Accredited CEUs
Universal Class is an IACET Accredited Provider

Course Lessons

Average Lesson Rating:
4.6 / 5 Stars (Average Rating)
"Extraordinarily Helpful"
(1,849 votes)


Introduction 1 Total Points
  • Lesson 1 Video
  • Review Practice Worksheet: CHAR_functions.xlsx
  • Review 2 Videos: What's New in Microsoft Office Excel 2016?; Compare Excel 2013 to 2016
  • Take Survey: Reasons for Taking this Course
  • Complete: Lesson 1 Assignment - Introduction

Lesson 2: Outlines, Custom Views, and Templates

In Excel, outlining gives you the ability to organize large amounts of data. 11 Total Points
  • Lesson 2 Video
  • Review 3 Videos: Excel 2016 Tutorial Applying and Removing Outlines Microsoft Training Lesson; Excel 2016: Groups and Subtotals; Excel 2016 Tutorials | How to Create and Use Custom Views
  • Complete: Lesson 2 Assignment
  • Complete: Exam 2

Lesson 3: Mathematical Functions

Functions are used to create formulas. In this lesson, we are going to start talking about some of the more basic functions, as well as teaching you to use them. 10 Total Points
  • Lesson 3 Video
  • Review Article: Overview of formulas in Excel 2016 for Windows
  • Review 3 Videos: Advanced Microsoft Excel 2013 Tutorial | AVERAGE, MODE, MEAN, And MEDIAN; How to use the SUM, SUMIF and SUMIFS functions in Excel 2013?; How to use the COUNTIF Function Instead of VLOOKUP in Excel
  • Complete: Lesson 3 Assignment
  • Complete: Exam 3

Lesson 4: Data Functions

Data functions give you various ways to search your data. 11 Total Points
  • Lesson 4 Video
  • Review 8 Videos: MS Excel Tip - How To Perform Calculations On Time; Excel: How to use the Match Function; Microsoft Excel 2013 - Using Excel's MATCH and INDEX functions; Highline Excel 2016 Class 11: Lookup Functions and Formulas, Comprehensive Lessons, 20 Examples; Excel: The excel ISERROR function; How to use the iferror function for your formulas in Excel; Create a Dynamic Excel Chart Using Offset Function; Using the Cell Function in Excel
  • Complete: Lesson 4 Assignment
  • Complete: Exam 4

Lesson 5: Data Lookups

Data lookup is quite simply the process where values in Excel are scanned until certain results are found. 10 Total Points
  • Lesson 5 Video
  • Review 4 Videos: Excel Vlookup Tutorial and Example; How to Create lookup tables in Excel; How to use HLOOKUP in Microsoft Excel 2013; IF statements with VLOOKUPs -- Microsoft Excel
  • Complete: Exam 5

Lesson 6: The IF Function

IF functions can be used to add comments to your data. They can also be used to hide errors in calculations. 10 Total Points
  • Lesson 6 Video
  • Review 5 Videos: Excel Tutorial 9 of 25 - Using the IF Formula; Use the IFNA Function in Excel 2013; Dates and Comparison Operators in Excel's IF Function; Advanced Microsoft Excel 2013 Tutorial | Display Cell Formulas In Another Cell; Showing Formulas in Cells - Microsoft Excel
  • Complete: Exam 6

Lesson 7: Manipulating Text

We are going to take this lesson to learn some of the text functions and formulas, then teach you how they are used. More specifically, we will show you how to use them. 11 Total Points
  • Lesson 7 Video
  • Review 5 Videos: Tutorial: How to Use the Left and Right Formulas in Excel 2016; CLEAN AND TRIM Functions in Excel; Learn Excel Functions - LEFT, RIGHT, MID and LEN; Excel Lesson 25 - The Concatenate Function; Excel formulas and functions - REPLACE and SUBSTITUTE
  • Complete: Lesson 7 Assignment
  • Complete: Exam 7

Lesson 8: Exploring Conditional Formatting

Conditional formatting is another way to visualize your data. 10 Total Points
  • Lesson 8 Video
  • Review 2 Articles: How to Use Conditional Formatting in Excel; Quickly Remove Formatting From Your Excel Spreadsheet
  • Complete: Exam 8

Lesson 9: Working with Sparklines

Sparklines represent data in your worksheet. They show the variations or trends in a section of your data, typically within a row. 11 Total Points
  • Lesson 9 Video
  • Review 3 Videos: Excel 2016 Tutorial Changing Color Schemes Microsoft Training Lesson; Excel 2016 Tutorial Modifying Sparklines Microsoft Training Lesson; Creating Excel 2013 Sparklines chart
  • Complete: Lesson 9 Assignment
  • Complete: Exam 9

Lesson 10: Linking To and Consolidating Worksheets

Consolidating means to combine or summarize data from two or more worksheets. 10 Total Points
  • Lesson 10 Video
  • Review 3 Videos: Create formula with external references in Excel; Excel 2016 Tutorial Arranging Open Workbook Windows Microsoft Training Lesson; Locate and Change Excel Pivot Table Data Source
  • Complete: Exam 10

Lesson 11: Importing and Cleaning Data

Excel gives you several methods to import different types of files that contain your data. 11 Total Points
  • Lesson 11 Video
  • Review 2 Videos: Import Bank Transactions into QuickBooks from QBO, QIF, CSV, and PDF Bank Statements; Excel 2016 Tutorial | How To Find and Remove Duplicates Records
  • Complete: Lesson 11 Assignment
  • Complete: Exam 11

Lesson 12: Pivot Tables

A pivot table is a tool that you can use to summarize data when you have a lot of it in a worksheet. 10 Total Points
  • Lesson 12 Video
  • Review 5 Videos: Data Structure for Pivot Tables; Excel Pivot Tables, Charts and Dashboards - Excel 2016, 2013 and 2010; How to Create an Interactive Excel Pivot Chart; Selecting, Copying, Moving, Clearing, Deleting a Pivot Table; How to Sort data in a Pivot Table or Pivot Chart
  • Complete: Exam 12

Lesson 13: Continuing to Work with PivotTables

PivotTables are an invaluable tool in Excel because they give you a way to summarize data; however, they also give you a way to look at the exact data that you need. 10 Total Points
  • Lesson 13 Video
  • Review 4 Videos: Excel Tip Split Pivot Table Filters Into Multiple Columns; Excel total: Subtotals and grand totals in a pivot table; Create Excel Pivot Table Calculated Field With a Count; How to Connect Slicers on EXCEL Dashboards with Multiple Charts/Tables/Graphs
  • Complete: Exam 13

Lesson 14: PivotCharts

A PivotChart is like a PivotTable in that it summarizes the data from a worksheet. 10 Total Points
  • Lesson 14 Video
  • Review 3 Videos: How to Create an Interactive Excel Pivot Chart; Excel 2016 Tutorial Formatting the Chart and Plot Areas Microsoft Training Lesson; interactive charts in Excel - filtering with chart's column selection
  • Complete: Exam 14

Lesson 15: The What-if Analyses

A what-if analysis lets you explore possibilities by entering possible values into the same equation so you can see the possible outcomes in the cells of your spreadsheet. 11 Total Points
  • Lesson 15 Video
  • Review Video: Excel 2010: What-If Analysis
  • Complete: Lesson 15 Assignment
  • Complete: Exam 15

Lesson 16: Goal Seek and Solver

Goal Seek determines what value needs to be in an input cell to achieve a desired result in a formula cell. Solver determines what values need to be in multiple input cells to achieve a desired result. 10 Total Points
  • Lesson 16 Video
  • Review 3 Videos: Excel 2016 from Scratch - What-If-Analysis C/1, Goal Seek and Scenario Manager; Installing Excel's Solver Add-In; Using Solver with Excel 2016 for Linear Optimization Problems
  • Complete: Exam 16

Lesson 17: The Analysis ToolPak

The Analysis ToolPak is an add-in, just like Solver is an add-in, that provides various methods of analysis. 10 Total Points
  • Lesson 17 Video
  • Review 4 Videos: How to Install the Data Analysis ToolPak in Microsoft Excel; Two-Way ANOVA with Replication using Excel 2016 Data Analysis Tools; How to Compute Variance and Covariance in Excel : Advanced Microsoft Excel; How to perform Fourier Analysis in Excel 2013
  • Complete: Exam 17

Lesson 18: All About Data Validation

Data validation in Excel allows you to define what type of data should be entered into a cell and allows you to prevent invalid data from being entered. 11 Total Points
  • Lesson 18 Video
  • Review 2 Videos: How to Find Where a Formula Containing an Invalid Reference Is in Excel : Using Microsoft Excel; Excel : Data Validation and Drop Down Lists
  • Complete: Lesson 18 Assignment
  • Complete: Exam 18

Lesson 19: Using Get & Transform to Perform Queries

You can also import data from other databases into Excel 2016, as we mentioned in the last lesson. Get & Transform is a tool you can use to do this. 11 Total Points
  • Lesson 19 Video
  • Review 5 Videos: Using Get and Transform to Perform Queries; How To Summarize Data in Excel Using Get and Transform - Power Query; How to transfer data from one workbook to another automatically using Excel VBA; How to Link Excel 2010 Worksheet to Another Workbook; How to import data from a Text file into Excel.
  • Complete: Lesson 19 Assignment
  • Complete: Exam 19

Lesson 20: Troubleshooting and Auditing in Excel 2016

Excel 2016 gives you the tools to help you efficiently audit and troubleshoot your workbooks and data. 10 Total Points
  • Lesson 20 Video
  • Review 3 Videos: Excel 2016 Tutorial Error Checking Microsoft Training Lesson; How to Use Trace in Excel; Excel 2016 Tutorial Using the Watch Window Microsoft Training Lesson
  • Complete: Exam 20

Lesson 21: Exploring Macros in Excel 2016

A macro is a series of instructions or commands that can be triggered by a keyboard shortcut, button in the toolbar, or by an icon that you can stick in a worksheet. 10 Total Points
  • Lesson 21 Video
  • Complete: Exam 21

Lesson 22: Continuing to Work with Macros

We are going to continue to explore macros in this lesson and learn more about creating and working with them. 116 Total Points
  • Lesson 22 Video
  • Take Poll: End of Course Poll
  • Take Survey: Course Comments
  • Take Survey: Program Evaluation Follow-up Survey (End of Course)
  • Complete: Lesson 22 Assignment
  • Complete: Exam 22
  • Complete: The Final Exam
Total Course Points

Learning Outcomes

By successfully completing this course, students will be able to:
  • Define outlines, custom views, and templates in Excel 2016.
  • Describe mathematical functions.
  • Describe data functions.
  • Describe data lookups.
  • Demonstrate usage of the if function.
  • Describe manipulating text.
  • Demonstrate conditional formatting.
  • Describe working with sparklines.
  • Describe linking to and consolidating worksheets.
  • Summarize importing and cleaning data.
  • Demonstrate creating Pivot Tables.
  • Demonstrate creating and using PivotCharts.
  • Demonstrate using the What-if Analyses
  • Demonstrate using the Goal Seek and Solver
  • Describe working with macros, and
  • Demonstrate mastery of lesson content at levels of 70% or higher.

Additional Course Information

Online CEU Certificate
  • Document Your Lifelong Learning Achievements
  • Earn an Official Certificate Documenting Course Hours and CEUs
  • Verify Your Certificate with a Unique Serial Number Online
  • View and Share Your Certificate Online or Download/Print as PDF
  • Display Your Certificate on Your Resume and Promote Your Achievements Using Social Media
Document Your CEUs on Your Resume
Course Title: Advanced Excel 2016
Course Number: 9770442
Course Requirements: View Course Requirements
Lessons Rating: 4.6 / 5 Stars (1,849 votes)
Languages: English - United States, Canada and other English speaking countries
Course Type: Computer Skill (Self-Paced, Online Class)
CEU Value: 1.7 IACET CEUs (Continuing Education Units)
CE Accreditation: Universal Class, Inc. has been accredited as an Authorized Provider by the International Association for Continuing Education and Training (IACET).
Grading Policy: Earn a final grade of 70% or higher to receive an online/downloadable CEU Certification documenting CEUs earned.
Assessment Method: Lesson assignments and review exams
Syllabus: View Syllabus
Duration: Continuous: Enroll anytime!
Course Fee: $60.00 (no CEU Certification) || with Online CEU Certification: $85.00

Choose Your Subscription Plan

Course Only
One Course
No Certificate / No CEUs
for 6 months
Billed once
This course only
Includes certificate X
Includes CEUs X
Self-paced Yes
Instructor support Yes
Time to complete 6 months
No. of courses 1 course
Certificate Course
One Course
Certificate & CEUs
for 6 months
Billed once
This course only
Includes certificate Yes
Includes CEUs Yes
Self-paced Yes
Instructor support Yes
Time to complete 6 months
No. of courses 1 course
Platinum Yearly
Certificates & CEUs
per year
Billed once
Includes all 500+ courses
Includes certificate Yes
Includes CEUs Yes
Self-paced Yes
Instructor support Yes
Time to complete 12 Months
No. of courses 500+
Platinum 2 Years
Certificates & CEUs
for 2 years
You save $79.00!
Billed once
Includes all 500+ courses
Includes certificate Yes
Includes CEUs Yes
Self-paced Yes
Instructor support Yes
Time to complete 24 Months
No. of courses 500+