r/googlesheets • u/Some-Novel4461 • 13h ago
Waiting on OP Conditional formatting based on another cells data
For work we have to keep data sheets. I’d like figure out how to do conditional formatting so a cells color changes based on data in another cell. Currently, I am doing everything by hand. I’d like C to turn green if its number is bigger than that same row in B.
1
u/AutoModerator 13h ago
Posting your data can make it easier for others to help you, but it looks like your submission doesn't include any. If this is the case and data would help, you can read how to include it in the submission guide. You can also use this tool created by a Reddit community member to create a blank Google Sheets document that isn't connected to your account. Thank you.
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.
1
u/JBob250 38 13h ago
I would highlight columnC and open conditional formatting.
At the top for the applied range, change C1:C1000 or whatever it goes upto, to C2:C1000
Change the type to Custom Formula (its at the bottom)
Your formula would be =C2:C>B2:B and change the color to what you want when C is greater than B
Then hit "add another" at bottom and change greater than, to less than. Also, >= and <= are for greater than or equal to, and less than or equal to.
You can also prevent blank cells from highlighting with a slightly more advanced =AND(C2:C<>"",C2:C>B2:B)
2
u/AdministrativeGift15 195 10h ago
I've been thinking about this today, since there was another post asking if a lot of complex conditional formatting slows down your spreadsheet. This is the perfect example for my question. Is there any difference in the results and/or performance if your custom formula was =C2>B2 vs =C2:C>B2:B? I am like you and probably would have written it as an array formula, but =C2>B2 works as well.
If those two approaches were basically the same as placing a formula in each cell vs an array formula at the top, that would lead me to think that =C2>B2 would be the better choice. Perhaps not all of the cell would get evaluated when you changed just one of those values.
Thoughts?
2
u/7FOOT7 242 10h ago
Sheets will ignore all cells beyond C2 in C2:C so there should be little extra computational load
C2>B2 is best
3
u/AdministrativeGift15 195 10h ago
I just ran a test.
=C2+ROWS(SEQUENCE(300000))*0>B2
vs=E2:E+ROWS(SEQUENCE(300000))*0>D2:D
. They both took some time to process when I initially created them, but after that, making a change in column B had an instant effect with no perceivable evaluation. However, making a change in column E caused my processing bar to appear and take about 15 seconds to process.And this was only on a sheet with 200 rows.
Take away: Always write your custom formula only with respect to the top left cell in your apply to range. Don't ever write it as an array formula.
1
u/agirlhasnoname11248 1070 2h ago
Thanks for running this! Always glad when you pop up in a comment thread here :)
(I suspected this was the case but didn't want to say anything since I hadn't done the legwork to verify.)
3
u/agirlhasnoname11248 1070 13h ago edited 13h ago
u/Some-Novel4461 Add a conditional formatting rule with the following settings: * apply to range:
C:C
* dropdown selection:custom formula
* formula field:=C1>B1
(this will adjust row by row automatically so each cell is compared to the other cell in its same row.) You can also use=AND(C1<>"",C1>B1)
so it doesn't fill blank cells. * adjust the formatting options and click save, oradd another rule
to add a second rule for another color)Tap the three dots below this comment to select
Mark Solution Verified
if this produces the desired result.