Traps of Custom Formatting

Numbers can coexist in Excel cells with text strings. What may surprise you is that the contents of such cells can be treated and used as numbers. It means that they can be used as ordinary numbers in mathematical calculations, functions and formulas. This happens when you apply some kinds of custom formatting for cells.

Here is such example of cell formatting and some confusing results of using it in formulas involving text strings.

I've used the following custom format for cells in column A:

[Blue][>0.9]#.### "In";[Red][<=0.9]#.### "Out";General

so that values higher than 0.9 are coloured in blue and have added (in the same cell) custom text "In" indicating that they meet my requirements. The values equal or below 0.9 are coloured in red and have added custom text "Out" indicating that they do not meet my requirements.

Cells in column C have the General or Number format and contain the underlying formulas (shown in column D), resulting in displayed values. As you can see, the formulas ignore the added text strings in column A cells and work fine, on numbers only.

However, the results shown in cells C6 and C7 look somewhat strange. What's wrong with them? Have a look at the value shown in the Formula Bar; it displays the full length of the number displayed in the cell A4. The same format applies to all cells in Column A, so cell C6 shows also the full length of the value, i.e. 17. And cell C7 displays the right five digits of the number displayed in cell A7 (where only first three digits are displayed).

Conclusion: custom formatting of cells in Excel can be very useful in most cases, but not always. Caution is advised, when applying complex custom formatting, because unreliable / unintended results can be obtained unexpectedly in some cases.