Start learning
My learning

The Complete Excel Course with AI Integration

Excel course for maintenance planners

For maintenance planners who plan preventive work, log breakdowns and run the backlog in Excel. The Complete Excel Course with AI Integration has no maintenance data of its own; this page picks out the lessons that carry the job: date functions for due dates, conditional totals for failures and repair hours, lookups for spares, the Pareto chart, PivotTables, the TaskFlow tracker for work orders, and recorded macros for the weekly list.

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:

  • Plan preventive maintenance by asset and interval, with next due dates from date arithmetic and overdue tasks flagged with IF and TODAY.
  • Log breakdowns and repair times, and count failures and total repair hours by asset and month with COUNTIFS, SUMIFS and PivotTables.
  • Work out MTBF and MTTR by asset from conditional totals, with IFERROR for an asset that has not failed.
  • Track the work order backlog with status and priority drop-downs, overdue orders in colour and working days to each due date.
  • Look up spare parts stock and asset details by code with XLOOKUP, and split asset codes into their parts with LEFT, MID and RIGHT.
  • Produce the weekly work list with FILTER and SORTBY, or with a recorded macro run from a button.

Projects

What you will build.

The course has 14 projects. These five 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.

    Built for tasks, it holds work orders with a priority, an owner and a due date, overdue ones in colour, and the timeline showing what is planned for the week.

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

    Failures and repair hours by asset and month from the breakdown log, with slicers for one area or one asset type.

  • 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 maintenance KPIs: columns for breakdowns by asset, lines for MTBF over the months, and a Pareto of failure causes.

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

    Spares and asset details looked up by code, and asset codes split into site, type and number with LEFT, MID and RIGHT.

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

    The first, recorded part of the project: the steps that turn your plan into next week's work list, kept as a macro on a button.

Lessons

The lessons that matter, in course order.

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

Where to start

Lesson 28 Logical functions in Excel

Begin at Lesson 28 if you can enter data and write a simple formula: due dates and failure counts start with IF and SUMIFS. New to Excel? Begin at Lesson 1. If SUMIFS and PivotTables are familiar, go to Lesson 74 for due dates and the weekly list, or to Lesson 79 for the backlog, where the task tracker begins.

51 of 186 lessons · 3 h 58 min

04 Essential formulas and functions (IF, SUMIF and more) 12 of 13 lessons · 1 h 9 min IF to mark a task overdue, AND and OR for a task due and not yet done, SUMIFS and COUNTIFS for repair hours and failures by asset, and date arithmetic with TODAY for next due dates. The lessons use sales data.
  1. 28 Start here 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
06 Data visualisation: charts and graphs 3 of 9 lessons · 13 min Choosing a chart, column charts for breakdowns by asset, and line charts for a maintenance KPI month by month.
  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
06 Data visualisation: charts and graphs 1 of 9 lessons · 5 min The Pareto chart: the assets or failure causes behind most breakdowns, in order.
  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 over the breakdown log and work orders: failures and hours by asset, area and month, grouping by date, slicers and PivotCharts. 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 11 of 12 lessons · 51 min XLOOKUP and INDEX and MATCH for spares and asset details, LEFT, MID and RIGHT for asset codes, TODAY, EOMONTH and NETWORKDAYS for due dates, then UNIQUE, FILTER and SORTBY for the list of work due. The lessons use HR data.
  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
09 Project: task automation with data validation and forms 9 lessons · 49 min TaskFlow, read as a work order backlog: status and priority drop-downs, overdue orders highlighted, a timeline, working days left, data bars and a protected 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
14 VBA and macros in Excel: FormatWizard 5 of 17 lessons · 19 min Recorded macros: the weekly list's sorting, filtering and formatting recorded once, made to follow the active cell, run from a button and saved in a macro-enabled workbook.
  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

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 maintenance planners?

The Complete Excel Course with AI Integration from Coding Liquids covers the spreadsheet side of maintenance planning: next due dates for preventive work with date functions, a work order backlog in the TaskFlow tracker, breakdowns and repair hours by asset with COUNTIFS and PivotTables, MTBF and MTTR from conditional totals, spares looked up with XLOOKUP, and a weekly work list from FILTER or a recorded macro. The 51 lessons on this page take 3 hours 58 minutes.

Can I calculate MTBF and MTTR in Excel?

Yes, from conditional totals in The Complete Excel Course with AI Integration: SUMIFS adds up operating hours and repair hours by asset, COUNTIFS counts the failures, and dividing one by the other gives mean time between failures and mean time to repair. IFERROR keeps an asset with no failures from showing an error, and a Pareto chart puts the assets with the most breakdowns first. No lesson teaches MTBF or MTTR as such.

How do I plan preventive maintenance by asset and interval?

Keep one row per task with its asset, interval and the date it was last done. With the date lessons of The Complete Excel Course with AI Integration, date arithmetic gives the next due date, subtracting TODAY shows the days left, IF marks a task overdue, and conditional formatting colours it. FILTER and SORTBY then list the tasks due in the coming week in date order.

Does it teach reliability engineering or SAP PM?

No. The Complete Excel Course with AI Integration teaches Excel, not maintenance engineering. Reliability engineering, maintenance systems such as SAP PM or Maximo, and condition monitoring are not taught. Its lessons apply to the data those systems export to Excel: the plan, the breakdown log, the backlog, the KPIs and the weekly list.

Can I produce the weekly work list with one click?

Two ways in The Complete Excel Course with AI Integration. FILTER with date conditions lists the work due next week and updates as the dates change, and SORTBY orders it by due date or priority. Or record the sorting, filtering and formatting you do each week as a macro, put it on a button and save the workbook as XLSM, so a click repeats the steps.

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