Advanced Microsoft Excel for Microsoft 365 is an advanced course in managing organisational data with Excel, from cleaning data, Excel tables, data validation, conditional formulas, XLOOKUP and dynamic arrays to summarising with PivotTables, GROUPBY and Power Query. It suits regular Excel users and staff who maintain data registers and produce reports.
Many organisations keep their core data in Excel, from customer and product registers to staff records and sales. As the data grows and several people enter it, the same problems keep appearing: numbers stored as text, duplicates, inconsistent spelling, lookup formulas that break and reports summarised by hand, even though Excel in Microsoft 365 already has the tools to deal with all of this.
This course builds on basic Excel and focuses on managing organisational data systematically. Learners prepare and clean data, store it in Excel tables, control data entry with data validation, write conditional formulas, look up data with XLOOKUP and use dynamic array formulas. They then summarise data with PivotTables and GROUPBY, pull data from other files with Power Query, and protect and share files safely. The course closes with a workshop that builds a data register for a department. (2 days, 6 hours per day, 12 hours in total, Intermediate-Advanced level.)
Course Details
This course is a 2026 edition that replaces the previous Advanced Microsoft Excel 2016/2019 course. It has been rewritten for Excel in Microsoft 365 and Excel 2024. It runs for 2 days, 6 hours per day (12 hours in total, 09:00-16:00), as lectures with labs on sample customer, product, staff and sales registers. Intermediate-Advanced level. The course focuses on managing a department's data in Excel, from data entry to summarising and sharing; those who want to go deeper into dashboards can continue with the Excel Dashboards for Data Analysis and Presentation course. Learners take home a lab guide, sample files with solutions for every lab and the data register built in the workshop.
Section 1: Lab: Getting Data Ready to Use
Section 2: Lab: Excel Tables and Data Management
Section 3: Lab: Controlling Data Entry with Data Validation
Section 4: Conditional Formulas and References
Section 5: Lab: Looking Up Data Across Tables
Section 6: Lab: Dynamic Array Formulas
Section 7: Conditional Formatting
Section 8: Lab: Summarising Data with PivotTables
Section 9: Lab: Summarising with GROUPBY and PIVOTBY
Section 10: Lab: Pulling and Combining Data with Power Query
Section 11: Protecting and Sharing Files Safely
Section 12: Workshop: A Department Data Register
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
A PivotTable is a summary table that groups, totals, counts or averages large amounts of data without formulas. Start by putting the data in an Excel table with complete headers and no blank rows, then choose Insert > PivotTable and drag fields into Rows, Columns, Values and Filters. When the source data changes, click Refresh, and add a slicer or PivotChart to filter and present the results more easily.
Data Validation sets what a cell will accept, such as numbers within a range, dates or a list of choices, which prevents entry mistakes at the source. To make a drop-down list, select the cells, go to Data > Data Validation, set Allow to List and give the source, ideally a column in an Excel table so the list grows automatically. You can also add an input message and an error alert for invalid entries.
VLOOKUP only searches the leftmost column and needs a column number for the result, so inserting a column can easily break it. INDEX with MATCH removes that limit but needs two nested functions. XLOOKUP takes separate lookup and return ranges, searches left or right, has a built-in value for no match and can search from the end of a list, so it suits new work in Microsoft 365 and Excel 2024. VLOOKUP is still worth reading because it is common in older files.
It suits regular Excel users who want to move up to advanced use, administration, HR, accounting and warehouse staff who maintain data registers, and supervisors who summarise data from several files. You should already be comfortable with basic Excel and formulas such as SUM, AVERAGE and IF. Excel from Microsoft 365 is recommended, because some topics such as GROUPBY and PIVOTBY are not available in Excel 2024.
You will be able to clean data so it is ready to use, store it in Excel tables, control entry with data validation, write multi-criteria formulas, look up data across tables with XLOOKUP, use dynamic array formulas, summarise reports with PivotTables and GROUPBY, combine data from several files with Power Query and protect files before sharing. Participants who complete the training receive a Certificate of Completion with its own number, which can be verified online.
Yes. It can be run in-house for your team, with examples adapted to your own work, such as the registers or reports your team already produces. 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.
Once you can write basic macros, real work soon gets harder. Data runs to tens of thousands of rows, so macros that loop cell by cell become slow; files have to be combined from many folders; totals must be summarised by unique customer code; PivotTables and…
Excel is used in almost every job, from keeping lists and working out expenses to summarising monthly sales, yet many users still retype figures by hand, copy formulas that give wrong results or spend too long setting up pages for printing. Getting the…
Many organisations keep product, customer or supplies records in Excel, but as the data grows the same problems keep appearing: codes lose their leading zeros, text and numbers are mixed together, filtering on several conditions means clicking through one box…
