Conditional Formatting (IF not empty)

15
votes

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.

6
excelexcel-2003conditional-formatting
What specifically did you try to do in Excel 2003? Can you include the example formula you use in the rule? - David Zemens
@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
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
@DavidZemens Yes, I have attached what I have tried so far (seperately). Thanks! - George
Looks like you have it all figured out now. Good job! - David Zemens

6 Answers

22
votes

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

19
votes

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

2
votes

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.

2
votes

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!

1
votes

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

0
votes

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.