Start learning
My learning

The Complete Excel Course with AI Integration

Excel course for insurance underwriters

For underwriters and underwriting assistants who rate risks, check submissions and watch a book's results in Excel. This page picks out the parts of The Complete Excel Course with AI Integration that underwriting work uses: lookups for rating sheets, rules that flag a referral, totals for loss ratios, PivotTables of exposure, and Power Query for the bordereaux and broker files that arrive each month.

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 rating sheet that fetches the base rate for a class and territory with XLOOKUP or INDEX and MATCH, and applies loadings as percentages held in fixed cells.
  • Flag submissions for referral when they break your rules, with IF, IFS, AND and OR, and list them for review with FILTER.
  • Work out loss ratios by line, segment and broker with SUMIFS, and colour the ones above your threshold with a highlight rule.
  • Summarise exposure, premium and claims by region, class or broker with PivotTables grouped by month and filtered with slicers.
  • Clean monthly bordereaux with Power Query and combine a folder of broker files into one table that refreshes.
  • Follow renewal dates with TODAY and date arithmetic, and pull the ones falling due into a list with FILTER and SORT.

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 lookups behind a rating sheet, practised on HR records: a value read from a table by row and column with a two-way lookup, and a clear message when a code is missing.

  • 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 clicks the lessons use on sales orders, applied to a bordereau: premium, claims and sums insured by line, region, class or broker, grouped by month, with a slicer per segment.

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

    Built on sales files, and the same steps fit bordereaux: monthly files from several brokers stacked into one table, codes merged to names, and a loss ratio measured in DAX as margin percent is.

  • Project 03

    Sales Goal Tracker

    Bands, conditions and totals on sales targets with IFS, AND, OR and SUMIFS: the functions a referral rule and a loss ratio by broker are written with.

Lessons

The lessons that matter, in course order.

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

Where to start

Lesson 28 Logical functions in Excel

Begin at Lesson 28 if you already enter data and write simple formulas: referral rules start with IF. The lessons on absolute references and percentages, Lesson 10 and Lesson 11, and on highlight rules, Lesson 15, sit earlier in the course and can be watched on their own. New to Excel? Begin at Lesson 1. If lookups and PivotTables are routine, go to Lesson 153, where Power Query begins.

60 of 186 lessons · 5 h 4 min

01 Microsoft Excel fundamentals: building a foundation from scratch 2 of 13 lessons · 6 min Absolute references that hold a base rate or a loading fixed while a premium formula is copied down, and percentages for loadings and discounts. The lessons use a personal budget.
  1. 10 Relative vs absolute references in Excel Dollar signs and mixed references 3 min
  2. 11 Calculating percentages in Excel Each expense as a percentage of income 2 min
02 Project: personal budget tracker 1 of 5 lessons · 3 min Highlight rules, data bars and icon sets, used to colour a loss ratio above a set level or a renewal that falls due this month. The lesson uses a personal budget.
  1. 15 Conditional formatting in Excel Highlight rules, data bars and icon sets 3 min
04 Essential formulas and functions (IF, SUMIF and more) 13 lessons · 1 h 17 min Underwriting rules as formulas: IF and nested IF or IFS for bands, AND and OR for a submission that breaks two rules, COUNT and COUNTA for missing fields, SUMIFS and COUNTIFS for premium, claims and policy counts by line and broker, and TODAY with date arithmetic for renewal dates.
  1. 28 Start here 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) 1 of 7 lessons · 4 min Drop-down lists for class, territory and cover, so each quote on a rating sheet uses a code the rate table holds.
  1. 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 exposure, premium and claims by region, class, line or broker, grouped by month and refreshed when the next bordereau 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 Lookups for rating (VLOOKUP, XLOOKUP with its two-way lookup for a rate by class and territory, INDEX and MATCH, IFERROR for an unknown code), CONCAT for a combined key, LEFT, RIGHT and MID for codes inside policy numbers, date functions, and FILTER, SORT and SORTBY for referral and renewal lists.
  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 21 lessons · 2 h 7 min SalesFusion, read for bordereaux: Power Query imports CSV files, cleans columns and text, fixes data types, merges a lookup table, appends one period to the next, combines a whole folder and unpivots wide month columns; Power Pivot relates the tables and DAX measures give ratios such as margin percent. 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 suits insurance underwriters?

The Complete Excel Course with AI Integration from Coding Liquids suits the spreadsheet side of underwriting: rating sheets that look up base rates by class and territory, referral rules written with IF, AND and OR, loss ratios by line and broker with SUMIFS, exposure summarised in PivotTables, and bordereaux cleaned and stacked with Power Query. This page lists 60 lessons, 5 hours 4 minutes of the course's 16 hours.

Does the course teach underwriting or actuarial pricing?

No. The Complete Excel Course with AI Integration teaches Excel, not underwriting: underwriting principles, actuarial pricing and catastrophe modelling are not covered, and no lesson builds a rating sheet. It teaches the parts one is made of: lookups that fetch a rate from a table, percentages for loadings held with absolute references, and rules that flag what needs referral.

Can Power Query tidy the bordereaux brokers send?

Yes. Each bordereau goes through the same recorded steps in The Complete Excel Course with AI Integration: import, remove and rename columns, trim text, set dates and numbers to the right type, merge broker codes to names, then append this month under last month. From Folder takes every file in a folder at once when the layouts match. The lessons use sales files; the Power Pivot data model built afterwards needs Excel for Windows.

How do I calculate loss ratios by broker in Excel?

With SUMIFS, which The Complete Excel Course with AI Integration teaches with several conditions: claims for a broker and line divided by the premium for the same broker and line gives the loss ratio, and IFERROR keeps a broker with no premium from showing an error. A PivotTable lays the totals out by broker and month, and a highlight rule colours any ratio above your threshold. The lessons total 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