Custom formatting in Excel is extremely powerful and, well, much more useful than you might think.
In a session I am going to present tomorrow on the beauties of my Standard Costing Variance calculation method, I am going to reveal to my delegates the following cell formatting rule:
<POSITIVE>;<NEGATIVE>;<ZERO>;<TEXT>
THAT is worth a fortune in the right hands: let’s call it an early Christmas present!
A standard costing variance can be positive, negative or zero. If it’s positive, we might want to label it Favourable, if it’s negative we could want to label it Adverse and if it’s zero, we want to leave it at zero with no label.
One approach is to program each cell where there is to be a variance calculation something like this:
=IF(G34-G38<0,ROUND(G34-G38,2)&"Adverse",IF(G34-G38>0,ROUND(G34-G38,2)&"Favourable",0))
This formula takes care of the positive, negative and zero requirements and it takes care of the rounding to two decimal places in case there are more than two!
ALTERNATIVELY, why not learn how to do this:
enter only your calculation in the variance cell(s) =G38-G34 AND then use custom formatting of the cell(s) as follows:
#,##0.00 "Favourable";-#,##0.00 "Adverse";#,## 0.00
TRY IT! RIGHT CLICK SELECT all necessary variance cells at the same time and set their custom formatting together … don’t waste any time by doing it cell by cell.
This works because every cell in an Excel spreadsheet is set up to accept formatting along the lines I introduced above;
<POSITIVE>;<NEGATIVE>;<ZERO>;<TEXT>
Which means that the first part of my formatting expression relates to ALL positive results, the second part to ANY negative results and the third part to zero values. I haven’t entered any text options because there can’t be any … unless your solution needs them.
This is a real top tip.
Duncan Williamson
Excel with Excel Master 