How do I do conditional formatting in Excel for cells where Length = 0?

johnrossh
johnrossh used Ask the Experts™
on
I am doing a spreadsheet comparing each month's revenue to the month before it. I have no problem setting the formatting if the revenue has increased or decreased. My problem is with the cells where the values haven't been calculated yet. (info coming in the future)  Since there is a formula calculating this cell, I think that I have to check for Length = 0. I don't know how to set this up from the "Conditional Formatting" button in Excel 2016. (Please note that I do NOT want to use any VBA macros for this unless absolutely necessary. I would prefer to just use the application's conditional formatting tool.) I have tried using: =Length(B5) = 0 and Length(B5) = 0 but don't get any results. Thanks for the help.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
you are close - the formula for length is =len(b5) however you might find it better to use =b5="" which means "if B5 has no value"t

As you are using 2016 consider looking at the 'highlight cells' rules which gives lots of options including icons to better highlight movement in the numbers these will automate some of the 'formula based' conditional formats

Author

Commented:
Thanks. I guess I didn't explain myself very well. I AM using the highlight cells rules but I can't get the rule set up to work properly.
ok, which highlight cells rule are you struggling with - can you post an example sheet?

- did you try the "=len(b5) = 0" ?

If the problem is that you've chosen one of the automatic rules and its not clear where to set the cells with 0 length then its difficult to help without knowing which one.

If the auto options are not flexible enough consider going for 'use a formula' so that you have more extensive control - though you will need to create a separate rule for each grouping
Microsoft Excel Expert
Top Expert 2014
Commented:
the length 0 meaning that cell has no value or is blank. you can do this in Condtional formatting plz see screnshot.
2016-02-29-18_13_02-Book1---Excel.jpg2016-02-29-18_13_16-Book1---Excel.jpg
but you if you did not mean empty and you meant cells that has 0 value. then simply select cell = 0 on the conditional formatting

Author

Commented:
Exactly what I was looking for. Thanks!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial