Universal Class: Over 500 Online Certificate Courses
 

Online Class: Advanced Excel 2016


Microsoft Training Picture
$60.00
no certificate
- OR -
$85.00
with CEU Certificate*
Start Right Now!
Taking multiple courses? Save with our platinum program.
 
 
 
 

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

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

Request More Information

Request More Information
Have a question? Check out our FAQ or contact us for more information.
 
Microsoft Training Picture Microsoft Training Picture
 
 

Course Lessons

Average Lesson Rating: (128 votes)
4.6 / 5 Stars (Average Rating) 4.6 / 5 Stars (Average Rating) 4.6 / 5 Stars (Average Rating) 4.6 / 5 Stars (Average Rating) 4.6 / 5 Stars (Average Rating)
"Extraordinarily Helpful"
3/25/2017 11:20:55 AM

Introduction

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
  • 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 4 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; Microsoft Excel 2016 using templates
  • 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 7 Videos: Excel Tutorial 9 of 25 - Using the IF Formula; Use the IFNA Function in Excel 2013; Nesting IF and AND Functions in Excel; Training: Advanced IF functions in Excel 2013: Nested IF functions - Video 2 of 5; 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 6 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; 31 Formatting numbers and dates | Excel 2016 Essential Training
  • 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
  • Review Video: Excel 2016 from Scratch - Conditional Formatting
  • 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 Article: Excel 2010 Working with Formulas External Reference
  • Review 5 Videos: Create formula with external references in Excel; Excel 2016 Tips and Tricks - Dependent cells, Formula Shortcuts; Excel 2016 Tutorial Arranging Open Workbook Windows Microsoft Training Lesson; Locate and Change Excel Pivot Table Data Source; How to use Consolidate in Excel to combine Data
  • 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 3 Videos: Import Bank Transactions into QuickBooks from QBO, QIF, CSV, and PDF Bank Statements; Data Import: Cleaning and Preparing Your Data Using Excel; 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 2 Videos: Excel 2010: What-If Analysis; Introduction to Scenarios in Excel
  • 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 3 Videos: Excel Data Validation to limit data input options; 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 6 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.; Performing Web Queries in 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 4 Videos: Excel 2016 Tutorial Error Checking Microsoft Training Lesson; EXCEL - DISPLAYING FORMULAS AND ERROR CHECKING; 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
  • Review Article: Excel 2016 Tutorial Running and Deleting Recorded Macros Microsoft Training
  • Review 3 Videos: 81 Exploring the need for macros | Excel 2016 Essential Training; Exploring font styles and effects | Excel 2016 Essential Training; 51 Renaming, inserting, and deleting sheets | Excel 2016 Essential Training
  • 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
  • Complete: Lesson 22 Assignment
  • Complete: Exam 22
  • Complete: The Final Exam
325
Total Course Points

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
Languages: English - United States, Canada and other English speaking countries
Category:
Course Type: Computer Skill
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
Instructor: UniversalClass Staff Instructor
Syllabus: View Syllabus
Duration: Continuous: Enroll anytime!
Course Fee: $60.00 (no CEU Certification) || with CEU Certification: $85.00

Choose Your Subscription Plan

Course Only
One Course
No Certificate / No CEUs
$60
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
$85
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
Best Value
ALL COURSES
Certificates & CEUs
$189
per year
You save 50%!
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 Monthly
ALL COURSES
Certificates & CEUs
$59 first month
$29.00 / each consecutive month thereafter
 
Billed monthly
Includes all 500+ courses
Includes certificate Yes
Includes CEUs Yes
Self-paced Yes
Instructor support Yes
Time to complete Monthly
No. of courses 500+
 

Related Courses

Follow Us Online
  • Follow us on Google Plus Follow us on FaceBook Follow us on Twitter Follow us on YouTube
© Copyright 1999-2017 Universal Class™ All rights reserved.