Data bars in Excel are a form of conditional formatting that makes it easy to visualise the values in a range of cells, by shading a portion of the cell proportional to the value in it. On their own they are pretty useful but have a few limitations, but by harnessing a little known ‘feature’ with a little bit of VBA code and some creative thinking they can be made much more useful.

You may not have heard about the so-called “floating point error” in Excel, in which seemingly simple calculations can give an apparently wrong result. It can certainly be bewildering if you experience it for the first time. To be clear this is not an issue with Excel but a function of how some numbers are represented in binary format⁽¹⁾. For very long numbers, more than 15 digits, one common solution is to represent them as text, but what happens when the text solution also produces a floating point error?