Corality check-lists in Excel using Webdings

Hi Denver, Yep, I quite like the approach of the custom format with one reservation…. From a technical perspective your approach is of course far superior and very elegant and the main reason that we don’t use it at Corality is that many people (clients) are struggling to master the Custom Format settings. The result of this is that for someone trying to figure out how the zeros and ones have been coded in a way to be represented as ‘ticks’ or other symbols they need to find this setting. I don’t know any way of getting around this and please correct me if you think I am wrong but in my view a majority of Excel users have problems with the logical link between the cell presentation and the Custom Format. If you did however want the even further enhance (technically) this solution you could apply the conditional formatting within the Custom Format window. Using my initial example it could be combined with your technique the result could be something like: [Red]"a";[Green]"r";[Yellow]";" I have added the image in the original post above (as I don't really know how to do such fancy things in a reply) to show what this looks like. Just keep in mind that I wouldn’t necessarily recommend this solution to be used in models that will communicated externally but it could certainly be an interesting feature for your own personal spreadsheets. Thank you for your thoughts Denver, much appreciated.

Rickard, Yes this is an old favourite of mine as well....I picked it up many years back from an old colleague Gavin Townshend, so credit to him for introducing me to this one.... Your approach can be enhanced even further in the following way, which makes the cell not only visibly intuative (ie providing a tick or cross or blank) but also avoids the need for conditional formatting as well (unless you really need to have further formatting applied). In addition the cells underlying numeric result can be used by other calculations either as flags, switches or as a count/sum of values. In short this approach combines the use of Webdings and custom number formatting of the cell, which effectively gives you the option to define a symbol for positive numbers ( >1), zero and negative numbers. 1) Start by selecting the range you want to apply the format to... 2) Right cick and select Format Cells 3) On the default "Number" tab set the category setting to Custom, and insert the following text in the Type field : "þ";"ý";"¨" 4) Select the "Font" tab , and specify the font as Webdings, and any other font specific settings you want to play with (colour etc). 5) Press "OK" and you're done You can use this methodology to define different symbols in place of the ones I put in the custom format...and away you go. Regards Denver

Hi Tracey, Thank you for your kind words! I totally agree that it is common to see this done in a more complicated way! I have probably been guilty of that too in the past… The use of WebDings and smart conditional formatting can make quite an impact on presentation of financial models and I will share any further developments on this topic.

Thank you for this innovative method. I have seen this thing done so badly in the past (including by me) using VBA and IF statements too. Using webdings is annoyingly simple - well done and thank you for sharing it. I love your blog and make sure our analysts read it too - keep it up!