Showing a Checkmark in Excel


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.


 


 

Comments (6)

  1. Nashtrump says:

    Hi

    Can anyone tell me if there is a huge benefit to upgrade to excel 2007.

    I am an Excel consultant and so far it seems to me that 90% of businesses are still using 2003, and they seem very happy with it.

    Ive read quite a bit about Excel 2007 but it seems to me that most people are happy with what they have…. whats the point in changing something that already works?

    Apppreciate any comments back .. thanks

    Lee

  2. Nashtrump says:

    Hi again,

    After reading this post, why didnt you just use a simple If(iserror()=TRUE,"X","") type formula…

    This would help you greatly… and would be very simple..

    If you need any other help Im contactable on my website NashProjects.com

  3. Del Record says:

    Well done!  I knew I saw this somewhere.  Thanks muchly.

    Del

  4. Conditional formating is cool, but I have two problems.

    1. When I copy-paste into Word, the symbols disapear.

    2. I would like to put the icons in a collumn other than the one where my numeric data is. Say I have a %complete. I just want a collumn for the symbol seperate from the value.

  5. Dalyce McCue says:

    Why can't Excell just offer a checkmark symbol?

  6. Ravi Sharma says:

    How to add check box in excel- simple way to do this is

    1) Go to run cmd

    2) type-:    charmap

    one window will open where you be able to find the check mark sign

    3) copy the check mark sign and paste in word

    4) copy from word and then paste in excel