Formatting Issues

How to Apply Excel Conditional Formatting Based on Another Column

Muhammad Talha Sep 27, 2026 868 views

Question details

The user needs to highlight blank cells in a specific column only if the corresponding cell in another column has a value.

How to Apply Excel Conditional Formatting Based on Another Column

Product: Microsoft Excel
Device & OS: not provided
Scenario: Tracking missing data or incomplete records across multiple columns in a spreadsheet by visually flagging empty cells.
Observed behavior: The user wants to automatically apply a specific format, such as a red fill or font, to empty cells in column W when their matching cells in column A are not empty.

Before you start

Ensure your dataset does not contain merged cells in the target columns, as merging can cause formula-based conditional formatting to apply to the wrong rows.

Solution 1Recommended

Use a Custom Formula in Conditional Formatting

Using the AND function combined with ISBLANK provides a precise way to format cells based on multiple conditions across different columns.

By leveraging a custom formula, you can check both cells in the same row simultaneously. The formula returns TRUE only when both conditions are met, triggering your chosen format.

Select the Target Range

Highlight the cells in column W that you want to format (for example, click and drag to select W2:W100).

Open the Conditional Formatting Menu

Navigate to the Home tab on the top ribbon, click on the 'Conditional Formatting' dropdown button, and select 'New Rule'.

Enter the Formula

Choose the option 'Use a formula to determine which cells to format'. In the formula input box, enter `=AND(ISBLANK(W2),NOT(ISBLANK(A2)))`. Make sure to adjust the row number '2' to match the very first row of your highlighted selection.

Set the Desired Format

Click the 'Format' button, navigate to the Fill tab to select a highlight color (such as red), click OK, and then click OK again to apply the rule to your range.

Use a Custom Formula in Conditional Formatting

Relative References: Make sure you use relative references (like W2 and A2 instead of $W$2 and $A$2) for the row numbers. This ensures the rule dynamically adjusts for each row down the column.

Seamless Spreadsheet Formatting

Easily Highlight Cells Using WPS Spreadsheet

WPS Spreadsheet provides full support for advanced conditional formatting formulas, allowing you to highlight missing data effortlessly. It is highly compatible with Microsoft Excel files, ensuring your complex rules work flawlessly.

1. Open Your Spreadsheet: Launch WPS Office and open your data file in WPS Spreadsheet.
2. Select the Data Range: Highlight the specific column or cells where you want the formatting to appear.
3. Apply Conditional Formatting: Go to Home > Conditional Formatting > New Rule from the top menu.
4. Input Formula and Format: Select 'Use a formula', input your custom AND/ISBLANK formula, choose a highlight color, and click OK.

100% compatible with Microsoft Excel conditional formatting rules and formulasUser-friendly interface for managing complex formatting conditionsLightweight application that runs smoothly on any deviceFree alternative offering professional-grade spreadsheet features

microsoft office alternative - wps office

Frequently Asked Questions

Why is my conditional formatting applying to the wrong rows?

This usually happens when the row number in your formula does not match the first row of your selected range. For example, if you highlighted the range W5:W50, your formula must specifically reference row 5 (e.g., `=AND(ISBLANK(W5),NOT(ISBLANK(A5)))`).

Can I highlight the entire row instead of just one cell in column W?

Yes. To highlight the entire row, you must select your entire data range (e.g., A2:Z100) before creating the rule. Then, lock the column references in your formula by adding a dollar sign before the column letters, like this: `=AND(ISBLANK($W2),NOT(ISBLANK($A2)))`.

How do I remove a conditional formatting rule I no longer need?

Go to the Home tab, click on Conditional Formatting, and choose Manage Rules. Select the rule you want to delete from the list, click Delete Rule, and then hit Apply or OK.

Will this formula update automatically if I add new data to the bottom of my sheet?

The formatting will automatically apply to new data only if the new rows fall within the range you originally selected. If you add data below that range, you will need to go to Conditional Formatting > Manage Rules and manually expand the 'Applies to' range.

Document & File Issues