Start learning
My learning

The Complete Excel Course with AI Integration

Excel course for training coordinators

Training coordinators and L&D administrators managing training records, renewal dates and session plans in Excel can use this guide to The Complete Excel Course with AI Integration. It selects drop-down lists, IF and COUNTIFS for statuses and counts, lookups to connect records with staff lists, expiry date functions, PivotTables for completions and feedback, and TaskFlow session planning.

See the plans Watch free lessons

Taught by Sagnik Bhattacharya and Virginia Thorn

The Complete Excel Course with AI Integration: Sagnik Bhattacharya and Virginia Thorn

What you will be able to do

At work, you will be able to:

  • Record which courses each person has completed, using status drop-downs and XLOOKUP to add their department from the staff list.
  • Use conditional formatting to colour certificates needing attention, with IFS, TODAY and EOMONTH determining whether they are valid, due for renewal or expired.
  • Summarise course completions by course and department using COUNTIFS and PivotTables, then divide a completion count by another count to calculate a completion rate.
  • Compare average feedback scores for courses and trainers using a PivotTable and slicers to filter the results.
  • Organise session dates and statuses in a tracker with a timeline, using NETWORKDAYS to calculate the working days until each session.
  • Use FILTER to identify people who have yet to complete a required course, then put the list in order with SORT.

Projects

What you will build.

The course has 14 projects. These three matter most here.

  • XLOOKUP formula in Excel (PeopleInsight): each employee row looks up its location from another sheet.

    Project 06

    PeopleInsight HR workbook

    Employee data joined across sheets with one formula.

    Apply XLOOKUP to bring departments from the staff list into training records, EOMONTH to place renewals at month end, and FILTER to list people with a course still to complete.

  • Task tracker in Excel (TaskFlow): start and due dates, status, priority and a progress bar for each task, with working days left, days past due and unfinished high-priority tasks highlighted.

    Project 07

    TaskFlow

    One sheet that shows what is done, what is late and what is urgent.

    Treat each session as a task with a status and date. Colour sessions whose dates have passed and calculate the working days remaining before the next deadline.

  • Excel PivotTables and a PivotChart of sales data (SalesPlusPerformance): net sales by product category as a column chart, with State, Region and City filters.

    Project 05

    SalesPlusPerformance

    A sales question answered in seconds, straight from the order data.

    Apply the project's order summaries and filtering approach to department completion counts and average course or trainer feedback.

Lessons

The lessons that matter, in course order.

The Complete Excel Course with AI Integration has 186 lessons. This page picks out 51 of them, 4 h 8 min of video. Each lesson keeps its number in the course.

Where to start

Lesson 28 Logical functions in Excel

If data entry and simple formulas are familiar, start with Lesson 28 and build statuses and counts using IF and COUNTIF. For a first introduction to Excel, start with Lesson 1. If you already use formulas and PivotTables routinely, begin at Lesson 67 for lookups that connect training records with the staff list.

51 of 186 lessons · 4 h 8 min

04 Essential formulas and functions (IF, SUMIF and more) 13 lessons · 1 h 17 min Sales examples introduce formulas you can apply to training records: COUNTIF and COUNTIFS count completions by course and department, while COUNTA helps identify who is missing. Use IF and IFS for valid, due or expired certificate statuses, and TODAY with date arithmetic for time left before expiry.
  1. 28 Start here Logical functions in Excel TRUE and FALSE comparisons for beginners 5 min
  2. 29 The IF function in Excel Pass or fail results with a single condition 3 min
  3. 30 IF function practice in Excel Categorise sales data with IF formulas 6 min
  4. 31 Nested IF and IFS in Excel Customer value tiers with nested IF 6 min
  5. 32 Nested IF and IFS in Excel Sales performance labels with the IFS function 5 min
  6. 33 AND and OR with IF in Excel Flag high-value deals with multiple conditions 8 min
  7. 34 SUM, COUNT and COUNTA in Excel SUM with FILTER and counting numbers with COUNT 7 min
  8. 35 SUM, COUNT and COUNTA in Excel COUNTA, unique counts and completeness checks 4 min
  9. 36 SUMIF and COUNTIF in Excel Conditional sum and count with one criterion 8 min
  10. 37 SUMIFS and COUNTIFS in Excel Sum and count with multiple criteria 7 min
  11. 38 The Excel function library Insert Function dialog and Formula AutoComplete 5 min
  12. 39 Date and time functions in Excel TODAY, NOW, MONTH and date arithmetic 6 min
  13. 40 Excel Ninja Challenge COUNTIF, SUMIF, IF, COUNTIFS, SUMIFS and IFS practice 8 min
