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.
  • 1
    Why do you want to do this?
    – David
    Mar 21, 2013 at 21:32
  • I want to add a $ value to items but diisplay their Name instead of actual $ value. Later I can make lists such as "Customer basket contains Aplle, Orange" total value = $3.
    – user209378
    Mar 21, 2013 at 21:44

3 Answers 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!

  • @ Peter Albert - how exactly do I use a hidden column to store the values - and type the names directly?
    – user209378
    Mar 21, 2013 at 21:49
  • Store the numbers 1 to 10 in A1 to A10. Select column A, right click the column header and select "Hide". Type "Apple", ... in B1 to B10. Use A1 to A10 in any formula you need to refer to the "underlying" value. Mar 21, 2013 at 22:25

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.

With Microsoft Excel 2016, You can do the below.

  • A1 to A3 => Enter names Apple, Orange, etc. to the cells
  • B1 to B3 => Enter their respective values you want (1, 2, ...)
  • Right Click on A1 and select the below option 'Define Name...'
  • Now, for 'refers to', give the respective B column cell and do this for the rest of the A column cells.
  • Hide the B column

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.