How to Highlight the Lowest Price in Each Row with Conditional Formatting
Question details
The user needs to automatically identify and highlight the minimum value, such as the lowest price, across multiple columns for every individual row in a selected dataset.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Comparing prices from different suppliers or checking minimum values row by row, where manual highlighting is too time-consuming and prone to error.
- Observed behavior
- The user requires a dynamic rule that evaluates each row independently and updates automatically if the data changes.
Ensure your dataset does not contain merged cells in the range you are evaluating, as merged cells can disrupt row-by-row formula calculations in conditional formatting.
Use Formula-Based Conditional Formatting with the MIN Function
This method uses a custom formula with mixed cell references to dynamically find and highlight the lowest value in each individual row across your selected range.
By applying a formula rule, you instruct the spreadsheet to check if the current cell is equal to the minimum value of its specific row. Using mixed references (like $A2) ensures the column range stays fixed while the row adapts dynamically.
Highlight the entire range of cells containing the prices you want to compare, for example, A2:H20. Make sure to note the top-left cell of your selection (e.g., A2).
Navigate to the Home tab on the ribbon, click on 'Conditional Formatting', and select 'New Rule' from the dropdown menu.
In the New Formatting Rule dialog box, select the option 'Use a formula to determine which cells to format'.
In the formula bar, type `=A2=MIN($A2:$H2)`. Replace A2 with the first cell of your selected range, and $A2:$H2 with the columns containing your row data.
Click the 'Format' button, go to the 'Fill' tab, choose a background color to highlight the lowest price, and click 'OK' twice to apply the rule.
Highlight Data Instantly in WPS Spreadsheet
WPS Spreadsheet provides powerful, intuitive conditional formatting tools that make analyzing pricing data effortless. It fully supports advanced formula rules, allowing you to highlight rows dynamically with zero hassle.
- 1. Open your file: Launch WPS Spreadsheet and open the document containing your pricing data.
- 2. Select the range: Click and drag to select the data area where you want the lowest prices highlighted.
- 3. Apply Conditional Formatting: Go to Home > Conditional Formatting > New Rule, choose the formula option, and input your MIN formula.
- 4. Customize and Save: Pick a highlighting color under Format, click OK, and watch your lowest prices instantly stand out.
Frequently Asked Questions
Why is my conditional formatting highlighting the wrong cells?
This usually happens if the active cell when you created the rule doesn't match the first cell reference in your formula. Ensure that if you start your selection from cell B2, your formula begins with =B2.
Can I highlight the highest price in each row instead?
Yes. You can follow the exact same steps but replace the MIN function with the MAX function in your formula, for example: =A2=MAX($A2:$H2).
How do I remove the conditional formatting rule if I make a mistake?
Go to the Home tab, click on Conditional Formatting, select 'Clear Rules', and then choose either 'Clear Rules from Selected Cells' or 'Clear Rules from Entire Sheet'.
What happens if there is a tie for the lowest price in a row?
If multiple cells share the exact same minimum value in a single row, the conditional formatting rule will highlight all cells that contain that lowest price.
