Start learning
My learning

The Complete Excel Course with AI Integration

Excel course for credit analysts and loan officers

For credit analysts and loan officers who assess borrowers, grade applications and report on a loan book in Excel. This page picks out the parts of The Complete Excel Course with AI Integration that lending work uses: the arithmetic behind ratios, rules that grade an application, arrears buckets totalled by branch and product, PivotTables of the book, lookups that check repayments against what was due, a pipeline log and charts for the credit committee.

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:

  • Work out a borrower's ratios side by side with formulas that keep their references when copied, and show a clean result instead of #DIV/0! with IFERROR.
  • Give each application a grade from your thresholds with IFS, and flag files that break two limits at once with AND and OR for referral.
  • Sort loans into arrears buckets with TODAY and IFS, and total balances and counts by bucket, branch and product with SUMIFS and COUNTIFS.
  • Report the loan book by branch, product and disbursement month with PivotTables, slicers and PivotCharts that refresh with each new extract.
  • Check repayments received against what was due with XLOOKUP or INDEX and MATCH, and list the loans behind, largest first, with FILTER and SORTBY.
  • Keep the application pipeline in a log with drop-down statuses and highlights, and show it to the credit committee with a funnel, a line and a combo chart.

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.

    The lookups behind a repayment check, practised on HR records: a value found by its key on another sheet, a plain mark where nothing is found, and EOMONTH and NETWORKDAYS for due dates.

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

    The loan book cut by branch, product and arrears bucket in a few clicks, grouped by month of disbursement, with a slicer for each product.

  • Nine Excel chart types from the Marketing Engagement Dashboard: combo, bar, funnel, Pareto, waterfall, histogram, box and whisker, sunburst and treemap.

    Project 04

    Marketing Engagement Dashboard

    Nine charts, each picked for the question it answers.

    A credit committee pack in charts, practised on marketing data: a line for the book's growth, a combo for balances against arrears, a funnel for the pipeline.

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

    A task tracker that serves as an application log from receipt to disbursement: a status picked from a drop-down list, and files waiting past their target date marked in colour.

  • Project 03

    Sales Goal Tracker

    Thresholds and conditions on sales targets with IFS, AND, OR and COUNTIFS; at a credit desk the same formulas grade applications and count the ones sent for referral.

Lessons

The lessons that matter, in course order.

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

Where to start

Lesson 8 Basic maths in Excel

Begin at Lesson 8 if you can enter figures in Excel: ratios start with plain arithmetic and absolute references. If those are routine, go to Lesson 28, where grading starts with IF. New to Excel? Begin at Lesson 1. If formulas and PivotTables are daily tools, go to Lesson 67 for lookups and the arrears list, or to Lesson 48 for the committee charts.

53 of 186 lessons · 4 h 12 min

01 Microsoft Excel fundamentals: building a foundation from scratch 5 of 13 lessons · 19 min Ratios are arithmetic: division and SUM, absolute references that keep a denominator fixed while a formula is copied across borrowers, percentages, and IFERROR for the #DIV/0! that a zero in the figures produces. The lessons use a personal budget.
  1. 8 Start here Basic maths in Excel Add, subtract, multiply and divide with formulas 3 min
  2. 9 SUM and AutoSum in Excel Total income, expenses and savings with MAX 6 min
  3. 10 Relative vs absolute references in Excel Dollar signs and mixed references 3 min
  4. 11 Calculating percentages in Excel Each expense as a percentage of income 2 min
  5. 12 Common Excel errors and how to fix them DIV0, NAME, VALUE errors and IFERROR 4 min
04 Essential formulas and functions (IF, SUMIF and more) 13 lessons · 1 h 17 min Grading and arrears as formulas: IF and IFS for a grade from thresholds, AND and OR for files that break two limits, COUNT and COUNTA for missing figures, SUMIFS and COUNTIFS for balances by bucket, branch and product, TODAY with date arithmetic for days past due, and a Ninja Challenge to practise them.
  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
06 Data visualisation: charts and graphs 9 lessons · 45 min Charts for the credit committee: which chart answers which question, column and line charts for the book over time, combo charts with a secondary axis for balances against the arrears rate, sparklines beside each branch, a funnel for the pipeline and a histogram for loan sizes. The lessons chart marketing data.
  1. 48 Choosing the right Excel chart Column, line, pie, combo and modern chart types 4 min
  2. 49 Column charts in Excel Insert, format and label a column chart 5 min
  3. 50 Line charts in Excel Show trends over time with a line chart 4 min
  4. 51 Pie charts in Excel Show proportions and parts of a whole 4 min
  5. 52 Combo charts in Excel Column and line chart with a secondary axis 4 min
  6. 53 Formatting charts in Excel Titles, data labels, colours and chart styles 7 min
  7. 54 Sparklines in Excel Line, column and win-loss mini charts in cells 5 min
  8. 55 Funnel, Pareto and waterfall charts in Excel Modern charts for business data 5 min
  9. 56 Histogram, box and whisker, treemap and sunburst Advanced Excel chart types 6 min
07 PivotTables and PivotCharts: data analysis made easy 10 lessons · 33 min PivotTables, slicers and PivotCharts for the loan book by branch, product, officer or month of disbursement, refreshed when the next extract replaces the data. The lessons use sales orders; a loan listing works the same way.
  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 Lookups that set repayments received against what was due (VLOOKUP, XLOOKUP, INDEX and MATCH, IFERROR), a two-way lookup for a limit by grade and product, LEFT, RIGHT and MID for branch codes inside account numbers, EOMONTH and NETWORKDAYS for due dates, and FILTER with SORTBY for the loans in arrears, largest first.
  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 The first part of TaskFlow, used as a pipeline log: one row per application, a status chosen from a drop-down list, and highlights for files that wait past their target date or are complete.
  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

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 credit analysts and loan officers?

Yes. The Complete Excel Course with AI Integration from Coding Liquids covers the Excel a lending desk uses: ratio arithmetic with absolute references and IFERROR, grades from thresholds with IFS, referral flags with AND and OR, arrears buckets and balances by branch with SUMIFS, the loan book in PivotTables, repayments checked with XLOOKUP, and charts for the credit committee. This page lists 53 lessons, 4 hours 12 minutes of the course's 16 hours.

Will it teach me how to analyse a borrower's accounts?

It teaches the spreadsheet half. The Complete Excel Course with AI Integration covers the arithmetic, percentages and absolute references that ratios are built from, and IFERROR for a zero denominator, but not which ratios to use or how to read financial statements. Credit risk models, scorecards, lending regulation and loan amortisation with PMT and IPMT are not taught; its financial functions, FV and XIRR, belong to the savings and investment lessons.

Can I build a portfolio at risk report in Excel?

Yes, with functions The Complete Excel Course with AI Integration teaches, though no lesson builds this report. TODAY less each instalment's due date gives days past due; IFS sorts the loans into buckets such as current, up to 30 days, 31 to 90 and over 90; SUMIFS adds the outstanding balance in each bucket by branch and product, and the balance more than 30 days late divided by the whole book is the share at risk. A PivotTable with slicers then shows it month by month, where each extract is kept with its month.

Can Excel grade loan applications against a lender's own thresholds?

Yes. In The Complete Excel Course with AI Integration IFS returns the first grade whose test is true, so thresholds written from strongest to weakest give each application a grade; AND and OR inside IF mark a file that breaks two limits for referral; and the two-way lookup in the XLOOKUP lesson fetches the limit for a grade and a product from a table. The lessons use sales tiers and HR records; your own credit policy supplies the numbers.

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