Formatting Issues

Highlight the Highest and Lowest Value in Each Excel Row

Emma BrownEmma Brown Nov 18, 2026 872 views

Question details

Highlight the highest and lowest prices in each row and update the colors automatically when prices change.

Automatically Highlight the Highest and Lowest Value in Each Excel Row
Product
Microsoft Excel
Device & OS
not provided
Scenario
Compare supplier prices across columns in each row using conditional formatting.
Observed behavior
The lowest and highest values in each row are highlighted with different colors and update when the underlying prices change.
Before you start

Identify the first and last price columns and the first data row. The examples below use C2:K100; adjust the formulas and selected range to match your worksheet.

Solution 1Recommended

Create conditional formatting rules for each row

Use two formula-based rules to color the lowest price green and the highest price red in every row.

The formulas compare each cell with the minimum or maximum of its own row. The dollar signs lock the price columns while allowing the row number to change.

1
Select the price range

Select C2:K100, or select the equivalent range in your worksheet. The top-left cell of the selection must match the first cell referenced in the formulas.

2
Create the minimum rule

Go to Home > Conditional Formatting > New Rule. Choose Use a formula to determine which cells to format, enter =C2=MIN($C2:$K2), and select Format > Fill to choose green. Click OK.

3
Create the maximum rule

With the same range selected, go to Home > Conditional Formatting > New Rule. Choose the formula option, enter =C2=MAX($C2:$K2), and select Format > Fill to choose red. Click OK.

4
Check the rules

Go to Home > Conditional Formatting > Manage Rules and confirm that both rules apply to =$C$2:$K$100. Change a price to check that the highlights update.

Create conditional formatting rules for each row
Ties and blank cells: All tied minimum or maximum values are highlighted. If a row has only one numeric value or all its prices are equal, both rules may apply; their order can affect the displayed color. Blank cells can also be highlighted when the minimum or maximum is zero.
Highlight prices in WPS Spreadsheets

Highlight row minimums and maximums in WPS Spreadsheets

Use formula-based conditional formatting in WPS Spreadsheets to compare supplier prices in each row and update their colors as values change.

  1. 1. Open and select prices: Open your workbook in WPS Spreadsheets and select the price cells, such as C2:K100.
  2. 2. Add the lowest-price rule: Go to Home > Conditional Formatting > New Rule, choose the formula-based rule, and enter =C2=MIN($C2:$K2). Set the fill to green and confirm.
  3. 3. Add the highest-price rule: Create another formula-based rule for the same selection using =C2=MAX($C2:$K2). Set the fill to red and confirm.
  4. 4. Test and save: Change a price to check the colors, then use File > Save to save the workbook.
Automatically highlight the lowest and highest price in each rowOpen and edit Microsoft Excel XLSX and XLS filesApply familiar spreadsheet formulas to a selected price rangeUse WPS Office for free
QA img-9

Frequently Asked Questions

How do I adapt the formulas to different price columns?

Replace C2 with the top-left cell of your selected range and replace $C2:$K2 with that row's first and last price columns. For example, for D3:H50, use =D3=MIN($D3:$H3) and =D3=MAX($D3:$H3).

What happens when two suppliers have the same lowest price?

Both cells are highlighted because each equals the row minimum. The same applies to tied maximum values.

Why is a blank cell highlighted when a row contains zero?

A blank cell can compare equal to zero in these rules. To exclude blanks, use =AND(ISNUMBER(C2),C2=MIN($C2:$K2)) for the minimum rule and =AND(ISNUMBER(C2),C2=MAX($C2:$K2)) for the maximum rule.

Why does a row with equal prices show only one color?

Every price matches both the minimum and maximum rules, so rule priority can determine the visible fill. In Excel, go to Home > Conditional Formatting > Manage Rules to review and reorder the rules.