Conditional Formatting (IF not empty)

Question

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.

What specifically did you try to do in Excel 2003? Can you include the example formula you use in the rule? — David Zemens, May 14 '13 at 02: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, May 14 '13 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, May 14 '13 at 13:43
@DavidZemens Yes, I have attached what I have tried so far (seperately). Thanks! — George, May 14 '13 at 14:03

Floris · Answer 1 · 2013-05-13T23:21:55.293

22

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....

edited May 13 '13 at 23:21

answered May 13 '13 at 21:07

Floris

45,857
6
70
122

You will need to chnage the value in the "Format only cells with:" dtopdown to "No Blanks". – Declan_K May 13 '13 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 May 13 '13 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 May 14 '13 at 01: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 May 14 '13 at 02:02
Glad someone else could help! – Floris May 14 '13 at 16:58

score 19 · Accepted Answer · answered May 14 '13 at 08:03

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

answered May 14 '13 at 08:03

teylyn

34,374
4
53
73

Figured out the problem, in my production list, I have fill with color white orginally. `=NOT(ISBLANK(A1))` works. Thank you Teylyn. – George May 14 '13 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 May 27 '17 at 23:00
1

What if what needed is that only if the cell is not empty apply the conditional formatting. – Royi Jul 17 '17 at 07:59
I like `=$B1<>""` better as the formula is shorter and easier for others to understand. This worked for me in Excel 2013. – jaylweb Dec 29 '17 at 14:40

score 2 · Answer 3 · answered May 14 '13 at 02:10

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.

answered May 14 '13 at 02:10

David Zemens

53,033
11
81
130

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 Dec 29 '17 at 14:37

score 2 · Answer 4 · answered Dec 26 '13 at 16:16

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!

answered Dec 26 '13 at 16:16

WayneGrantham

21
1

score 1 · Answer 5 · answered May 23 '18 at 12:48

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 '$').

score 0 · Answer 6 · answered May 14 '13 at 08:53

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.

answered May 14 '13 at 08:53

pnuts

58,317
11
87
139

Conditional Formatting (IF not empty)

6 Answers6

Linked