Start learning
My learning

The Complete Excel Course with AI Integration

Excel course for accounts payable teams

For accounts payable clerks and AP teams who match, check and schedule supplier invoices in Excel. This page picks out the parts of The Complete Excel Course with AI Integration that payables work uses: clean invoice lists, keys built from text, lookups across orders, receipts and invoices, duplicate checks, the payment run list, ageing by supplier, and a Power Query merge for exports too large to match by hand.

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:

  • Match invoices to purchase orders and goods receipts with XLOOKUP or INDEX and MATCH before approval, and mark what has no match with IFERROR.
  • Find possible duplicate invoices by supplier, number and amount with COUNTIFS on a key built with CONCAT, and highlight them with conditional formatting.
  • Build the weekly payment run list with FILTER on due date and approval status, ordered with SORTBY, using EOMONTH for terms that run to month end.
  • Tidy supplier names and invoice numbers before matching with TRIM, UPPER and SUBSTITUTE.
  • Age unpaid invoices into buckets with IFS and TODAY, and total them by supplier with SUMIFS or a PivotTable.
  • Join a purchase order export to an invoice export with a Power Query merge, and keep the steps for next month's files.

Projects

What you will build.

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

    The matching behind a three-way match: a value found in another sheet by its key, a plain answer when nothing is there, and EOMONTH for terms that run to the end of a month.

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

    Unpaid invoices totalled by supplier and by month in a few clicks, ready for planning the weekly payment run.

  • Power Query sales report in Excel (SalesFusion): sales by category, sales by region and a monthly trend, with slicers for channel, year, category and region.

    Project 12

    SalesFusion

    A sales report that refreshes itself from a folder of files.

    The first steps of SalesFusion, up to the merge: exports cleaned in Power Query, then a left join that sets one list beside another, as invoices are set beside purchase orders.

  • Project 03

    Sales Goal Tracker

    COUNTIFS and SUMIFS on sales targets; in accounts payable, COUNTIFS on supplier, invoice number and amount is how a duplicate invoice shows up.

Lessons

The lessons that matter, in course order.

The Complete Excel Course with AI Integration has 186 lessons. This page picks out 52 of them, 4 h 31 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 key invoices into Excel: a clean, sortable list comes first. The highlight rules lesson, Lesson 15, sits earlier in the course and can be watched on its own. New to Excel? Begin at Lesson 1. If lookups are routine, go to Lesson 153, where Power Query begins.

52 of 186 lessons · 4 h 31 min

02 Project: personal budget tracker 1 of 5 lessons · 3 min Highlight rules, the conditional formatting that puts a possible duplicate or an overdue invoice in colour, taught on a personal budget.
  1. 15 Conditional formatting in Excel Highlight rules, data bars and icon sets 3 min
03 Managing data in Excel: sort, filter and tables (Dunder Michael Paper employee database) 5 of 9 lessons · 44 min Invoice lists as Excel Tables, sorted and filtered by supplier or due date, cleaned with TRIM and Find and Replace, and checked for repeated rows with Remove Duplicates. 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
04 Essential formulas and functions (IF, SUMIF and more) 10 of 13 lessons · 1 h 3 min Nested IF and IFS for ageing buckets, AND and OR for invoices that are both unapproved and due, COUNTIF and COUNTIFS for duplicate checks, SUMIF and SUMIFS for totals by supplier, and TODAY with date arithmetic for the days left to pay.
  1. 31 Nested IF and IFS in Excel Customer value tiers with nested IF 6 min
  2. 32 Nested IF and IFS in Excel Sales performance labels with the IFS function 5 min
  3. 33 AND and OR with IF in Excel Flag high-value deals with multiple conditions 8 min
  4. 34 SUM, COUNT and COUNTA in Excel SUM with FILTER and counting numbers with COUNT 7 min
  5. 35 SUM, COUNT and COUNTA in Excel COUNTA, unique counts and completeness checks 4 min
  6. 36 SUMIF and COUNTIF in Excel Conditional sum and count with one criterion 8 min
  7. 37 SUMIFS and COUNTIFS in Excel Sum and count with multiple criteria 7 min
  8. 38 The Excel function library Insert Function dialog and Formula AutoComplete 5 min
  9. 39 Date and time functions in Excel TODAY, NOW, MONTH and date arithmetic 6 min
  10. 40 Excel Ninja Challenge COUNTIF, SUMIF, IF, COUNTIFS, SUMIFS and IFS practice 8 min
