Apply Excel Conditional Formatting to Rows When Cells E to G Are Blank
Question details
Create a formula-based conditional formatting rule that formats an entire row when column A contains a value and the cells in columns E through G on that row are blank.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user wants to identify records that have a value in column A but have not yet received values in columns E, F, and G.
- Observed behavior
- The formatting rule must evaluate each row independently across the full selected target range.
Identify the first data row in the range; this example starts at row 3. Select the complete range you want to format and ensure that cell A3 is the active cell before creating the rule.
Create a formula rule with relative row references
Use this method to format every selected row when column A is not empty and cells E through G are all blank.
The dollar signs lock the checked columns, while the row number remains relative. As Excel evaluates each row in the applied range, the formula changes from row 3 to row 4, row 5, and so on.
Click cell A3, then select A3 and all cells across the rows that should receive formatting. Keep A3 as the active cell in the selection.
On the Home tab, select Conditional Formatting > New Rule > Use a formula to determine which cells to format.
Enter =AND($A3<>"",COUNTBLANK($E3:$G3)=3). This checks that column A has a value and that E, F, and G are all blank on the same row.
Select Format, choose a Fill color or other formatting, then click OK. Confirm the rule again with OK to apply it to the selected range.
Use explicit blank-cell checks when column A must contain text
Use this alternative if rows should be formatted only when the column A value is text rather than any nonblank value.
Highlight incomplete rows with blank cells in WPS Spreadsheets
WPS Spreadsheets lets you create a formula-based conditional formatting rule to highlight rows where column A contains a value while columns E through G are blank. Its familiar spreadsheet interface makes it easy to apply the same logic to Excel-compatible workbooks.
- 1. Select the formatting range: In WPS Spreadsheets, select the target rows starting from A3 and keep A3 as the active cell.
- 2. Open conditional formatting: Go to Home > Conditional Formatting > New Rule.
- 3. Choose a formula rule: Select the option to use a formula for formatting, then enter =AND($A3<>"",COUNTBLANK($E3:$G3)=3).
- 4. Choose the highlight style: Set the fill color or other desired format, then confirm the rule to apply it to the selected rows.
Frequently Asked Questions
Why does only the first row receive conditional formatting in Excel?
Check the Applies to range and ensure it includes all intended rows. Also make sure the formula uses a relative row reference such as $A3, not an absolute reference such as $A$3.
How do I highlight the entire row instead of only column A?
Select all columns that should be highlighted before creating the rule, such as A3:G1000. The formula can still use $A3 and $E3:$G3 to test values on each row.
Does COUNTBLANK treat formulas that return an empty string as blank?
Yes. COUNTBLANK generally counts cells containing formulas that return "" as blank. If this is not desired, use explicit tests appropriate for your worksheet data.
What formula highlights rows where E, F, and G are all empty?
Use =AND($A3<>"",COUNTBLANK($E3:$G3)=3) in Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format.
