Start learning
My learning

The Complete Excel Course with AI Integration

Excel course for reconciliation analysts

For reconciliation analysts who match one record against another every day: bank against ledger, system against statement. This page picks out the parts of The Complete Excel Course with AI Integration that reconciliation work uses: cleaning references so two sides agree, match keys and lookups, ageing the breaks, PivotTables by type and owner, Power Query merges for files too large for formulas, and VBA for the daily routine.

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:

  • Build a match key from reference and amount with CONCAT, after TRIM, UPPER and SUBSTITUTE have made the two sides agree on spelling.
  • Match one side to the other with XLOOKUP or COUNTIFS, mark each line with no partner as a break with IFERROR, and list the open breaks with FILTER.
  • Age every break from its value date with TODAY and IFS, and summarise breaks by type, owner and age with PivotTables and slicers.
  • Cut a reference out of bank narrative text with FIND, MID, LEFT and RIGHT, so a payment can be matched even when its description is messy.
  • Join two large exports with a Power Query left join instead of thousands of lookup formulas, and pick up each day's files from a folder.
  • Run the repeat checks from a button with a VBA macro that loops through the rows, flags missing or non-numeric amounts and handles errors.

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.

    Matching practised on HR records: each item looked up on the other side by its reference, and a plain flag where nothing comes back, which is what a break is.

  • 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 Power Query half, up to the fact table: both sides imported and cleaned, set side by side with a left join, and the day's files picked up from a folder.

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

    The daily routine as code: a macro that loops down the rows, colours lines with missing or non-numeric amounts, and copes with errors, run from a button.

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

    Open breaks totalled by type, owner and age in a few clicks, with a slicer per account and a refresh when the day's results land.

Lessons

The lessons that matter, in course order.

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

Where to start

Lesson 22 Cleaning data in Excel

Begin at Lesson 22 if you already match items in Excel: clean references come before any match. If IF formulas are new, take Lesson 28 first. New to Excel? Begin at Lesson 1. If lookups and PivotTables are routine, go to Lesson 136 for VBA or to Lesson 153 for Power Query merges.

70 of 186 lessons · 5 h 43 min

03 Managing data in Excel: sort, filter and tables (Dunder Michael Paper employee database) 1 of 9 lessons · 13 min Cleaning before matching: TRIM for stray spaces, Paste Special values to freeze a day's results, and Find and Replace for characters that stop two references agreeing. The lesson uses an employee database.
  1. 22 Start here Cleaning data in Excel TRIM, Paste Special values and Find and Replace 13 min
04 Essential formulas and functions (IF, SUMIF and more) 9 of 13 lessons · 55 min Nested IF and IFS for ageing buckets, AND and OR for a line that matches on reference but not on amount, COUNT and COUNTA for completeness, COUNTIF and COUNTIFS to test whether a key exists on the other side, SUMIFS to compare totals, and date arithmetic for the age of a break.
  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
05 Text functions and data validation (enhancing data quality) 5 of 7 lessons · 25 min Text work that makes references comparable: CONCAT and the & operator for a key of reference and amount, TRIM and UPPER for consistent spelling, FIND, SEARCH and SUBSTITUTE for stray characters, Text to Columns for split narratives, and drop-down lists for each break's status.
  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
07 PivotTables and PivotCharts: data analysis made easy 10 lessons · 33 min PivotTables, slicers and PivotCharts for the open breaks by type, owner, account or age bucket, refreshed when the day's results replace 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 find each item on the other side, IFERROR to mark the ones with no partner, LEFT, RIGHT and MID to cut a reference out of narrative text, EOMONTH and NETWORKDAYS for value dates, and UNIQUE, FILTER and SORTBY for the list of open breaks.
  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
14 VBA and macros in Excel: FormatWizard 17 lessons · 1 h 16 min FormatWizard: recorded macros for the routine steps, then VBA with variables, If statements and loops that walk a sheet, flag missing values, test amounts with IsNumeric, colour each problem and stop safely on an error, ready to run from a button each morning.
  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
15 SalesFusion: advanced data tools and a Power Query modelling system 16 of 21 lessons · 1 h 28 min Power Query for files too large for lookups: import CSV files, clean columns and text, fix data types, add conditional columns, check totals with Group By, set one side beside the other with a left join, reuse the steps, append tables and combine a folder of daily files. 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
  12. 164 Reuse a Power Query pipeline Duplicate queries for a new year of sales data 5 min
  13. 165 Append queries in Power Query Combine 2024 and 2025 sales tables 3 min
  14. 166 From Folder in Power Query Combine every sales file in a folder automatically 9 min
  15. 167 From Folder automation in Power Query Profit metrics and fiscal year columns 7 min
  16. 168 Build a sales fact table in Power Query Merge lookups and append history 6 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.

What is a good Excel course for reconciliation analysts?

The Complete Excel Course with AI Integration from Coding Liquids teaches the Excel behind a daily reconciliation: references cleaned and joined into match keys, items found on the other side with XLOOKUP or COUNTIFS, breaks aged with IFS and summarised by type and owner in PivotTables, Power Query merges for large files, and a VBA macro that runs the checks from a button. The 70 lessons here come to 5 hours 43 minutes of the course's 16 hours.

Does it teach reconciliation software or accounting?

No. The Complete Excel Course with AI Integration teaches Excel only: reconciliation platforms, payment systems and the accounting behind a break are not covered, and no lesson reconciles a bank statement to a ledger. It teaches the steps a reconciliation in Excel is built from: cleaning both sides, matching them with lookups or a Power Query merge, ageing what is left and reporting it.

Should I match with lookup formulas or with Power Query?

Both are in The Complete Excel Course with AI Integration. XLOOKUP, INDEX and MATCH and COUNTIFS show each match in a formula you can audit cell by cell. For exports too large for thousands of formulas, a Power Query merge joins the two sides with a left join, leaves an empty column where an item has no partner, and repeats every step on the next file. The lessons apply both to sales and HR data.

Can a macro run my daily reconciliation?

The repeat checks, yes. FormatWizard in The Complete Excel Course with AI Integration starts with recorded macros run from a button, then writes VBA that loops through a sheet, flags missing values, tests amounts with IsNumeric, colours each problem and handles errors in one subroutine. The lessons check a sales sheet; adapting the loop to both sides of your own reconciliation is left to you.

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