Is it possible in Excel to have a cell show text that is different from its value?

I would like to have a a cell that has the value 1,2,3 ... or 7 but it will show day of the week as text - but when I use the cell value in formulas it should use the numbers!

Any suggestions?

2 Answers 2


You can indeed use conditional formatting, resonably simply:

Apply conditional format to one sample cell (using cell A1 as the example)
Create a set of 7 format conditions,

Condition formula       Format (Number: Custom)
=A1=1                   "Sunday"
=A1=2                   "Monday"
=A1=3                   "Tuesday"
=A1=4                   "Wednesday"
=A1=5                   "Thursday"
=A1=6                   "Friday"
=A1=7                   "Saturday"

Then copy paste special format (or use the format painter) to apply to other cells as required

  • 1
    +1 might add that the custom cell format is DDDD
    – datatoo
    May 10, 2011 at 22:03
  • custom cell format is DDDD works if the cell value is a date serial number May 11, 2011 at 8:45
  • 1
    DDDD works iff you are happy to have Sunday as day 1, since by coincidence 1/1/1900 is a Sunday and 7/1/1900 is a Saturday. Otherwise you need multiple conditional formats to make this work as Chris describes. NB: you can improve the efficiency of the conditions if you tick the "stop if true" box for each one, since you know these conditions are mutually exclusive. You would not want to do that if you are also applying other later conditions though (shuffle the order to suit).
    – AdamV
    Jul 19, 2011 at 11:43

If your cell values are going to remain static then you can apply a custom number format individually for each cell. For example, select your cell containing 1 and apply the custom number format "Monday" (or "Sunday", depending on your preference). Then similarly for the cell with 2, its own custom format, and so on.

If your cells values will be dynamic (in the range 1..7) then you might be able to use conditional formatting but it would be awkward.

  • Agree - this seems to be a missing feature - the Data Validation need a text/value concept.
    – PeterX
    Nov 7, 2017 at 1:14

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.