Formatting Issues

How to Highlight Cells When Another Cell is Not Blank in Excel & WPS

Huda Qurayshi Oct 10, 2026 868 views

Question details

The user wants to apply formatting to specific columns in a row (e.g., Columns A through C) based on whether a corresponding cell in another column (e.g., Column M) contains data.

How to Highlight Cells When Another Cell is Not Blank

Product: Excel / WPS Spreadsheet
Device & OS: not provided
Scenario: Tracking task progress or sign-out dates where a visual indicator needs to automatically apply across multiple cells in a row as soon as a target status cell is filled.
Observed behavior: The selected range of cells automatically changes its background fill color whenever the specific trigger cell in the same row is not empty.

Before you start

Ensure your data is organized in a tabular layout without merged cells, and identify both the range you want to highlight and the specific column that will act as your condition trigger.

Solution 1Recommended

Use Conditional Formatting with a Custom Formula

Create a conditional formatting rule using the "not equal to blank" (<>"") formula to dynamically format your target cells.

This method uses an absolute column reference and a relative row reference, allowing the single formula to correctly evaluate every row in your selected dataset.

Select the Target Range

Highlight the specific cells you want to color. For example, click and drag to select the range A2:C100.

Open Conditional Formatting

Navigate to the 'Home' tab on your top ribbon, click on the 'Conditional Formatting' button, and select 'New Rule' from the drop-down menu.

Choose the Formula Option

In the New Formatting Rule dialog box, select 'Use a formula to determine which cells to format'.

Enter the Condition Formula

In the formula input box, type =$M2<>"". The dollar sign ($) locks the column M so the condition always checks there, while row 2 remains relative to check row-by-row.

Set the Fill Color

Click the 'Format' button, switch to the 'Fill' tab, choose your desired highlight color, and click 'OK' twice to apply the rule to your selection.

Use Conditional Formatting with a Custom Formula

Reference Alignment: Always ensure the row number in your formula matches the top row of your selected range. If your selection starts at A2, your formula must reference row 2 (e.g., $M2).

WPS Spreadsheet Solution

Dynamically Highlight Data with WPS Spreadsheet

WPS Spreadsheet offers a robust Conditional Formatting engine that makes it incredibly easy to visualize data patterns, track project statuses, and automate row highlighting based on complex cell references.

1. Select Data Range: Highlight the columns or rows you want to apply the formatting to (e.g., A2:C100).
2. Access Formatting Tools: Go to Home > Conditional Formatting > New Rule in the top navigation ribbon.
3. Apply Custom Formula: Select the formula option, input =$M2<>"", pick your custom background color, and click OK.

Fully compatible with Microsoft Excel conditional formatting rules and formulas.Intuitive and user-friendly interface for setting up complex data tracking.Lightweight performance that smoothly processes large datasets with active formatting rules.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my conditional formatting highlighting the wrong rows?

This mismatch usually occurs if the row number in your formula does not match the active starting cell of your highlighted range. If you selected A2:C100, ensure your formula explicitly uses row 2 (e.g., =$M2<>"") rather than row 1.

How can I highlight the entire row instead of just columns A through C?

To highlight the entire row, simply expand your initial selection to encompass all the columns in your data set (e.g., A2:Z100) before setting up the conditional formatting rule. The formula (=$M2<>"") remains exactly the same.

Can I highlight cells only if another cell contains a specific word?

Yes. Instead of checking for a non-blank status, you can check for specific text. Use the formula =$M2="Completed" in the conditional formatting rule to apply the color only when column M contains the exact word 'Completed'.

Will the highlight disappear if I delete the contents of the trigger cell?

Yes, conditional formatting is fully dynamic. If you clear the data in the trigger cell (e.g., cell M5 becomes blank), the highlighted color on cells A5 through C5 will automatically disappear instantly.

Document & File Issues