Excel conditional formatting: formula blanks treated inconsistently?
up vote
1
down vote
favorite
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
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) 
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.
microsoft-excel conditional-formatting
asked Nov 21 at 15:37
pateksan
143
add a comment |
up vote
1
down vote
favorite
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
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)
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.
microsoft-excel conditional-formatting
asked Nov 21 at 15:37
pateksan
143
add a comment |
up vote
1
down vote
favorite
up vote
1
down vote
favorite
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
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)
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.
microsoft-excel conditional-formatting
asked Nov 21 at 15:37
pateksan
143
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
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)
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.
microsoft-excel conditional-formatting
microsoft-excel conditional-formatting
asked Nov 21 at 15:37
pateksan
143
asked Nov 21 at 15:37
pateksan
143
edited Nov 22 at 7:04
asked Nov 21 at 15:37
pateksan
143
asked Nov 21 at 15:37
pateksan
143
asked Nov 21 at 15:37
pateksan
143
143
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
up vote
0
down vote
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.
answered Nov 21 at 16:34
Doc Brown
308312
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 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 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 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 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 at 10:38
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
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.
answered Nov 21 at 16:34
Doc Brown
308312
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 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 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 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 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 at 10:38
add a comment |
up vote
0
down vote
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.
answered Nov 21 at 16:34
Doc Brown
308312
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 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 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 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 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 at 10:38
add a comment |
up vote
0
down vote
up vote
0
down vote
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.
answered Nov 21 at 16:34
Doc Brown
308312
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.
answered Nov 21 at 16:34
Doc Brown
308312
answered Nov 21 at 16:34
Doc Brown
308312
answered Nov 21 at 16:34
Doc Brown
308312
answered Nov 21 at 16:34
Doc Brown
308312
308312
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 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 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 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 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 at 10:38
add a comment |
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 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 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 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 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 at 10:38
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 at 0:03
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 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 at 6:26
@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 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 at 7:31
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 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 at 5:53
@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 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 at 10:38
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 at 10:38
add a comment |
Thanks for contributing an answer to Super User!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1377322%2fexcel-conditional-formatting-formula-blanks-treated-inconsistently%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
