Online Class: Advanced Excel 2019


$60.00
no certificate
- OR -
$85.00
with CEU Certificate*
Start Right Now!
Taking multiple courses? Save with our platinum program.
 
  • 20
    Lessons
  • 36
    Exams &
    Assignments
  • 16
    Hours
    average time
  • 1.6
    CEUs
  • 306
    Students
    have taken this course
 
 
Search Button
 
 
 

Course Description

Excel is one of the most powerful tools in Microsoft Office. You can import, query, analyze and manipulate data to create powerful reports that can be used by individuals and enterprise businesses alike. This course explores some of the more advanced features of Microsoft Excel to give you a more powerful experience when you need the most out of spreadsheet software.

In this Excel 2019 advanced course, you'll learn all about...

  • Outlines, Custom Views, and Templates
  • Mathematical Functions
  • Data Functions and Data Lookups
  • The IF Function
  • Manipulating Text
  • Conditional Formatting
  • Sparklines
  • Importing, Cleaning and Validating Data
  • Pivot Tables and Pivot Charts
  • The What-If Analysis
  • Goal Seek and Solver
  • The Analysis ToolPak
  • Using Get & Transform to Perform Queries
  • Macros
  • And Much More! 
 
 
The course starts with functions and data lookups, as these components of Excel help the spreadsheet creator with advanced calculations and import functionality. We then cover conditional formatting for highlighting important cells based off of conditions. Sparklines are also covered so that the learner can get a quick data visualization of the information stored in each worksheet.

Pivot tables are one of the most beneficial tools in Excel, and this course covers pivot tables both basic and advanced features. It then moves on to pivot charts, which are graphs that can be used with pivot table data.

Analysis tools in Excel 2019 are some of the most advanced and useful to users that want to run complex reports. Excel has three What-If analysis tools that can be run on your data and provide reports on goals that give answers to your problems. The Solver and Analysis Toolpak are two add-ins that expand on these tools, and they are also covered in this course.

The final lessons cover data validation, troubleshooting, querying data and macros. These lessons provide ways to work with data and clean up unstructured information. This course fully covers advanced features in Excel so that you can create complex spreadsheets for business or individual use.

  • 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) 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"
(93 votes)

Lesson 1: Outlines, Custom Views, and Templates

Microsoft includes numerous templates that you can choose from, and many times these templates include formulas and outlines that you can use to build a spreadsheet. 10 Total Points
  • Lesson 1 Video
  • Review 2 Articles: Free Excel Templates; How to Use Excel Custom Views Like a Pro
  • Take Survey: Reasons for Taking this Course
  • Complete Assignment: My Excel Experience
  • Complete: Lesson 1 Exam

Lesson 2: Mathematical Functions

Mathematical functions are a part of the spreadsheet creation process, but Excel has several that do the calculations for you. You can manually create your own functions or use the pre-created Excel functions. 11 Total Points
  • Lesson 2 Video
  • Review 4 Articles: SUM function; AVERAGE function; How to Use the RAND and RANDBETWEEN Functions in Excel; Countif function
  • Complete: Lesson 2 Activity Assignment
  • Complete: Lesson 2 Exam

Lesson 3: Data Functions

Data functions (also called information functions) work with providing feedback about cell data based on the function used and the condition criteria. These functions include error information, numerical information or other information that tells you the type of data contained in a cell. 11 Total Points
  • Lesson 3 Video
  • Review 6 Articles: Excel ISERROR Function; ERROR.TYPE function; Excel ISNUMBER Function; Excel ISREF Function; Excel ISBLANK Function; Excel TYPE Function
  • Complete: Lesson 3 Activity Assignment
  • Complete: Lesson 3 Exam

Lesson 4: Data Lookups

Excel has three ways to search for data within a spreadsheet: vertical, horizontal and both. This lesson will focus on these different search methods. 11 Total Points
  • Lesson 4 Video
  • Review 2 Articles: Excel VLOOKUP Function; HLOOKUP function
  • Complete: Lesson 4 Activity Assignment
  • Complete: Lesson 4 Exam

Lesson 5: The IF Function

