Microsoft Tools · MIC-74

Microsoft Excel Advanced

Microsoft Excel Advanced is a course in applying advanced formulas to real work, from summarising with SUMIFS and looking up data with XLOOKUP to newer Microsoft 365 functions such as FILTER, GROUPBY and LAMBDA, followed by Power Query, PivotTables and file protection. It suits regular Excel users in HR, inventory, sales and accounting, and entry-level data analysts.

Updated 21 Aug 2026

From 6,750 THB / person 7,500 −10% excl. VAT 7% · group rates available

Corporate training quote Join the waiting list

PDFDownload the course outline

Duration12 hours · 2 days
FormatOnsite / live online
Next roundOn request
CertificateIncluded

Course overview

Microsoft Excel Advanced is designed for people who already use Excel regularly but want to move up to more complex formulas and apply Excel's tools to real organizational work - HR, inventory, sales and marketing, or accounting and finance. Learners come to understand when to choose each group of functions and how to combine them for correct, maintainable results. The content spans using Names to make formulas read professionally, conditional functions such as IFS and SWITCH, conditional aggregation with SUMIFS, COUNTIFS and AVERAGEIFS, lookups with XLOOKUP, OFFSET and INDIRECT, and on to the new Microsoft 365

formulas that completely change how you work with data - FILTER, SORT, UNIQUE, VSTACK, GROUPBY, PIVOTBY, TEXTSPLIT - and building your own functions with LAMBDA. For data work, learners practice using Tables as a flexible data foundation, preparing and merging data from multiple files automatically with Power Query, summarizing and analyzing with PivotTable and PivotChart, customizing charts for professional presentation, and finishing with file security - hiding formulas, protecting sheets and setting file passwords. (Hands-on training, 2 days, 6 hours per day, 12 hours in total, hybrid onsite or online via Microsoft Teams; Excel for Microsoft 365 recommended.)

What you’ll gain

Build calculation tables with applied Excel functions, combine multiple functions, and use Names to make formulas readable, maintainable and less error-prone
Choose conditional functions (IFS, SWITCH) and conditional aggregation functions (SUMIFS, COUNTIFS, AVERAGEIFS) appropriately
Look up and reference data with XLOOKUP, OFFSET, INDIRECT, understanding the differences from VLOOKUP and HLOOKUP
Apply the new Microsoft 365 formulas (FILTER, SORT, UNIQUE, VSTACK, TAKE, GROUPBY, PIVOTBY, TEXTSPLIT) to work with data faster, and build your own functions with LAMBDA
Handle formula errors systematically with ISNA and IFERROR, and use Conditional Formatting and Data Validation to reduce data errors at the source
Prepare and transform data with Power Query, including merging multiple Excel files automatically
Analyze and summarize data with PivotTable and PivotChart, and customize charts with format codes for professional presentation
Configure Excel file security - hiding formulas, protecting sheets and setting file passwords

Who this course is for

People who already use Excel but want their work to be easier and faster
Those who need to apply more complex formulas to real work and handle large volumes of data
HR, inventory, sales and marketing, and accounting and finance staff
Entry-level data analysts who want a foundation before moving to Power BI
Anyone who regularly produces summary reports and wants to reduce repetitive work

Prerequisites

Prior Microsoft Excel experience - creating and saving files, working with worksheets and formatting documents
Basic formula skills such as SUM, COUNT, IF, TODAY, and a solid understanding of relative and absolute cell references
Basic data skills such as sorting and filtering
Having completed Microsoft Excel Intermediate helps but is not required

Curriculum

Course Details

A 2-day hands-on workshop, 6 hours per day (12 hours in total), delivered hybrid - onsite or online via Microsoft Teams. Every module has an exercise or real-world case study using sample datasets (sales, inventory and employee data) provided throughout. Excel for Microsoft 365 is strongly recommended because several topics use new functions not supported in Excel 2019 and 2021 (8-15 people per class, Advanced level).

Day 1 Advanced Formulas and the New Microsoft 365 Functions

Section 1: Microsoft Excel for Advanced Use

