Start learning
My learning

The Complete Excel Course with AI Integration

Excel course for teachers and lecturers

For teachers, lecturers and tutors who keep marks, attendance and results in Excel. The course works on business data, not school data, so this page picks out the lessons whose skills carry straight to a mark book: totals, averages and weighting, IF and IFS for results and grades, counts for attendance, cleaning class lists, PivotTables and charts of results, and AI that drafts formulas you then check.

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 mark book that totals and averages each pupil's marks, weights assessments with references held fixed, and shows each result as a percentage.
  • Turn a mark into pass or fail with IF and into a grade with IFS, and count the pupils in each grade with COUNTIFS.
  • Track attendance with COUNTIF, and highlight low attendance or missing work with conditional formatting.
  • Clean and split a class list exported from the school system with TRIM, Remove Duplicates, Flash Fill and Text to Columns, and keep the names in view with frozen panes.
  • Analyse results by class, topic or question with PivotTables and charts, including a histogram of the spread of marks.
  • Draft a formula with Copilot or ChatGPT, then check it on a few pupils whose results you know before you rely on it.

Projects

What you will build.

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

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

    Charts for results, practised on marketing data: a column chart per class, a line for progress across a term, and a histogram for the spread of marks.

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

    Results by class, topic or question in a few clicks, built with the steps the lessons use on sales orders, with a slicer for each class.

  • Claude AI in Excel fixing a formula: the Claude panel explains the mistake in a unit cost column and edits the cells, beside the profit percentage column.

    Project 14

    Agentic AI in Excel

    AI finds and fixes the formula; you check it before it counts.

    Formulas drafted by Copilot or ChatGPT and checked with a QA checklist; in a mark book that means trying a new formula on a few pupils whose results are already known.

  • Project 03

    Sales Goal Tracker

    IF, IFS, AND, OR, COUNTIF and COUNTIFS on sales figures; in a mark book the same functions give pass or fail, a grade band and the number of pupils in each band.

  • Project 02

    Dunder Michael Paper Company employee database

    A class list exported from the school system, cleaned the way the lessons clean employee records: names split, spaces trimmed, duplicates removed and the header row frozen.

Lessons

The lessons that matter, in course order.

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

Where to start

Lesson 5 Your first Excel spreadsheet

Begin at Lesson 5 if you have opened Excel but never built a sheet of your own. If you already total columns and copy formulas, go to Lesson 19 for class lists: that section, Managing data in Excel, is free by email with its practice files. For results and grades go to Lesson 29, where IF turns a mark into pass or fail. New to Excel altogether? Begin at Lesson 1, with the Excel window.

Get the free section by email

61 of 186 lessons · 5 h 2 min

01 Microsoft Excel fundamentals: building a foundation from scratch 8 of 13 lessons · 37 min The groundwork of a mark book: entering data, percentage formats, cell formatting, arithmetic, SUM and AutoSum, absolute references that keep a weight fixed as a formula is copied down a class, percentages, and errors such as a division by zero. The lessons build a personal budget.
  1. 5 Start here Your first Excel spreadsheet Cells, rows, columns and data types in a budget 8 min
  2. 6 Number formatting in Excel Currency, date and percentage formats for a budget 5 min
  3. 7 Cell formatting in Excel AutoFit column width, the #### error and cell styles 6 min
  4. 8 Basic maths in Excel Add, subtract, multiply and divide with formulas 3 min
  5. 9 SUM and AutoSum in Excel Total income, expenses and savings with MAX 6 min
  6. 10 Relative vs absolute references in Excel Dollar signs and mixed references 3 min
  7. 11 Calculating percentages in Excel Each expense as a percentage of income 2 min
  8. 12 Common Excel errors and how to fix them DIV0, NAME, VALUE errors and IFERROR 4 min
02 Project: personal budget tracker 2 of 5 lessons · 7 min AVERAGE beside SUM for a pupil or a class, and conditional formatting with highlight rules, data bars and icon sets to mark low attendance or a missing piece of work.
  1. 14 SUM and AVERAGE in Excel Personal budget tracker totals and average expense 4 min
  2. 15 Conditional formatting in Excel Highlight rules, data bars and icon sets 3 min
03 Managing data in Excel: sort, filter and tables (Dunder Michael Paper employee database) 9 lessons · 1 h 1 min Class lists brought under control: Excel Tables, sorting by surname or result, filtering by class, cleaning an export with TRIM and Find and Replace, removing duplicates, splitting names with Flash Fill and Text to Columns, freezing the names in view and printing. The lessons use an employee database.
  1. 19 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) 10 of 13 lessons · 59 min TRUE and FALSE tests, IF for pass or fail, nested IF and IFS for grade bands, AND and OR for two conditions such as a mark and attendance together, COUNT and COUNTA for missing marks, and COUNTIF, COUNTIFS, SUMIF and SUMIFS for attendance and results by class.
  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
06 Data visualisation: charts and graphs 9 lessons · 45 min Charts of results: the right chart for the question, column charts by class, a line for progress over a term, pie and combo charts, formatting, sparklines beside each pupil, and histograms and box and whisker charts for the spread of marks. 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 results by class, topic, question or date, refreshed when new marks are added. 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 4 of 12 lessons · 15 min Lookups that bring marks from another sheet or export by pupil number (VLOOKUP, XLOOKUP), with IFERROR for a pupil who has no mark yet. The lessons use an HR dataset.
  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
10 Boosting productivity with AI in Excel (Copilot, ChatGPT and Claude) 8 of 17 lessons · 44 min AI with care: what Copilot, ChatGPT and Claude can and cannot do, preparing a table, writing clear prompts, and letting Copilot build summaries, PivotTables, charts, highlights and formula columns that you verify. The lessons use sales data.
  1. 88 AI in Excel What Copilot, ChatGPT and Claude can and cannot do 8 min
  2. 89 Prepare data for Copilot in Excel Tables, headers and clean structure 6 min
  3. 90 Copilot executive summary in Excel Summarise a dataset in seconds with AI 5 min
  4. 91 Copilot prompts for Excel Weak vs strong prompts and the PRO prompt pattern 5 min
  5. 92 PivotTables with Copilot Build an Excel PivotTable from a prompt 5 min
  6. 93 Charts with Copilot and ChatGPT Create and choose the right Excel chart 6 min
  7. 94 Conditional formatting with Copilot and ChatGPT Highlight problems in Excel 4 min
  8. 95 Excel formulas with AI Copilot and ChatGPT write formula columns you verify 6 min
10 Boosting productivity with AI in Excel (Copilot, ChatGPT and Claude) 1 of 17 lessons · 2 min A short QA checklist for anything an AI returns, the habit behind trying a new formula on a few known results first.
  1. 99 Check AI output in Excel A quick QA checklist for Copilot results 2 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 teachers and lecturers?

Yes. The Complete Excel Course with AI Integration from Coding Liquids is not built around school data, but a teacher's mark book uses its lessons throughout: SUM, AVERAGE and percentages, IF for pass or fail, IFS for grades, COUNTIFS for attendance and grade counts, PivotTables and charts of results, and cleaning class lists. This page lists 61 lessons, 5 hours 2 minutes of the course's 16 hours.

Can I build a gradebook with weighted marks in Excel?

Yes, from lessons in The Complete Excel Course with AI Integration, though it has no gradebook project and no school dataset. Each mark times its weight, with the weights held in fixed cells by absolute references, then summed and shown as a percentage, gives the weighted result; IFS turns that into a grade, and COUNTIFS counts each grade by class. The examples use budgets and sales data.

Does it cover Google Sheets or a school management system?

No. The Complete Excel Course with AI Integration teaches Microsoft Excel only: Google Sheets, school information systems and assessment statistics are not covered. It starts from data already in an Excel sheet, so a class list exported from the school system is cleaned with the lessons on this page before any marks go in.

Can AI write my mark book formulas?

It can draft them. The Complete Excel Course with AI Integration shows Copilot and ChatGPT writing formula columns that you then verify, prompts that get a better answer, and a short QA checklist for anything an AI returns. Try a drafted formula on a few pupils whose results you already know before copying it down the class; the lessons use sales data, not marks.

Can I see how a class is doing across a term?

Yes. With the charts The Complete Excel Course with AI Integration teaches, a line chart shows a pupil's or a class's marks over time, a column chart compares classes, sparklines draw each pupil's trend in a cell beside the marks, and a histogram shows how the marks are spread. A PivotTable grouped by date or by topic gives the figures behind them.

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