How to Use the Outliers Function in Excel

1. Enter the set of numbers into a range of cells in the 'A' column of an Excel worksheet. As an example, we will use a set of 20 numbers, running from cell A1 to A20.
2. Select cell B1 and enter the 'AVERAGE' function, with a range from the first number in the set to the last number in the set. In this example, cell B1 should read '=AVERAGE(A1:A20).'
3. Calculate the standard deviation of the set in cell B2 using the 'STDEV' function. The formula in cell B2 should be '=STDEV(A1:A20).' Any figures in the set of numbers that fall more than two standard deviations from the mean are to be considered outliers.
4. Find the minimum and maximum values in the set that will be considered. Enter '=B1-(2*B2)' (mean minus two standard deviations) in cell B3 for the minimum, and enter '=B1 (2*B2)' (mean plus two standard deviations) in cell B4 for the maximum.
5. Select the original set of numbers (range A1:A20). Go to the first cell in the range (A1), select 'Conditional Formatting' on the 'Home' tab, and open the 'New Formatting Rule' dialog box.
6. Select 'Use a formula to determine which cells to format' under 'Select a Rule Type.' In the 'Format values where this formula is true' box that opens, type '=OR(A1
$B$4)' and click 'Format' next to the Preview box, then select 'Bold' under 'Font Style.'
7. Click 'OK' on both of these dialog boxes to close them and apply the rule. This will cause all outliers to appear in bold on the spreadsheet.