16

How do I conditionally format a cell so if not blank it is grey?

I tried to do 'not equal', but it didn't work.

I am using Windows Office 2003 with Windows XP at work. I don't see the same feature as below:

enter image description here

What I have tried so far:

enter image description here

Edit: Figured what was wrong. In my production (actual work Excel), they were filled with white color. It wasn't my Excel file, so I was not aware of this before.

Improve this question
5
  • What specifically did you try to do in Excel 2003? Can you include the example formula you use in the rule?
    – David Zemens
    Commented May 14, 2013 at 2:08
  • @DavidZemens I am working on a excel sheet where I need to keep track of multiple items, and when it is not blank I need to high light them.
    – George
    Commented May 14, 2013 at 12:54
  • Can you include an example of the formatting formula that you used? You indicated that you tried some formula, I tried to do not equal, but it didn't work -- can you show us what you tried?
    – David Zemens
    Commented May 14, 2013 at 13:43
  • @DavidZemens Yes, I have attached what I have tried so far (seperately). Thanks!
    – George
    Commented May 14, 2013 at 14:03
  • Looks like you have it all figured out now. Good job!
    – David Zemens
    Commented May 14, 2013 at 14:31

6 Answers 6

Reset to default
23

Does this work for you:

enter image description here

You find this dialog on the Home ribbon, under the Styles group, the Conditional Formatting menu, New rule....

Improve this answer
5
  • You will need to chnage the value in the "Format only cells with:" dtopdown to "No Blanks".
    – Declan_K
    Commented May 13, 2013 at 21:12
  • @Declan_K - thanks you are right. I'm sure OP can figure it out, but I will update later (when I have a computer).
    – Floris
    Commented May 13, 2013 at 21:40
  • @Floris - Sorry for the delay response, I posted the question before I left my office. My office is still using Window XP with office 2003. I don't think I see the same box like yours.
    – George
    Commented May 14, 2013 at 1:47
  • 3
    Ouch... Microsoft change things a lot between versions. I may have an old laptop with XP somewhere. If I can get it to come back to life I may take another look. Otherwise - maybe someone else in the community has an answer for you in the meantime. I have updated the tag...
    – Floris
    Commented May 14, 2013 at 2:02
  • Glad someone else could help!
    – Floris
    Commented May 14, 2013 at 16:58
19

You can use Conditional formatting with the option "Formula Is". One possible formula is

=NOT(ISBLANK($B1))

enter image description here

Another possible formula is

=$B1<>""

enter image description here

Improve this answer
4
  • Figured out the problem, in my production list, I have fill with color white orginally. =NOT(ISBLANK(A1)) works. Thank you Teylyn.
    – George
    Commented May 14, 2013 at 14:13
  • I can confirm that this solution works in a formatted table. Formula Is =NOT(ISBLANK($B1)) Then, make sure that the region your setting to conditional formatting is correct. Not sure exactly why this works but it does work correctly. I wanted a similar solution but I had a week # in my Column-A, then blank cells until the next week but I wanted that week row highlighted. This did the trick!
    – BeachBum68
    Commented May 27, 2017 at 23:00
  • 1
    What if what needed is that only if the cell is not empty apply the conditional formatting.
    – Royi
    Commented Jul 17, 2017 at 7:59
  • I like =$B1<>"" better as the formula is shorter and easier for others to understand. This worked for me in Excel 2013.
    – jaylweb
    Commented Dec 29, 2017 at 14:40
2

In Excel 2003 you should be able to create a formatting rule like:

=A1<>"" and then drag/copy this to other cells as needed.

If that doesn't work, try =Len(A1)>0.

If there may be spaces in the cell which you will consider blank, then do:

=Len(Trim(A1))>0

Let me know if you can't get any of these to work. I have an old machine running XP and Office 2003, I can fire it up to troubleshoot if needed.

Improve this answer
1
  • 1
    Using =$A1<>"" worked for me in Excel 2013. I like this better than =NOT(ISBLANK($A1)). It's a smaller formula and easier for others to understand.
    – jaylweb
    Commented Dec 29, 2017 at 14:37
2

This worked for me:

=NOT(ISBLANK(A1))

I wanted a box around NOT Blank cells in an entire worksheet. Use the $A1 if you want the WHOLE ROW formatted based on the A1, B1, etc result.

Thanks!

Improve this answer
1

This method works for Excel 2016, and calculates on cell value, so can be used on formula arrays (i.e. it will ignore blank cells that contain a formula).

  • Highlight the range.
  • Home > Conditional Formatting > New Rule > Use a Formula.
  • Enter "=LEN(#)>0" (where '#' is the upper-left-most cell in your range).
  • Alter the formatting to suit your preference.

Note: Len(#)>0 be altered to only select cell values above a certain length.

Note 2: '#' must not be an absolute reference (i.e. shouldn't contain '$').

Improve this answer
0

An equivalent result, "other things being equal", would be to format all cells grey and then use Go To Special to select the blank cells prior to removing their grey highlighting.

Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.