Excel - display text different to the actual value?

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.