Yesterday, a contractor submitted his bill for some work that had been carried out and, not surprisingly, it was a little different from the original estimate. I often make an extra set of columns in the spreadsheet to show the bill and estimate alongside each other. This way the client can easily see what has changed.
On this occasion, I decided to do a little more; I decided to colour-code the changes in the bill vis-a-vis the estimate. It could be done manually, of course but this can quickly become tedious if you have anything more than a handful of cells to format. Instead, we can let the spreadsheet do the grunt work. After all, that’s what it’s there for.
Here is a brief outline of how it is done so anybody (my future self included) can see how to do it. The instructions are for LibreOffice but they couldn’t be that different for Excel.
Here are the steps in brief.
Set up the styles using the style manager (F11). I created three with different colours.
Modify the font, colour and background to suit your requirements
Click the cell where you want to format to be applied (K3 in this example) then go to Format>Conditional Formatting>Condition…
And add the three conditions
TIP: If the cell for comparison is listed as $F$3 that means it is an absolute value. If, however, you remove the $ signs, it will refer to a cell placed relative to the one where the condition is applied.
Format Paintbrush
Once you have a cell set up the way you want, simply use the format paintbrush to apply it to other cells.
And finally, here is a sample of the output
