Link to home
Start Free TrialLog in
Avatar of J. Ross Hughson
J. Ross HughsonFlag for Canada

asked on

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

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.
Avatar of regmigrant
Flag of United Kingdom of Great Britain and Northern Ireland image

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
Avatar of J. Ross Hughson


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
Avatar of Professor J
Professor J

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Exactly what I was looking for. Thanks!