I’m trying to do some tricky conditional formatting.
Basically, I want a databar to show if the cell value is between 0 and 1, but if the value is greater than 1, then the databar should not be visible, to allow other conditional formatting to show instead.
A cell with an input value between 0 and 1 should show the databar, but a cell with an integer value above 1 should show a specific fill colour from a preset list.
I have tried:
-
putting the Data bar rule at the bottom of the heirachy, but changing the fill colour doesn’t hide the bar – the bar is shown on top and you can only see the edges of the fill, like a border.
[EDIT – this didn’t work when using a “cells that contain Cell Value…” rule, but does seem to work if you write the formula manually…] -
Using the Formula option to set the databar maximum to 0 when the cell value is over 1, but I get the error that I’m not allowed to use relative references inside conditional formatting criteria.
-
I have thought of a potential workaround whereby I instead format a helper column that returns NA() when the original input cell value is over 1 – and then the data bar will not appear. But, this means inputting the data into a different cell than the one which would be formatted.
I have a grid of squares that could have data put into them which is hundreds of rows wide and tall, so the helper column really isn’t a solution, sadly. It could maybe be helpful if somehow Excel was able to ‘copy the look’ of another cell (copying the databar’s visibility onto the input cell from a hidden helper range) but as I understand it, the best I can hope for is copying the formatting – and that would bring the databar back as the input cell wouldn’t have NA().
Any other ideas are gratefully appreciated!
Create a second conditional format with the following formula:
=A1>1
and a formatting that is the same as a “regular cell”
Place it above the Data Bar and make sure “Stop if true” is checked
Alternatively, you can use the option of cells that contain a value greater than 1. But you still have to check “Stop if true” for the data bar not to appear:
2
Could your data live on one sheet and you could have a second sheet that contains formulas to pull values into it and use those formulas to translate your larger values into zeros. This translation might be required so your larger values wouldn’t push your data bars lower as they are all related and relative to each other.
I did a quick test on a single sheet but you could do something similar with an additional sheet.
1