Share via

Make an Excel cell show something different than the value...

Anonymous
2013-02-19T08:15:04+00:00

Hi. This may be a bit delusional. I thought in an Excel course one time I'd learned that you could type a value into a cell, which would lead the cell to display something different.

The exact need I have is that I'm making a checklist for a group to collaborate on a recurring multi-part task. So what I'd like is to have a list of steps in a spreadsheet column. The next column over would be narrow, to have a small square cell next to each item. In that cell team-members could type either "Y" or "N". If they typed Y, a small green checkmark-symbol would display. And if they typed N, a red x would display.

This would be a nifty way to have a checklist that can be quickly reviewed. Is the above possible within Excel?

Thanks in advance.

deedeecee

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. Anonymous
    2013-02-19T09:47:05+00:00

    This method is a little complicated so I'll walk through it step-by-step based upon a single cell. Let's assume D8 is the first cell.

    1. First thing to do is restrict the values that the cell can contain to blank, Y or N. With D8 selected, go to to the Data tab's Data Validation and choose Allow: Custom, Formula: =OR(D8="Y",D8="N") and leave Ignore blank checked.
    2. Next, with D8 still selected, go to the Home tab's Conditional Formatting, New rule. Create a new rule using Use a formula to determine which cells to format and use the following for Format values where this formula is true: 

             =D8="Y"

    Click Format then go to the Number tab and set a Custom number format of:

             ;;;[Color50]ü

    Click OK to save the formatting and then OK again to save the rule. 3. Almost done. With D8 still selected, set the font to Wingdings, then tap Ctrl+1 and set a Custom number format of:

             ;;;[Color9]û 4. Set cell alignment, column width, etc as you see fit.

    Copy those Custom number formats directly from this page and paste them into the Format Cells dialog's Number tab. The trailing characters are different although they may look the same at first glance.

    As you can see from this image, D8 actually contains a Y (see the formula bar) but is showing a green check. I've copied and pasted D8 into D9 and changed D9 to N to get a red x. The copying brings across all of the special cell attributes previously created for D8.

    If you want a boxed x and check then consider using ý for x and þ for check. There are many other checks and exes in teh graphic fonts available within Character Map. (All programs, Accessories, System Tools, Character Map or Start, charmap.exe).

    The Custom number formats are using the following table to get their color numbers. Change the xx in [Colorxx] to any of these possibilities:

    3 people found this answer helpful.
    0 comments

Answer accepted by question author

  1. Anonymous
    2013-02-21T07:10:35+00:00

    Actually, it doesn't require a second rule.

    By setting the Data Validation, the cell(s) can only contain a Y or N or be blank.

    Using custom number formats in this manner will result in a blank being blank as it should be.

    Setting the default custom number format to ;;;[Color9]û (the second one mentioned) and the Wingdings font makes anything typed into the cell a stylized x. So at this point blanks are blank, and both Y and N are x. Nothing else can be typed into the cell(s).

    Creating a single CF rule to change that custom number format to ;;;[Color50]ü when the cell contents are Y will let the N's stay as x and Y's change to checks and the blanks stay blank.

    Two CF rules will work just as well or course, but setting a default behavior lets you reduce two CF rules to a single one.

    0 comments

11 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2013-04-07T19:22:25+00:00

    Hi, Jeeped. Can I ask your help on a tweak of the above info? The custom formats you created work fine with Symbol font. But I would like my cell to do this: if I type nn, I would like it to insert two dashes (--) to show "not applicable". I've tried to create a custom format similar to the ones you described. For example, ;;;[Color9]û. I substituted in "--" instead of the û, and it didn't work. I tried '-- as well, and it didn't work.

    Is it possible to get it to show the "--" that I'd like?

    Thanks.

    ddc

    0 comments
  2. Anonymous
    2013-02-21T14:06:39+00:00

    That's good to know--I'll give it another shot with the one-rule. I must have mistyped the first time.

    Thanks again.

    ddc

    0 comments
  3. Anonymous
    2013-02-21T05:31:54+00:00

    THANK YOU! That was a spectacularly clear, useful answer. It works excellently to achieve what I was trying to do.

    The only detail/clarification it might need is that one has to make two separate rules in the "conditional formatting" step, and enter =D8="N" for the second rule, and then proceed to use the second custom number format for the second rule. Some might miss that.

    Thanks again, it is very satisfying to solve this problem and learn for the first time what this data validation and custom formatting are about to boot.

    deedeecee.

    0 comments