Formatting Issues

How to Apply Conditional Formatting to an Entire Row Based on a Dropdown in Excel

Maira Mehtab Sep 22, 2026 869 views

Question details

The user wants to change the appearance of an entire row automatically based on the selection from a dropdown menu, such as highlighting the row when a task is marked 'Yes'.

Product: Excel
Device & OS: not provided
Scenario: Highlighting an entire row when a task is marked as completed via a dropdown list, without manually applying cell formatting each time.
Observed behavior: Excel cannot conditionally apply a built-in cell style (like 'Good') directly based on a dropdown, but formula-based conditional formatting can achieve the desired row highlighting effect.

Before you start

Ensure your dropdown lists are already set up in a specific column using Data Validation before creating the conditional formatting rule.

Solution 1Recommended

Use a Formula-Based Conditional Formatting Rule

Apply conditional formatting using an absolute column reference and relative row reference to highlight multiple rows efficiently.

By using a formula like =$M2="yes", you lock the column containing the dropdown (M) with a dollar sign ($) while leaving the row number relative. This allows Excel to dynamically apply the rule to subsequent rows in your selected range without needing manual adjustments.

Select the target range

Highlight the entire range of rows you want to apply the formatting to (for example, select rows 2 through 100).

Open Conditional Formatting

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

Enter the formula

Choose 'Use a formula to determine which cells to format'. Enter the formula (e.g., =$M2="yes") where 'M' is the column with your dropdown and '2' is the first row of your selection.

Set the format

Click the 'Format' button, choose your desired fill color and font style (like a green font and fill), and click OK to apply the rule.

Absolute vs. Relative References: Make sure you only place the dollar sign before the column letter (e.g., $M2). If you use $M$2, the rule will only check the first row for all cells.

Solution 2

Use the Format Painter to Copy Row Formatting

If you have already created the rule for a single row, you can quickly copy it to other rows using the Format Painter.

Manage Data Effortlessly

Highlight Rows Easily in WPS Spreadsheet

WPS Office Spreadsheet fully supports formula-based conditional formatting and data validation. You can easily highlight entire rows based on dropdown selections while enjoying a highly compatible and free office suite.

1. Select Range: Open your file in WPS Spreadsheet and select the rows you want to format.
2. Create Rule: Navigate to the Home tab, click 'Conditional Formatting', select 'New Rule', and choose the formula option.
3. Apply Formula: Enter your formula with an absolute column reference (e.g., =$M2="yes"), set your formatting style, and click OK.

Seamless compatibility with Microsoft Excel (.xlsx) files and conditional formatting rules.Full support for advanced conditional formatting formulas and dropdown lists.Free to download with a familiar, user-friendly interface.Lightweight software that runs smoothly on Windows, Mac, and Linux.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my conditional formatting only highlighting one cell instead of the whole row?

This happens if you do not lock the column reference. Make sure to place a dollar sign ($) before the column letter in your formula (e.g., =$M2="yes") so the program checks that specific column for every cell in the row.

Can I apply multiple conditional formatting rules to the same row?

Yes, you can create multiple rules for different dropdown values. For example, you can have one rule for =$M2="yes" with a green fill, and another rule for =$M2="no" with a red fill. Go to Conditional Formatting > Manage Rules to prioritize them.

How do I edit an existing conditional formatting rule?

Select the cells with the formatting, go to the Home tab, click Conditional Formatting, and select 'Manage Rules'. Choose the rule you want to edit and click 'Edit Rule'.

Does this work with text other than 'Yes' or 'No'?

Absolutely. You can replace 'yes' in the formula with any exact text from your dropdown, such as 'Completed', 'In Progress', or 'Pending'. Ensure the text is enclosed in double quotation marks within the formula.

Document & File Issues