Excel's capabilities and the scope suited to each kind of work, and the components advanced users should know
Excel file types (.xlsx, .xlsm, .xlsb, .csv), checking the Excel version and its impact on available functions
Designing maintainable workbooks (data layer, calculation layer, report layer) plus a workshop exploring the sample file structure

Section 2: Essential Shortcut Keys

Navigating and selecting large data ranges precisely without the mouse
Shortcuts for formatting, working with formulas, copying and Paste Special
Workshop: a timed exercise doing the same task with the mouse versus with shortcuts

Section 3: Working with Names in Excel

What Names are and how they make formulas more readable, with naming rules and editing
Referencing Names with cells, ranges and formulas, using Name Manager and creating Dynamic Names
Workshop: convert cell-reference formulas in a sample report into Name references

Section 4: Conditional Functions

Reviewing IF and the limits of deeply nested IFs, moving to IFS and SWITCH
Choosing between IF, IFS and SWITCH and using AND, OR, NOT with complex conditions
Workshop: compute employee appraisal grades and segment customers by purchase value with IFS and SWITCH

Section 5: Conditional Aggregation Formulas

SUMIFS, COUNTIFS and AVERAGEIFS for summing, counting and averaging on multiple conditions
Using wildcards, comparison operators and date-range conditions such as sales within a month or quarter
Workshop: build a per-employee sales summary by month and product group with SUMIFS and COUNTIFS

Section 6: Lookup & Reference Formulas

OFFSET for shifting references and INDIRECT for references from text
XLOOKUP and its advantages, compared with VLOOKUP and HLOOKUP, plus approximate match and search mode
Workshop: look up customer names, product prices and employee names from several reference tables

Section 7: New Microsoft 365 Formulas for Data Work

The Dynamic Array and Spill Range concept with FILTER, SORT, SORTBY and UNIQUE
TOCOL, TOROW, VSTACK, HSTACK, TAKE, DROP, and one-line summarization with GROUPBY and PIVOTBY
Workshop: extract Top 10 and Bottom 10 from sales data by the conditions you want

Section 8: Text and Image Formulas and Building Your Own Functions

TEXTBEFORE, TEXTAFTER, TEXTJOIN and TEXTSPLIT to cut, join and split text, and IMAGE to show pictures in cells
LAMBDA to build your own functions, naming them via Name Manager, and reusing them across the organization
Workshop: split first and last names, create a QR code with IMAGE and build a TOP3 formula with LAMBDA

Day 2 Data Management, Power Query, PivotTable and Security

Section 9: Handling Errors in Formulas

