Online Class: Advanced Excel 2016

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.
$70.00
no certificate
- OR -
$95.00
with CEU Certificate*
Start Right Now!
$95.00 (with CEU Certificate)
Taking multiple courses? Save with our platinum program.
 
  • 22
    Lessons
  • 33
    Exams &
    Assignments
  • 3,060
    Students
    have taken this course
  • 17
    Hours
    average time
  • 1.7
    CEUs
 
 

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.

 
Courses Frequently Bought Together:
  • 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,855 votes)

Introduction

Introduction Additional lesson topics: Compare Excel to ; Excel Bible; What's New in Microsoft Office Excel ?; Microsoft Excel Introduction Quick Reference Guide 1 Total Points
  • Lesson 1 Video
  • Review Practice Worksheet: CHAR_functions.xlsx
  • Lesson discussions: 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. Additional lesson topics: Excel : Groups and Subtotals; Excel Tutorial Applying and Removing Outlines Microsoft Training Lesson 11 Total Points
  • Lesson 2 Video
  • Complete: Lesson 2 Assignment
  • Assessment: 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. Additional lesson topics: Overview of formulas in Excel for Windows; Excel Formulas and Functions Tutorial 1 Microsoft Excel Basics Excel Tutorial Excel ; How to use the SUM, SUMIF and SUMIFS functions in Excel ?; Excel Tutorial; How to use the COUNTIF Function Instead of VLOOKUP in Excel 10 Total Points
  • Lesson 3 Video
  • Complete: Lesson 3 Assignment
  • Assessment: Exam 3

Lesson 4: Data Functions

Data functions give you various ways to search your data. Additional lesson topics: Highline Excel Class 11: Lookup Functions and Formulas, Comprehensive Lessons, Several Examples; Microsoft Excel ; Excel: How to use the Match Function; Using the Cell Function in Excel; Create a Dynamic Excel Chart Using Offset Function; How to use the iferror function for your formulas in Excel; MS Excel Tip; Excel: The excel ISERROR function 11 Total Points
  • Lesson 4 Video
  • Complete: Lesson 4 Assignment
  • Assessment: Exam 4

Lesson 5: Data Lookups

Data lookup is quite simply the process where values in Excel are scanned until certain results are found. Additional lesson topics: How to Create lookup tables in Excel; How to use HLOOKUP in Microsoft Excel ; Excel Vlookup Tutorial and Example; IF statements with VLOOKUPs -- Microsoft Excel 10 Total Points
  • Lesson 5 Video
  • Assessment: 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. Additional lesson topics: Dates and Comparison Operators in Excel's IF Function; Excel Tutorial Several of 25; Use the IFNA Function in Excel ; Showing Formulas in Cells 10 Total Points
  • Lesson 6 Video
  • Assessment: 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. Additional lesson topics: Learn Excel Functions; Tutorial: How to Use the Left and Right Formulas in Excel ; CLEAN AND TRIM Functions in Excel; Excel Lesson 25; Excel formulas and functions 11 Total Points
  • Lesson 7 Video
  • Complete: Lesson 7 Assignment
  • Assessment: Exam 7

Lesson 8: Exploring Conditional Formatting

Conditional formatting is another way to visualize your data. Additional lesson topics: How to Use Conditional Formatting in Excel; Quickly Remove Formatting From Your Excel Spreadsheet 10 Total Points
  • Lesson 8 Video
  • Assessment: 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. Additional lesson topics: Excel Tutorial Modifying Sparklines Microsoft Training Lesson; Microsoft Excel / pt Several Conditional format, Sparklines, other Charts ; Excel Tutorial Changing Color Schemes Microsoft Training Lesson; Creating Excel Sparklines chart 11 Total Points
  • Lesson 9 Video
  • Complete: Lesson 9 Assignment
  • Assessment: Exam 9

Lesson 10: Linking To and Consolidating Worksheets

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

Lesson 11: Importing and Cleaning Data

Excel gives you several methods to import different types of files that contain your data. Additional lesson topics: Import Bank Transactions into QuickBooks from QBO, QIF, CSV, and PDF Bank Statements; Importing Excel CSV or any Spreadsheet file into QuickBooks with built-in tools. Plus Zed Axis 11 Total Points
  • Lesson 11 Video
  • Complete: Lesson 11 Assignment
  • Assessment: 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. Additional lesson topics: How to Create an Interactive Excel Pivot Chart; Data Structure for Pivot Tables; Excel Pivot Tables, Charts and Dashboards; Selecting, Copying, Moving, Clearing, Deleting a Pivot Table; How to Sort data in a Pivot Table or Pivot Chart 10 Total Points
  • Lesson 12 Video
  • Assessment: 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. Additional lesson topics: Excel total: Subtotals and grand totals in a pivot table; Create Excel Pivot Table Calculated Field With a Count; Excel Tip Split Pivot Table Filters Into Multiple Columns; How to Connect Slicers on EXCEL Dashboards with Multiple Charts/Tables/Graphs 10 Total Points
  • Lesson 13 Video
  • Assessment: Exam 13

