Excel Top/Bottom conditional formatting can automatically color the largest or smallest values in a range. The highlight follows the data, so if values change, the top items update too.
Does this affect you?
Use this for Excel for Microsoft 365, Excel 2021, and Excel 2019 on Windows or Mac when sales, scores, prices, counts, or other numbers need the highest or lowest values to stand out.
Highlight the top values in a column
The built-in rule needs no formula.
- Select the numeric range.
- Open Home > Conditional Formatting > Top/Bottom Rules > Top 10 Items.
- Change 10 to another count if needed, such as 5.
- Choose a built-in format or Custom Format.
- Click OK.
Use Bottom 10 Items from the same menu when you want the lowest values instead.
Highlight the whole row for top values
Use a formula rule when the entire record should stand out.
- Select the full table area to format, such as A2:D50.
- Open Home > Conditional Formatting > New Rule.
- Choose Use a formula to determine which cells to format.
- Enter a formula such as =$D2>=LARGE($D$2:$D$50,10), where column D contains the values.
- Choose a fill or font format.
- Click OK.
The dollar sign locks the value column, while the row number changes as Excel checks each row.
More control
Use top percentage instead of item count
In the Top 10 Items dialog, change Items to percent. This highlights the top percentage of values, which scales better for very short or very long lists.
Extract the top values into a list
Use LARGE to return ranked values, such as =LARGE($D$2:$D$50,1) for the highest and =LARGE($D$2:$D$50,2) for the second-highest.
Expand the rule for new rows
If new rows are not highlighted, open Conditional Formatting > Manage Rules and adjust Applies to. Excel Tables can expand formatting more reliably as rows are added.
Combine with color scales
Top rules show extremes. Color Scales show the full value spread. Using both can make the standout values and overall pattern clear.
Sources
- Microsoft Support – Use conditional formatting to highlight information (2025)
- Microsoft Support – Video: conditional formatting with Top/Bottom rules (2024)
- Microsoft Support – LARGE function (2025)