Error types (#N/A, #REF!, #VALUE!, #DIV/0!, #NAME?) and the ISNA and IFERROR functions
Cautions when IFERROR can hide the real problem, and Formula Auditing tools (Trace Precedents/Dependents)
Workshop: inspect and fix a sample report with several errors and identify the root cause

Section 10: Conditional Formatting

How it works and rule priority, using Data Bars, Color Scales and Icon Sets
Writing rules with formulas and managing them with Manage Rules, resolving overlapping rules
Workshop: a profit/loss Data Bar, icons for stock below the reorder level, and highlighting late shipments

Section 11: Preventing Errors with Data Validation

Preventing invalid input, setting alert messages and custom validation with formulas
Creating dropdown lists from lists and ranges, and checking with Circle Invalid Data
Workshop: prevent duplicate entries, prevent invalid entries, and build a dynamic dropdown list (choose a brand and the model list changes)

Section 12: Working with Data in Excel and Using Tables

An overview of data tools (Table, Power Query, PivotTable, PivotChart) and choosing the right one
Creating a Table, calculating with structured references, removing duplicates and adding a Slicer
Workshop: convert product and customer data into Tables for further analysis and summary

Section 13: Power Query for Data Preparation

What Power Query is, the basic ETL concept, the Power Query Editor and Applied Steps
Importing from multiple Excel files, CSV and folders, cleaning data, unpivoting, merging with Append/Merge and refreshing
Workshop: import same-structure Excel files from a whole folder, merge them automatically and refresh in one click

Section 14: Analysis and Summary with PivotTable and PivotChart

Preparing data and building a PivotTable, arranging fields, changing Summarize Values By and Show Values As (percentage, growth)
Grouping by date, month, quarter and numeric ranges, creating Calculated Fields, and building PivotCharts with Slicers and Timelines
Workshop: build an interactive sales report from Power Query-prepared data with Slicers and Timelines

Section 15: Presentation and Chart Customization

Choosing the right chart type for your message and customizing axes, labels, legends and gridlines
Formatting numbers with format codes (millions as M, thousands as K), building Combo Charts and Sparklines
Workshop: polish a monthly sales chart for an executive presentation and add Sparklines to the summary table

Section 16: File Security

Setting file passwords (Password to Open and Password to Modify) and protecting the worksheet and workbook
Preventing formula visibility and cell edits (Lock and Hidden) and allowing edits only in certain ranges (Allow Edit Ranges)
Workshop: build a data-entry form where users can only fill designated cells, cannot see formulas, and the structure is password-protected

Section 17: Capstone Workshop

Brief: build a complete monthly sales summary report from several raw data files, preparing and merging data with Power Query so it refreshes automatically
Build the calculation layer with the formulas learned (SUMIFS, XLOOKUP, FILTER, GROUPBY), summarize with a PivotTable and Slicer, customize the chart, and apply Conditional Formatting and Data Validation
Protect the file and hide formulas before handoff, with guidance on advancing to advanced Power Query, advanced PivotTable, Macro/VBA and Power BI

Schedule & training options

For individuals — public rounds

No public rounds are open right now. Join the waiting list and we will contact you first when the next round opens, or ask us on LINE. Or call 02-570-8449 or 088-807-9770

Join the waiting list Ask about rounds

For organisations — in-house / private

Tailor the content to your team’s tools and projects
Your dates, at your office or live online
Quotation with tax ID for procurement

Corporate training quote

Instructors

Samit KoyomFounder & CEO, IT Genius Engineering

Frequently asked questions

How is XLOOKUP different from VLOOKUP?

VLOOKUP only searches the leftmost column of a table, returns values to the right and needs a column number, which breaks easily when columns are inserted. XLOOKUP takes separate lookup and return ranges, so it can return values to the left or right, uses an exact match by default, lets you set a not-found message within the same formula and can search from the bottom up.

How do you combine several Excel files in a folder with Power Query?

Keep files with the same structure in one folder, then go to Data, choose Get Data from Folder and select Combine and Transform Data. Pick a sample sheet or table and Power Query combines every file, after which you clean the data in the Power Query Editor and choose Close and Load. When new files are added to the folder, a simple refresh brings them in without repeating the steps.

What are the GROUPBY and LAMBDA functions in Microsoft 365 used for?

GROUPBY summarises data by group in a single formula, such as total sales by region, using a grouping column, a values column and a summary function. The result spills into a table that updates when the data changes, much like a PivotTable but without refreshing. LAMBDA lets you create your own function by defining parameters and a calculation, then naming it in Name Manager so it can be reused across the workbook like a built-in function.

Who is the Microsoft Excel Advanced course for, and what should I know beforehand?

It suits people who already use Excel and want to work faster, HR, inventory, sales and accounting staff handling large volumes of data, anyone producing regular summary reports, and entry-level data analysts heading towards Power BI. You should be able to use formulas such as SUM, COUNT and IF, understand relative and absolute references and sort and filter data. Completing Excel Intermediate first helps.

What will I be able to do after the Microsoft Excel Advanced course?

You will be able to write conditional formulas with IFS and SWITCH, summarise by multiple criteria with SUMIFS and COUNTIFS, look up data with XLOOKUP, use newer functions such as FILTER, UNIQUE and GROUPBY, build your own functions with LAMBDA, combine and transform data with Power Query, summarise with PivotTables and PivotCharts, and protect formulas and files. Participants who complete the training receive a Certificate of Completion with its own number, which can be verified online.

Can the Microsoft Excel Advanced course be run in-house for our team?

Yes. It can be run in-house for your team, with examples adapted to your own work, such as the sales reports, staff data or stock files your team actually uses. You can request a quotation from this page, or contact us on LINE @itgenius or by phone on 02-570-8449.

Articles are published in Thai.