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.

COURSE CLOSED

We're sorry, this course is no longer open for enrollment.
 
  • 22
    Lessons
  • 23
    Exams &
    Assignments
  • 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.

  • 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

Lesson 1. Advanced Techniques in Excel 2016

Students are advised to ensure they have foundational Excel skills before tackling the advanced concepts in this course, potentially refreshing their knowledge with the basic Excel 2016 course if necessary. The course requires significant engagement with Excel’s features, emphasizing the application of learning through exercises and practical assignments. 1 Total Points
  • Lesson 1 Video
  • Review Practice Worksheet: CHAR_functions.xlsx
  • Lesson discussions: Reasons for Taking this Course
  • Complete Assignment: Why this Course?

Lesson 2. Excel Essentials: Navigating Outlines and Templates

Excel 2016 introduces outlining to structure large datasets with options to automate or manually create outlines, as well as facilitate data management with nested subtotals. Users can harness the power of custom views to preserve display settings and tap into a rich collection of templates for consistent workbook creation. 10 Total Points
  • Lesson 2 Video
  • Assessment: Exam 2

Lesson 3. Exploring Basic Mathematical Operations in Excel 2016

Lesson Summary 3: Excel 2016 empowers users to handle both basic and advanced arithmetic through a vast array of mathematical functions including ROUND, INT, MOD, and PMT. These tools are invaluable for financial calculations, helping estimate values such as loan payments, with key functions assisting in pinpointing different financial metrics, such as interest rates and loan durations. 9 Total Points
  • Lesson 3 Video
  • Assessment: Exam 3

Lesson 4. Harnessing Advanced Excel Functions for Efficient Data Handling

In Excel 2016, data functions such as MATCH, INDEX, and CHOOSE provide tools for locating, retrieving, and handling data within worksheets. These functions, with capabilities like wildcard searches and error management, improve data efficiency and allow dynamic data referencing for comprehensive analysis. 10 Total Points
  • Lesson 4 Video
  • Assessment: Exam 4

Lesson 5. Harness the Power of Excel with Advanced Lookup Techniques

Excel’s VLookup and HLookup functions aid in data retrieval, aligning exact or nearest matches based on specified criteria in tabular data. Nesting lookups within other lookups enables comprehensive data extraction from multiple tables with ease. 10 Total Points
  • Lesson 5 Video
  • Assessment: Exam 5

Lesson 6. Unveiling the Potential of Excel's Logical Functions

The IF function in Excel allows you to display different outputs based on specific conditions, making it a crucial tool for data management. It can help add comments, hide errors, and perform more complex nestings with logical operators like AND, OR, and NOT. 10 Total Points
  • Lesson 6 Video
  • Assessment: Exam 6

Lesson 7. Excel Text Tools: From TRIM to TEXT

Mastering the CHAR and TEXT functions allows Excel users to refine their data by introducing non-standard characters or formatting mixed-content cells. Whether it's inserting carriage returns for better readability or converting numerical representations of dates into familiar formats, these functions enhance data clarity and utility. 10 Total Points
  • Lesson 7 Video
  • Assessment: Exam 7

Lesson 8. Mastering Conditional Formatting in Excel

Excel’s conditional formatting features enable users to automatically apply formats to cells that meet defined conditions, simplifying the process of identifying key information within a dataset. It provides various presets like Color Scales and Icon Sets, alongside options for creating custom rules, hence personalizing data visualization effectively. 10 Total Points
  • Lesson 8 Video
  • Assessment: Exam 8

Lesson 9. The Art of Creating and Customizing Sparklines in Excel

In Excel, Sparklines offer a unique method of visual data presentation through minimalistic charts embedded within individual cells. These versatile tools support dynamic range tracking and group formatting, providing clarity and consistency across various datasets. 10 Total Points
  • Lesson 9 Video
  • Assessment: Exam 9

Lesson 10. Harnessing External Reference Formulas

Linking worksheets allows one to integrate data from an external worksheet while consolidating involves merging data from multiple worksheets, which can exist in different workbooks. Learning these techniques enhances efficiency in managing dynamic data that updates automatically or merges data comprehensively through summary formulas. 10 Total Points
  • Lesson 10 Video
  • Assessment: Exam 10

Lesson 11. Efficient Data Handling in Excel

Excel’s import functionality enables seamless integration of external data, covering various file formats, thus facilitating comprehensive data handling without direct entry. Post-import, Excel offers cleaning functions ensuring that data anomalies like duplicates and unusual characters are rectified for optimal dataset preparation. 10 Total Points
  • Lesson 11 Video
  • Assessment: Exam 11

