Online Class: Advanced Excel 2019

This advanced Excel 2019 course will show you how to do everything in Excel to make you an Excel Power User.
COURSE CLOSED
We're sorry, this course is no longer open for enrollment.
 
  • 20
    Lessons
  • 22
    Exams &
    Assignments
  • 16
    Hours
    average time
  • 1.6
    CEUs
 
 
 

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.

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

Lesson 1. Custom Views, Outlines, and Templates in Excel

To streamline spreadsheet management, Excel offers templates and outlines to simplify data presentation, making it easier to share and emphasize important sections. Custom views can be created to easily hide unnecessary data ranges, improving focus while working. 11 Total Points
  • Lesson 1 Video
  • Lesson discussions: Reasons for Taking this Course
  • Complete Assignment: Motives for Taking this Course
  • Assessment: Lesson 1 Exam

Lesson 2. Math Functions: Simplifying Excel Calculations

Excel's functions such as ROUND and SUMIF elevate data processing by providing specialized calculations that cater to specific scenarios like rounding to desired precision and conditional summing. Mastering these functions transforms you into a proficient data analyst, making Excel an invaluable tool. 10 Total Points
  • Lesson 2 Video
  • Assessment: Lesson 2 Exam

Lesson 3. Error Types Unveiled: Excel's Toolset for Troubleshooting

Use Excel's ISNUMBER to differentiate numbers from text, allowing for dynamic calculations based on cell content. For distinguishing text, ISTEXT and ISNONTEXT offer simple solutions for checking if cells contain text or non-textual data. 10 Total Points
  • Lesson 3 Video
  • Assessment: Lesson 3 Exam

Lesson 4. Excel Data Lookups Made Simple

VLOOKUP is ideal when you need to find specific data in a vertical column by referencing adjacent cells. It's particularly useful for dynamic results that auto-update when datasets change. 10 Total Points
  • Lesson 4 Video
  • Assessment: Lesson 4 Exam

Lesson 5. IF Function Unpacked: Building Powerful Excel Formulas with Conditions

Lesson Summary 1: The IF function in Excel evaluates conditions and displays results based on whether they are true or false, allowing for complex calculations with nested statements and logical operators. Common issues include logical errors and adjusting conditions for greater accuracy, such as using 'greater than or equal to' rather than 'greater than' alone to prevent bugs. 10 Total Points
  • Lesson 5 Video
  • Assessment: Lesson 5 Exam

Lesson 6. Exploring Excel's Text Functions: A Comprehensive Guide

Excel text functions are integral for efficient data formatting, including FIND and SUBSTITUTE for text searches and replacements and LEFT, RIGHT, and MID for substring extractions. UPPER, LOWER, and PROPER functions standardize text case, enhancing readability and consistency across spreadsheets. 10 Total Points
  • Lesson 6 Video
  • Assessment: Lesson 6 Exam

Lesson 7. Color-Coding with Logic in Excel

By leveraging conditional formatting, Excel users can personalize cell styling based on predefined data conditions, such as numeric comparisons or text occurrences. This enables efficient data analysis and visually appealing spreadsheets without manual formatting. 10 Total Points
  • Lesson 7 Video
  • Assessment: Lesson 7 Exam

Lesson 8. From Rows to Visuals: A Journey with Sparklines

Sparklines in Excel enhance data visualization by embedding small, dynamic graphs within individual cells to show trends quickly. Whether using line, column, or win-loss types, they adapt effortlessly to data updates, offering an efficient means of presentation. 10 Total Points
  • Lesson 8 Video
  • Assessment: Lesson 8 Exam

Lesson 9. Linking to External Excel Workbooks

Excel's 'Get and Transform' feature supports retrieving data from various sources, enabling users to easily integrate and analyze data across distinct worksheets and workbooks. 10 Total Points
  • Lesson 9 Video
  • Assessment: Lesson 9 Exam

Lesson 10. Beyond the Workbook: Exploring Importing Techniques for Various Data Sources

Importing data into Excel from various sources, including SQL databases and web pages, is streamlined through the 'Get & Transform' feature, which supports diverse data formats. While SQL databases offer easily integrated structured data, web page data likely requires meticulous cleaning and formatting to fit analytical needs. 10 Total Points
  • Lesson 10 Video
  • Assessment: Lesson 10 Exam

Lesson 11. Exploring the Dynamic World of Pivot Tables in Excel

Excel's pivot tables represent a shift in data analysis, providing a user-friendly method for restructuring and summarizing data from multiple sources. By accommodating various aggregation and grouping settings, pivot tables empower users to gain insights efficiently without manual data manipulation. 10 Total Points
  • Lesson 11 Video
  • Assessment: Lesson 11 Exam

