Excel Formulas for Database Management

Course overview

Once employee, customer or product records in Excel run to thousands of rows, work that used to be done by hand becomes slow and error-prone, such as calculating length of service and overtime, looking up data across tables, summarising totals on several conditions or rebuilding a report every time the data changes. Modern Excel formulas turn these tasks into a single formula that updates itself, without copying long nested formulas or filtering data by hand each time.

This new edition is updated for Excel in Microsoft 365 and focuses on the formula side of database work in particular, from conditional, date, time and text formulas, conditional counts and summaries, XLOOKUP and XMATCH and dynamic array formulas such as FILTER, SORT, SORTBY and UNIQUE to formula-driven data validation, summaries with GROUPBY and PIVOTBY, and formulas that are easy to read and reuse with LET and LAMBDA. (3 days, 6 hours per day, 18 hours in total, Intermediate level.)

What you’ll gain

Write formulas against database-style data systematically and trace the causes of formula errors
Use date, time and text formulas to calculate ages, due dates and overtime and to fix data formats
Count, sum, average and find maximums and minimums on several conditions
Look up data with XLOOKUP and XMATCH, including multiple-criteria and two-way lookups
Build self-updating reports with FILTER, SORT, SORTBY, UNIQUE and dynamic arrays
Use formulas to control data entry with data validation and to highlight data with conditional formatting
Summarise data with GROUPBY and PIVOTBY, and write readable, reusable formulas with LET and LAMBDA

Who this course is for

HR staff who calculate length of service, leave and overtime in Excel
Accounting, finance and warehouse staff who regularly summarise large data sets
Data analysts and report writers who want to cut down on copying and filtering by hand
Long-time Excel users who want to move from older formula techniques to modern formulas

Prerequisites

Confident Excel use, such as entering data, formatting and copying formulas
Able to write basic formulas such as SUM, AVERAGE and IF, and understand $ cell references
Experience of working with data of several hundred rows or more
A laptop with Excel from Microsoft 365 (recommended) or Excel 2024

Curriculum

Course Details

This course is the 2026 edition of Database Management with Microsoft Excel: Applying Formulas, focused on the formula side of database work in particular. Unlike Building and Filtering Databases in Excel, which teaches building tables, sorting and filtering with AutoFilter and Advanced Filter, this course uses modern formulas in Excel for Microsoft 365 to solve the same kinds of problems so the results update themselves. It runs for 3 days,

6 hours per day (18 hours in total, 09:00-16:00), as lectures alternating with labs and a workshop. Intermediate level. Learners use date, time and text formulas, conditional summaries, lookups with XLOOKUP and XMATCH, reports with FILTER, SORT, SORTBY and UNIQUE, formula-driven data entry control, summaries with GROUPBY and PIVOTBY, and reusable formulas with LET and LAMBDA. They take home the answer files and a reporting workbook.

Day 1 Formula Foundations for Database Work: Dates, Times and Text

Section 1: Thinking in Formulas with Database-Style Data

Separate raw data from reports, and write formulas that need no editing as data grows
Relative and absolute references, named ranges and structured references
Causes of and fixes for #VALUE!, #NAME?, #DIV/0!, #REF!, #SPILL! and #CALC!
Step through formulas with Evaluate Formula and Trace Precedents
Lab: repair a report whose formulas break when rows are added

Section 2: Lab: Readable Conditional Formulas

Nested IF versus IFS and SWITCH, and when to use each
AND, OR and NOT with IF, and writing conditions without IF
Check data types with ISNUMBER, ISTEXT and ISBLANK
Handle errors with IFERROR and IFNA in moderation
Lab: grade employee appraisal results on several conditions

Section 3: Lab: Date Formulas for Ages and Due Dates

Combine day, month and year from separate columns with DATE
Convert dates entered in the Buddhist Era so they can be calculated
Calculate age and length of service with DATEDIF and YEARFRAC
Delivery dates, warranty expiry and working days with EDATE, EOMONTH and WORKDAY.INTL
Lab: an employee register with length of service and probation end dates

Section 4: Lab: Working Hours and Overtime

How Excel stores time, and converting time to hours
Calculate shifts that end after midnight with MOD
Overtime pay by rate, and rounding with MROUND and FLOOR.MATH
Conditional formatting with formulas, such as overdue returns or short hours
Lab: a monthly working hours and overtime summary

Section 5: Lab: Text Formulas for Splitting, Joining and Fixing Data

Split text with TEXTBEFORE, TEXTAFTER and TEXTSPLIT
Join text with TEXTJOIN and spell out amounts in Thai with BAHTTEXT
Keep leading zeros in codes with TEXT, and fix text with SUBSTITUTE
Format phone numbers with REGEXTEST and REGEXREPLACE
Lab: clean up a customer list entered in inconsistent formats

Day 2 Summarising, Looking Up and Extracting Data with Formulas

Section 6: Lab: Conditional Counts, Sums and Averages

COUNTIFS, SUMIFS and AVERAGEIFS with text, number and date range criteria
MAXIFS and MINIFS for conditional maximums and minimums
Wildcard criteria and criteria taken from cells
Running numbers that restart for each group, and ranking with RANK.EQ
Lab: summarise sales by branch, salesperson and period

Section 7: Lab: Lookups with XLOOKUP and XMATCH

