1

I am adding some functionality to a spreadsheet I've used for ages and it seems that conditional formatting behaves inconsistently with empties and formula blanks. Please see snippets below, followed by checks I've done so far.

Case 1: Old part of spreadsheet working as previously: Formula blank does not get formatted blank not formatted

Case 2: New part of spreadsheet, misbehaving: Empty and Formula blank both get formatted, and differently (P44 is a formula blank as you can see, P45 is an empty included for troubleshooting) blank formatted

Intended action: Both places in the spreadsheet are checking what "band" a value falls into, and the band thresholds are defined in "Movement limits". The spreadsheet was originally made on Excel 2007 if not older but all CF was redone when we upgraded to 2016 which allows "CF by formula referencing other sheets".

Things I've tried so far: 1. ABS() wrapped around the formula in the condition - doesn't make a difference 2. Applying "CF by formula" to multiple cells (you may have spotted in case 1 the cell highlighted is the first in the range, but it's not in case 2) - this seems to work as expected otherwise. So in case 2 above, if I enter values that should get green/orange/red, they do. 3. Manual/direct formatting: There is no fill or text color applied. The number format in both cases is "Custom +#;-#;0". 4. Recalculated. Saved, closed and reopened. 5. The values that I'm comparing with - in both cases, the formula checks against a simple positive number, and a 0 should show green. 6. Excel versions - both cases are in the same .xlsx in Excel 2016.

The main question: why is the blank not formatted in case 1 but formatted in case 2?

What else can I check? I know there are workarounds to get the results I need, but I want the entire spreadsheet to behave the same as it used to for a seamless user experience. The CF on empty P45 is only included for troubleshooting - the final product will not have CF on empties, but I do need it on some formula blanks, and I would like them not to format. So obviously I could add a new rule at the top to leave blanks unformatted and stop if true, but it didn't use to be necessary.

0

When, for example, formatting cell A1, you can try

=OR(A1=0,ISBLANK(A1))

as a condition, that should give you the same result for formular blanks as well as for empty cells. I tried it with Excel 2010, I would not expect Excel 2016 to behave differently.

  • Thanks Doc. Don't get me wrong, this could be useful if someone needed it... but I don't. I must have explained my question badly. Firstly, I'm trying to avoid having a separate CF to keep blanks unformatted. In other words my question could be read as "why didn't I need to include a conditional format for blanks in Case 1, and they remained unformatted, but they don't in Case 2". Secondly, as mentioned, there won't be any empties with CF in the final product, I only included one in "Applies to" to see if it helps me or anyone else trace the issue. – pateksan Nov 22 '18 at 0:03
  • @pateksan: did you note this is not a separate CF? You can apply it to all cells uniformly, and the condition will be true for empty cells as well as for the empty string returned by a formula (which Excel seems to interpret as zero). So you can flawlessly copy the CF around the sheet from one cell to another. I thought that's what you were after. – Doc Brown Nov 22 '18 at 6:26
  • I suppose it is what I'm after if I can't get my main question answered. If it helps, I just edited my OP to state the main question directly. In case I do end up using your suggestion, are you saying it can be used without being an additional rule? – pateksan Nov 22 '18 at 7:31
  • @pateksan: from what you wrote I am not really sure what the difference is between case 1 and case 2. You wrote in case 2 you used "CF by formula referencing other sheets", but the only place in your pictures where I see a formula referencing another sheet is in case 1, and it is not the formula of the CF (I doubt this is the cause). Anyway, I would not overthink this, we cannot look "inside" Excel's source code. Just test for empty cells, empty text and zeros separately and use AND and OR operators to build a condition which works everywhere, that's probably the most robust approach. – Doc Brown Nov 24 '18 at 5:53
  • Thanks for coming back to me. It's tricky to explain, if I upload the document when I'm on my desktop would you consider looking at it? – pateksan Nov 24 '18 at 10:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.