05 Text functions and data validation (enhancing data quality) 7 lessons · 35 min Clean names exported from a system with TRIM and PROPER, or combine them using CONCAT and TEXTJOIN. Data validation provides drop-down lists to keep course names, trainer names and statuses consistent.
  1. 41 CONCAT, the & operator and TEXTJOIN in Excel Combine text from multiple cells 6 min
  2. 42 TRIM, UPPER, LOWER and PROPER in Excel Clean messy text and fix capitalisation 6 min
  3. 43 FIND, SEARCH and SUBSTITUTE in Excel Locate and replace text with formulas 5 min
  4. 44 Text to Columns wizard in Excel Split a column by a comma delimiter 4 min
  5. 45 Data validation in Excel Create drop-down lists for consistent data entry 4 min
  6. 46 Ninja Challenge: problem Clean messy data with text functions and validation 4 min
  7. 47 Ninja Challenge: solution PROPER, Text to Columns and drop-down lists 7 min
07 PivotTables and PivotCharts: data analysis made easy 10 lessons · 33 min Apply the sales-based PivotTable lessons to a training log: summarise completions by department and set feedback scores to average by course or trainer through Value Field Settings. Slicers provide filtering, alongside PivotCharts.
  1. 57 PivotTables 101 in Excel What a PivotTable does and the sales dataset 5 min
  2. 58 Preparing data for PivotTables Clean data with TRIM, PROPER, find and replace 5 min
  3. 59 Create your first PivotTable in Excel Insert a PivotTable from a sales table 1 min
  4. 60 PivotTable fields in Excel Rows, values, columns and Value Field Settings 2 min
  5. 61 PivotTable summaries in Excel Sales by product category and grouping by date 4 min
  6. 62 Slicers in Excel Filter PivotTables with clickable buttons 3 min
  7. 63 PivotCharts in Excel Create interactive charts from a PivotTable 5 min
  8. 64 PivotTable design Tab Report layouts, PivotTable styles and number formats 5 min
  9. 65 Refresh a PivotTable in Excel Update pivots when source data changes 2 min
  10. 66 Excel Ninja Challenge PivotTable sales by region, category and monthly trend 1 min
08 Advanced Excel functions: lookups, text and arrays 12 lessons · 54 min Work with the PeopleInsight HR dataset to learn VLOOKUP, XLOOKUP and INDEX and MATCH for connecting training records to departments or managers in the employee list. Handle unmatched names with IFERROR, calculate dates with EOMONTH and NETWORKDAYS, and use UNIQUE, FILTER and SORT to list people still needing training.
  1. 67 Lookup functions in Excel The PeopleInsight HR dataset and why lookups matter 2 min
  2. 68 VLOOKUP in Excel Find employee data across sheets with exact match 4 min
  3. 69 XLOOKUP in Excel Exact match, if not found and two-way lookup 6 min
  4. 70 IFERROR in Excel Handle lookup errors in VLOOKUP and XLOOKUP formulas 3 min
  5. 71 INDEX and MATCH in Excel A flexible alternative to VLOOKUP 6 min
  6. 72 CONCAT and TEXTJOIN in Excel Combine employee names and text with a delimiter 3 min
  7. 73 LEFT, RIGHT and MID in Excel Extract parts of a text code like DAL-SAL-0001 7 min
  8. 74 TODAY, EOMONTH and NETWORKDAYS in Excel Date calculations for HR data 5 min
  9. 75 UNIQUE function in Excel Extract a distinct list with dynamic arrays 6 min
  10. 76 FILTER function in Excel Dynamic array filtering with AND and OR criteria 5 min
  11. 77 SORT and SORTBY in Excel Sort data dynamically with formulas 4 min
  12. 78 Excel Ninja Challenge XLOOKUP, totals and text concatenation lookup challenge 3 min
