Start learning
My learning

The Complete Excel Course with AI Integration

Excel course for fund accountants

For fund accountants and fund administrators who agree positions, prepare NAV packs and allocate to investors in Excel. This page picks out the parts of The Complete Excel Course with AI Integration that fund work uses: lookups that agree holdings and cash to custodian files, SUMIFS and a waterfall for the NAV movement, the arithmetic of accruals and allocations, XIRR, recorded macros, and Power Query with a data model for broker files.

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:

  • Agree positions and cash to the custodian's statement with XLOOKUP and a key built with CONCAT, and list the differences with IFERROR and FILTER.
  • Explain the period's NAV movement with SUMIFS totals by type and a waterfall chart from opening to closing value.
  • Accrue a fee for the days in a period with date arithmetic, a rate held in a fixed cell by an absolute reference, and percentages.
  • Allocate income and expenses to investors by their share of units, with percentages and absolute references that copy cleanly down a register.
  • Work out an investor's money-weighted return from dated subscriptions, redemptions and closing value with XIRR.
  • Combine broker and custodian files with Power Query, and show each holding's weight with a percent of total DAX measure.

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.

    Agreeing positions, practised on HR records: each holding looked up in the custodian's list by its identifier, and IFERROR marking the ones that only one side holds.

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

    Holdings by asset class, country or account, and income by type, laid out with the steps the lessons apply to sales orders, with a slicer for each fund.

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

    One lesson of it matters here: XIRR, the rate of return on a series of dated amounts, which gives an investor's money-weighted return from subscriptions, redemptions and a closing value.

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

    Only its first steps: recorded macros, a button to run them and a macro-enabled workbook, enough to replay the formatting of a NAV pack each period.

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

    Broker and custodian files brought into one model, practised on sales files: cleaned, merged with a security list, stacked from a folder, and weighted with a percent of total measure.

Lessons

The lessons that matter, in course order.

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

Where to start

Lesson 36 SUMIF and COUNTIF in Excel

Begin at Lesson 36 if you already use Excel every day: SUMIFS is where NAV totals start. The lessons on absolute references and percentages, Lesson 10 and Lesson 11, sit earlier in the course and can be watched on their own. New to Excel? Begin at Lesson 1. If SUMIFS and XLOOKUP are routine, go to Lesson 153, where Power Query begins.

56 of 186 lessons · 4 h 34 min

01 Microsoft Excel fundamentals: building a foundation from scratch 2 of 13 lessons · 6 min Absolute references that hold a fee rate or an income total fixed while a formula is copied down a register, and percentages for each investor's share. 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
04 Essential formulas and functions (IF, SUMIF and more) 4 of 13 lessons · 25 min SUMIF and SUMIFS for positions, income and expenses by security, type and period, the function library for finding the rest, and date arithmetic with TODAY and MONTH for the days in an accrual period.
  1. 36 Start here SUMIF and COUNTIF in Excel Conditional sum and count with one criterion 8 min
  2. 37 SUMIFS and COUNTIFS in Excel Sum and count with multiple criteria 7 min
  3. 38 The Excel function library Insert Function dialog and Formula AutoComplete 5 min
  4. 39 Date and time functions in Excel TODAY, NOW, MONTH and date arithmetic 6 min
06 Data visualisation: charts and graphs 1 of 9 lessons · 5 min The waterfall chart, the shape of a NAV walk from opening value through subscriptions, redemptions, income, expenses and gains to closing value. The lesson charts business data.
  1. 55 Funnel, Pareto and waterfall charts in Excel Modern charts for business data 5 min
07 PivotTables and PivotCharts: data analysis made easy 10 lessons · 33 min PivotTables, slicers and PivotCharts for holdings by asset class, country or account and income by type, refreshed when the period's data replaces the old. 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 that agree each holding to the custodian's list (XLOOKUP, VLOOKUP, INDEX and MATCH, IFERROR for an item only one side holds), CONCAT for a key of account and security, LEFT, RIGHT and MID for parts of identifiers, EOMONTH and NETWORKDAYS for period ends, and FILTER and SORTBY for the list of differences.
  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
13 WealthFlow investment and retirement simulator 1 of 11 lessons · 6 min XIRR and the monthly rate, taught in the WealthFlow investment model: the function that turns dated amounts into a yearly rate of return.
  1. 127 WealthFlow SIP model in Excel Input assumptions, XIRR and monthly rate 6 min
14 VBA and macros in Excel: FormatWizard 5 of 17 lessons · 19 min The first steps of FormatWizard: recording a macro, relative references, running it from a button and saving a macro-enabled workbook, so the formatting of a recurring pack is replayed in one click.
  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
15 SalesFusion: advanced data tools and a Power Query modelling system 21 lessons · 2 h 7 min SalesFusion, with broker and custodian files in mind: each CSV loaded and tidied in Power Query with its types set, a lookup table merged in, one period stacked on the next and a whole folder taken in at once, then a Power Pivot model whose DAX measures report totals and the percent of total that gives each holding's weight. Sales files are used throughout, and Power Pivot needs 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 helps fund accountants?

The Complete Excel Course with AI Integration from Coding Liquids helps with the spreadsheet side of fund work: holdings and cash agreed to custodian files with XLOOKUP, NAV movements totalled with SUMIFS and shown in a waterfall chart, accruals and investor allocations with absolute references and percentages, returns with XIRR, and broker files combined in Power Query. The 56 lessons here come to 4 hours 34 minutes of the course's 16 hours.

Does it teach NAV calculation or fund accounting rules?

No. The Complete Excel Course with AI Integration teaches Excel, not fund accounting: NAV methodology, accounting standards for funds, valuation policies and fund administration systems are not covered, and no lesson builds a NAV pack. It teaches the Excel those packs are made of: SUMIFS totals, lookups against custodian data, percentages for allocations, a waterfall chart and Power Query for the files.

Can I work out an investor's return with XIRR?

Yes. The Complete Excel Course with AI Integration teaches XIRR in the WealthFlow investment model, where it turns a series of dated amounts into a yearly rate of return. Set out an investor's subscriptions, redemptions and closing value with their dates and the same function gives a money-weighted return. Time-weighted returns and performance standards are not taught.

How do I allocate income to investors in Excel?

With arithmetic The Complete Excel Course with AI Integration teaches early on: each investor's units divided by the total units give a percentage, an absolute reference holds that total fixed as the formula is copied down the register, and the share times the period's income or expense is the allocation. A SUM of the allocations should equal the total. The lessons practise this on a personal budget, not a fund.

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