I had a problem that was surprisingly not easy to do in Excel, and since it seems like a pretty common thing, I thought I’d provide a note here on how to do this.
Problem: With Excel 2007, you want to display a “checkmark” or a “red x” in a column that has a logical value – i.e. if the value is TRUE then display a checkmark, if it is FALSE then display a red X.
Excel 2007 has some really nice new conditional formatting options, one of which is “Icon Sets”. This would seem to totally do the trick, but it takes thinking about this problem a bit differently to get it right.
Icon sets are all based around numeric values, not logical values. So my mistake was thinking that for what is really boolean data – true or false – I should, well, put TRUE or FALSE in the column. Nooooooo… Instead, put a non-zero numerical value (e.g.1, or 10 or 100) in the column if you want to show a checkmark. Put zero if you want to show a RED X. Then select the column or cells, go to Conditional Formatting and select Icon Set, then select the set that has the red x, the checkmark, etc.
With the columns or cells still selected, go back to conditional formatting and select “Manage Rules”. You’ll see a rule called “Icon Set” in the list. Select that and click Edit Rule. Now check the check box “Show Icon Only” – that way you don’t see your values, just the checkmark or the red X.