Lesson 12. Mastering the Art of Pivot Tables: An In-Depth Guide

Pivot tables are Excel tools that efficiently summarize and analyze large datasets by counting, averaging, and sorting, with a focus in this lesson on proper data structuring. Structuring requires organizing data into a table with headers starting from cell A1, avoiding blanks, and emphasizing rows for data input, while columns define data types, which enhances pivot table functionality. 10 Total Points
  • Lesson 12 Video
  • Assessment: Exam 12

Lesson 13. Unleashing the Potential of PivotTables in Excel

This lesson delves into the intricacies of PivotTables, demonstrating their flexibility to display critical data aspects like grand totals and subtotals, and how to customize their appearance through different styles. Mastering these tables includes learning about adding calculated fields and using external data sources like SQL servers. 10 Total Points
  • Lesson 13 Video
  • Assessment: Exam 13

Lesson 14. Enhanced Data Insights with PivotCharts

It’s easy to move a PivotChart to a different sheet, similar to how you manage PivotTables, providing better organization within your workbook. Removing unnecessary chart buttons can also streamline the visual presentation for printouts. 10 Total Points
  • Lesson 14 Video
  • Assessment: Exam 14

Lesson 15. Exploring Excel's What-If Capabilities: Transforming Data Analysis Beyond Calculations

Unveil Excel's analytical prowess through its what-if analysis features which enable hypothetical data exploration, utilizing tools like data tables and scenario frameworks. This lesson focuses on data manipulation techniques to forecast sales projections and compare growth scenarios without altering main data sets. 10 Total Points
  • Lesson 15 Video
  • Assessment: Exam 15

Lesson 16. Dynamic Solutions with Excel's Solver Tool

Excel's Goal Seek adjusts a single variable to meet desired outcomes, while Solver handles more complex scenarios with multiple changing variables. This lesson demonstrates both tools, detailing steps for activation, execution, and troubleshooting ineffective solutions. 10 Total Points
  • Lesson 16 Video
  • Assessment: Exam 16

Lesson 17. Advanced Data Analysis Techniques with Excel's Analysis ToolPak

Excel's Analysis ToolPak is designed for non-business users such as engineers or educators, providing tools like correlation and histogram creation that are not included by default. Users can enable it by selecting it through the Add-ins interface, thus expanding their analytical capacity significantly. 10 Total Points
  • Lesson 17 Video
  • Assessment: Exam 17

Lesson 18. Harnessing the Power of Data Validation in Excel

Data validation in Excel safeguards data entry by setting rules to restrict invalid inputs and allows users to receive instructions through messages, making it crucial for consistency and accuracy. Custom messages and dropdown lists further enhance user experience by guiding correct data entry and highlighting invalid data for correction. 10 Total Points
  • Lesson 18 Video
  • Assessment: Exam 18

Lesson 19. The Power of Get & Transform: A Guide to Data Import and Manipulation

Excel's Get & Transform tool is introduced as a robust feature for importing and tailoring data from diverse sources. It emphasizes the lasting benefits of automatically saving transformation steps for future use, simplifying repetitive data processing tasks. 10 Total Points
  • Lesson 19 Video
  • Assessment: Exam 19

Lesson 20. Mastering Excel 2016: Comprehensive Guide to Troubleshooting and Auditing

Tracer arrows in Excel 2016 help visualize the flow of data, showing users which cells are dependent or precedent to a selected cell, thus aiding in efficient formula troubleshooting. This feature speeds up the error identification process by clearly displaying how data interconnections could be causing issues. 10 Total Points
  • Lesson 20 Video
  • Assessment: Exam 20

Lesson 21. Macro Magic: Automate Tasks in Excel 2016

The lesson guides you through creating and managing Excel macros, emphasizing the process of recording, saving as macro-enabled files, and adjusting security settings for seamless use. Macros can be stored for individual workbooks or universally on a machine via the Personal Macro Workbook, which needs backing up for security. 10 Total Points
  • Lesson 21 Video
  • Assessment: Exam 21

Lesson 22. Empowering Excel: Efficient Formats with Macros

Lesson Summary 2: Excel macros extend beyond mere automation to include seamless formatting and scenario switching within worksheets. Learn to use keyboard shortcuts and worksheet buttons for easy access and efficient workflow management. 115 Total Points
  • Lesson 22 Video
  • Lesson discussions: End of Course Poll; Reasons for Taking this Course; Course Comments
  • Assessment: Exam 22
  • Assessment: The Final Exam
315
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.
 

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.