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.
Do more with
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
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
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
- 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
Premium Content
You need an Expert Office subscription to comment.Start Free Trial