Microsoft Excel Level 3 with Drills

Course Prerequisite(s)

Please note that this course has the following prerequisites which must be completed before it can be accessed
Microsoft Excel 2021 Level 3

About Course

Advanced Microsoft Excel: Level 3 with Drills is a comprehensive 15-hour course designed to equip participants with advanced skills in Excel, focusing on complex data analysis, automation, and visualization techniques. This course is ideal for individuals who have a solid understanding of Excel basics and intermediate features and want to expand their proficiency to an advanced level. Through a combination of instructor-led lectures, hands-on drills, and practical exercises, participants will learn advanced functions and formulas, data modeling, automation using macros and VBA (Visual Basic for Applications), and advanced data visualization techniques.

Master advanced functions and formulas for complex data analysis and manipulation.
Learn advanced data modeling techniques, including pivot tables, Power Query, and Power Pivot.
Develop proficiency in automation and workflow optimization using macros and VBA.
Explore advanced data visualization techniques for creating interactive dashboards and reports.
Practice hands-on drills to reinforce learning and enhance problem-solving skills.
Gain practical experience through real-world projects and scenarios.

Course Content

Advanced Functions and Formulas
Review of basic and intermediate Excel functions Advanced lookup and reference functions: INDEX-MATCH, VLOOKUP, HLOOKUP Statistical functions: SUMIFS, COUNTIFS, AVERAGEIFS Text functions: CONCATENATE, LEFT, RIGHT, MID

Data Modeling with Pivot Tables
Creating pivot tables for data analysis and reporting Advanced pivot table techniques: grouping, calculated fields, and items Connecting multiple data sources with external data connections Using slicers and timelines for interactive filtering

Power Query and Data Transformation
Introduction to Power Query for data import and transformation Cleaning and shaping data with Power Query: filtering, merging, and appending Using Power Query to combine multiple data sources and create data models

Power Pivot and Data Analysis Expressions (DAX)
Introduction to Power Pivot for advanced data modeling Creating relationships between tables and managing data models Writing DAX formulas for calculated columns and measures Analyzing data with advanced DAX functions: CALCULATE, FILTER, RELATED

Automation with Macros
Recording and editing macros to automate repetitive tasks Introduction to VBA (Visual Basic for Applications) for Excel Writing VBA code to perform complex operations and tasks Creating custom functions and user-defined functions (UDFs) in VBA

Advanced Data Visualization Techniques
Creating dynamic charts and graphs for data analysis Building interactive dashboards using form controls and slicers Using sparklines and conditional formatting for visual insights Advanced charting techniques: waterfall charts, Pareto charts, and heatmaps

Drill Sessions and Problem-Solving Exercises
Hands-on drills and exercises to reinforce advanced Excel skills Solving real-world data analysis and modeling challenges Practicing data visualization techniques through guided exercises Reviewing solutions and discussing best practices

Real-World Projects and Case Studies
Applying advanced Excel skills to real-world projects and scenarios Creating comprehensive data analysis reports and dashboards Presenting projects and receiving feedback from instructors and peers

Course Prerequisite(s)

About Course

What Will You Learn?

Course Content

Advanced Functions and Formulas Review of basic and intermediate Excel functions Advanced lookup and reference functions: INDEX-MATCH, VLOOKUP, HLOOKUP Statistical functions: SUMIFS, COUNTIFS, AVERAGEIFS Text functions: CONCATENATE, LEFT, RIGHT, MID

Data Modeling with Pivot Tables Creating pivot tables for data analysis and reporting Advanced pivot table techniques: grouping, calculated fields, and items Connecting multiple data sources with external data connections Using slicers and timelines for interactive filtering

Power Query and Data Transformation Introduction to Power Query for data import and transformation Cleaning and shaping data with Power Query: filtering, merging, and appending Using Power Query to combine multiple data sources and create data models

Power Pivot and Data Analysis Expressions (DAX) Introduction to Power Pivot for advanced data modeling Creating relationships between tables and managing data models Writing DAX formulas for calculated columns and measures Analyzing data with advanced DAX functions: CALCULATE, FILTER, RELATED

Automation with Macros Recording and editing macros to automate repetitive tasks Introduction to VBA (Visual Basic for Applications) for Excel Writing VBA code to perform complex operations and tasks Creating custom functions and user-defined functions (UDFs) in VBA

Advanced Data Visualization Techniques Creating dynamic charts and graphs for data analysis Building interactive dashboards using form controls and slicers Using sparklines and conditional formatting for visual insights Advanced charting techniques: waterfall charts, Pareto charts, and heatmaps

Drill Sessions and Problem-Solving Exercises Hands-on drills and exercises to reinforce advanced Excel skills Solving real-world data analysis and modeling challenges Practicing data visualization techniques through guided exercises Reviewing solutions and discussing best practices

Real-World Projects and Case Studies Applying advanced Excel skills to real-world projects and scenarios Creating comprehensive data analysis reports and dashboards Presenting projects and receiving feedback from instructors and peers