Applying Conditional Row Formatting in Spreadsheet Based on Cell Content

Other Questions Google Sheets
1

I’m working on a spreadsheet and need help with conditional formatting. Here’s what I want to do:

  1. If a cell in column D starts with ‘BLDUK-ORD’, the whole row should turn orange.
  2. If any cell in column E has content, that row should become green.

This needs to work for my entire spreadsheet, which has over 30,000 rows of data. I’m not sure how to set this up. Can anyone guide me through the process or share a formula that would work?

I’ve tried a few things but can’t get it right. It would be great if someone could explain how to do this step-by-step. Thanks for any help!

2

hey liamj, i can help with that! for orange rows, use this formula in conditional formatting: =LEFT($D1,9)=“BLDUK-ORD”. for green rows, use: =NOT(ISBLANK($E1)). apply to whole range, orange rule first. lemme know if u need more help!

3

Having worked extensively with large datasets, I can offer some insights on your conditional formatting question. To achieve what you’re looking for, you’ll need to create two separate conditional formatting rules.

For the orange rows, use this formula: =LEFT($D1,9)=“BLDUK-ORD”
For the green rows, use: =NOT(ISBLANK($E1))

Apply these rules to your entire data range, ensuring the orange rule is set first. This approach should work efficiently even with 30,000+ rows.

One tip: If you notice performance issues, consider using helper columns instead of direct conditional formatting. This can significantly improve spreadsheet responsiveness when dealing with large datasets.

4

I’ve dealt with similar conditional formatting challenges in my work. Here’s how you can set this up:

For the orange rows, go to Conditional Formatting > New Rule > Use a formula. Enter this formula:
=LEFT($D1,9)=“BLDUK-ORD”

Then set the format to fill the cells orange.

For the green rows, create another rule with this formula:
=NOT(ISBLANK($E1))

Set this to fill cells green.

Apply both rules to your entire data range. The order matters - put the orange rule first, then the green one.

This should work for large datasets. If it’s slow, try applying the rules to smaller ranges or using helper columns. Hope this helps!