Start learning
My learning

The Complete Excel Course with AI Integration

Excel course for AML and KYC analysts

For AML and KYC analysts who review customers, work screening alerts and report on cases in Excel. This page picks out the parts of The Complete Excel Course with AI Integration that this work uses: cleaning customer data, review dates set from risk ratings, consistent decisions on alerts, a review tracker, case volumes and ageing in PivotTables, rule-based lists for quality checks, and VBA for checks that repeat.

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:

  • Clean customer records before review: TRIM, PROPER and SUBSTITUTE for names, Remove Duplicates for repeated rows, and Flash Fill to split names into their parts.
  • Set each customer's next review date from the risk rating with IFS and EOMONTH, and flag the reviews that are overdue with TODAY.
  • Log screening alerts with a decision chosen from a drop-down list, so every case reads cleared, escalated or confirmed in the same words.
  • Report case volumes and ageing by team, analyst and month with COUNTIFS, PivotTables and slicers.
  • Pull the cases that meet set rules into a quality-check list with FILTER, ordered with SORTBY.
  • Run the same checks on every case file with a VBA macro that loops through the rows, flags missing fields and colours each gap.

Projects

What you will build.

The course has 14 projects. These five 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.

    A customer list set against the review schedule with XLOOKUP, practised on HR records, and IFERROR showing who has no review on file.

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

    Case volumes and ageing by team, analyst and month, built with the steps the lessons use on sales orders, and a slicer for each review type.

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

    Built for tasks, it serves as a periodic review tracker: each customer's review status from a drop-down list, icons for the risk rating, the working days left before the review falls due, and a locked sheet.

  • Excel VBA macro editor (FormatWizard): a macro that runs every validation check on a sheet and reports when it has finished.

    Project 11

    FormatWizard

    One click that checks a whole sheet for you.

    Its VBA, written to check a sales sheet, repeats the checks on a case file: a loop that flags missing identity fields, colours each gap and stops safely on an error, run before every QA round.

  • Project 02

    Dunder Michael Paper Company employee database

    Customer records cleaned the way the lessons clean employee records: spaces trimmed, duplicate rows removed, names split and spelled consistently before any review starts.

Lessons

The lessons that matter, in course order.

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

Where to start

Lesson 19 Excel tables

Begin at Lesson 19 if you already keep case or customer lists in Excel: clean data comes before any review. That section, Managing data in Excel, is free by email with its practice files: ask for it on the course page. New to Excel? Begin at Lesson 1. If tables and IF formulas are routine, go to Lesson 79 for the review tracker or Lesson 136 for VBA.

Get the free section by email

75 of 186 lessons · 6 h 16 min

03 Managing data in Excel: sort, filter and tables (Dunder Michael Paper employee database) 9 lessons · 1 h 1 min Customer data under control: Excel Tables, sorting and filtering by risk rating or review date, cleaning with TRIM and Find and Replace, Remove Duplicates for repeated records, Flash Fill and Text to Columns for names and addresses, frozen headers and printing. The lessons use an employee database.
  1. 19 Start here Excel tables Create and format a table, structured references and total row 8 min
  2. 20 Sorting data in Excel Single and multi-level sort on an employee database 6 min
  3. 21 Filtering data in Excel AutoFilter, text and number filters on employee data 10 min
  4. 22 Cleaning data in Excel TRIM, Paste Special values and Find and Replace 13 min
  5. 23 Remove duplicates in Excel Find and delete duplicate rows in an employee list 6 min
  6. 24 Flash Fill in Excel Split first and last names automatically 3 min
  7. 25 Text to Columns in Excel Split data by delimiter into separate columns 6 min
  8. 26 Freeze panes in Excel Lock header rows and columns while scrolling 3 min
  9. 27 Printing in Excel Print area, page orientation, scaling and page layout 6 min
04 Essential formulas and functions (IF, SUMIF and more) 13 lessons · 1 h 17 min Review rules as formulas: IFS for a review cycle from the risk rating, AND and OR for a case that is both high risk and overdue, COUNT and COUNTA for missing fields, COUNTIFS for cases by status, analyst and outcome, and TODAY with date arithmetic for days overdue.
  1. 28 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 Names and entries made consistent: CONCAT for a key of name and date of birth, TRIM, UPPER and PROPER for spelling, FIND, SEARCH and SUBSTITUTE for stray characters, Text to Columns, drop-down lists for alert decisions, and a Ninja Challenge on a messy list.
  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 PivotTables, slicers and PivotCharts for case volumes and ageing by team, analyst, risk rating or month, refreshed when the next extract replaces the data. The lessons use sales orders.
  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 XLOOKUP, VLOOKUP and INDEX and MATCH to set a customer list against the review schedule, IFERROR for customers with no record, LEFT, RIGHT and MID for parts of customer codes, EOMONTH and NETWORKDAYS for due dates, and FILTER, SORT and SORTBY for the cases due next and for quality-check lists. The lessons use an HR dataset.
  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 4 of 9 lessons · 25 min TaskFlow laid out as a periodic review tracker: one row per customer, status and priority from drop-down lists, and highlights for reviews that are overdue or done.
  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
