Excel – Usage of Checkmark with formulas and custom formatting

Plenty of times, when I open some someones Excel files I see something as a check column, where you have only “YES” and “No” as values. They check something and if you are used to it, it can be quite ok. Some advanced Excel users use conditional formatting, formatting the values for “Yes” and “No” with some color. That is not that bad, but if you have used so much conditional formatting as me, probably you would understand why I would like to avoid it. And there are clever, outstanding users, who are able to come up with something witty as this:



I am pretty sure, you would be asking yourself what kind of dirty sorcery is this, as far as there is not a single Excel font that has the “check” and the “cross” for 0 and 1 correspondingly. Indeed, this is sorcery. Actually it is hidden here >Format>Number>Custom>Type [Color50][=1]”P”;[Color3][=0]”Д

Then change the font to the good old Wingdings 2 and it works like a charm. No VBA, you simply have to make the formula to return a 0 or 1. Then format the font and simply enjoy it! :



Tagged with: , , , ,