This lesson will focus on how to use the IF Function. An IF statement returns "true" or "false" as a result, and you can display data based on this result. Within an IF statement, you can have other functions and formulas to create complex, embedded calculations that display data based on evaluation of several rows and columns in your spreadsheet. 11 Total Points
  • Lesson 5 Video
  • Review 4 Articles: Excel IF Function; Conditional Formulas in Excel and Tricks Using the IF Function in Excel; Excel logical formulas: 12 simple IF statements to get started; Excel Nested IF statement: examples, best practices and alternatives
  • Complete: Lesson 5 Activity Assignment
  • Complete: Lesson 5 Exam

Lesson 6: Manipulating Text

Excel has numerous numerical and logical functions, but text functions used to manipulate text are often used. These functions are usually used in combination with formatting functions to automatically and dynamically stylize the way information displays in a cell. 11 Total Points
  • Lesson 6 Video
  • Review 4 Articles: Excel FIND Function; Functions LEFT ? RIGHT ? MID; Change the case of text; Excel SUBSTITUTE Function
  • Complete: Lesson 6 Activity Assignment
  • Complete: Lesson 6 Exam

Lesson 7: Exploring Conditional Formatting

With conditional formatting, you can change colors and styles of a cell based on the results of a formula. Formulas return values, and Excel's conditional formatting features will take these results and change a cell's style based on a true or false value. 11 Total Points
  • Lesson 7 Video
  • Review Article: Analyze and format in Excel
  • Review Video: Microsoft Excel 2019: Conditional Formatting
  • Complete: Lesson 7 Activity Assignment
  • Complete: Lesson 7 Exam

Lesson 8: Working with Sparklines

One reason to use Excel over other spreadsheet software is for its numerous graphing and visual data representation tools. Sparklines are one graphing tool that allows you to display a visual representation of data within one cell. 11 Total Points
  • Lesson 8 Video
  • Review 3 Articles: Use formulas with conditional formatting; Use sparklines to show data trends; Excel Sparklines ? A Complete Guide with Examples
  • Complete: Lesson 8 Activity Assignment
  • Complete: Lesson 8 Exam

Lesson 9: Linking To and Consolidating Worksheets

Excel has functions that let you retrieve and consolidate data across multiple sheets and multiple workbooks. These features let you calculate data in different workbooks and worksheets without the need to copy and paste data to the same location. 11 Total Points
  • Lesson 9 Video
  • Review Article: Consolidate data in Excel and merge multiple sheets into one worksheet
  • Complete: Lesson 9 Activity Assignment
  • Complete: Lesson 9 Exam

Lesson 10: Importing and Cleaning Data

This lesson will focus on how to import and use many forms of data. 11 Total Points
  • Lesson 10 Video
  • Review 3 Articles: How to use Goal Seek in Excel for What-If analysis; Microsoft Excel: How to use Scenario Manager; Data Tools
  • Complete: Lesson 10 Activity Assignment
  • Complete: Lesson 10 Exam

Lesson 11: Pivot Tables

Pivot tables have a bad reputation of being complex and difficult to understand, but recent versions of Excel have made working with pivot tables much easier. 11 Total Points
  • Lesson 11 Video
  • Review 3 Articles: How To Create A New Pivot Table With The Excel 2019 Quick Analysis Tool; Create a PivotTable to analyze worksheet data; Summarizing Text Data With Pivot Tables
  • Complete: Lesson 11 Activity Assignment
  • Complete: Lesson 11 Exam

Lesson 12: Continuing to Work with Pivot Tables

This lesson will continue exploring various uses of Pivot Tables 10 Total Points
  • Lesson 12 Video
  • Review 4 Articles: Calculate values in a PivotTable; Calculated Field or Calculated Item; Use slicers to filter data; Create a timeline
  • Complete: Lesson 12 Exam

Lesson 13: Pivot Charts

A pivot chart displays data in various ways so that you can review and define the story that your information tells you about your business. A pivot chart complements this data by giving you a visual representation of the information in your pivot table. 11 Total Points
  • Lesson 13 Video
  • Review 3 Articles: Create a PivotChart; Add a data series to your chart; Change the layout or style of a chart
  • Complete: Lesson 13 Activity Assignment
  • Complete: Lesson 13 Exam

