Use Copilot in Excel to add data quality flag columns to a results table

What This Does

Reviewing a lab results table usually means scanning for a blank result, a unit that does not match the rest, or a field duplicate that drifted apart. Copilot in Excel drafts flag columns for those checks, so your review time goes to the flagged rows. It builds the flags. It does not decide what a flag means for the project.

The tolerance for a duplicate pair is the project's. You type it into a cell, and the flag reads that cell.

Before You Start

• You have Excel open on a copy of the workbook. Microsoft's page says Copilot in Excel works on Windows, Mac and the web, and opens in edit mode by default. Edit mode changes the workbook directly, so work on a copy.
• Copilot is in your Microsoft 365 license and switched on. Look for the Copilot icon in the lower-right corner of Excel. Microsoft's page says it may be missing if your subscription does not include it or your organization's settings turn it off. Ask your IT group or check Microsoft's plan page. This guide states no price or plan name.
• The data is invented, or your firm and the client have approved Copilot for this lab data. Lab reports for client sites stay in the firm's approved systems. The flag formulas only need column names and a few rows, so an invented copy with the same headers gives you the same formulas.
• You know where the duplicate tolerance comes from. It is in your work plan or comes from your project manager. This guide does not supply one.

Steps

1. Set up the Results sheet with a Unit column

Use the sheet from the screening-comparison guide, or type these headers in row 1: Location, Sample date, Result (ug/L), Qualifier, Screening value, Comparison, Unit, Unit flag, Missing flag. Enter the five invented rows in the Real Example, plus the two invented test rows at the bottom. The test rows exist to make the flags fire.

2. Open Copilot in plan or chat mode

Select the Copilot icon in the lower-right corner. Microsoft's page describes chat mode as analysis without changes and plan mode as a plan you confirm first. Choose one of those, and look in the pane for the mode choice.

3. Ask for the units check

Paste this prompt:

In the Results sheet, column G holds the unit for each result. In column H, write a formula for row 2 that returns "CHECK UNITS" when the unit cell is not exactly "ug/L", and a blank otherwise. Return a blank when Location is empty. Explain the formula.

The formula that gives the correct answer is:

=IF(A2="","",IF(G2<>"ug/L","CHECK UNITS",""))

Excel treats upper and lower case as equal in this comparison, so a unit typed as "UG/L" will not be flagged. A unit typed as "mg/L" or left blank will be.

4. Ask for the missing result flag

Prompt:

In column I, write a formula for row 2 that returns "MISSING" when Location is filled in and the Result in column C is empty, and a blank otherwise. Explain the formula.

The correct formula is:

=IF(A2="","",IF(C2="","MISSING",""))

5. Add the duplicate check on a QC tab

Add a sheet named QC. Type these headers in row 1: Pair, Primary result, Duplicate result, RPD, Tolerance, Duplicate check. In row 2 type MW-5 / MW-5-DUP, 140, 120, and leave D2 for the formula. Type the tolerance in E2. The invented example tolerance is 30. In real work, type the figure your project sets.

Ask Copilot for a relative percent difference formula for row 2 and a check column that reads the tolerance cell. The correct formulas are:

=ABS(B2-C2)/AVERAGE(B2,C2)*100
=IF(OR(D2="",E2=""),"",IF(D2<=E2,"Within tolerance","Outside tolerance"))

6. Compare Copilot's output with the correct results

Your sheet should show the values in the Real Example. If Copilot's formula differs in logic, replace it with the correct one above. If a flag fires on a row where you expected a blank, read the formula before you read the data.

Real Example

Scenario: Site S-01 (invented), September 2026 groundwater event (invented), Analyte A (an invented analyte). Screening value 50 (an invented screening value, not a real standard). Rows 7 and 8 are invented test rows added only to trigger the flags.

QC tab (invented): primary result 140, duplicate result 120. The difference is 20 and the average is 130, so the RPD is 20 divided by 130, times 100, which is 15.38 to two decimal places. With the invented example tolerance of 30, the check column reads Within tolerance.

What you get: Two flagged rows out of seven, one in each flag column, and a duplicate check with the RPD you can recompute by hand. Anything that does not match this table is a formula to fix.

Tips

• A flag is a prompt to look, not a finding. A units flag can come from a typing slip, and a missing flag can mean the lab has not reported yet. Ask the lab or your project manager before you change a cell.
• Keep the tolerance in its own cell. When the project sets a different number, you change one cell.
• Copilot's formulas work on the column layout you describe. If your lab's export puts the result in a different column, say so in the prompt, and recheck the references.
• Go one step past flags by asking Copilot, in chat mode, which locations have the most flags across several events. Then count them with a COUNTIF on the flag columns to confirm.

Tool interfaces change. If a button has moved, look for the Copilot icon or a similar AI option in the same area of the window.