05 Text functions and data validation (enhancing data quality) 3 of 7 lessons · 16 min CONCAT and the & operator for a key of supplier and invoice number, TRIM and UPPER for consistent names, and FIND and SUBSTITUTE for stray characters in references.
  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
07 PivotTables and PivotCharts: data analysis made easy 10 lessons · 33 min PivotTables for unpaid invoices by supplier, month or ageing bucket, with slicers, PivotCharts and Refresh. 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 for the match between invoice, order and receipt, IFERROR for the gaps, LEFT, RIGHT and MID for parts of a reference, EOMONTH for month-end terms, and FILTER, SORT and SORTBY for the payment run list.
  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
15 SalesFusion: advanced data tools and a Power Query modelling system 11 of 21 lessons · 58 min Power Query up to the merge: import a CSV, clean columns and text, fix data types, add month fields, total with Group By, and join two tables with a left join, as an invoice list is joined to purchase orders. The lessons use sales files.
  1. 153 SalesFusion Power Query project Excel ETL pipeline and data model overview 5 min
  2. 154 Introduction to Power Query in Excel The ETL workflow explained 5 min
  3. 155 Import a CSV with Power Query Get data from text and transform data 2 min
  4. 156 Clean columns in Power Query Remove and rename columns for modelling 5 min
  5. 157 Clean text in Power Query Trim, clean, replace values and fix text case 6 min
  6. 158 Data types in Power Query Fix date, whole number, decimal and text types 7 min
  7. 159 Date columns in Power Query Add year, month and year-month fields for reports 7 min
  8. 160 Business logic in Power Query Custom and conditional columns for profitability 6 min
  9. 161 Group By in Power Query Summarise sales by month and region to validate data 5 min
  10. 162 Close and Load in Power Query Connection only, table or Data Model 5 min
  11. 163 Merge queries in Power Query Look up product and region names with a left join 5 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 accounts payable teams?

Yes. The Complete Excel Course with AI Integration from Coding Liquids covers the Excel behind the checks an invoice goes through before it is paid: matched to its order and receipt with XLOOKUP, tested for a duplicate with COUNTIFS, aged with IFS and TODAY, then picked for the payment run with FILTER and SORTBY and totalled by supplier in a PivotTable. The 52 lessons on this page come to 4 hours 31 minutes of the course's 16 hours.

Can Excel catch duplicate invoices?

Yes, with what The Complete Excel Course with AI Integration teaches: CONCAT builds a key from supplier, invoice number and amount, COUNTIF counts how often each key appears, and conditional formatting colours any key that appears more than once. TRIM and SUBSTITUTE first remove the spaces and stray characters that hide a duplicate. The lessons use other data; none of them is about invoices.

Does it teach three-way matching?

Not as a lesson, but The Complete Excel Course with AI Integration teaches the pieces: XLOOKUP or INDEX and MATCH to bring the order and the receipt onto each invoice line, IFERROR to mark a missing one, and a Power Query merge to join two exports with a left join. ERP systems, accounting entries and payment systems are not taught.

Can I build a payment run list in Excel?

Yes. In The Complete Excel Course with AI Integration FILTER returns the rows that meet several conditions, such as due before a date and approved, SORTBY orders them by due date or amount, EOMONTH finds the end of a month for terms that run to it, and SUMIFS totals the run by supplier. The lessons teach these on HR and sales data.

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