Start learning
My learning

The Complete Excel Course with AI Integration

Excel course for actuarial analysts

For actuarial analysts and students who prepare data, analyse experience and build projection models in Excel. This page picks out the parts of The Complete Excel Course with AI Integration that actuarial work uses: checks on policy and claims data, bands and conditional totals for experience studies, PivotTables, year-by-year projections with growth and inflation, VBA for repeatable checks, and Power Query with a data model for extracts.

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:

  • Check policy and claims extracts for gaps and duplicates with COUNT, COUNTA and COUNTIFS before any analysis starts.
  • Build an experience analysis by age band, duration and product with IFS bands, SUMIFS and COUNTIFS, and PivotTables grouped by date.
  • Project values year by year with FV, monthly rates, values after inflation and IF, using mixed references that copy across a projection table.
  • Rerun a projection under tougher assumptions and compare the results, as the WealthFlow stress test does.
  • Write VBA that loops through an extract, flags missing or non-numeric values, colours each one and stops safely on an error.
  • Combine data extracts with Power Query, unpivot wide tables into long ones, and summarise them with DAX measures in a data model.

Projects

What you will build.

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

  • SIP and SWP retirement simulator in Excel (WealthFlow): withdrawal and investment plans beside four charts of corpus, withdrawals and portfolio growth over 30 years.

    Project 10

    WealthFlow

    A retirement plan that shows how long the money lasts.

    A projection built year by year with age columns, rates turned from annual to monthly, values deflated for inflation, and the same model rerun under harsher assumptions.

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

    Experience laid out by age band, policy year and product with the steps the lessons use on sales orders, grouped by date and filtered with slicers.

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

    Data checks as code: written to check a sales sheet, the same VBA loops through a policy or claims extract, flags missing and non-numeric values, colours them, prints to the Immediate window and handles errors.

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

    Extracts combined into one model, practised on sales files: merged on a key, appended across years, wide columns unpivoted, and measures written in DAX.

Lessons

The lessons that matter, in course order.

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

Where to start

Lesson 31 Nested IF and IFS in Excel

Begin at Lesson 31 if you already work in Excel every day: experience studies start with bands and conditional counts. The lesson on mixed references, Lesson 10, sits earlier in the course and can be watched on its own. New to Excel? Begin at Lesson 1. If PivotTables are routine, go to Lesson 125 for projections or to Lesson 136 for VBA.

68 of 186 lessons · 5 h 55 min

01 Microsoft Excel fundamentals: building a foundation from scratch 1 of 13 lessons · 3 min Relative, absolute and mixed references: the difference between a projection table that copies cleanly across ages and years and one that breaks. The lesson uses a personal budget.
  1. 10 Relative vs absolute references in Excel Dollar signs and mixed references 3 min
04 Essential formulas and functions (IF, SUMIF and more) 7 of 13 lessons · 45 min Nested IF and IFS for age and duration bands, AND and OR for records that fail two checks, COUNT and COUNTA for completeness, and SUMIFS and COUNTIFS for exposure and claim counts by band, the inputs to an experience study.
  1. 31 Start here 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
06 Data visualisation: charts and graphs 1 of 9 lessons · 6 min Histograms and box and whisker charts for the spread of claim sizes or ages, beside treemaps and sunbursts. The lesson charts marketing data.
  1. 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 experience by age band, policy year, product or cause, grouped by date and refreshed when a new 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
