How to Use Copilot in Excel to Highlight Outliers in a Dataset

You have a dataset in Excel and need to spot data points that fall far outside the normal range. Manually scanning hundreds of rows for outliers is slow and error-prone. Copilot in Excel can analyze your data and apply conditional formatting to highlight these extreme values automatically. This article explains how to set up your data, write a natural-language prompt to detect outliers, and apply the formatting Copilot suggests.

How Copilot Detects Outliers in Excel

Copilot in Excel uses statistical methods to identify outliers. When you ask it to find outliers, Copilot calculates the mean and standard deviation for the selected numeric column. It then marks any value that falls more than a specified number of standard deviations above or below the mean. By default, Copilot uses a threshold of two standard deviations, which covers approximately 95 percent of data in a normal distribution. You can change this threshold in your prompt.

Copilot does not delete or modify your data. It only applies conditional formatting rules that highlight cells meeting the outlier condition. This means your original values remain unchanged and you can remove the formatting at any time. The feature works with Excel tables created using the Insert > Table command. A named range or a simple range of cells also works as long as the first row contains column headers.

Prerequisites for Using Copilot for Outlier Detection

Before you begin, confirm these requirements:

• You have a Microsoft 365 Copilot license assigned to your user account. Copilot in Excel is part of Copilot for Microsoft 365, not the free Copilot.
• Excel version 2402 (Build 17328.20000) or later on Windows, or Excel for the web with the same update channel.
• Your data is in a contiguous range with a single header row. No blank rows or columns inside the range.
• The column you want to analyze contains numeric data. Text or blank cells in that column will cause Copilot to skip the row.

Steps to Highlight Outliers Using Copilot in Excel

Follow these steps exactly. Each step assumes you have already opened the workbook containing your dataset.

1. Open the Copilot pane
   Go to the Home tab and click the Copilot button on the right side of the ribbon. Alternatively, press Alt+Windows logo key+I. The Copilot pane opens on the right side of the Excel window.
2. Select the data range or table
   Click any cell inside your dataset. Copilot needs to know which table or range to analyze. If your data is not formatted as a table, press Ctrl+T to create a table. Confirm that the Create Table dialog shows the correct range and that the My table has headers box is checked.
3. Type your outlier detection prompt
   In the Copilot pane, type a prompt such as: “Highlight values in the Revenue column that are more than 2 standard deviations from the mean.” Replace Revenue with your actual column name. You can also use IQR-based prompts: “Highlight values in the Quantity column that are below Q1 minus 1.5 times IQR or above Q3 plus 1.5 times IQR.” Press Enter.
4. Review the proposed conditional formatting rule
   Copilot responds with a summary of the rule it will apply. It shows the number of cells that will be highlighted and a preview of the formatting. Read the rule description carefully. If the threshold is not what you want, type a follow-up prompt like “Change the threshold to 3 standard deviations” or “Use IQR method instead.”
5. Apply the formatting
   Click the Apply button in the Copilot response. Excel immediately applies a conditional formatting rule to the selected column. The rule uses a light red fill with dark red text by default. Cells that are outliers are highlighted in your worksheet.
6. Verify the highlighted cells
   Scroll through your data and check the highlighted values. Confirm that the highlighted cells are genuinely unusual compared to the rest of the column. If too many or too few cells are highlighted, repeat step 3 with a different threshold.

Alternative Prompt: Highlight Outliers in Multiple Columns

You can ask Copilot to check more than one column at the same time. Type a prompt such as: “Highlight outliers in both the Revenue and Cost columns using a threshold of 2 standard deviations.” Copilot creates two separate conditional formatting rules, one for each column. Each rule uses the same highlight color. To distinguish between columns, you can follow up with: “Change the highlight color for Cost outliers to yellow fill with dark yellow text.”

Common Mistakes and Limitations When Using Copilot for Outliers

Copilot Does Not Recognize the Column Name

If Copilot responds with “I can’t find that column” or “Please select a valid column,” the column name in your prompt does not match the header exactly. Check for extra spaces, typos, or special characters in the header. Also confirm that the first row of your selection is formatted as a header row. If you are using a named range instead of a table, Copilot may not parse headers correctly. Convert the range to a table using Ctrl+T and try again.

Copilot Highlights All Values or No Values

This usually means the standard deviation threshold is too low or too high. With a threshold of 1 standard deviation, roughly 32 percent of values are highlighted. With a threshold of 3 standard deviations, only about 0.3 percent are highlighted. Adjust your prompt to use a threshold between 1.5 and 3. For small datasets with fewer than 30 rows, standard deviation is less reliable. Use the IQR method in your prompt instead: “Highlight values in the Score column that are below Q1 minus 1.5 times IQR or above Q3 plus 1.5 times IQR.”

Copilot Applies Formatting to the Wrong Column

If Copilot highlights a different column than the one you specified, you may have multiple tables or named ranges in the same worksheet. Select only the table or range that contains the column you want to analyze before typing your prompt. If the problem persists, move the dataset to a new worksheet and repeat the steps.

Method	Standard Deviation (2 SD)	Interquartile Range (1.5 IQR)
Description	Flags values more than 2 SD from the mean	Flags values below Q1-1.5 IQR or above Q3+1.5 IQR
Best for	Approximately normal distributions	Skewed distributions or data with outliers
Copilot prompt example	“Highlight values in Revenue more than 2 SD from mean”	“Highlight values in Revenue outside 1.5 IQR”
Number of flags (typical)	About 5% of data	About 0.7% of data (for normal distribution)

You can now use Copilot in Excel to detect and highlight outliers in any numeric column. Start by selecting your table and typing a clear prompt with the column name and threshold method. If the first result does not match your expectations, adjust the threshold or switch to the IQR method. For advanced analysis, combine the highlighted outliers with a new column that flags them as TRUE or FALSE using a formula based on the conditional formatting rule Copilot created.