Formatting Issues

How to Highlight Blank Cells But Ignore Rows With Blank Column A in Excel

Nimra Malik Oct 1, 2026 868 views

Question details

The user needs to highlight blank cells within a specific range (like columns B through Z) but wants to prevent the formatting from applying if the corresponding cell in column A of the same row is also blank.

How to Highlight Blank Cells But Ignore Rows With Blank Column A in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Tracking missing data across multiple columns for active records while avoiding the highlighting of completely empty, unused rows at the bottom of a dataset.
Observed behavior: Applying a standard 'format blank cells' rule highlights every empty cell in the selected range, including those in empty rows. The goal state is to restrict the highlighting via a formula so that only rows with an active entry in Column A are evaluated.

Before you start

Ensure your dataset is organized in a clear tabular format without merged cells, and identify the exact range of cells you want the formatting applied to, deliberately excluding Column A from your selection.

Solution 1Recommended

Use the AND Formula in Conditional Formatting

By utilizing the AND function with mixed cell references, you can command Excel to verify multiple conditions before applying a highlight to a blank cell.

The formula =AND($A2<>"",B2="") requires both conditions to be true. The $A2<>"" portion ensures that column A in the current row contains data, with the dollar sign locking the reference to column A. The B2="" portion checks if the current cell is blank, adjusting automatically across your selected range because it lacks dollar signs.

Select the target data range

Highlight the specific cells you want to format, such as B2:Z20. It is crucial that you do not include Column A in this highlighted selection.

Open the Conditional Formatting menu

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

Choose the formula rule type

In the dialog box that appears, select 'Use a formula to determine which cells to format' from the list of rule types.

Input the AND formula

In the formula input box, type =AND($A2<>"",B2=""). Make sure the row number in the formula matches the very first row of your selected range.

Set the highlight format and apply

Click the 'Format' button, go to the 'Fill' tab, choose a highlight color like yellow or light red, and click 'OK' twice to apply the formatting to your data.

Use the AND Formula in Conditional Formatting

Absolute vs. Relative References: Make sure to include the dollar sign before the 'A' ($A2) so Excel always evaluates Column A for the row's status. Omit the dollar sign for the row number so the rule can check each row individually.

Advanced Data Management

Easily Apply Advanced Conditional Formatting in WPS Spreadsheet

WPS Spreadsheet provides a highly compatible and intuitive interface for applying complex conditional formatting rules. You can use the exact same formulas as Excel to seamlessly highlight your data.

1. Open your workbook in WPS Spreadsheet: Launch WPS Office and open the spreadsheet containing the data you wish to format.
2. Highlight your data range: Select the cells you want to evaluate (e.g., B2:Z20), making sure that Column A is not included in the selection.
3. Create a new formatting rule: Navigate to the 'Home' tab, click on 'Conditional Formatting', and click 'New Rule' followed by 'Use a formula to determine which cells to format'.
4. Apply the rule: Enter the formula =AND($A2<>"",B2=""), set your preferred background fill color in the formatting options, and click 'OK'.

Fully compatible with Microsoft Excel (.xlsx) conditional formatting rules and advanced formulas.Clean, familiar user interface that requires no steep learning curve.Lightweight installation with robust, lag-free performance for large datasets.Completely free software for basic and advanced spreadsheet tasks.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my conditional formatting highlighting the wrong rows entirely?

This typically occurs if the starting row in your formula does not match the first row of your selected range. For example, if you highlight B5:Z20, your formula must reference row 5 (e.g., $A5 and B5), otherwise the formatting will be offset.

How can I highlight the entire row instead of just the blank cells?

To highlight an entire row when a specific cell (like B2) is blank but Column A is not, you need to select the entire row range including Column A. Then, change the formula to lock the target column: =AND($A2<>"",$B2="").

Will this formula still work if Column A contains spaces instead of being completely empty?

No. If a cell in Column A contains hidden space characters, Excel will not consider it blank. You can modify the formula to use the TRIM function, such as =AND(TRIM($A2)<>"",B2=""), which tells Excel to ignore random spaces.

Can I copy this conditional formatting rule to other columns or sheets?

Yes. You can use the Format Painter tool found on the Home tab to copy the conditional formatting rule from one range and paint it over another range. Just ensure your mixed references (the dollar signs) align with your new layout.

Document & File Issues