A quick primer before the walkthrough:

In plain English: Organize and quality-check messy plate-reader data with plain-English prompts in Excel — Copilot writes the formulas and flags outliers for you.

Key terms:

• Plate reader — the instrument that outputs raw absorbance/fluorescence readings.
• Quality check — a quick check that catches bad wells, edge effects, or pipetting errors.
• Outlier — a well that looks very different from its replicates.

What you'll need: Microsoft 365 with Copilot and one plate-reader export.

Comfort level: Comfortable opening a spreadsheet. No formulas required — Copilot writes them.

Organizing and checking assay data with Copilot in Excel

Most bench scientists live in Excel. Microsoft 365 Copilot now sits inside it — this tutorial gets you from raw plate-reader output to an analysis-ready dataset without writing code.

Prerequisites

• Microsoft 365 Copilot license (E3/E5 add-on).
• Data in an Excel Table (Ctrl+T). Copilot works best on structured tables, not free-form ranges.
• Plate-reader export as CSV or XLSX.

Step 1 — Structure the sheet

Convert your raw export to a Table with clear headers: Plate, Well, Sample_ID, Signal, Control_Type. Copilot cannot reason well over merged cells or multi-row headers — flatten first.

Step 2 — Ask Copilot to profile

Open the Copilot pane and prompt:

Give me a summary of this table: row count per plate, mean and SD of Signal 
per Control_Type, and flag any plate whose positive-control CV exceeds 15%.

Copilot returns the answer in-pane and can insert a summary sheet with one click.

Step 3 — Outlier flagging

Add a new column "Outlier" that marks any well whose Signal is more than 
3 SD from the mean of its Control_Type within the same plate.

Copilot writes the formula (a nested IF/AVERAGEIFS/STDEV.S expression) and applies it to the whole column. Review the formula — do not accept blindly.

Step 4 — Normalization

Add a "Normalized" column computed as (Signal - mean of Negative on same plate) / 
(mean of Positive - mean of Negative on same plate).

Step 5 — Data-check report

Create a new sheet called "Data Check" with:
- Z' factor per plate
- Number of outliers per plate
- A conditional-formatted table highlighting plates that failed Z' >= 0.5

Verify everything

Copilot in Excel occasionally uses the wrong column when names are ambiguous (Signal_1 vs Signal). Always:

1. Click into the generated formula bar and read it.
2. Spot-check one plate manually.
3. Save a frozen copy before running Copilot — its edits are hard to undo cleanly.

Pitfalls

• Copilot rewrites your data. Always work on a copy.
• Regional formats. European decimal commas break the formulas Copilot writes in en-US.
• No PHI/PII. Even in Enterprise tenants, confirm your Copilot data boundary before uploading clinical assay data with patient IDs.

When to escalate to Python

If you need bootstrap CIs, mixed-effect models, or reproducible pipelines across studies — export the organized table and hand it to a bioinformatician. Copilot in Excel is for checking and first-pass exploration, not final analysis.

If something goes wrong

• Copilot changed data you did not intend it to change. Always work on a copy, and undo in one step rather than patching over it.
• It cannot find your columns. Give exact header names in the prompt, and make sure the sheet has a single header row with no merged cells.
• Rows silently disappear after a tidy-up. Ask for the row count before and after, and for a list of what was removed and why.
• Dates or IDs get reformatted. Set those columns to Text before organizing, then re-check a handful of values.