Highlight the Highest and Lowest Value in Each Excel Row
Question details
Highlight the highest and lowest prices in each row and update the colors automatically when prices change.

- 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.
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.
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.
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.
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.
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.
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.

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. Open and select prices: Open your workbook in WPS Spreadsheets and select the price cells, such as C2:K100.
- 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. 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. Test and save: Change a price to check the colors, then use File > Save to save the workbook.

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.




