Microsoft Excel Intermediate to Advanced

Inquire now

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.

Inquire now

Best selling courses

Duration: 5 days – 35 hrs   Overview The “SOC Network and Threat Detection and Analysis” training course is designed to equip Security Operations Center (SOC) analysts and IT security professionals with the skills and knowledge required to detect, analyze, and respond to network threats effectively. This comprehensive course covers essential topics such as threat...

Duration 1 day – 7 hrs   Overview   This 1-day training builds upon basic warehouse operations knowledge and introduces key logistics concepts involved in the movement and coordination of goods—especially wet and dry food items—within and outside the warehouse. Participants will explore transport logistics, inbound and outbound coordination, documentation practices, and cold chain considerations,...

Duration 2 days – 14 hrs   Overview   This hands-on course provides an introduction to Splunk, a powerful platform for searching, monitoring, and analyzing machine-generated data. The training focuses on how developers and QA professionals can leverage Splunk to gain insights from logs and metrics, improve application observability, detect anomalies, and support test validation....

Duration 3 days – 21 hrs   Overview.   This course is designed for fresh graduates aspiring to build a career in Data Science. It introduces the fundamentals of data science, focusing on data analysis, visualization, and basic machine learning concepts using Python. The course provides hands-on practice with real-world datasets, equipping participants with the...

Duration 5 days – 35 hrs   Overview   The Microsoft Certified: Azure Administrator Associate (AZ-104) Training Course is designed for IT professionals who want to develop expertise in managing Microsoft Azure environments. This course provides hands-on training on deploying, managing, and monitoring Azure services, including virtual machines, networking, storage, identity, and security.   Objectives...

ARTIFICIAL INTELLIGENCE / MACHINE LEARNING / DEEP LEARNING

Capstone Workshop: Build Your Own GenAI Toolkit TECH AI

GenAI Toolkit Training is a practical, hands-on capstone workshop designed to help participants bring their generative AI knowledge together into a personalized toolkit of AI tools, prompts, workflows, and practical solutions. Rather than simply learning individual AI platforms, participants will learn how to select and combine the right tools to address real-world technical and professional...

In this course, you will gain practical foundation level training that enables immediate and effective participation in big data and other analytics projects. Learn ways of storing data that allow for efficient processing and analysis, and gain the skills you need to store, manage, process, and analyze massive amounts of unstructured data to create an appropriate data lake.

Advanced Laravel Engineering Scalable Architecture, Performance, Testing, Queues & DevOps equips developers with the skills to design scalable Laravel applications, optimize performance, implement automated testing, manage queues, and integrate DevOps practices for reliable, production-ready deployments.   Duration 5 Days – 35 hrs   Overview This advanced-level bootcamp is designed for teams building large-scale or mission-critical...

// BG EARTH WHEN NOT PLAYING

We use cookies on our website to personalize your experience by storing your preferences and recognizing repeat visits. By clicking “Accept”, you agree to the use of all cookies. You can also select “Cookie Settings” to adjust your preferences and provide more specific consent. Cookie Policy