top of page
Blue Background

Microsoft Excel 2019 - Advanced


Microsoft Excel 2019 - Advanced 



Duration: 2 Days



Course Overview:

This advanced Microsoft Excel course is designed for individuals who already possess a solid understanding of Excel fundamentals and wish to deepen their proficiency in using the software for data analysis, automation, and advanced functions. Through hands-on exercises and practical applications, participants will learn advanced techniques, functions, and features to effectively manage, analyze, and visualize data in Excel 2019.



Course Objectives:

  1. Enhance proficiency in data analysis using advanced Excel functions and tools.

  2. Automate tasks and streamline workflows through macros and advanced automation techniques.

  3. Master data visualization techniques for presenting insights effectively.

  4. Develop skills in managing large datasets efficiently.

  5. Explore advanced data manipulation and analysis methods.



Course Outline:


Advanced Formulas and Functions

  • Review of basic functions

  • Nested functions and logical functions

  • Lookup and reference functions (VLOOKUP, HLOOKUP, INDEX, MATCH)

  • Array formulas and advanced calculation techniques



Data Validation and Protection

  • Advanced data validation techniques

  • Protecting worksheets and workbooks

  • Managing permissions and sharing workbooks

  • Workbook security best practices



PivotTables and Pivot Charts

  • Advanced PivotTable features and options

  • Calculated fields and items

  • Slicers and Timelines

  • Customizing Pivot Charts for data visualization




Session 4: Data Analysis Tools

  • What-If Analysis tools (Scenario Manager, Goal Seek, Data Tables)

  • Advanced charting techniques

  • Sparklines for visualizing trends within data

  • Trendlines and forecasting


Advanced Data Manipulation

  • Power Query basics and advanced features

  • Merging and appending queries

  • Cleaning and transforming data with Power Query

  • Introduction to Power Pivot and Power View



Macros and Automation

  • Recording and running macros

  • Editing and debugging macros

  • Introduction to VBA (Visual Basic for Applications)

  • Automating repetitive tasks using VBA



Conditional Formatting and Data Visualization

  • Advanced conditional formatting techniques

  • Icon Sets and Data Bars

  • Creating interactive dashboards

  • Data visualization best practices



Collaboration and Sharing

  • Collaborating in real-time using Excel Online

  • Co-authoring workbooks

  • Reviewing and tracking changes

  • Sharing workbooks via OneDrive and SharePoint



Advanced Data Analysis

  • Descriptive statistics and data summarization

  • Regression analysis

  • Goal Seek and Solver for optimization problems

  • Data analysis add-ins and tools



Managing Large Datasets

  • Data model overview

  • Introduction to Power Pivot

  • Managing relationships in Power Pivot

  • Creating calculated columns and measures



Advanced Charting Techniques

  • Customizing charts with advanced options

  • Combination charts and dual-axis charts

  • Interactive charting with form controls

  • Dynamic charts using named ranges



Final Project and Review

  • Application of learned concepts in a real-world scenario

  • Review of course material and Q&A session

  • Final assessment and certification




  • Basic proficiency in Microsoft Excel (comfortable with basic functions, formatting, and data entry)

  • Understanding of fundamental data analysis concepts



Target Audience:

  • Business professionals

  • Data analysts

  • Financial analysts

  • Anyone seeking to enhance their Excel skills for advanced data analysis and reporting.



This advanced Microsoft Excel course offers a structured approach to learning essential skills for data analysis and visualization using Microsoft Excel. By the end of this course, participants will have the knowledge and confidence to leverage Microsoft Excel effectively in their professional roles, enabling them to manage, analyze, and visualize data in Excel 2019.

bottom of page