Start learning
My learning

The Complete Excel Course with AI Integration

Excel course for quality engineers

For quality engineers and quality managers who log inspections, count defects and report quality in Excel. The Complete Excel Course with AI Integration has no quality data of its own; this page picks out the lessons quality work uses: pass or fail tests and COUNTIFS, the Pareto, histogram and box and whisker charts, PivotTables for supplier quality, a tracker for corrective actions and a macro that checks an inspection sheet.

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:

  • Count defects by type, line and shift with COUNTIFS, and mark each reading pass or fail against its limits with IF, AND and OR.
  • Work out first-pass yield and scrap rate as percentages and defects per million from the same counts, with limits held in fixed cells and IFERROR for a shift with no output.
  • Rank defect causes in a Pareto chart, and show the spread of a measurement with a histogram and a box and whisker chart.
  • Summarise supplier quality by supplier and month with PivotTables, slicers and PivotCharts.
  • Track corrective actions with owners, due dates, status drop-downs and overdue actions in colour.
  • Check an inspection sheet with a VBA macro that finds blank and non-numeric readings and colours them before the sheet is signed off.

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 chosen for quality questions: a Pareto chart for the few causes behind most defects, a histogram and a box and whisker chart for spread.

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

    A task tracker kept for corrective actions: each with an owner and a due date, overdue ones in colour, and data bars for how far each action has got.

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

    Built on a sales sheet, it serves as a sign-off check for inspection sheets: blank readings and text in number columns found and coloured before anyone relies on them.

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

    Rejects and defects by supplier, part and month in one PivotTable, with slicers to look at one supplier at a time.

  • Project 03

    Sales Goal Tracker

    COUNTIFS and IF on inspection results instead of sales: defects by type, line and shift, and pass or fail against a limit.

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, 4 h 55 min of video. Each lesson keeps its number in the course.

Where to start

Lesson 10 Relative vs absolute references in Excel

Begin at Lesson 10 if you can enter data and write a simple formula: quality ratios start with fixed references and percentages. New to Excel? Begin at Lesson 1. If COUNTIFS and PivotTables are already familiar, go to Lesson 48 for the quality charts, or to Lesson 136, where the checking macro begins.

61 of 186 lessons · 4 h 55 min

01 Microsoft Excel fundamentals: building a foundation from scratch 3 of 13 lessons · 10 min The quality ratios: a limit or a target held in one cell with an absolute reference, yield and scrap as percentages, and IFERROR for a shift with no output. The lessons build a personal budget; no lesson teaches yield, scrap or defects per million as such.
  1. 10 Start here Relative vs absolute references in Excel Dollar signs and mixed references 3 min
  2. 11 Calculating percentages in Excel Each expense as a percentage of income 2 min
  3. 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 An inspection or defect log kept as an Excel Table, sorted by date and filtered to one line, one part or one defect type.
  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
04 Essential formulas and functions (IF, SUMIF and more) 10 of 13 lessons · 59 min TRUE and FALSE tests against a limit, IF for pass or fail, IFS for grades, AND and OR for a reading that must sit between two limits, then COUNTIF and COUNTIFS for defects by type, line and shift. The lessons use sales data.
  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 The quality charts Excel draws: a Pareto chart of defect causes, a histogram and a box and whisker chart for the spread of a measurement, line and combo charts for a rate over time, and sparklines for each line.
  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 over receiving inspections or a defect log: rejects by supplier 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
09 Project: task automation with data validation and forms 9 lessons · 49 min TaskFlow, read as a corrective action log: status and priority drop-downs, overdue actions highlighted, working days to each due date, data bars for progress 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 17 lessons · 1 h 16 min FormatWizard: recorded macros, then VBA with loops and If statements that check a sheet for blank and non-numeric values, colour what they find and handle errors. Built on a sales sheet, it checks an inspection sheet's columns the same way.
  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
  6. 141 The VBA Editor in Excel Open it with Alt+F11 and learn the workspace 4 min
  7. 142 Working in the VBA editor Macro security, trust settings, switching to Excel 6 min
  8. 143 Your first VBA code Modules, worksheets, rows, columns and formatting 4 min
  9. 144 VBA variables and data types Sub vs function for Excel beginners 6 min
  10. 145 VBA functions in Excel Build GetLastUsedRow with find and exit function 5 min
  11. 146 Clear old flags with VBA Reset messages and fill colours with if statements 5 min
  12. 147 For Next loop in VBA Flag missing region values in Excel 4 min
  13. 148 VBA If Else and loops Check missing sales data and build a FlagText message 5 min
  14. 149 Highlight cells with VBA Interior.Color and RGB for missing data 5 min
  15. 150 IsNumeric in VBA Validate unit price and amount data types in Excel 7 min
  16. 151 Debugging VBA for beginners Debug.Print and the Immediate window 5 min
  17. 152 Complete Excel validation macro One VBA subroutine with an error handler 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 quality engineers and quality managers?

Yes. The Complete Excel Course with AI Integration from Coding Liquids teaches the Excel behind quality reporting: COUNTIFS for defects by type, line and shift, pass or fail with IF and AND, Pareto, histogram and box and whisker charts, PivotTables for supplier quality, a corrective action tracker, and a VBA macro that checks an inspection sheet. The 61 lessons on this page take 4 hours 55 minutes.

Does the course teach control charts, Cpk or Six Sigma tools?

No. The Complete Excel Course with AI Integration teaches Excel, not statistical quality methods. Control charts, process capability such as Cpk, measurement system analysis, design of experiments and Minitab are not taught. The charts it does teach still serve quality work: Pareto, histogram and box and whisker, all built from chart types Excel already has.

Can Excel draw a Pareto chart of defect causes?

Yes, as a chart type of its own. Lesson 55 in The Complete Excel Course with AI Integration builds Pareto, funnel and waterfall charts, and Lesson 56 adds histograms and box and whisker charts for the spread of a measurement. Count defects by cause with COUNTIF or a PivotTable first; the Pareto chart puts the causes in order and draws the cumulative line.

Can a macro check inspection sheets for missing readings?

Yes. The FormatWizard section of The Complete Excel Course with AI Integration writes one: a For Next loop walks down the rows, If statements find blank cells, IsNumeric catches text typed into a number column, RGB colours what is found, and the whole check runs as one subroutine with an error handler. It checks a sales sheet; a reading outside its limits takes one more If.

How do I follow corrective actions through to closure in Excel?

With a tracker like TaskFlow in The Complete Excel Course with AI Integration: each action is a row with an owner, a priority and a due date, status comes from a drop-down list, conditional formatting colours overdue and closed actions, NETWORKDAYS counts the working days left, and data bars show progress. Protect the sheet and the formulas stay intact as people update it.

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