Microsoft Excel Intermediate to Advanced Course Overview

This course develops participants’ ability to use Microsoft Excel efficiently for data management, analysis, reporting, and automation. It progresses from intermediate features—such as advanced formulas, structured tables, and data validation—to advanced tools including PivotTables, Power Query, dashboards, and basic macro automation.

Training combines instructor demonstrations, guided exercises, practical business scenarios, and a final integrated project.

 

Duration 3 Days – 21 hrs.

Objectives

• Organize, clean, validate, and manage large datasets.
• Apply intermediate and advanced formulas to solve business problems.
• Use lookup, logical, text, date, and dynamic-array functions.
• Summarize and analyze data using PivotTables and PivotCharts.
• Import, clean, combine, and transform data with Power Query.
• Create clear charts, reports, and interactive dashboards.
• Apply conditional formatting and data validation effectively.
• Use What-If Analysis tools for forecasting and decision-making.
• Record and run basic macros to automate repetitive tasks.
• Protect, audit, and optimize Excel workbooks.
• Complete an integrated data-analysis and reporting project.

 

Target Audience 

• Administrative and executive support staff
• Finance and accounting personnel
• Sales and marketing professionals
• Human resources personnel
• Operations and supply-chain staff
• Project coordinators and managers
• Data and reporting analysts
• Business owners and supervisors
• Employees who regularly prepare Excel-based reports

 

Prerequisites 

• Navigate Excel worksheets and workbooks.
• Enter, edit, copy, and format data.
• Create basic formulas using arithmetic operators.
• Use basic functions such as SUM, AVERAGE, MIN, MAX, and COUNT.
• Apply basic sorting and filtering.
• Save, open, print, and manage Excel files.Participants should bring a laptop with a current desktop version of Microsoft Excel. Microsoft 365 or Excel 2021 and later is recommended because some functions may not be available in older versions.

 

Course Outline 

Day 1: Intermediate Excel and Advanced Formulas

Module 1: Efficient Workbook and Data Management

• Cell references and named ranges
• Excel Tables and structured references
• Advanced sorting and filtering
• Data cleaning and duplicate removal
• Custom number formats

 Module 2: Data Validation and Conditional Formatting

• Dropdown lists and validation rules
• Formula-based conditional formatting
• Highlighting trends, errors, and exceptions

 Module 3: Logical and Error-Handling Functions

• IF, IFS, SWITCH, AND, and OR
• Nested formulas
• IFERROR and IFNA
• Formula troubleshooting

 Module 4: Lookup and Reference Functions

• XLOOKUP
• VLOOKUP and HLOOKUP
• INDEX and MATCH
• Multiple-criteria and two-way lookups

Day 1 output: Cleaned and validated workbook using intermediate and advanced formulas.

 

Day 2: Data Analysis and Transformation

Module 5: Text, Date, and Time Functions

• Text extraction and cleanup functions
• Date calculations
• Working-day and deadline calculations

 Module 6: Conditional Calculations and Dynamic Arrays

• SUMIFS, COUNTIFS, and AVERAGEIFS
• FILTER, SORT, SORTBY, and UNIQUE
• Dynamic reports

 Module 7: PivotTables and PivotCharts

• Creating and formatting PivotTables
• Grouping and custom calculations
• Slicers and timelines
• PivotCharts 

Module 8: Power Query

• Importing data
• Cleaning and transforming data
• Appending and merging queries
• Consolidating files
• Refreshing query results

Day 2 output: An analytical report containing transformed data, advanced formulas, a PivotTable, and a PivotChart.

 

Day 3: Dashboards, Forecasting, and Automation

Module 9: Advanced Charts and Dashboards

• Selecting appropriate charts
• Combination and dynamic charts
• Key performance indicators
• Interactive dashboard development
• Dashboard-design principles

 Module 10: Workbook Auditing, Protection, and Optimization

• Formula auditing
• Error checking
• Worksheet and workbook protection
• External-link management
• Workbook-performance optimization

 Module 11: Introuction to Macros

• Recording and running macros
• Relative and absolute macro recording
• Assigning macros to buttons
• Macro-enabled workbooks
• Basic macro security

 Final Integrated Project

• Import and clean a dataset
• Apply advanced formulas
• Create a PivotTable analysis
• Build an interactive dashboard
• Automate a repetitive task

Day 3 output: Completed interactive dashboard and automated final workbook.