Excel - display text different to the actual value?

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

For example:

• Cell A1 displays a word Apple but has a value of 1.
• Cell B1 displays a word Orange but has a value of 2.
• Cell C1 will have a formula =A1+B1, but would display a value of 3.

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!

This is a valuable thing to be able to do, because it can make your spreadsheet more readable.

1. Define Apple and Orange to be names whose values are 1 and 2. To do this, the best way is to put the words Apple and Orange in cells A1 and A2.
2. Then in cells B1 and B2, put the values 1 and 2.
3. Now use Insert->Name->Create to tell Excel you want these four cells to be a dictionary. (They don't use that word.)
4. Now you can use "Apple" and "Orange" anywhere in a formula and they will be replaced during calculation with the numbers 1 and 2.
5. Type the words Apple and Orange into, say, cells D1 and D2. Then in, say, cell F1, enter the formula: =INDIRECT(D1)+INDIRECT(D2). The result displayed in F1 will be 3.