Highlight Top and Bottom Values

What it does

You have a column of numbers. You want to see the highest and lowest at a glance. Excel's Conditional Formatting has a "Top 10 items" rule that does this, but the rule sticks around forever and recalculates as data changes. The macro is a one-shot version. Pick how many top and bottom to highlight, the macro paints top values green and bottom values red. Hit Ctrl+Z when you're done eyeballing.

Real-world example

Picture a regional sales manager at a 60-store retail chain reviewing the quarterly sales-by-store report. She wants to call out the 10 highest-performing and 10 lowest-performing stores in the email to the leadership team. The data is in a flat 60-row block.

Her current method: sort by sales descending, copy the top 10 store names into a Word doc, sort by sales ascending, copy the bottom 10. Or alternatively: apply Conditional Formatting's "Top 10 items" rule. Either way takes 2 to 3 minutes and leaves either a Word doc to maintain or a CF rule cluttering the spreadsheet.

With this macro she selects the sales column, runs the tool, types 10, and the top 10 turn light green and the bottom 10 turn light red in 1 second. The popup confirms count. She visually scans the colored stores, copies the names into the email, hits Ctrl+Z to revert the colors.

On a 60-store quarterly review, expect about 2 minutes saved per pass. Across 4 quarters that's 8 minutes back. The bigger win is the discipline of "highlight, scan, decide, undo" without leaving CF rules in the workbook for the next person to wonder about.

How to use

First time? See How to run our tools for the 30-second setup.

1. Select the range of numbers to scan.
2. Press Alt + F8 (Windows) or Option + F8 (Mac), pick HighlightTopBottomValues, click Run.
3. Type the number N (e.g., 5 for top 5 + bottom 5).
4. The macro paints top N values green and bottom N values red. Popup reports counts.
5. Hit Ctrl/Cmd + Z to revert the colors when you're done.

FAQ

What if there are ties at the threshold value?

All cells matching the threshold are colored. So if N=5 and the 5th-highest value is 100 but two cells both have 100, both get colored green even though that gives you 6 highlighted top values. This is consistent with Excel's LARGE function semantics.

What about non-numeric cells in the selection?

They're skipped. Text cells, blank cells, formula cells returning errors don't count toward the threshold and don't get painted.

Will it overwrite existing cell colors?

Yes, on cells in the top N or bottom N. If those cells already had a color, the macro replaces it with green or red. Ctrl+Z immediately reverts. To preserve existing colors, work on a copy.

Can I customize the colors?

Not from the macro UI. Edit the .bas source: top is RGB(198, 239, 206) for fill and RGB(0, 97, 0) for font. Bottom is RGB(255, 199, 206) and RGB(156, 0, 6). Change those values, re-import.

Can I run it on a multi-column selection?

Yes. The macro treats the entire selection as a single pool of numbers and finds the top N and bottom N across the whole pool. For per-column top-N, run the macro one column at a time.

What to do next

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