How to Highlight Top and Bottom Values in Excel

The "show me the outliers" problem

You have a column of numbers. You want the highest 5 and lowest 5 to stand out so you can call them out in a report. Excel has Conditional Formatting for this, but the rule sticks in the workbook and recalculates as data changes. Sometimes you want a one-shot snapshot, not a live rule.

TL;DR — Key takeaways

• Conditional Formatting > Top/Bottom Rules is built-in but the rule lingers in the workbook.
• =RANK() with a fill column is precise but leaves a helper column.
• A VBA macro paints top N green and bottom N red as static colors that revert with Ctrl+Z.

Method 1: Conditional Formatting Top 10 / Bottom 10

The built-in option:

1. Select the range.
2. Home > Conditional Formatting > Top/Bottom Rules > Top 10 Items (or Bottom 10).
3. Set the count (you can change from 10 to any N).
4. Pick a format. Click OK.

Result: top N cells get the format. Repeat for bottom N with a different format.

The catch: the rules stay in the workbook. They recalculate on every data change, which is sometimes confusing if you wanted a snapshot. To remove, Conditional Formatting > Manage Rules > Delete.

Method 2: =RANK formula

For more control:

=RANK(B2, $B$2:$B$100)

Drop in a helper column. Filter to top N and bottom N values.

The catch: helper column. Filter setup. Multi-step.

Method 3: The free VBA macro

Download Highlight Top and Bottom Values. Free .xlsm with one macro.

1. Select the range.
2. Alt + F8, pick the macro, click Run.
3. Type N (e.g., 5 for top 5 + bottom 5).
4. Top N turn green, bottom N turn red. Hit Ctrl+Z when you're done eyeballing to revert.

Static colors. No lingering rules.

A common scenario: quarterly sales review

60-store retail report. You want to call out the 10 best and 10 worst in the leadership email.

1. Select the sales column.
2. Run Highlight Top and Bottom Values.
3. Type 10.
4. Top 10 stores light up green; bottom 10 light up red.
5. Eyeball. Copy the names into the email.
6. Ctrl+Z to revert the colors before saving.

Versus Conditional Formatting: the macro doesn't leave rules to manage. Versus RANK formula: no helper column.

Tie behavior

If multiple cells share the threshold value (e.g., the 5th-highest value is 100 and three cells have 100), all three are colored. The macro uses Excel's LARGE and SMALL functions for thresholds, which means ties at the threshold are inclusive.

Frequently asked questions

What if my range has non-numeric cells?

They're skipped. Text, blanks, and error cells don't count toward the threshold and don't get painted.

Will it overwrite existing colors?

Yes, on the painted cells. Ctrl+Z immediately reverts. To preserve existing colors, work on a copy.

Can I customize the colors?

Edit the .bas source. Top is RGB(198, 239, 206) for fill; bottom is RGB(255, 199, 206). Change those values.

Can I run it on multiple columns?

Yes. The macro treats the entire selection as a single pool of numbers. For per-column top/bottom, run once per column.

Can I undo it?

Yes, Ctrl/Cmd + Z right after running.

What to do next

If you want to count or sum the highlighted cells, use Count and Sum Cells by Color. Sample one of the green or red cells to get the totals.