How to Highlight the Highest and Lowest Values in Google Sheets Using Conditional Formatting

When analyzing sales performance, student test scores, quarterly revenue projections, or server response times in Google Sheets, scanning through hundreds of numerical rows to spot the top and bottom values manually is slow and prone to human error. While sorting columns can reveal extreme values, sorting disrupts your original chronological or alphabetical row order.

The most elegant solution is to use Google Sheets’ built-in Conditional Formatting engine with dynamic formulas. By pairing conditional formatting rules with the MAX() and MIN() functions, Google Sheets will automatically shade the highest value in soft green and the lowest value in soft red. Furthermore, as new rows are added or existing numbers change, the formatting updates in real time. In this guide, you will learn how to configure automated maximum and minimum formatting rules in Google Sheets.

Why Automate Extreme Value Highlighting?

Applying dynamic conditional formatting delivers immediate analytical clarity:

• Instant visual hierarchy: Highlights peak performers and underperforming metrics immediately during stakeholder presentations.
• Dynamic recalculation: Automatically moves the highlight to new cells whenever data points are updated or imported.
• Preserves data organisation: Eliminates the need to resort your spreadsheet, keeping categories and dates in their original positions.

Step 1: Highlight the Highest (Maximum) Value

To configure a rule that automatically shades the highest number in a column:

1. Open your spreadsheet in Google Sheets and highlight the range of numbers you want to evaluate (for example, cells B2:B50).
2. In the top menu bar, click on Format > Conditional formatting. The Conditional format rules panel will open on the right.
3. Ensure your selected range appears under Apply to range (e.g. B2:B50).
4. Under the Format rules section, click the drop-down menu labelled Format cells if… and scroll to the bottom to select Custom formula is.
5. In the formula text box that appears, enter the following formula (adjusting cell letters to match your column):

   =B2=MAX(B$2:B$50)

6. Under Formatting style, select a soft green background fill and dark text colour.
7. Click Done.

Important Note: Notice the dollar signs ($) in B$2:B$50. These absolute row references lock the comparison range, ensuring every cell is evaluated against the entire column.

Step 2: Highlight the Lowest (Minimum) Value

To simultaneously highlight the lowest number in the same dataset:

1. With the same range selected, click Add another rule in the conditional formatting sidebar.
2. Under Format cells if…, choose Custom formula is.
3. Enter the minimum formula:

   =B2=MIN(B$2:B$50)

4. Under Formatting style, select a soft red or light coral background fill.
5. Click Done.

Your spreadsheet now displays your highest and lowest metrics in distinct, professional visual accents.