Skip to content
TheChatArchiveThe Chat Archive
Geminigemini-2.5-flash-preview-04-17· GoogleHugging Face LMArena Human Preference 140K

Apply conditional formatting to give a different fill colour to entire rows (all…

From a Chatbot Arena battleThe same person sent this prompt to two anonymous models at once. Compare it with Mistralmistral-medium-2505’s answer.
  1. 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.

  2. Gemini 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:

    1. Your data has a header row.
    2. Your "Sub-Category" column is, for example, column C.
    3. Your data starts on row 2.
    4. You will specify your chosen starting letter (e.g., 'A') and ending letter (e.g., 'Z').

    Steps:

    1. 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 F1 for the "Start Letter" and F2 for the "End Letter".
      • Label these cells next to them (e.g., put "Start Letter:" in E1 and "End Letter:" in E2) so you remember what they are for.
      • Enter your desired letters in F1 and F2. (e.g., type A in F1 and Z in F2). You can change these letters later to update the formatting instantly.
    2. 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.
    3. Open Conditional Formatting:

      • Go to the Home tab on the Excel ribbon.
      • Click on Conditional Formatting.
      • Select New Rule....
    4. Create a New Formatting Rule:

      • In the "New Formatting Rule" dialog box, select "Use a formula to determine which cells to format".
    5. 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 $C2 with 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 F1 and F2 for your letters, keep $F$1 and $F$2. If you are hardcoding the letters directly into the formula, replace $F$1 with "A" (including quotes) and $F$2 with "Z".
      =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 $ before C keeps it locked to column C, but the 2 without a $ allows the row number to change as the rule is applied down the sheet (C3, C4, C5, etc.). Make sure C2 is 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 return TRUE.
    6. 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.
    7. 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 F1 and does not end with the letter in F2 will now have the fill colour you chose. You can easily change the letters in F1 and F2 to update the formatting.

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.