Freigeben über

Showing a Checkmark in Excel

  • Artikel

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

  • Anonymous
    April 12, 2008
    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

  • Anonymous
    May 01, 2008
    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

  • Anonymous
    May 03, 2008
    Well done!  I knew I saw this somewhere.  Thanks muchly. Del

  • Anonymous
    July 11, 2008
    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.

  • Anonymous
    February 13, 2012
    Why can't Excell just offer a checkmark symbol?

  • Anonymous
    December 17, 2013
    The comment has been removed