Lesson 12. Innovative Uses of Pivot Tables for Data Analysis

Transform large data sets into comprehensive reports by leveraging pivot table capabilities, including advanced calculated fields and Excel functions. Simplify your filtering tasks with slicers and timelines, while keeping your data up-to-date through easy refresh options. 10 Total Points
  • Lesson 12 Video
  • Assessment: Lesson 12 Exam

Lesson 13. Creating a Pivot Chart

Excel's pivot charts transform data into a visually engaging format, making complex datasets more accessible to third-party viewers. Through customizable elements and filters, these charts enhance data clarity and decision-making processes. 10 Total Points
  • Lesson 13 Video
  • Assessment: Lesson 13 Exam

Lesson 14. Goal Seek and Scenarios: Unveiling Future Revenue Insights

By leveraging Excel's What-If analysis tools, businesses can foresee the effects of proposed variable adjustments, allowing them to predict outcomes and refine strategies proactively. Tools like Goal Seek offer targeted analyses, while Scenario Manager provides comprehensive exploration of multiple potential adjustments, aiding effective decision-making. 10 Total Points
  • Lesson 14 Video
  • Assessment: Lesson 14 Exam

Lesson 15. Advanced Problem Solving: From Goal Seek to Solver

Excel's Goal Seek enables basic What-If analysis by adjusting one variable at a time, but complex scenarios benefit from Solver for managing multiple unknowns and constraints. Available via add-in, Solver offers advanced algorithms for optimization, equipped to handle large data sets and multiple decision parameters. 10 Total Points
  • Lesson 15 Video
  • Assessment: Lesson 15 Exam

Lesson 16. Excel Analysis Toolpak: Intro

The Analysis ToolPak enhances Excel’s capabilities by allowing complex statistical evaluations, offering tools for users to perform tests like t-tests and ANOVA without needing to write intricate formulas. It serves non-statisticians and statisticians alike, simplifying data processing and making advanced statistical features accessible within the Excel interface. 9 Total Points
  • Lesson 16 Video
  • Assessment: Lesson 16 Exam

Lesson 17. Data Validation 101

Data validation in Excel helps manage the type of data that third parties enter into spreadsheets, avoiding potential formatting issues. This ensures data integrity by restricting entries to specific types such as numbers or dates and providing feedback or warnings as needed. 10 Total Points
  • Lesson 17 Video
  • Assessment: Lesson 17 Exam

Lesson 18. Unleashing Data Potential: Mastering Get & Transform Queries

By utilizing 'Get & Transform,' users can import, query, and segment large datasets from databases with Excel, offering a robust framework to perform advanced data manipulation. Power Query streamlines data editing, allowing for the creation of targeted reports and insights from meticulous data extraction. 10 Total Points
  • Lesson 18 Video
  • Assessment: Lesson 18 Exam

Lesson 19. Exploring Excel's Formula Tools for Error-Free Sheets

Excel 2019 offers comprehensive tools for formula auditing, essential for ensuring data integrity and avoiding unintended changes. Key features like the 'Show Formulas' function allow users to identify and address formula-related issues efficiently. 9 Total Points
  • Lesson 19 Video
  • Assessment: Lesson 19 Exam

Lesson 20. Automating Excel with Macros and VBA: A Comprehensive Guide

Excel 2019 offers the ability to record and run macros for automating repetitive tasks, saving time during spreadsheet formatting or data management. More advanced users can delve into Microsoft's Visual Basic for Applications to create complex macros, enabling enhanced automation through custom programming. 85 Total Points
  • Lesson 20 Video
  • Lesson discussions: End of Course Poll; Course Comments; Course Comments
  • Assessment: Lesson 20 Exam
  • Assessment: The Final Exam
274
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.
  • Demonstrate mastery of lesson content at levels of 70% or higher.
 

Student Testimonials

  • "i found this course to be very thorough in explanation. The detail and explanation was very easy to follow and look back at." -- Patricia A.
  • "Some parts were easier than others but I was able to learn many new things in Excel that I was not aware of previously. I look forward to using these in my job." -- Patti M.
  • "This course was well put together and helped me grasp all concepts offered." -- David G.
  • "Thank you for going the extra mile. Loved the course!" -- Sean B.
  • "I think the course is very helpful. I learned a lot of things I did not know." -- Myrtal W.
  • "Very helpful! Thank you." -- Kathleen P.
  • "Excellent Course." -- Evelyn E.
  • "This course if very helpful for an analyst." -- Brittany H.
  • "Great! I learned a lot." -- Deborah H.

Related Courses