13 WealthFlow investment and retirement simulator 11 lessons · 1 h 5 min WealthFlow as a projection model: compounding, annual rates turned monthly, FV with yearly step-ups, values in today's money after inflation, withdrawals from a fund with a stress test, then a simulator with year and age columns and IF formulas, read through scenarios. The model is a personal retirement plan; its year-by-year layout is the shape of a cash-flow projection.
  1. 125 SIP and SWP calculators What is an SIP, lump sum and compounding 6 min
  2. 126 SIP and SWP calculators What is an SWP, inflation and FIRE 6 min
  3. 127 WealthFlow SIP model in Excel Input assumptions, XIRR and monthly rate 6 min
  4. 128 WealthFlow SIP model in Excel Step-up SIP table and FV formulas 6 min
  5. 129 WealthFlow SIP model in Excel Real portfolio value, inflation and charts 7 min
  6. 130 WealthFlow SWP model in Excel Real rate, corpus and yearly withdrawals 6 min
  7. 131 WealthFlow SWP model in Excel Real corpus, stress test and SWP chart 5 min
  8. 132 Retirement simulator in Excel WealthFlow inputs, year and age columns 5 min
  9. 133 Retirement simulator in Excel SIP, withdrawal and portfolio formulas with IF 7 min
  10. 134 Retirement simulator in Excel Reading the results and retirement scenarios 5 min
  11. 135 Retirement simulator in Excel Claude builds the WealthFlow dashboard 5 min
14 VBA and macros in Excel: FormatWizard 17 lessons · 1 h 16 min FormatWizard, from the macro recorder to a checking routine written by hand: the VBA editor and its security settings, variables, If tests and For Next loops that step through each record, blanks marked, IsNumeric for values that should be numbers, failures shaded, Debug.Print while testing, and one subroutine that reports an error instead of crashing.
  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 21 lessons · 2 h 7 min SalesFusion read for policy and claims extracts: Power Query imports and cleans files, fixes data types, adds date fields, merges on a key, appends years, combines a folder and unpivots wide columns into a long table; Power Pivot relates the tables and DAX measures summarise them. The lessons use sales files, and the Power Pivot lessons need Excel for Windows.
  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
  17. 169 Power Pivot Data Model in Excel Relationships and Diagram View 6 min
  18. 170 DAX measures in Power Pivot Total sales, margin percent and percent of total 10 min
  19. 171 PivotTable report from the Data Model Format the SalesFusion report in Excel 5 min
  20. 172 Interactive Excel dashboard Data model PivotTables, slicers and styling 10 min
  21. 173 Unpivot columns in Power Query Turn wide month columns into a long table 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.

Which Excel course is useful for actuarial analysts?

The Complete Excel Course with AI Integration from Coding Liquids covers the Excel around actuarial work: data checks with COUNTIFS and VBA, experience analyses by band in PivotTables, year-by-year projections with FV, inflation and IF in the WealthFlow model, scenario reruns, and Power Query with DAX for combining extracts. The 68 lessons here come to 5 hours 55 minutes of the course's 16 hours.

Does the course teach actuarial methods or software?

No. The Complete Excel Course with AI Integration teaches Excel, not actuarial science: actuarial mathematics, mortality tables, reserving and pricing methods, and actuarial software are not covered, and the financial functions its lessons name are FV and XIRR. What it gives is the spreadsheet craft: clean data, banded totals, projection tables, VBA checks and Power Query.

Can I build a projection model in Excel with this course?

A simple one, yes. WealthFlow in The Complete Excel Course with AI Integration lays out a projection with one row per year and an age column, grows contributions with FV and a yearly step-up, turns values into today's money after inflation, switches from saving to withdrawing with IF, and is rerun under tougher assumptions. It projects a personal plan; mortality, decrements and valuation bases are not taught.

Can VBA automate checks on policy data?

Recorded macros come first in The Complete Excel Course with AI Integration, then VBA: variables and data types, a function that finds the last used row, For Next loops, If and Else tests, IsNumeric on number fields, Debug.Print for tracing and an error handler around the routine. Practised on a sales sheet, the same loop can test each policy record for the gaps and wrong types your data checks look for.

Can Power Query reshape a claims triangle?

It can turn one from wide to long. The Complete Excel Course with AI Integration teaches Unpivot Columns in Power Query, which turns wide month columns into a long table with one row per record and period; a triangle laid out by development period across the columns is reshaped the same way, ready for PivotTables. The lessons unpivot sales months, and reserving methods such as chain ladder are not taught.

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