It's often a good idea to highlight the top values in a data set to help easily identify the outliers. Here, you can find out how you can do this dynamically using Excel formulas.

Syntax

=relative cell reference >=  LARGE(absolute data range reference, top value count)

Steps

1. Begin by choosing the data range (i.e. your table)
2. Open the Conditional Formatting window by going to HOME > Conditional Formatting > Add New Rule
3. Select Use a formula to determine which cells to format
4. Enter the formula LARGE and the logic (i.e. =B2>=LARGE($B$2:$G$7,3))
5. Click the Format button to edit formatting settings
6. Click OK to continue and apply your settings

How to highlight the top values in a data set

The conditional formatting feature applies selected formatting options to a cell, when a given condition is met. If this condition is provided by a formula, Excel will check whether the formula returns TRUE before applying formatting options. Therefore, we need a formula that will return TRUE when the value in a cell is in the top N values within the selected range. The value in top N means that the value is greater than or equal to the Nth largest number, which can be found with the LARGE function.

The LARGE function returns the Nth largest number from a range. So, the idea is to check every cell in the range, to see whether the cell value is greater than, or equal to the largest Nth. If it is, we want it highlighted. The LARGE function takes two arguments which, range of values, and the nth value, to specify which values to return. For example,

=B2>=LARGE($B$2:$G$7,3)

Fortunately, we do not need to add conditional formatting to each cell one-by-one. Excel can handle this by looking at absolute and relative references. All you need to do is to make an absolute range reference (i.e. $B$2:$G$7) because this range shouldn't change and leave the original cell (B2) relative. This way, the range can be updated for other cells to be added.

If you'd rather rank these values, see How to Rank Scores.