Excel cells - show text different to the actual value?

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?

Data-Base

Posted 2011-05-10T07:42:20.720

Reputation: 263

Answers

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

chris neilsen

Posted 2011-05-10T07:42:20.720

Reputation: 4 005

1+1 might add that the custom cell format is DDDD – datatoo – 2011-05-10T22:03:23.297

custom cell format is DDDD works if the cell value is a date serial number – chris neilsen – 2011-05-11T08:45:36.380

1DDDD 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 – 2011-07-19T11:43:08.780

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.

Mike Fitzpatrick

Posted 2011-05-10T07:42:20.720

Reputation: 15 062

Agree - this seems to be a missing feature - the Data Validation need a text/value concept. – PeterX – 2017-11-07T01:14:38.543