microsoft excelmicrosoft-excel-2007microsoft-excel-2010
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?
You can do this with custom format:
Select cell A1, go to Format Cells->Number->Category->Customer and enter "Apple"in the Type field.
Repeat the process for all other cells.
However, note that this "information hiding" is not best practice, as this makes your file hard to audit/debug later. Better use a hidden column to store the values - and type the names directly!
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.
Best Answer
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,
Then copy paste special format (or use the format painter) to apply to other cells as required