how to highlight in Excel

Select the column’s numeric cells, then create separate Top 1 and Bottom 1 conditional-formatting rules with different colors.

Highlight the highest value

1. Select the data cells in the column, excluding the heading—for example, B2:B100.
2. Go to Home → Conditional Formatting → Top/Bottom Rules → Top 10 Items.
3. Replace 10 with 1.
4. Choose a formatting style, such as Green Fill.
5. Select OK.

Highlight the lowest value

1. With the same range selected, go to Home → Conditional Formatting → Top/Bottom Rules → Bottom 10 Items.
2. Replace 10 with 1.
3. Choose a different style, such as Red Fill.
4. Select OK.

Excel supports top/bottom rules with a user-specified cutoff, so setting each rule to 1 identifies the highest and lowest entries in the selected range.^1^

Optional: show the full high-to-low range

To display every value using a color gradient instead:

1. Select the numeric cells.
2. Choose Home → Conditional Formatting → Color Scales.
3. Select a two-color scale.

The top color represents higher values and the bottom color represents lower values. This colors the entire range rather than only the two extremes.^1^

References

1. Windows