Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
up vote 7 down vote accepted

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

share|improve this answer
+1 might add that the custom cell format is DDDD – datatoo May 10 '11 at 22:03
custom cell format is DDDD works if the cell value is a date serial number – chris neilsen May 11 '11 at 8:45
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 '11 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.

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .