Start learning
My learning

The Complete Excel Course with AI Integration

Excel course for quantity surveyors

For quantity surveyors and estimators who keep bills, valuations and cost reports in Excel. The Complete Excel Course with AI Integration has no construction data of its own; this page picks out the lessons a bill and a cost report are built from: quantity times rate, conditional totals by trade, lookups into a rate library, PivotTables, a protected sheet, and the FinanceGuard budget vs actual model for the cost plan.

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:

  • Set out a bill with quantity, unit, rate and amount columns, multiply quantity by rate, and hold a markup or an allowance in one cell with an absolute reference.
  • Bring each item's rate in from a rate library with XLOOKUP or INDEX and MATCH, and flag an item the library has no rate for with IFERROR.
  • Total a bill by trade and section with SUMIFS, count the items still unrated with COUNTIFS, and summarise the whole bill by trade in a PivotTable.
  • Keep a long bill readable: an Excel Table with a total row, sorting and filtering by trade, frozen headings and a print area that fits each section on the page.
  • Set the cost plan against cost to date by package, with variance and percent variance coloured by custom number formats, KPI cards and a combo chart.
  • Lock the formula cells and protect the sheet before a pricing sheet goes out to subcontractors for their rates.

Projects

What you will build.

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

  • Budget vs actual dashboard in Excel (FinanceGuard): total budget, actuals and variance cards above a monthly line chart and a column chart by department.

    Project 09

    FinanceGuard

    The budget vs actual report finance teams send every month.

    Read as a cost report: each package's cost plan against cost to date, overspends shown in their own colour, and a dashboard a project team can filter by package.

  • 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 a rate library needs: an item code finds its rate on another sheet, and a code with no match shows a flag instead of an error.

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

    Totals by trade, section or package from a long list of items, grouped and filtered with slicers, the way the course totals orders by region.

  • Project 03

    Sales Goal Tracker

    SUMIFS and COUNTIFS on a bill instead of a sales sheet: the value of each trade, the items still unrated, and variations approved or pending.

Lessons

The lessons that matter, in course order.

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

Where to start

Lesson 8 Basic maths in Excel

Begin at Lesson 8 if you can enter data and format cells: a bill starts with quantity times rate and an absolute reference. New to Excel? Begin at Lesson 1. If SUMIFS and XLOOKUP are already daily tools, go to Lesson 115, where FinanceGuard and the cost plan against actual begin.

46 of 186 lessons · 3 h 40 min

01 Microsoft Excel fundamentals: building a foundation from scratch 5 of 13 lessons · 19 min The arithmetic of a bill: quantity times rate, totals with SUM, an absolute reference that holds a markup in one cell, percentages, and IFERROR for a division that fails. The lessons build a personal budget; a bill takes the same formulas.
  1. 8 Start here Basic maths in Excel Add, subtract, multiply and divide with formulas 3 min
  2. 9 SUM and AutoSum in Excel Total income, expenses and savings with MAX 6 min
  3. 10 Relative vs absolute references in Excel Dollar signs and mixed references 3 min
  4. 11 Calculating percentages in Excel Each expense as a percentage of income 2 min
  5. 12 Common Excel errors and how to fix them DIV0, NAME, VALUE errors and IFERROR 4 min
03 Managing data in Excel: sort, filter and tables (Dunder Michael Paper employee database) 3 of 9 lessons · 24 min A bill kept as an Excel Table, with a total row and structured references, sorted by trade and filtered to one section at a time.
  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
03 Managing data in Excel: sort, filter and tables (Dunder Michael Paper employee database) 2 of 9 lessons · 8 min Frozen headings for a long bill, then a print area, orientation and scaling so each section prints as a clean page for the tender documents.
  1. 26 Freeze panes in Excel Lock header rows and columns while scrolling 3 min
  2. 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 IF, IFS, AND and OR to flag an item with no rate or a variation still pending, then SUMIF, SUMIFS, COUNTIF and COUNTIFS to total and count by trade, section and status. The lessons use sales figures.
  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