The limits of VLOOKUP and what XLOOKUP fixes
Look up to the left, set a value when nothing is found, and search from the end
Look up score bands and look up on several criteria
Two-way lookups with nested XLOOKUP or XMATCH with INDEX
Lab: pull customer and price data from several tables

Section 8: Lab: Dynamic Arrays with SORT, SORTBY and UNIQUE

The spill concept and referring to results with the # operator
Automatic running numbers that stay correct when rows are deleted, with SEQUENCE
Distinct lists and distinct counts with UNIQUE
Sort data with the SORT and SORTBY formulas without touching the source
Lab: a distinct product list ranked by sales

Section 9: Lab: Multi-Condition FILTER and Self-Updating Reports

FILTER with AND and OR conditions
Pick columns with CHOOSECOLS and trim rows with TAKE and DROP
Reports that change with the value chosen in a criteria cell
Combine data from several sheets with VSTACK instead of switching sheet names in formulas
Lab: a quarterly sales report with selectable criteria

Section 10: Lab: Formula-Driven Data Validation

Block duplicate codes with a COUNTIF rule
Enforce code formats and date ranges with formulas
Drop-down lists from UNIQUE and SORT results
Dependent drop-down lists, such as province and district, with FILTER
Lab: a customer entry form that prevents bad data

Day 3 Advanced Summaries and Reusable Formulas

Section 11: Lab: Summarising Data with GROUPBY and PIVOTBY

How GROUPBY and PIVOTBY differ from a PivotTable
Group by several fields, choose the summary function and set subtotals
Shares of the total with PERCENTOF, and filtering rows with filter_array
Lab: a self-updating sales summary by branch and month

Section 12: Lab: SUMPRODUCT and Array Formulas

Weighted averages with SUMPRODUCT
Sum while skipping error cells with AGGREGATE
Sum every other row or every Nth row in one formula
Lab: weighted appraisal scores and totals with bad data mixed in

Section 13: Lab: LET for Readable Formulas

The problems with deeply nested mega formulas
Name variables inside a formula with LET and avoid repeated calculation
Break formulas into steps so they are easy to check and fix
Lab: rewrite an existing long formula with LET

Section 14: Lab: LAMBDA Functions for Your Organisation

Create your own functions with LAMBDA and name them in Name Manager
Use BYROW and MAP to calculate row by row in one formula
Ranges that grow automatically with TRIMRANGE and trim references
Lab: functions for your organisation's length of service and overtime rules

Section 15: Workshop: A Formula-Driven Database Reporting System

An entry sheet with data validation and conditional formatting
Pull data with XLOOKUP and extract reports with FILTER
Summarise with GROUPBY, PIVOTBY and the LAMBDA functions you built
Test with new data, and document the formulas for your team

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

Nattasira YauvasutaInstructor

Frequently asked questions

How does this course differ from Building and Filtering Databases in Excel?

Building and Filtering Databases in Excel teaches table design, Excel tables, sorting and filtering with the AutoFilter and Advanced Filter commands. This course focuses on formulas, using modern formulas in Excel for Microsoft 365 such as XLOOKUP, FILTER, SORTBY, UNIQUE, GROUPBY, LET and LAMBDA to look up, extract and summarise data so the results update when the data changes, along with date, time and text formulas and formula-driven data validation. If you need both sides, you can take the two courses one after the other without repeated content.

How do I calculate age from a date of birth in Excel?

Use DATEDIF(date_of_birth, TODAY(), "Y") to get age in whole years, add a second part with the "YM" unit for years and months, or use YEARFRAC for age as a decimal. The key is that the date of birth must be a real Excel date. If it was entered as Buddhist Era text, convert it to a date first, for example with DATE and subtracting 543 from the year, then calculate the age.

Why is XLOOKUP better than VLOOKUP?

XLOOKUP can look left or right without counting column numbers, so it does not break when a column is inserted. It matches exactly by default, lets you set a message when nothing is found in the same formula, can search from the end of a list and can return several columns at once. Combined with multiple conditions or XMATCH, it also handles multiple-criteria and two-way lookups. XLOOKUP is available in Excel for Microsoft 365 and in one-time-purchase Excel from Excel 2021 onwards.

Who is the Excel Formulas for Database Management course for, and what should I know beforehand?

It suits HR, accounting and finance, warehouse and data analysis staff who have used Excel for some time and work with large data sets. You should use Excel confidently, write basic formulas such as SUM, AVERAGE and IF, and understand $ cell references. Excel for Microsoft 365 is recommended because some functions, such as GROUPBY and PIVOTBY, are only available there.

What will I be able to do after the Excel Formulas for Database Management course?

You will be able to fix formula errors systematically, calculate length of service, due dates and overtime, split and fix text, summarise on several conditions with COUNTIFS and SUMIFS, look up data with XLOOKUP and XMATCH, build self-updating reports with FILTER, SORTBY and UNIQUE, control data entry with formulas, summarise with GROUPBY and PIVOTBY and build your organisation's own functions with LET and LAMBDA. Participants who complete the training receive a Certificate of Completion with its own number, which can be verified online.

Can the Excel Formulas for Database Management course be run in-house for our team?

Yes. It can be run in-house for your team, with exercises adapted to the files and data your team actually uses, such as employee registers, time records or sales lists. You can request a quotation from this page, or contact us on LINE @itgenius or by phone on 02-570-8449.