Copilot can help highlight unusual values in Excel, but you must define what “unusual” means and verify the resulting rule. There is no documented universal Copilot default of two standard deviations, and a highlighted value is not automatically an error.
The example below uses a transparent interquartile-range rule and keeps the original numbers unchanged. Copilot interface guidance checked October 14, 2026.
Prepare the column and choose a defensible rule
Work on a copy or confirm that an earlier version can be recovered. Identify one numeric column, its header, and its exact rows. Resolve spreadsheet errors and decide what blanks, text, totals, and missing observations mean before analysis. Do not include a grand total in a column of individual transactions.
For this example, use the values in B2:B101 and no other cells. The rule flags values below Q1 − 1.5 × IQR or above Q3 + 1.5 × IQR, where IQR is Q3 minus Q1. It is a screening convention, not a probability guarantee or a business judgment.
Use the same quartile definition throughout. Excel’s QUARTILE.INC defines the first and third quartiles used here. A small, discrete, highly skewed, or mixed-population dataset may need a different business rule. Choose the rule before seeing which answer you prefer.
Current Copilot in Excel can edit the workbook directly. Do not rely on an old promise that every action waits for an Apply button. Use a non-editing or planning mode initially when it is available in your account.
Ask for an explicit rule and verify it independently
- Open Copilot using the icon available in your Excel version. In current documentation it can appear in the lower-right corner; older layouts may expose it on the ribbon.
- Request a plan: “Use only Sheet1!B2:B101. Explain an outlier rule using QUARTILE.INC, Q1 minus 1.5 IQR and Q3 plus 1.5 IQR. Exclude blanks and nonnumeric values. Do not edit or delete values.” Replace Sheet1 and the range with your actual data.
- Check that the proposed source and thresholds match your decision. If you want formatting applied, explicitly request conditional formatting only for that range. Review the resulting changes; the exact controls depend on your mode and rollout.
- Independently calculate the thresholds in unused cells, or a separate check sheet, before trusting the highlights.
For a clean numeric B2:B101 example, these helper formulas make the method inspectable. Keep F2:F5 outside the source data:
- F2, first quartile:
=QUARTILE.INC($B$2:$B$101,1) - F3, third quartile:
=QUARTILE.INC($B$2:$B$101,3) - F4, lower boundary:
=F2-1.5*(F3-F2) - F5, upper boundary:
=F3+1.5*(F3-F2)
A row-by-row check in C2, filled down to C101, is:
=IF(ISNUMBER(B2),OR(B2<$F$4,B2>$F$5),FALSE)This returns TRUE outside the boundaries, not on them. Resolve error values first. The fixed references keep the boundaries constant while the row reference changes. If your regional Excel settings use semicolons, use the appropriate local argument separator.
Compare the TRUE rows with the highlighted cells. Inspect the conditional-formatting formula and its applied range in Excel’s rule manager; do not accept a verbal explanation in the chat as proof that the correct rule was installed.
Investigate the flags instead of deleting them
Too many values are highlighted
Check units, mixed currencies, totals, duplicate imports, and whether different groups belong in the same population. An unusual sales spike may be legitimate. Changing the threshold solely to reduce the number of flags can hide the issue you intended to find.
No values are highlighted
That can be a valid result. Check the range and whether the cells contain numbers rather than numeric-looking text. Test the formula on a small copy containing a deliberately extreme value; remove that test data before using the real workbook.
Copilot answered in chat instead of formatting cells
An analysis response is not necessarily a workbook edit. Microsoft’s direct-analysis guidance distinguishes answers and static outputs from editing features. Use the mode available for editing, or apply a reviewed conditional-formatting rule manually.
The wrong cells changed
Stop further edits, inspect the changed range, and use Undo or a verified earlier version as appropriate. Reissue the request with a precise sheet and range. Keep the original values and a record of the screening method so someone else can reproduce the analysis.