09 Project: task automation with data validation and forms 3 of 9 lessons · 15 min Icon sets for the risk rating, the working days left before each review falls due with NETWORKDAYS and TODAY, and a protected sheet the team can update safely.
  1. 84 Data bars and icon sets in Excel Visual priority and progress in TaskFlow 6 min
  2. 85 NETWORKDAYS and TODAY in Excel Days remaining and task deadlines in TaskFlow 5 min
  3. 86 Protect a sheet and lock cells in Excel Secure the TaskFlow tracker workbook 4 min
14 VBA and macros in Excel: FormatWizard 17 lessons · 1 h 16 min FormatWizard in full: a macro recorded and put on a button, the VBA editor, variables and data types, a helper function that finds the last used row, For Next loops and If tests that mark empty cells, IsNumeric checks, fill colours for each finding, Debug.Print in the Immediate window, and one complete routine with an error handler.
  1. 136 Excel macros and VBA for beginners What macros are, the FormatWizard project 3 min
  2. 137 Record your first Excel macro Name, run and use absolute references 6 min
  3. 138 Relative references in Excel macros Make recorded macros dynamic 4 min
  4. 139 Developer Tab in Excel Run macros from a button and the quick access toolbar 4 min
  5. 140 Save macros in Excel Macro-enabled workbooks and the XLSM file format 3 min
  6. 141 The VBA Editor in Excel Open it with Alt+F11 and learn the workspace 4 min
  7. 142 Working in the VBA editor Macro security, trust settings, switching to Excel 6 min
  8. 143 Your first VBA code Modules, worksheets, rows, columns and formatting 4 min
  9. 144 VBA variables and data types Sub vs function for Excel beginners 6 min
  10. 145 VBA functions in Excel Build GetLastUsedRow with find and exit function 5 min
  11. 146 Clear old flags with VBA Reset messages and fill colours with if statements 5 min
  12. 147 For Next loop in VBA Flag missing region values in Excel 4 min
  13. 148 VBA If Else and loops Check missing sales data and build a FlagText message 5 min
  14. 149 Highlight cells with VBA Interior.Color and RGB for missing data 5 min
  15. 150 IsNumeric in VBA Validate unit price and amount data types in Excel 7 min
  16. 151 Debugging VBA for beginners Debug.Print and the Immediate window 5 min
  17. 152 Complete Excel validation macro One VBA subroutine with an error handler 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.

Is there an Excel course for AML and KYC analysts?

Yes. The Complete Excel Course with AI Integration from Coding Liquids teaches the Excel that KYC and AML operations run on: customer data cleaned with TRIM, PROPER and Remove Duplicates, review dates set from risk ratings with IFS and EOMONTH, alert decisions kept consistent with drop-down lists, case volumes and ageing in PivotTables, and VBA macros for repeat checks. The 75 lessons here come to 6 hours 16 minutes of the course's 16 hours.

Does it teach AML rules or screening tools?

No. The Complete Excel Course with AI Integration teaches Excel, not financial crime: anti-money-laundering regulation, customer due diligence standards, screening systems and investigation methods are not covered. It covers the spreadsheets around a review: clean customer lists, due dates, decision logs, case reports and checks that run from a button.

Can Excel choose cases for a quality check?

Only by rule. In The Complete Excel Course with AI Integration FILTER returns the cases that meet the conditions you choose, such as high risk, closed this month and handled by one analyst, and SORTBY puts the list in date order for the reviewer. Random picks and sampling methods are not taught; if your QA standard asks for a random sample, take that method from your own procedures.

Can I track periodic KYC reviews in Excel?

Yes. In The Complete Excel Course with AI Integration TaskFlow is a tracker with drop-down statuses, highlights for overdue items and NETWORKDAYS counting the working days left; used for reviews, each row is a customer. IFS turns a risk rating into a number of months and EOMONTH moves the last review date forward by them to the next due date. TaskFlow is taught as a task tracker; the review rules are your own.

Can VBA check case files for missing details?

Yes. FormatWizard in The Complete Excel Course with AI Integration writes a VBA routine that loops down a sheet, flags empty cells, tests entries with IsNumeric, colours each problem, handles errors and runs from a button. The lessons check a sales sheet for missing regions and amounts; the fields your case files must hold are yours to set in the loop.

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