Formatting Issues

How to Change Excel Row Colors Based on Names and Cell Entries

John Wilson Sep 28, 2026 871 views

Question details

The user wants to format and highlight an entire row (columns A through M) with a specific color depending on a person's name in column B, but only if column M is not blank.

How to Change Excel Row Colors Based on Names and Cell Entries

Product: Excel
Device & OS: not provided
Scenario: Highlighting specific data rows dynamically based on multiple criteria, combining a text match in one column and a non-blank check in another column.
Observed behavior: Rows need to automatically change to designated colors (e.g., red for John, orange for Mike) across a specified range when both conditions are met simultaneously.

Before you start

Identify the exact starting row of your dataset (e.g., Row 3) to ensure your conditional formatting formula aligns correctly with your data range.

Solution 1Recommended

Use Conditional Formatting with the AND Function

Apply a custom formula rule in Conditional Formatting to check both the name column and the text entry column at the same time.

By utilizing the AND function alongside absolute column references ($), you can ensure that Excel evaluates both the specific name and the presence of data, while correctly applying the color fill across the entire specified row.

Select your data range

Highlight the entire range of cells you want the color to apply to. For example, select $A$3:$M$1000. Do not include your headers if they are in row 1 or 2.

Open Conditional Formatting

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

Set up the first color rule

Choose 'Use a formula to determine which cells to format'. In the formula box, enter =AND($B3="John",$M3<>""). Click 'Format', go to the 'Fill' tab, choose the color Red, and click OK.

Set up the second color rule

Repeat the process by creating another New Rule. Use the formula =AND($B3="Mike",$M3<>""), and set the fill format to Orange. Click OK to apply.

Use Conditional Formatting with the AND Function

Locking Columns: Placing a dollar sign ($) before the column letters ($B3 and $M3) is crucial. It locks the condition to those specific columns so the formatting spans across columns A through M.

Smart Data Formatting

Highlight Data Dynamically in WPS Spreadsheet

WPS Spreadsheet fully supports advanced conditional formatting formulas, allowing you to highlight rows dynamically based on multiple conditions with an intuitive and familiar interface.

1. Select the target range: Open your workbook in WPS Spreadsheet and highlight your data range, such as A3:M1000.
2. Create a new formatting rule: Click 'Conditional Formatting' under the Home tab and select 'New Rule'.
3. Apply your custom formula: Choose 'Use a formula', input =AND($B3="John",$M3<>""), and select your desired background color.

100% compatible with Microsoft Excel conditional formatting rules and formulas.Lightweight architecture ensures smooth performance even with large datasets.Rich set of formatting and data visualization tools available for free.Familiar ribbon interface requires no learning curve to apply rules.

microsoft office alternative - wps office

Frequently Asked Questions

Why is only one cell changing color instead of the entire row?

This happens if you omit the dollar sign ($) before the column letter in your formula. Ensure your formula uses absolute column references like $B3 and $M3 so the rule properly applies across all selected columns.

Can I apply this rule to the entire worksheet instead of a specific range?

While possible, selecting the entire worksheet (e.g., A:M) is not recommended because formatting thousands of blank rows can significantly slow down your workbook's performance. Always try to limit the range to your actual dataset.

How do I edit a conditional formatting rule I've already created?

Go to the Home tab, click Conditional Formatting, and select 'Manage Rules'. In the dialog box, change the 'Show formatting rules for' dropdown to 'This Worksheet', select your rule, and click 'Edit Rule'.

What if I want to match multiple names with the same row color?

You can use the OR function nested inside the AND function. For example, to make the row red for either John or Jane, use the formula: =AND(OR($B3="John",$B3="Jane"),$M3<>"").

Document & File Issues