Start learning
My learning

The Complete Excel Course with AI Integration

Excel course for facilities managers

For facilities managers and coordinators using Excel for asset registers, maintenance plans and budgets. The Complete Excel Course with AI Integration uses no building data; this guide selects work order formulas with IF and SUMIFS, energy charts, site and trade PivotTables, asset and contract lookups and date functions, planned maintenance with TaskFlow, and the FinanceGuard budget report.

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:

  • Retrieve asset register details by asset ID using XLOOKUP, and assemble or extract site, floor and asset codes with CONCAT, LEFT and MID.
  • Build a preventive maintenance tracker that calculates the next due date from the last visit, records statuses, colours overdue visits and displays a Gantt-style timeline.
  • Calculate service contract notice deadlines, then use TODAY, FILTER and SORT to produce a list of those approaching.
  • Measure response times in working days with NETWORKDAYS, and summarise open work order counts by building and trade using COUNTIFS and PivotTables.
  • Show monthly energy and utility consumption in line, column and combo charts, with a separate sparkline for each meter.
  • Compare actual facilities spending with the budget, calculating variance and percent variance and applying colour-coded number formats.

Projects

What you will build.

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

  • Task tracker in Excel (TaskFlow): start and due dates, status, priority and a progress bar for each task, with working days left, days past due and unfinished high-priority tasks highlighted.

    Project 07

    TaskFlow

    One sheet that shows what is done, what is late and what is urgent.

    Use the tracker for a planned maintenance schedule and work order log, assigning job statuses and priorities, colouring missed visits, plotting a Gantt-style plan and calculating working days until each due date.

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

    Apply XLOOKUP to an asset register to retrieve location, make and last service by asset ID, then use FILTER and SORT to display contracts nearing their end.

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

    Present monthly energy and water consumption through column and line charts, compare consumption with cost in a combo chart on two axes, and give each meter a sparkline.

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

    Compare facilities spending with budget by cost heading and site, colour overspends and use the dashboard to focus on a chosen site.

Lessons

The lessons that matter, in course order.

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

Where to start

Lesson 29 The IF function in Excel

If lists and simple formulas are familiar, start with Lesson 29: IF and SUMIFS let a work order log report on its contents. For a first introduction to Excel, choose Lesson 1 and build towards these skills. If you already use formulas routinely, begin the maintenance tracker at Lesson 79.

57 of 186 lessons · 4 h 40 min

04 Essential formulas and functions (IF, SUMIF and more) 11 of 13 lessons · 1 h 4 min Turn the last service date into the next with date arithmetic. Use COUNTIF and COUNTIFS to count open work orders by building, SUMIF and SUMIFS to total spending by site and trade, nested IF and IFS to assign condition ratings, and IF with AND to identify jobs that are both urgent and overdue.
  1. 29 Start here The IF function in Excel Pass or fail results with a single condition 3 min
  2. 30 IF function practice in Excel Categorise sales data with IF formulas 6 min
  3. 31 Nested IF and IFS in Excel Customer value tiers with nested IF 6 min
  4. 32 Nested IF and IFS in Excel Sales performance labels with the IFS function 5 min
  5. 33 AND and OR with IF in Excel Flag high-value deals with multiple conditions 8 min
  6. 34 SUM, COUNT and COUNTA in Excel SUM with FILTER and counting numbers with COUNT 7 min
  7. 35 SUM, COUNT and COUNTA in Excel COUNTA, unique counts and completeness checks 4 min
  8. 36 SUMIF and COUNTIF in Excel Conditional sum and count with one criterion 8 min
  9. 37 SUMIFS and COUNTIFS in Excel Sum and count with multiple criteria 7 min
  10. 38 The Excel function library Insert Function dialog and Formula AutoComplete 5 min
  11. 39 Date and time functions in Excel TODAY, NOW, MONTH and date arithmetic 6 min
06 Data visualisation: charts and graphs 7 of 9 lessons · 34 min Give each meter a trend within its row using sparklines, and present monthly energy and utility use with column and line charts. The chart lessons also cover clear formatting and a combo chart on two axes, which suits consumption against cost.
  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
