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

- Begin by choosing the data range (i.e. your table)
- Open the Conditional Formatting window by going to
*HOME > Conditional Formatting > Add New Rule* - Select
**Use a formula to determine which cells to format** - Enter the formula LARGE and the logic (i.e.
**=B2>=LARGE($B$2:$G$7,3)**) - Click the
*Format*button to edit formatting settings - 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 *N*th largest number, which can be found with the **LARGE** function.

The **LARGE** function returns the *N*th 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.