How to Highlight the Lower of Two Prices in Excel with Conditional Formatting
Question details
The user needs to highlight the lower of two product prices in an Excel row without highlighting anything if the prices are equal.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Comparing two price columns in a spreadsheet to visually identify the cheaper option for each product.
- Observed behavior
- The lower price in each row is automatically highlighted with a designated color, while rows with identical prices remain unformatted.
Ensure your price data is organized in adjacent columns (e.g., Column B and Column C) and that there are no blank cells that might be incorrectly evaluated as zero by the formula.
Use a Conditional Formatting Formula
Apply a custom formula in the conditional formatting rules to evaluate both prices and highlight the minimum value when they are not equal.
By utilizing the AND and MIN functions together within a conditional formatting rule, you can force Excel to compare the two values directly. The formula ensures that formatting is only applied when the values differ, preventing identical prices from being highlighted.
Highlight all the cells containing the prices you want to compare (e.g., select range B2:C20). Make sure to start your selection from the top-left cell of the data range.
Navigate to the Home tab on the Excel ribbon, click on the Conditional Formatting button, and choose New Rule from the dropdown menu.
Select the option 'Use a formula to determine which cells to format'. In the formula box, type: =AND($B2<>$C2,B2=MIN($B2:$C2)) assuming your data begins in row 2.
Click the Format button, go to the Fill tab, and choose a highlight color like green or yellow. Click OK twice to apply the rule to your selected range.
Easily Compare and Highlight Data with WPS Spreadsheet
WPS Spreadsheet fully supports Excel conditional formatting formulas, allowing you to seamlessly highlight minimum values, compare columns, and analyze pricing data for free.
- 1. Open your document: Launch WPS Spreadsheet and open the document containing your pricing data. Select the range of cells you wish to compare.
- 2. Access Conditional Formatting: Go to the Home tab and click on the Conditional Formatting icon, then select New Rule from the list.
- 3. Input the formula: Choose the option to use a formula and enter your custom comparison formula: =AND($B2<>$C2,B2=MIN($B2:$C2)).
- 4. Set color and apply: Click the Format button, select your desired highlight color, and click OK to instantly apply the formatting to the lower prices.
Frequently Asked Questions
Why is my conditional formatting highlighting blank cells?
Excel often evaluates blank cells as zero. You can update your formula to exclude blanks by adding an additional check to the AND function, such as: =AND($B2<>$C2, B2=MIN($B2:$C2), B2<>"").
Can I highlight the entire row instead of just the price cell?
Yes. To highlight the whole row, select the entire table range before creating the rule, and ensure your formula uses absolute column references (like $B2 and $C2) while checking the condition. The relative row reference allows the format to apply across the whole row when the condition is met.
What if I have more than two price columns to compare?
The MIN function can handle multiple columns seamlessly. You can adjust the range inside the MIN formula (for example, =MIN($B2:$D2)) to find the lowest price among three or more columns.
