Start learning
My learning

The Complete Excel Course with AI Integration

Excel course for bookkeepers

For bookkeepers and accounts assistants who keep a business's transactions in Excel, or tidy up what the accounting software exports. This page picks out the parts of The Complete Excel Course with AI Integration that this work leans on: clean lists, totals by account code and month, lookups for matching one list against another, date formulas for what is overdue, and PivotTables for the month's summary.

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:

  • Keep a cash book or an invoice list as an Excel Table with a total row, with account codes picked from a drop-down list so each one is entered the same way.
  • Clean a bank or card export: TRIM stray spaces, split a description column with Text to Columns or Flash Fill, and find duplicate rows before anything is posted.
  • Total sales, purchases and VAT or GST by account code and by month with SUMIFS, and count entries with COUNTIF and COUNTIFS.
  • Set one list against another with XLOOKUP, VLOOKUP or INDEX and MATCH, show what has no match with IFERROR, and list the leftovers with FILTER.
  • Work out how many days an invoice is overdue with TODAY and date arithmetic, and label it with IFS.
  • Summarise a month of transactions by account and by date with a PivotTable that refreshes when new rows are added.

Projects

What you will build.

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

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

    Month-end totals without typed formulas: rows grouped into months, totals by category, a slicer to pick one client or code, and Refresh when new transactions arrive.

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

    Practice for reconciliation work: find a record from one list in another, return a plain message when it is missing, and keep the gaps from showing as errors.

  • Project 02

    Dunder Michael Paper Company employee database

    The clean-up drill on an employee list: stray spaces trimmed, duplicate rows found, names split into their own columns. A bank or card export takes the same steps before its lines are coded.

  • Project 03

    Sales Goal Tracker

    Conditional totals and labels on sales targets. For a bookkeeper the same SUMIFS and COUNTIFS total a month by account code, and IFS labels an invoice by how late it is.

Lessons

The lessons that matter, in course order.

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

Where to start

Lesson 19 Excel tables

Begin at Lesson 19 if you already type entries and simple formulas into Excel: tidy lists come before everything else in bookkeeping. That section, Managing data in Excel, is free by email with its practice files: ask for it on the course page. New to Excel? Begin at Lesson 1. If SUMIFS and PivotTables are already routine, go to Lesson 67, where the lookups begin.

Get the free section by email

49 of 186 lessons · 4 h 9 min

03 Managing data in Excel: sort, filter and tables (Dunder Michael Paper employee database) 9 lessons · 1 h 1 min Transaction lists kept in order: Excel Tables with a total row, sorting and filtering, TRIM and Find and Replace for messy exports, Remove Duplicates, Flash Fill and Text to Columns, frozen headers and tidy printing. The lessons use an employee database; a bank export takes the same steps.
  1. 19 Start here Excel tables Create and format a table, structured references and total row 8 min
  2. 20 Sorting data in Excel Single and multi-level sort on an employee database 6 min
  3. 21 Filtering data in Excel AutoFilter, text and number filters on employee data 10 min
  4. 22 Cleaning data in Excel TRIM, Paste Special values and Find and Replace 13 min
  5. 23 Remove duplicates in Excel Find and delete duplicate rows in an employee list 6 min
  6. 24 Flash Fill in Excel Split first and last names automatically 3 min
  7. 25 Text to Columns in Excel Split data by delimiter into separate columns 6 min
  8. 26 Freeze panes in Excel Lock header rows and columns while scrolling 3 min
  9. 27 Printing in Excel Print area, page orientation, scaling and page layout 6 min
04 Essential formulas and functions (IF, SUMIF and more) 13 lessons · 1 h 17 min The formulas a bookkeeper writes every week: SUMIF and SUMIFS for totals by account code and month, COUNTIF and COUNTIFS for counting matches, IF and IFS for labels, COUNTA for completeness checks, and TODAY and MONTH for dates.
  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
05 Text functions and data validation (enhancing data quality) 5 of 7 lessons · 25 min Text functions for descriptions and references (CONCAT, TRIM, PROPER, FIND and SUBSTITUTE), the Text to Columns wizard for comma-separated lines, and drop-down lists that keep account codes consistent.
  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: a month of transactions totalled by category or by account, grouped by date, filtered with slicers and refreshed when rows are added. The lessons use sales orders; a cash book 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 for setting one list against another (VLOOKUP, XLOOKUP with its if-not-found value, INDEX and MATCH, IFERROR), LEFT, RIGHT and MID for references, EOMONTH for month ends, and UNIQUE, FILTER and SORT for lists such as unmatched items.
  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

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 bookkeepers?

Yes. The Complete Excel Course with AI Integration from Coding Liquids teaches the Excel that bookkeeping runs on: transaction lists kept clean, SUMIFS totals by account code and month, lookups that set one list against another, days overdue with TODAY, and PivotTables for the month. The 49 lessons on this page come to 4 hours 9 minutes of the course's 16 hours.

Does it teach double-entry bookkeeping or accounting software?

No. The Complete Excel Course with AI Integration teaches Excel only. Double entry, VAT and GST rules, and software such as QuickBooks, Xero, Sage or Tally are not covered. The course assumes you know your chart of accounts and starts from what you export to Excel: cleaning it, totalling it by code and month, and matching it against another list.

Can I reconcile a bank statement in Excel after this course?

No lesson in The Complete Excel Course with AI Integration reconciles a bank statement, but the functions a reconciliation is built from are taught: COUNTIF and COUNTIFS to see whether an amount appears in both lists, XLOOKUP with an if-not-found value, IFERROR, and FILTER to list the items left over. They are practised on employee and sales data.

Will it help me clean bank and card exports?

Yes. The cleaning lessons in The Complete Excel Course with AI Integration cover TRIM for stray spaces, Find and Replace, Text to Columns and Flash Fill for splitting one description column into several, FIND, MID and SUBSTITUTE for pulling a reference out of text, and Remove Duplicates. They use an employee list and sales data instead of bank files; the steps do not change.

Can I follow the bookkeeping lessons on a Mac?

Yes. The lessons picked out on this page work in Excel for Mac as well as for Windows. In The Complete Excel Course with AI Integration only the Power Pivot and Power Automate lessons need Windows, and none of them is on this path.

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