Lesson 14: PivotCharts

A PivotChart is like a PivotTable in that it summarizes the data from a worksheet. Additional lesson topics: How to Create an Interactive Excel Pivot Chart; Excel Tutorial Formatting the Chart and Plot Areas Microsoft Training Lesson; Introduction to Pivot Tables, Charts, and Dashboards in Excel Part 1 ; interactive charts in Excel 10 Total Points
  • Lesson 14 Video
  • Assessment: 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. Additional lesson topics: Excel : What-If Analysis 11 Total Points
  • Lesson 15 Video
  • Complete: Lesson 15 Assignment
  • Assessment: 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. Additional lesson topics: Microsoft Excel Goal Seek, Scenarios, Solver ; Installing Excel's Solver Add-In; Excel from Scratch; Using Solver with Excel for Linear Optimization Problems 10 Total Points
  • Lesson 16 Video
  • Assessment: 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. Additional lesson topics: One-Way ANOVA ANOVA: Single Factor using Excel Data Analysis Tools; Installing Excel Toolpak Data Analysis on Mac; How to Install the Data Analysis ToolPak in Microsoft Excel; Two-Way ANOVA with Replication using Excel Data Analysis Tools; How to perform Fourier Analysis in Excel ; How to Compute Variance and Covariance in Excel : Advanced Microsoft Excel 10 Total Points
  • Lesson 17 Video
  • Assessment: 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. Additional lesson topics: How to Find Where a Formula Containing an Invalid Reference Is in Excel : Using Microsoft Excel; Excel : Data Validation and Drop Down Lists; Excel Video 433 Message Boxes Part 2 11 Total Points
  • Lesson 18 Video
  • Complete: Lesson 18 Assignment
  • Assessment: 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. Additional lesson topics: How to transfer data from one workbook to another automatically using Excel VBA; Using Get and Transform to Perform Queries; How to Summarize Data in Excel Using Get and Transform; How to Link Excel Worksheet to Another Workbook; How to import data from a Text file into Excel. 11 Total Points
  • Lesson 19 Video
  • Complete: Lesson 19 Assignment
  • Assessment: 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. Additional lesson topics: Excel Tutorial Using the Watch Window Microsoft Training Lesson; Excel Tutorial Error Checking Microsoft Training Lesson; How to Use Trace in Excel 10 Total Points
  • Lesson 20 Video
  • Assessment: 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
  • Assessment: 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
  • Lesson discussions: End of Course Poll; Course Comments; Program Evaluation Follow-up Survey (End of Course)
  • Complete: Lesson 22 Assignment
  • Assessment: Exam 22
  • Assessment: The Final Exam
325
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.
  • 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
Lessons Rating: 4.6 / 5 Stars (1,855 votes)
Languages: English - United States, Canada and other English speaking countries
Availability: This course is online and available in all 50 states including: California, Florida, Georgia, Illinois, New York, Pennsylvania, Ohio, Texas, and Washington.
Last Updated: July 2022
Course Type: 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
Course Fee: $95.00 U.S. dollars

Choose Your Subscription Plan

Course Only
One Course
No Certificate / No CEUs
$70
for 6 months
 
Add to Cart
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
$95
for 6 months
 
Add to Cart
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
ALL COURSES
Certificates & CEUs
$189
per year
 
Start Now
Billed once
Includes all 600+ courses
Includes certificate Yes
Includes CEUs Yes
Self-paced Yes
Instructor support Yes
Time to complete 12 Months
No. of courses 600+
Platinum 2 Years
ALL COURSES
Certificates & CEUs
$299
for 2 years
You save $79.00!
Start Now
Billed once
Includes all 600+ courses
Includes certificate Yes
Includes CEUs Yes
Self-paced Yes
Instructor support Yes
Time to complete 24 Months
No. of courses 600+
 

Student Testimonials

  • "I really enjoyed this Course as I've always been interested in learning more about Excel, and this fueled my interest and desire to learn more. The main feature of your Courses is the dual option content of reading about the topic chapter, and then watching the video ( to see the demonstration of what was discussed) about the text. This is key, and based on my preferred visual learning style, it's far more effective than reading a chapter in an Excel text book." -- Richard B.

Related Courses