I read this topic: Excel cells - show text different to the actual value?, and now I'm trying to use it as a Data Validation list in Excel. But when I choose for the list those cells, when I choose one of them it shows me the value data and not the display text (I'm using Microsoft 2007).

Here is an example:


Instead of showing "Thursday" it shows me in the cell the value of Thursday: "5"

How can I get the chosen value of the list to show the display text and not the cell value?

EDIT: Please take the days of week just as an easy example, the real question is how to do that when the values are some other text (Like: 1=Dog, 2=Cat".....)

  • possible duplicate of Excel cells - show text different to the actual value?
    – Andi Mohr
    Aug 4, 2014 at 11:34
  • how can it be a duplicate if I put this question as a reference to my question? Aug 4, 2014 at 11:45
  • It's ok - technically speaking your question has the same solution as the original question so some people here on SU may consider it a duplicate - but that doesn't mean you shouldn't have asked your question. You had a problem and we helped you solve it :)
    – Andi Mohr
    Aug 4, 2014 at 11:50

1 Answer 1


Let's say I want dog;cat;mountain lion;armadillo... etc to appear when I type 1;2;3;4... etc.

We can use conditional formatting to create a rule for each possibility.

Create a new rule. Select a rule type of Format only cells that contain, set the value to 3 for mountain lion, then click the format button.

In the format cells box, set the custom number format to "mountain lion". Make sure to enter quotes around the text you're using so Excel understands this is text and not really a number at all.

enter image description here

enter image description here

  • My real issue is how can I fix this problem when the values are some other text, the days of week is an easy example... I edit my question. Aug 4, 2014 at 11:13
  • ah I see. Hang on a tick and I'll change my answer.
    – Andi Mohr
    Aug 4, 2014 at 11:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.