Lesson 14: The What-if Analyses

In business, you must make projections and changes that could increase revenue. Before you make any changes, you should analyze data. A What-If analysis lets you create scenarios where you can ask a question from your data and get results based on the data and the scenario that you input. 10 Total Points
  • Lesson 14 Video
  • Review 2 Articles: Find Quick Answers With Excel Goal Seek; Excel Scenarios Create and Show
  • Complete: Lesson 14 Exam

Lesson 15: Goal Seek and Solver

The Goal Seek tool in Excel helps you find answers to possible outcomes. You control these outcomes using variables and values until you find a number that matches your goals. 11 Total Points
  • Lesson 15 Video
  • Review 3 Articles: How to Use Excel?s Goal Seek and Solver to Solve for Unknown Variables; Load the Solver Add-in in Excel; Define and solve a problem by using Solver
  • Complete: Lesson 15 Activity Assignment
  • Complete: Lesson 15 Exam

Lesson 16: The Analysis ToolPak

The Solver tool offers ways to find solutions to your problems, but the Analysis Toolpak takes analysis a step further and lets you perform evaluations on complex statistical and engineering problems. 9 Total Points
  • Lesson 16 Video
  • Review 3 Articles: Use the Analysis ToolPak to perform complex data analysis; Load the Analysis ToolPak in Excel; The t-Test Tool
  • Complete: Lesson 16 Exam

Lesson 17: All About Data Validation

You can control the type of data entered into a cell by using Excel's data validation tools. These tools will block other formats and reduce the number of mistakes made when you rely on others to enter the right type of data into a record. 11 Total Points
  • Lesson 17 Video
  • Review 3 Articles: Apply data validation to cells; Display or hide circles around invalid data; How To Remove Duplicates In Excel 2019: Eliminating Records With Duplicate Fields
  • Complete: Lesson 17 Activity Assignment
  • Complete: Lesson 17 Exam

Lesson 18: Using Get & Transform to Perform Queries

The "Get & Transform" feature imports data from a third-party source and pours the data into a worksheet. This could be a dozen records or thousands of records. 10 Total Points
  • Lesson 18 Video
  • Review 2 Articles: Overview of Excel tables; Create and format tables
  • Complete: Lesson 18 Exam

Lesson 19: Troubleshooting and Auditing in Excel 2019

Excel has some internal features that help you find dependent cells and troubleshoot issues. 9 Total Points
  • Lesson 19 Video
  • Review 3 Articles: Troubleshooting Excel: The Five Minute Flowchart; Detecting And Correcting Errors In Excel 2019 Formulas; How To Delete Tracer Arrows In Excel?
  • Complete: Lesson 19 Exam

Lesson 20: Exploring Macros in Excel 2019

Macros are useful for repetitive steps you perform on every spreadsheet. You can record macros in the main Excel interface or create highly complex automated macros in Microsoft's proprietary Visual Studio for Applications (VBA). 91 Total Points
  • Lesson 20 Video
  • Take Poll: End of Course Poll
  • Take Survey: Course Comments
  • Complete: Lesson 20 Activity Assignment
  • Complete: Lesson 20 Exam
  • Complete: The Final Exam
292
Total Course Points
 

Learning Outcomes

By successfully completing this course, students will be able to:
  • Demonstrate working with outlines, custom views, and templates in Excel 2019.
  • Demonstrate using mathematical functions.
  • Demonstrate using data functions, data lookups, and the IF function.
  • Demonstrate using conditional formatting.
  • Demonstrate working Sparklines.
  • Demonstrate linking to and consolidating worksheets.
  • Describe importing and cleaning data.
  • Demonstrate creating and using Pivot Tables and Pivot Charts.
  • Demonstrate creating and using the What-if Analyses.
  • Demonstrate creating and using the Goal Seek and Solver.
  • Demonstrate creating and using the the Analysis ToolPak.
  • Demonstrate creating and using the using get & transform to perform queries, 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 2019
Course Number: 9770523
Languages: English - United States, Canada and other English speaking countries
Category:
Course Type: Professional Development (Self-Paced, Online Class)
CEU Value: 1.6 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 Instructors
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
$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
$69 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+