Apply conditional formatting to give a different fill colour to entire rows (all…
- User
Apply conditional formatting to give a different fill colour to entire rows (all columns) where the Sub-Category does not start with a letter of your choice and does not end with another letter of your choice.
Assistant
Okay, let's set this up in Excel. We'll use a Conditional Formatting formula that checks the first and last characters of the cell in your "Sub-Category" column for each row.
Here's how to do it:
Assumptions:
- Your data has a header row.
- Your "Sub-Category" column is, for example, column C.
- Your data starts on row 2.
- You will specify your chosen starting letter (e.g., 'A') and ending letter (e.g., 'Z').
Steps:
-
Choose Cells for Your Letters (Optional but Recommended):
- Pick two empty cells outside your data range to enter the starting and ending letters you want to check against. For example, you could use
F1for the "Start Letter" andF2for the "End Letter". - Label these cells next to them (e.g., put "Start Letter:" in
E1and "End Letter:" inE2) so you remember what they are for. - Enter your desired letters in
F1andF2. (e.g., typeAinF1andZinF2). You can change these letters later to update the formatting instantly.
- Pick two empty cells outside your data range to enter the starting and ending letters you want to check against. For example, you could use
-
Select Your Data Range:
- Select all the rows and columns of your data, excluding the header row. For example, if your data is in columns A through D and goes down to row 100, select the range
A2:D100.
- Select all the rows and columns of your data, excluding the header row. For example, if your data is in columns A through D and goes down to row 100, select the range
-
Open Conditional Formatting:
- Go to the Home tab on the Excel ribbon.
- Click on Conditional Formatting.
- Select New Rule....
-
Create a New Formatting Rule:
- In the "New Formatting Rule" dialog box, select "Use a formula to determine which cells to format".
-
Enter the Formula:
- Click into the "Format values where this formula is true:" box.
- Enter the following formula. You'll need to adjust the cell references:
- Replace
$C2with the first cell in your "Sub-Category" column (making sure the column letter is absolute with$but the row number is relative without$). If your Sub-Category is in column E and data starts row 3, this would be$E3. - If you used cells
F1andF2for your letters, keep$F$1and$F$2. If you are hardcoding the letters directly into the formula, replace$F$1with"A"(including quotes) and$F$2with"Z".
- Replace
=AND(LEFT($C2,1)<>$F$1, RIGHT($C2,1)<>$F$2)- Explanation of the formula:
$C2: Refers to the cell in column C for the current row being evaluated. The$beforeCkeeps it locked to column C, but the2without a$allows the row number to change as the rule is applied down the sheet (C3, C4, C5, etc.). Make sureC2is the very first data cell in your Sub-Category column.LEFT($C2,1): Gets the first character of the text in the cell$C2.LEFT($C2,1)<>$F$1: Checks if the first character is not equal to the letter in cell$F$1. (Use<>"A"if hardcoding 'A').RIGHT($C2,1): Gets the last character of the text in the cell$C2.RIGHT($C2,1)<>$F$2: Checks if the last character is not equal to the letter in cell$F$2. (Use<>"Z"if hardcoding 'Z').AND(...): This function ensures that both conditions (first character is not the start letter AND last character is not the end letter) must be true for the formula to returnTRUE.
-
Choose Your Formatting:
- Click the Format... button.
- Go to the Fill tab.
- Select the fill colour you want to use for the highlighted rows.
- Click OK.
-
Apply the Rule:
- In the "New Formatting Rule" dialog box, you should see the formula and a preview of your chosen format.
- Click OK.
The selected rows where the Sub-Category value does not start with the letter in
F1and does not end with the letter inF2will now have the fill colour you chose. You can easily change the letters inF1andF2to update the formatting.
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.