Excel for Pharma Quality & SPC Control Charts & Capability

What you'll learn

---

Rebuild a real 41-batch pharmaceutical Product Quality Review dashboard exactly as it ships in production, defects included.
Structure any workbook into three roles - Raw, Calculation, Dashboard - so a stuck formula, a wrong chart, and a wrong number are never found by searching.
Build named ranges that resize themselves with OFFSET and COUNTA, so a new batch never needs a formula dragged down by hand.
Compute a moving-range sigma as one array formula, with no helper column, and use it to build honest Cp and Cpk from the process, not the specification.
Tell whether a process is in control before capability means anything, using Nelson and Western Electric run rules on real out-of-control points.
Bind a chart series to a constant through a self-sizing named range, so one reference line updates and resizes for every batch added to the file.
Audit a Name Manager with 84 defined names by what each one actually does - plotted, read, plumbing, or orphaned - and find the ones nobody uses.
Break a working file on purpose: reproduce a locale parse trap, a silent VLOOKUP change, and a sort-order bug, so you recognize them in your own files.
Requirements

---

Comfortable already with VLOOKUP, basic named ranges, and building a simple chart - this course does not start with what a formula is.
Excel 2016 or later on Windows, or Microsoft 365. The workbook uses OFFSET, INDEX and array formulas; older builds and Excel for Mac are untested.
No statistics background required. Every control chart and capability concept - sigma, control limits, Cp, Cpk - is built from the data, on screen, from nothing assumed.
No VBA, Power Query or Power Pivot needed anywhere in this course. Every technique is a worksheet formula, a defined name, or a chart binding.
Description

---

This course contains the use of artificial intelligence.
The dashboard you will build in this course is my own work. I designed and built it for a real pharmaceutical Product Quality Review - every formula, every named range and every chart in it is mine. Artificial intelligence helps me during production only: recording, editing and visuals. It does not design the workbook, and it does not replace the live Excel testing you see on screen.
This is not a tutorial dataset built to demonstrate a function. It is a Product
Quality Review dashboard for Metformin HCl 850 mg, tracking ten quality attributes across forty-one batches, built on the architecture and the defects of a real pharmaceutical production file - and you rebuild it in this course exactly as it ships, techniques and problems together.
Three sheets do the whole job.Rawholds the data and is never touched by a formula.Calculationholds all 622 formulas and is never shown.Dashboardholds the charts and zero cells of its own. That one rule turns debugging from a search into a lookup: a wrong chart means look at Calculation, a wrong number means look at Raw.
Every range in the file resizes itself.OFFSETandCOUNTAturn a static block of cells into a range that grows the moment a new batch is typed in, and eighty-four defined names are built on top of it - sixty-two of them taking their length, directly or through another name, from a single column: the batch key. Add a batch and every chart, every control limit and every capability number moves with it, with no range dragged down by hand.
The centrepiece is a way to put a constant on a chart with no helper column and no cell anywhere on the sheet. A defined name multiplies the specification value by an array shaped exactly like the batch range - using ISNUMBER and ROW to build that shape independent of what the data actually contains - then hands the whole array to the chart through INDEX with both arguments left empty. Thirty-eight of these names bind chart reference lines straight to the specification cells, and the technique is built up live, one move at a time, including the two obvious alternatives that look
correct and quietly fail.
The statistics are taught the way they actually work, not the way a template pretends they work. A moving range becomes sigma with a single array formula and no helper column. Sigma becomes three-sigma control limits, and control limits become Cp and Cpk - computed two ways in this file, with the smaller one never taken, which is what Cpk is supposed to mean. Three of the ten attributes fall below the usual 1.33 capability bar. Three points sit outside the control limits while every one of the forty-one batches still passes its specification, which is the argument for checking control before capability rather than the other way around. Two manufacturing dates are out of order in the raw data, and the course shows what that does to a moving-range sigma once the rows are put back in true order.
The last section audits and then breaks the finished file. Every defect is real,
present in the file as shipped, and reproduced on camera, not staged after the fact: a chart axis floor that misdraws a specification band, a decimal-separator setting that silently changes what every parsed measurement means, a lookup whose behaviour changes when one character goes missing, and sixty-seven dead charts still adding to the file's size. Nothing here is broken in an obvious way. Every number looks completely reasonable on screen and is wrong anyway, and finding that is the actual skill this course teaches.
Built for QA/QC analysts, process engineers, and anyone maintaining an SPC or capability report in Excel - comfortable already with VLOOKUP, named ranges and charts, ready to see all three combined into one system that maintains itself.No VBA, no Power Query, no Power Pivot: every technique here is a worksheet formula, a defined name, or a chart binding.
Who this course is for

---

QA/QC analysts, process engineers and manufacturing analysts who build or maintain SPC and capability reports in Excel.
Anyone handed a dashboard, PQR or batch-record workbook they did not build, who needs to understand it before they can safely change it.
Excel users who already know VLOOKUP, named ranges and charts, and want to see all three combined into one production system, including its defects.
Not for total beginners to Excel. If VLOOKUP and a basic chart are still new to you, start with a general Excel course first.
Homepage

---