07 PivotTables and PivotCharts: data analysis made easy 10 lessons · 33 min Summarise work orders by month, site and trade, or desks and rooms in use by floor, using PivotTables. Group dates, add slicers and PivotCharts, and refresh the report after updating the helpdesk export. The teaching examples are sales orders; the same summarising approach applies to a work order export.
  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 10 of 12 lessons · 49 min Adapt the HR examples to an asset register: retrieve records by asset ID with VLOOKUP, XLOOKUP or INDEX and MATCH, and handle errors with IFERROR. Build and read site, floor and asset codes using CONCAT, LEFT and MID. TODAY and EOMONTH support contract end dates, NETWORKDAYS measures response times, and FILTER with SORT lists contracts ending soon.
  1. 68 VLOOKUP in Excel Find employee data across sheets with exact match 4 min
  2. 69 XLOOKUP in Excel Exact match, if not found and two-way lookup 6 min
  3. 70 IFERROR in Excel Handle lookup errors in VLOOKUP and XLOOKUP formulas 3 min
  4. 71 INDEX and MATCH in Excel A flexible alternative to VLOOKUP 6 min
  5. 72 CONCAT and TEXTJOIN in Excel Combine employee names and text with a delimiter 3 min
  6. 73 LEFT, RIGHT and MID in Excel Extract parts of a text code like DAL-SAL-0001 7 min
  7. 74 TODAY, EOMONTH and NETWORKDAYS in Excel Date calculations for HR data 5 min
  8. 75 UNIQUE function in Excel Extract a distinct list with dynamic arrays 6 min
  9. 76 FILTER function in Excel Dynamic array filtering with AND and OR criteria 5 min
  10. 77 SORT and SORTBY in Excel Sort data dynamically with formulas 4 min
09 Project: task automation with data validation and forms 9 lessons · 49 min Fit maintenance inspections and services into TaskFlow's task columns to create a planned maintenance schedule. Choose status and priority from drop-downs, highlight overdue visits, show a Gantt-style timeline and working days remaining, and protect the sheet.
  1. 79 TaskFlow project tracker in Excel Overview of the task tracker dashboard 4 min
  2. 80 Designing a task tracker in Excel TaskFlow layout, columns and headers 5 min
  3. 81 Data validation in Excel Status and priority drop-down lists in TaskFlow 8 min
  4. 82 Conditional formatting in Excel Highlight overdue and completed tasks 8 min
  5. 83 Gantt chart in Excel Build a project timeline with conditional formatting 6 min
  6. 84 Data bars and icon sets in Excel Visual priority and progress in TaskFlow 6 min
  7. 85 NETWORKDAYS and TODAY in Excel Days remaining and task deadlines in TaskFlow 5 min
  8. 86 Protect a sheet and lock cells in Excel Secure the TaskFlow tracker workbook 4 min
  9. 87 Testing an Excel task tracker Stress test TaskFlow with sample data 3 min
12 Project: FinanceGuard budget variance analyser (budget vs actual Excel dashboard) 10 lessons · 52 min Apply FinanceGuard's company budget structure to a facilities site budget. Budget and actual tables feed variance and percent variance calculations, with colours marking overspends. Present the results through PivotTables, KPI cards and a combo chart, using slicers to select a site or cost heading.
  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.

Which Excel course should facilities managers choose?

The Complete Excel Course with AI Integration from Coding Liquids teaches skills for the registers and trackers used in facilities work. Apply XLOOKUP to join asset records by asset ID, adapt TaskFlow into a planned maintenance schedule, and use FILTER with TODAY to list contract notice dates. Energy charts include a sparkline per meter, while FinanceGuard's budget against actual model fits a facilities budget. The course data covers tasks, HR records, sales and budgets. The selection on this page contains 57 lessons, taking 4 hours 40 minutes in total.

How can facilities managers use Excel to plan preventive maintenance?

A maintenance inspection or service can occupy a row in the TaskFlow tracker taught in The Complete Excel Course with AI Integration. Calculate its next due date by adding the service interval to the last visit through date arithmetic. TaskFlow provides status and priority drop-downs, highlighting for overdue items, a Gantt-style timeline created with conditional formatting, and working days remaining until each date, within a protected sheet. These rules show both approaching visits and missed ones.

How can facilities managers find maintenance contracts that need notice soon in Excel?

Start with the last day on which notice can be given. Using the date arithmetic taught in The Complete Excel Course with AI Integration, subtract the notice period in days from the contract end date to calculate that deadline. TODAY supports the calculation of days remaining; FILTER and SORT produce a date-ordered list of contracts with notice deadlines in the coming weeks. Conditional formatting can also colour those rows within the register.

How can facilities managers report energy and utility use with Excel?

The marketing and sales chart examples in The Complete Excel Course with AI Integration also apply to meter readings. Use column and line charts to present monthly consumption, a combo chart with two axes to compare use and cost, and a single-cell sparkline to show each meter's trend. SUMIFS adds up consumption by site and month, and dividing consumption by floor area calculates use per square metre.

Does the course teach facilities managers CAFM software or building regulations?

The Complete Excel Course with AI Integration teaches Excel, so it does not cover computer-aided facility management systems, building regulations, health and safety law or energy engineering. Its data sets do not represent buildings. The relevant workbooks support work alongside a CAFM system: asset registers, maintenance schedules, contract lists, energy figures and facilities budgets.

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