09 Project: task automation with data validation and forms 9 lessons · 49 min Use the TaskFlow task tracker approach for session planning: a Gantt-style training calendar, drop-downs for priority and status, and highlighting for overdue items. NETWORKDAYS calculates working days remaining, and sheet protection protects the tracker.
  1. 79 TaskFlow project tracker in Excel Overview of the task tracker dashboard 4 min
  2. 80 Designing a task tracker in Excel TaskFlow layout, columns and headers 5 min
  3. 81 Data validation in Excel Status and priority drop-down lists in TaskFlow 8 min
  4. 82 Conditional formatting in Excel Highlight overdue and completed tasks 8 min
  5. 83 Gantt chart in Excel Build a project timeline with conditional formatting 6 min
  6. 84 Data bars and icon sets in Excel Visual priority and progress in TaskFlow 6 min
  7. 85 NETWORKDAYS and TODAY in Excel Days remaining and task deadlines in TaskFlow 5 min
  8. 86 Protect a sheet and lock cells in Excel Secure the TaskFlow tracker workbook 4 min
  9. 87 Testing an Excel task tracker Stress test TaskFlow with sample data 3 min

Instructors

Who teaches it.

Virginia Thorn smiling at the camera, dark hair tied back, wearing a navy top.

Instructor, Excel with AI

Virginia Thorn

Emmy Award winner. Teaches Excel with AI at Coding Liquids.

Questions

Asked before starting.

Which Excel course covers the work of training coordinators?

The Complete Excel Course with AI Integration from Coding Liquids covers spreadsheet tasks in L&D administration. These include linking training records to staff lists through XLOOKUP, calculating expiry dates with TODAY and EOMONTH, counting completions through COUNTIFS and PivotTables, averaging course and trainer feedback, and organising sessions in a tracker. The selection on this page comprises 51 lessons, taking 4 hours 8 minutes within the course's 16 hours.

Can a training coordinator use this course to build a training matrix in Excel?

A training matrix is not a ready project in The Complete Excel Course with AI Integration, but the course teaches its components using sales and HR data. You can keep statuses consistent with drop-down lists, retrieve departments with XLOOKUP and count course completions with COUNTIFS. IFS and TODAY classify certificates as valid, due or expired, while conditional formatting colours those needing attention.

How can training coordinators keep track of certificate renewals and expiry?

Use the tools taught in The Complete Excel Course with AI Integration: calculate remaining days with TODAY and date arithmetic, and use EOMONTH for certificates that expire at month end. IFS supplies valid, due or expired labels. TaskFlow's conditional formatting can colour overdue certificates whenever the sheet is opened.

Does this course teach training coordinators about learning management systems or training needs analysis?

No. The scope of The Complete Excel Course with AI Integration is Excel; it does not teach learning management systems, instructional design or training needs analysis. Its cleaning, joining, counting and reporting lessons apply once completion data has been exported from your system to a spreadsheet.

Excel with AI

See the whole course.

The Complete Excel Course with AI Integration: 16 hours of video in 186 lessons, built around 14 projects, with lifetime access, a Certificate of Completion and a 14-day money-back guarantee.

See the plans Watch free lessons

The same course

For another job, level or topic.

By job

Finance and accounting
HR and office administration
Operations and supply chain
Data and reporting
Sales, marketing and customer service
Business and management
Engineering, construction and manufacturing
Retail, property and hospitality
Healthcare and life sciences
Education and careers
Public sector, non-profit and legal
Technology

By level and topic