Drop down list changes number to store number as text when select

New Contributor


      Using a Drop Down List to select a number formatted as a number from another table. However, when selected it converts the number to text. I have conditionally formatted the cells using the drop down list to colour code the result. When the number is converted to text, the colour conditional formatting does not work. I have to manually convert the numbers stored as text back to numbers.


Is there a way to ensure that the numbers remain formatted as numbers when selected form the drop down list?


Thanks in advance

7 Replies

Hello @HConlin_2012,


Make sure that the cell that contains the drop-down is formatted as Number or General.

@PReagan Cells with drop down list are formatted for numbers but as soon as select from the list the value (that is a number from a cell also formatted as a number) changes to stored as text. Yes data validation list is all numbers.



If possible, could you share a sample version of your workbook? (please remove any sensitive data of course)

best response confirmed by HConlin_2012 (New Contributor)



The reference for your data validation list was the header of a table. These header values are stored as text, hence why the numbers were stored as text. Please refer to changes in the attached workbook.

@PReagan - thanks, that makes sense. Didn't need to look at your mods - was able to correct so not referencing headers any more. Thanks again