How to Highlight the Lowest Value Only When Multiple Values Exist in Excel
Question details
The user wants to apply conditional formatting to highlight the minimum numeric value in a specified range, but only if there are at least two numeric values present in that range.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Analyzing data where a single entry shouldn't be highlighted as the "lowest" value by default if it is the only number in the dataset.
- Observed behavior
- Standard "Bottom 10%" or "Minimum" conditional formatting rules will highlight a single value if it is the only one in the range, which the user wants to avoid.
Ensure your target data range contains purely numeric values, and identify the exact cell references for the range you want to format (e.g., G2:G25) before creating the formula.
Use the COUNT and MIN Functions in Conditional Formatting
This method combines the COUNT function to check for multiple values and the MIN function to find the lowest number, wrapping them in an AND statement.
By using the AND function, you can set two conditions for your formatting rule. The COUNT function ensures there is more than one numeric value in the range, while the MIN function identifies the lowest value. Both conditions must be met for the formatting to apply.
Click and drag to select the cells you want to format (e.g., G2:G25), ensuring the top cell (G2) is the active cell.
Go to the Home tab on the ribbon, click on 'Conditional Formatting', and select 'New Rule'.
Choose 'Use a formula to determine which cells to format'. In the formula box, enter =AND(COUNT($G$2:$G$25)>1,G2=MIN($G$2:$G$25)). Adjust the cell references to match your specific range.
Click the 'Format' button, choose your desired fill color or font color, click 'OK' to close the format dialog, and click 'OK' again to apply the rule.
Highlight the Lowest Value Only if it is Unique
If you also want to avoid highlighting the lowest value when it appears more than once in your dataset, use a combination of MIN and COUNTIF.
Easily Apply Complex Conditional Formatting with WPS Spreadsheet
WPS Spreadsheet fully supports advanced formula-based conditional formatting. You can easily highlight specific data points, such as the lowest value among multiple entries, using the exact same formulas as Microsoft Excel.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your data.
- 2. Access Conditional Formatting: Select your data range, go to the Home tab, and click on 'Conditional Formatting' > 'New Rule'.
- 3. Apply the formula: Select 'Use a formula to format cells', input your =AND(...) formula, customize the fill color, and click OK.
Frequently Asked Questions
Why is my conditional formatting rule highlighting the wrong cells?
This usually happens if the active cell when creating the rule does not match the relative reference in your formula. Ensure that if your formula uses G2 as the relative reference, G2 is the active (first selected) cell in your highlighted range.
Can I highlight the highest value instead using a similar formula?
Yes, you can replace the MIN function with the MAX function. The formula would simply be updated to =AND(COUNT($G$2:$G$25)>1,G2=MAX($G$2:$G$25)).
Does this formula work if there are blank cells or text in the range?
Yes, the COUNT function only counts cells containing numbers. Blank cells and text are ignored, so the rule will still correctly identify if there is more than one numeric value in the range.
