Use Copilot in Excel to draft and check screening-comparison formulas

What This Does

Every results table ends up compared with the screening values your project manager picked, and the formula that does it is often something nobody can explain a year later. Copilot in Excel drafts that formula, explains it in plain words and builds the count cells beside it. Excel does the computing. You check the boundary cases by hand before anyone relies on the column.

Copilot never supplies a result or a screening value. The lab reports the results, and your project manager gives you the screening values to type in.

Before You Start

• You have a workbook open in Excel on Windows, Mac or the web. Microsoft's page says Copilot in Excel is available on those platforms, with chat, plan and edit modes. On iPad and phone, edit mode is limited or still rolling out.
• Copilot is part of your Microsoft 365 license. Look for the Copilot icon in the lower-right corner of Excel. Microsoft's page says that if you do not see Copilot, it may not be included with your subscription or may be turned off by your organization's settings. Ask your IT group, or check the plan details on Microsoft's own plan page. Pricing and plan names are Microsoft's to state, so this guide gives none.
• You are working on invented data, or on lab data your firm and the client have approved for Copilot. Client lab reports, site names and addresses stay in the firm's approved systems. If you are unsure, build the sheet with the column names and invented rows below and get the formulas right there. Then paste the finished formulas into the real workbook yourself.
• You saved a copy of the file. Copilot opens in edit mode by default, and edit mode changes the workbook directly.

Steps

1. Build the practice table

Rename a sheet Results. Type these headers in row 1: Location, Sample date, Result (ug/L), Qualifier, Screening value, Comparison. Then type the five invented rows from the Real Example below. Type the screening value 50 in column E on every row. In real work, your project manager selects the screening values from the applicable source, and you type them in. Copilot does not pick them.

2. Open Copilot and choose a low-risk mode

Select the Copilot icon in the lower-right corner of Excel. A pane opens. Microsoft's page describes three modes. Chat mode answers without changing the workbook. Plan mode shows a plan for you to confirm before anything changes. Edit mode makes the changes itself. Use chat mode or plan mode for this first pass, and look in the pane for the mode choice. If you do use edit mode, you can select the Stop button in the lower-right corner of the chat input field at any time.

3. Ask for the formula

Paste this prompt:

The Results sheet holds an invented dataset. Columns A to E are Location, Sample date, Result, Qualifier and Screening value. Write a formula for F2 that compares the result with the screening value. Return a blank when Location, Result or Screening value is empty. Return "Not detected" when the Qualifier is ND. Otherwise return "Above SV" when the result is greater than the screening value, and "At or below SV" when it is not. Explain each part of the formula in plain words.

Read the explanation. If a part is unclear, ask a follow-up question about that part only.

4. Compare the formula with the correct one

The formula that matches the prompt is:

=IF(OR(A2="",C2="",E2=""),"",IF(D2="ND","Not detected",IF(C2>E2,"Above SV","At or below SV")))

Copilot's wording may differ. The logic has to match. The check that catches most mistakes is the greater-than sign. A version written with a greater-than-or-equal sign would label a result that equals the screening value as Above SV. Put the correct formula in F2 and fill it down to F6.

5. Ask for the counts and check them

Ask Copilot for a small summary block that counts each label in column F, and for the average and the highest of the detected results. The formulas that give the correct answers are:

=COUNTIF(F2:F6,"Above SV")
=COUNTIF(F2:F6,"At or below SV")
=COUNTIF(F2:F6,"Not detected")
=AVERAGEIF(D2:D6,"<>ND",C2:C6)
=MAXIFS(C2:C6,D2:D6,"<>ND")

Compare Copilot's cells with the expected values in the Real Example. Any difference means one formula needs fixing before the table goes anywhere.

6. Check three cells by hand

Pick MW-2, MW-4 and MW-3. For each one, read the result and the screening value and say what the label should be. Then look at column F. MW-4 is the one to watch, because its result equals the screening value exactly.

Real Example

Scenario: Site S-01 (invented), September 2026 groundwater event (invented), Analyte A (an invented analyte), results in ug/L. The screening value is 50 (an invented screening value, not a real standard).

What you type:

For MW-3, ND means not detected, and the result column holds the reporting limit.

What you get: A Comparison column that matches the expected labels and these summary values:

• Above SV: 2 (MW-2 and MW-5).
• At or below SV: 2 (MW-1 and MW-4). The value 50 is not above 50.
• Not detected: 1 (MW-3).
• Average of detected results: 67.5, which is 12 + 68 + 50 + 140 = 270, divided by 4.
• Highest detected result: 140, at MW-5.
• MW-5 divided by the screening value: 2.8, from =C6/E6.

"Above SV" describes what the spreadsheet's comparison prints. It is not a finding. What a label means for the site, and whether anything follows from it, belongs to your project manager and the licensed professional who signs the report.

Tips

• Keep a visible Screening value column. Your project manager can change the number in one place, and every label updates.
• Go one step past speed once the formulas are right. Add a column with an invented June 2026 result for each location: 15, 55, ND, 47 and 128 for MW-1 to MW-5. Ask Copilot for a Change column that subtracts the June result from the September result and stays blank when either one is ND. The correct values are -3 for MW-1, 13 for MW-2, 3 for MW-4 and 12 for MW-5, with MW-3 left blank because both results are ND. The column shows differences only. Whether a difference matters is a question for your project manager. For more on asking for more than speed, read Aim Higher with AI.
• Ask Copilot to explain any formula you inherit from an older workbook. Confirm its explanation by changing one input and watching the result.
• Save the finished formulas in a template workbook with invented rows, so the next project starts from formulas you have already checked.

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.