How to change row color in Excel based on text input?

To change row color in Excel based on text input, you'll use Conditional Formatting with a formula. This powerful feature allows you to automatically apply specific formatting, such as a fill color, to an entire row when a cell within that row contains a particular text value.

How to Change Row Color Based on Text Input in Excel

The most effective way to highlight an entire row based on text in a specific cell is by creating a Conditional Formatting rule that uses a formula. This method ensures the formatting applies dynamically as your data changes.

Step-by-Step Guide: Highlighting Rows Based on Text

Follow these steps to set up your conditional formatting rule:

1. Select Your Data Range

First, select the range of cells or rows you want to apply the formatting to. Crucially, do not select the header row (if you have one) and start your selection from the first data row. For example, if your data starts in cell A2 and goes down to D100, select the range A2:D100. This initial selection is vital for the formula to work correctly across all rows.

• Tip: When selecting, click and drag from the top-left cell of your data (e.g., A2) down to the bottom-right cell (e.g., D100). Avoid selecting entire rows by clicking the row numbers on the left, as this might apply the rule to cells outside your actual data.

2. Access Conditional Formatting

With your data range selected:

1. Go to the Home tab on the Excel ribbon.
2. In the "Styles" group, click on Conditional Formatting.
3. From the dropdown menu, select New Rule....

3. Create a New Formatting Rule

In the "New Formatting Rule" dialog box:

1. Choose the rule type: "Use a formula to determine which cells to format".

4. Enter the Formatting Formula

This is where you define the condition based on your text input. In the "Format values where this formula is true" field:

1. You will type an equals sign (=) to start your formula.

2. Follow this with a cell reference that points to the first cell in the column you want to check for text, within your selected data range. This reference must be an absolute column and relative row reference (e.g., $A2).

   • For instance, if your text input is in column A, and your selected range starts at row 2, your reference would be $A2. The $ before A locks the column, ensuring the rule always checks column A for every row, but the 2 without a $ allows the row reference to adjust as the rule applies down your selected range (e.g., checking A3 for row 3, A4 for row 4, and so on).

3. After the cell reference, add an equals sign (=) followed by the specific status name in quotation marks. For example, ="Pending" or ="Completed".

   Example Formula:
   =$A2="Pending"

   This formula tells Excel: "For each row in my selected range, check if the cell in column A (e.g., A2, A3, A4...) contains the exact text 'Pending'."

5. Choose Your Desired Format

1. Click the Format... button to open the "Format Cells" dialog box.
2. Go to the Fill tab to select a background color for the row.
3. You can also use the Font, Border, or Number tabs to apply other formatting like bold text, a specific font color, or borders.
4. Once you've made your selections for how you want the cells to look, click OK.

6. Apply the Rule

1. You'll return to the "New Formatting Rule" dialog box, where you can see a preview of your chosen format.
2. Click OK to apply the rule to your selected data.

Your rows will now automatically change color based on the specified text input in the chosen column.

Practical Formula Examples for Text Input

Here are some common formulas you can use, adapting them to your specific needs:

Scenario	Formula Example (assuming check in Column B, starting at B2)	Explanation
Exact Match	=$B2="Completed"	Highlights the row if cell B2 (or B3, B4, etc.) contains exactly "Completed". Case-sensitive.
Exact Match (Any Case)	=UPPER($B2)="COMPLETED"	Highlights the row if cell B2 contains "Completed", "completed", "COMPLETED", etc. (converts cell text to uppercase for comparison).
Partial Match (Contains)	=SEARCH("Urgent", $B2)>0	Highlights the row if cell B2 contains "Urgent" anywhere within its text (e.g., "Urgent Review", "This is Urgent"). Not case-sensitive.
Multiple Conditions (AND)	=AND($B2="High", $C2="Pending")	Highlights the row if cell B2 is "High" and cell C2 is "Pending".
Multiple Conditions (OR)	=OR($B2="Low", $D2="Expired")	Highlights the row if cell B2 is "Low" or cell D2 is "Expired".
Text Not Equal To	=$B2<>"Done"	Highlights the row if cell B2 does not contain "Done".
Cell is Not Empty	=$B2<>""	Highlights the row if cell B2 is not empty.

Managing Your Conditional Formatting Rules

To view, edit, or delete your conditional formatting rules:

1. Select any cell within the range where the rule is applied.
2. Go to the Home tab > Conditional Formatting > Manage Rules....
3. In the "Conditional Formatting Rules Manager," you can select a rule and choose to Edit Rule..., Delete Rule, or change its Order (important if you have multiple overlapping rules).

By mastering conditional formatting with formulas, you gain immense control over the visual presentation of your Excel data, making it easier to spot critical information at a glance.