07 PivotTables and PivotCharts: data analysis made easy 10 lessons · 33 min PivotTables over a whole bill or a cost report: amounts by trade, section or package, slicers to pick one, PivotCharts, and a refresh when quantities change. The lessons use sales orders; a bill kept as a list 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 5 of 12 lessons · 21 min Lookups into a rate library: VLOOKUP, XLOOKUP and INDEX and MATCH fetch an item's rate by its code, and IFERROR shows a flag where the library has none. The lessons look up employee records.
  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
09 Project: task automation with data validation and forms 1 of 9 lessons · 4 min Locked cells and sheet protection, so a pricing sheet or a valuation leaves your hands with its formulas safe from a stray edit.
  1. 86 Protect a sheet and lock cells in Excel Secure the TaskFlow tracker workbook 4 min
12 Project: FinanceGuard budget variance analyser (budget vs actual Excel dashboard) 10 lessons · 52 min FinanceGuard, read as a cost plan against cost to date: budget and actual tables, a model built on SUMIFS, variance and percent variance by package, custom number formats, KPI cards, a combo chart and slicers.
  1. 115 FinanceGuard budget vs actual dashboard Excel project overview 3 min
  2. 116 Excel tables for budget and actual data Organise the FinanceGuard datasets 4 min
  3. 117 Budget vs actual model in Excel SORT, UNIQUE, VSTACK, LET, TEXTSPLIT, SUMIFS 8 min
  4. 118 Budget variance in Excel Calculate variance and percent variance 4 min
  5. 119 Custom number formats in Excel Show budget variances with colours and signs 2 min
  6. 120 PivotTables for budget vs actual The FinanceGuard pivot workspace 8 min
  7. 121 KPI cards in Excel Summary cards for a budget variance dashboard 4 min
  8. 122 Budget vs actual charts in Excel Combo chart with secondary axis for variance 5 min
  9. 123 Slicers and PivotCharts in Excel Interactive FinanceGuard budget dashboard 4 min
  10. 124 Finish the FinanceGuard dashboard in Excel Final polish and variance insights 10 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 quantity surveyors and estimators?

Yes. The Complete Excel Course with AI Integration from Coding Liquids is not a quantity surveying course, but the Excel that bills and cost reports rest on is in it: quantity times rate with absolute references, SUMIFS totals by trade, XLOOKUP into a rate library, PivotTables, protected sheets, and FinanceGuard, a budget vs actual model that serves as a cost plan against cost to date. The 46 lessons on this page take 3 hours 40 minutes.

Can I build a bill of quantities in Excel with this course?

The Excel of one, yes, though no lesson builds a bill. With what The Complete Excel Course with AI Integration teaches, you multiply quantity by rate, hold a markup in one cell with an absolute reference, fetch rates from a library with XLOOKUP, flag a missing rate with IFERROR, total each trade with SUMIFS and keep the bill as a Table with a total row. The lessons use budgets, sales orders and employee records; a bill takes the same steps.

Does it teach measurement standards or take-off from drawings?

No. The Complete Excel Course with AI Integration teaches Excel, not quantity surveying. Measurement standards such as NRM, SMM7, CESMM or IS 1200, quantity take-off from drawings, rate analysis and estimating software are not taught. The course starts once quantities and rates are in a spreadsheet, and makes the totals, checks and reports built on them quicker to produce.

How would I compare a cost plan with actual cost by package?

With the FinanceGuard section of The Complete Excel Course with AI Integration. It sets a budget table against an actuals table, totals both with SUMIFS, works out variance and percent variance, and colours an overspend with a custom number format, then adds KPI cards, a combo chart and slicers. Use your packages as its categories and the same model reports cost plan against cost to date.

Can I stop subcontractors overwriting the formulas in a tender sheet?

Yes. Lesson 86 in The Complete Excel Course with AI Integration covers locked cells and sheet protection: the formula cells are locked, the sheet is protected, and only the cells you leave unlocked, such as the rates column, take an entry. It is taught on a task tracker, and works the same way on a pricing sheet.

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