Formatting Issues

How to Use Excel Data Validation & Conditional Formatting to Compare Cells

Natalie Taylor Sep 28, 2026 869 views

Question details

The user needs to validate data entry by comparing two cell values and highlight invalid entries using conditional formatting, while properly handling rules for blank cells.

How to Compare Cells Using Data Validation and Conditional Formatting in Excel

Product: Excel
Device & OS: not provided
Scenario: Creating data entry rules and visual highlights to ensure values in one column remain greater or less than values in a reference column (e.g., Inside vs. Outside Diameter).
Observed behavior: Data validation works for direct comparisons, but requires specific custom formulas or settings to prevent invalid data entry when the reference cells are intentionally left blank.

Before you start

Ensure your data is organized in adjacent columns (such as Column AA and Column AB) and identify which column will serve as the reference value for the validation rule.

Solution 1Recommended

Apply a Custom Data Validation Formula

Use a custom formula in the Data Validation tool to restrict entries based on another cell's value and control blank cell behavior.

Data validation rules can enforce logic across columns, but special attention is needed for blank cells. By default, Excel may ignore blanks, allowing invalid inputs if the reference cell is empty.

Select the target range

Highlight the cells where you will enter data, for example, AB3:AB10.

Open Data Validation

Navigate to the 'Data' tab on the Excel ribbon and click on 'Data Validation'.

Set up a custom rule

In the Settings tab, select 'Custom' from the 'Allow' dropdown menu.

Input the comparison formula

Enter your logical formula in the Formula box. For example, to ensure AB3 is greater than AA3, type =AB3>AA3.

Manage blank cell settings

If you want to prevent users from entering data when the source cell (AA) is empty, uncheck the 'Ignore blank' option, then click 'OK'.

Advanced Blank Cell Handling: If you want to allow entries when the source cell is blank but still restrict them when populated, use a formula like =OR(ISBLANK(AA3), AB3>AA3).

Solution 2

Highlight Invalid Entries with Conditional Formatting

Set up a conditional formatting rule to visually flag cells that violate your comparison logic.

Apply Advanced Validation and Formatting with WPS Spreadsheet

WPS Office provides powerful and intuitive tools for data validation and conditional formatting, making it simple to create complex cell comparison rules, highlight errors, and manage blank inputs effectively.

1. Open your workbook in WPS: Launch WPS Spreadsheet and open your document containing the data columns.
2. Configure Data Validation: Highlight the target column, navigate to Data > Validation, choose Custom, and type your comparison formula.
3. Apply Conditional Highlights: Navigate to Home > Conditional Formatting > New Rule, enter your validation logic, and choose a highlight color.

Fully compatible with Microsoft Excel formulas, validation rules, and formatting.Intuitive Data Validation menu with seamless custom formula support.Robust Conditional Formatting options for immediate visual data analysis.Lightweight and free to use across Windows, Mac, and mobile platforms.

Frequently Asked Questions

Why does my data validation rule allow invalid entries when the reference cell is blank?

By default, Excel checks the 'Ignore blank' option in the Data Validation menu. If the reference cell is empty, it bypasses the rule. You must uncheck 'Ignore blank' or wrap your rule in a strict formula like =AND(AA3<>"", AB3>AA3) to enforce validation.

Can I compare cells across different worksheets using data validation?

Yes, you can compare values across worksheets by referencing the sheet name in your formula. For example, =A1>Sheet2!A1 will validate that the cell A1 on your current sheet is greater than A1 on Sheet2.

How do I apply conditional formatting to an entire row based on a two-cell comparison?

Select the entire data table range, then use absolute column references in your formula (e.g., =$AB3<=$AA3). Adding the dollar sign ($) locks the comparison to those specific columns while allowing the formatting to apply across the entire row.

Document & File Issues