Google Sheets Conditional Formatting

ZeroG · 2 Jul 2022 at 03:26

I'm using Google Sheets for tracking a currency and I thought this would be a simple thing to do but I can't seem to get it right.

In column E I have a formula D2-C2. In column E I have conditional formatting. If the number is positive then colour the cell green, if it's negative colour the cell red. Simple so far. But then I wanted to add into the mix that if the cell D7 for example is empty then the cell E7 should stay white. For the life of me I just can't seem to work out how to apply this 3rd condition. Would it be using conditional formatting, formulas or a mix of both? Example of table below.

Semple · 2 Jul 2022 at 08:31

ZeroG said:
I'm using Google Sheets for tracking a currency and I thought this would be a simple thing to do but I can't seem to get it right.

In column E I have a formula D2-C2. In column E I have conditional formatting. If the number is positive then colour the cell green, if it's negative colour the cell red. Simple so far. But then I wanted to add into the mix that if the cell D7 for example is empty then the cell E7 should stay white. For the life of me I just can't seem to work out how to apply this 3rd condition. Would it be using conditional formatting, formulas or a mix of both? Example of table below.

It'll still be conditional formatting, was there a rule for if cell doesn't contain a value then do this.

I'm pretty certain I've done this so I'll have a dig through my sheets and see how it was done.

ZeroG · 2 Jul 2022 at 08:36

There is an "Is Not Empty" rule but I couldn't seem to get it to work.

Semple · 2 Jul 2022 at 08:39

Set up a rule that says if the cell = 0 then paint it white.

ZeroG · 2 Jul 2022 at 08:42

Semple said:
Set up a rule that says if the cell = 0 then paint it white.

I don't think that would work as the Cell won't have 0 in it. For the current day it will be empty.

gpuerrilla · 2 Jul 2022 at 08:52

=ISBLANK()

?

ZeroG · 2 Jul 2022 at 09:05

I've tried using the ISBLANK function but I don't think I'm using it right. Is there a command for colouring a cell?

=IF D7 ISBLANK() THEN E7(insert colour command here). I know what I'm trying to do in my head but I just can't find the way of doing it heh.

Semple · 2 Jul 2022 at 09:12

ZeroG said:
I don't think that would work as the Cell won't have 0 in it. For the current day it will be empty.

You could default the values to zero, that way once the formula populates the currency column the difference column will update with a value.

gpuerrilla · 2 Jul 2022 at 09:12

Are you using the Format > Conditional Formatting from the menu or trying to hard code it in?

ZeroG · 2 Jul 2022 at 09:13

gpuerrilla said:
Are you using the Format > Conditional Formatting from the menu or trying to hard code it in?

I was using the menu but wondered if there was a way to hardcode it too.

t31os · 3 Jul 2022 at 00:10

Code:

=IF(ISBLANK(A1),B1,A1-B1)

Purely as an example, adjust cell references as required.

A1 would be the cell you're checking for a blank value, B1 is the value you use if that's true, else do the subtraction.

Breaking that down...

Code:

=IF(
ISBLANK(A1), if this cell is blank/empty
B1, apply this value
A1-B1 else apply this (this part is the equivalent of your existing formula, adjust as needed as with the rest)
)

If your empty cells might actually contain a 0 value, ISEMPTY might be more appropriate because 0 will evaluate to empty, but not blank (at least iirc).