Microsoft Tools · MIC-74

Microsoft Excel Advanced

12 hours 2 days

Last updated 25 Sep 2026

Microsoft Excel Advanced is a 12-hour training course by IT Genius Institute. 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…

Training schedule

No public rounds are open right now — register your interest and we will contact you when the next round opens, or request an in-house session for your team.

Corporate training quote

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.)

Objectives

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

Frequently asked questions

Who is Microsoft Excel Advanced for, and what background is needed?

Built 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 Background you should have: 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 Not sure the fit is right? Talk to our team on LINE @itgenius or call 02-570-8449.

How much does Microsoft Excel Advanced cost and how long does it run?

THB 7,500 (currently THB 6,750 on promotion). The course runs 12 hours. The fee covers course materials, lunch and refreshments throughout. Pay by bank transfer to the company account, confirm it on our payment page, and we can issue the receipt or tax invoice in your company's name.

Do I get a certificate?

Yes. Everyone who completes the course receives a Certificate of Completion from IT Genius Institute. Each certificate carries its own number, and anyone holding that number can verify it online on our certificate page, so you can add it to your portfolio or pass it to HR as evidence of training.

Where does the training take place, and is there an online option?

You can attend onsite at IT Genius Institute or arrange to join online, and we also run it as a private in-house session for your team. Ask about dates and venues on LINE @itgenius or call 02-570-8449.

What if I fall behind or miss a session — can I retake it?

Yes. You may retake the same course free of charge in a later round, under the institute's conditions. Tell our team which course and round you attended, and we will check it and offer you the rounds that still have seats. Ask us on LINE @itgenius or call 02-570-8449.

How do I enrol, or request a quotation for my company?

Enrol online with the registration form on this page. You can register several attendees at once and enter your tax ID and billing address for the tax invoice. Or request a company quotation straight from the quote button. For anything else call 02-570-8449 or reach us on LINE @itgenius.