Excel 2010 conditional formatting – color cell if blank and other cells are not blank

Question

I'm very new to Excel, so any help on the below would be much appreciated. I've done endless Google searching to try to find the answer to my question, but always get stuck with the random symbols in formulas.

I am using Excel 2010 and trying to perform conditional formatting.

I have a spreadsheet with columns A to G as a register of client queries I've received and when they have been responded to. As I receive new inquiries, I log them in this register to keep track of whom I've responded to and what I said. Column G is the date of my response to the client – this is entered after I've responded.

I am trying to format the spreadsheet so that, if I enter a new client inquiry in a new row, it automatically highlights the cell in column G until a date is entered. So basically it will act as a highlighter for all unanswered inquiries.

I understand that I need to click "Conditional Formatting" → "New Rule" → "Use a formula to determine which cells to format", but I am unsure what I need to put into the formula box.

Solution 1

To highlight the cells in column G that is empty, you can select column G and click 'Conditional Formatting' (assuming G1 is the active cell while the whole column G is selected) and use the formula =ISBLANK(G1) in the formula box you have mentioned.

Edit:

To highlight the cells in column G that is empty, and only if all of cells A to F in the same row are not empty, you amend the formula as =AND(ISBLANK(G1), NOT(ISBLANK($A1)), NOT(ISBLANK($B1)), NOT(ISBLANK($C1)), NOT(ISBLANK($D1)), NOT(ISBLANK($E1)), NOT(ISBLANK($F1))).

Scott's answer explains well how to highlight the cells if G is empty, and any of cells A to F in the same row is not empty.

Solution 2

The question is a little imprecise.  Kenneth L’s answer is a good start, but it doesn’t highlight Column G until you’ve entered data into Columns A, B, C, D, E and F.  To highlight Column G if it is blank and any of A-F is non-blank, use

=AND(ISBLANK(G1), OR(NOT(ISBLANK(A1)), NOT(ISBLANK(B1)), NOT(ISBLANK(C1)), NOT(ISBLANK(D1)), NOT(ISBLANK(E1)), NOT(ISBLANK(F1))))

or

=AND(ISBLANK(G1), NOT(AND(ISBLANK(A1), ISBLANK(B1), ISBLANK(C1), ISBLANK(D1), ISBLANK(E1), ISBLANK(F1))))

(You don’t really need the $ signs.)  Of course, ISBLANK(something) is almost equivalent to something="", and NOT(ISBLANK(something)) is almost equivalent to something<>"", so the above are almost equivalent to

=AND(G1="", OR(A1<>"", B1<>"", C1<>"", D1<>"", E1<>"", F1<>""))

and

=AND(G1="", NOT(AND(A1="", B1="", C1="", D1="", E1="", F1="")))

But what we’re talking about, in English, is

If Column G is blank and Columns A-F (collectively) are non-blank

and we can do that with

=AND(G1="", A1&B1&C1&D1&E1&F1<>"")

using the text concatenation operator, &.  For example, if A1 is “Acme”, B1 is “How much does it cost?”, and C1-F1 are blank, then A1&B1&C1&D1&E1&F1 is “AcmeHow much does it cost?”.

Comments

[ClaireJL]

Thanks, I think this is kind of what I want it to do except I only want G to be highlighted if there is some kind of text in the rest of the row..

[ClaireJL]

Sorry I'm a newbie - I don't understand your response!

[ClaireJL]

Sorry, perhaps I'm not being very clear - I don't really understand this language myself.. I would like for G to be highlighted if two conditions are met: 1)G is blank AND 2)any cell in A-F has text in it

[Kenneth L]

You can try the formula =AND(ISBLANK(G1), NOT(ISBLANK($A1)), NOT(ISBLANK($B1)), NOT(ISBLANK($C1)), NOT(ISBLANK($D1)), NOT(ISBLANK($E1)), NOT(ISBLANK($F1))). Hope that it's what you need.

[Kenneth L]

Thanks for stating the logic flaw in my answer! And I would like to share there's a slight difference between ISBLANK() and ="". ISBLANK() is TRUE only if the cell under test is nothing, not even an empty string like ' or a formula returning an empty string. ="" returns true if the cell is blank, or the cell contains an empty string or formula returning an empty string. Despite the difference your solution make it cleaner.

[Scott - Слава Україні]

Yes; I know they are different (that's why I said "almost equivalent to" rather than "the same as").  Thanks for spelling out the distinction.