Formatting Issues

How to Highlight Rows with Missing Data Using Excel Conditional Formatting

Khadija Khan Oct 14, 2026 869 views

Question details

The user wants to conditionally format rows in a spreadsheet where specific verification data is missing, provided that another related column in the same row contains data.

How to Highlight Rows with Missing Data Using Excel Conditional Formatting

Product: Excel
Device & OS: not provided
Scenario: Setting up an automated tracker or database where incomplete records need to be highlighted for users without formatting completely unused empty rows below the dataset.
Observed behavior: The goal is to apply highlighting exclusively to active rows with missing verification elements using formula-based conditional formatting that scales automatically as new entries are added.

Before you start

Before applying your conditional formatting rules, consider converting your data range into an Excel Table (Ctrl+T). This ensures any rules you apply will automatically extend as future users add new rows to the database.

Solution 1Recommended

Use the AND Formula in Conditional Formatting

Apply a formula-based conditional formatting rule to highlight rows only when specific target cells are blank while other cells in the same row contain data.

Using a built-in 'highlight blank cells' rule often fails in databases because it highlights empty rows at the bottom of the sheet. Using an =AND() formula allows you to test two conditions simultaneously: whether a mandatory column is filled, and whether a subsequent verification column is left blank.

Select your data range

Highlight the range where you want the formatting applied, such as A2:P1000. If you are using an Excel Table, select all the active data rows of the table.

Open the Conditional Formatting menu

Navigate to the 'Home' tab on the Excel ribbon, click on 'Conditional Formatting' in the Styles group, and select 'New Rule'.

Enter the formula for missing data

Choose 'Use a formula to determine which cells to format'. Enter the formula =AND($K2="",$P2<>"") to highlight rows where column K (e.g., Verified By) is blank, but column P has data. Make sure to use the exact absolute column ($) and relative row references.

Set the highlight format and apply

Click the 'Format' button, go to the 'Fill' tab, choose a bright color to highlight the incomplete row, click 'OK', and then 'OK' again to apply the rule to your selection.

Use the AND Formula in Conditional Formatting

Highlighting Missing Verification Dates: To highlight a missing 'Verified Date' (e.g., in column L) when 'Verified By' (column K) is already completed, you can repeat the entire process and create a second rule using the formula =AND($K2<>"",$L2="").

Advanced Formatting with WPS Spreadsheet

Easily Highlight Missing Data with WPS Office

WPS Spreadsheet fully supports advanced formula-based conditional formatting. You can easily highlight incomplete rows using the exact same formulas, providing a seamless database management experience without the high costs.

1. Open your dataset in WPS Spreadsheet: Launch WPS Office, open your existing spreadsheet, and select the data range you want to monitor for missing verification data.
2. Create a New Formatting Rule: Go to the Home tab, click on Conditional Formatting, and select New Rule from the drop-down menu.
3. Apply your formula: Select 'Use a formula to determine which cells to format', input your AND formula (such as =AND($K2="",$P2<>"")), choose your preferred fill color, and click OK.

100% compatible with Microsoft Excel conditional formatting rules and syntax.Lightweight software that runs smoothly on various devices with large datasets.Free and easy to use with a highly familiar user interface, requiring no relearning.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my conditional formatting highlight completely empty rows at the bottom?

If you simply set a rule to format blank cells, Excel applies it to all empty cells in your selected range, including unused rows. Using an =AND() formula fixes this by ensuring the row is only highlighted if the specific field is blank while another field in that exact row contains actual data.

How do I ensure my conditional formatting rules apply automatically to new rows?

The most robust method is to format your dataset as a Table (Insert > Table or Ctrl+T) before applying any conditional formatting. When you type in the row immediately below a Table, it expands automatically, bringing the conditional formatting rules with it.

What do the dollar signs mean in the formatting formula =AND($K2="",$P2<>"")?

The dollar sign ($) creates an absolute reference for the column. This forces Excel to always evaluate the status of columns K and P regardless of which cell within that row is currently being formatted. This ensures the entire row gets highlighted simultaneously rather than just a single cell.

Document & File Issues