I had to create a number of drop downs with the options ranging from 1 to 5. To make it more user-friendly, I also wanted to give an option of ‘Not Selected’, when a user does not want to make a selection in the drop down list in excel. Something as shown below in the pic:

The problem here is that when I  choose ‘Not Selected’ from the drop down, it returns the text Not Selected (see in the formula bar in the pic above). Since I have to use this selection in some formulas, I want this to return a 0. Now there are 2 ways to format numbers as text using Number Custom Formatting.  You can format numbers as text in the drop down list in Excel in such a way, that it shows text in the drop down, but when selected, gets stored as a number in the cell. Here are the steps to do this:

While the above trick works fine, in terms of creating dashboards, it makes more sense to display ‘Not Selected’ in the drop down menu as well as in the cell (when it is selected), instead of a 0 (as shown in the pic below; notice the value in formula bar). This makes it easier for someone else to pick-up the spreadsheet and works on it.

Again, this can be done very easily using custom formats. Here are 2 quick ways to do this:

How it works

Custom Number Formatting has for components (separated by semi-colon):  ;  ;  ; These four parts can be formatted separately to give the desired format. For example, in the case above, we wanted to display 0 as Not Selected. In the number formatting sequence, 0 is the third part of the format, so we changed the sequence to 0;0;”Not Selected”. This means that positive and negative numbers are displayed as it is, and whenever there is a zero, it is displayed as Not Selected. The other way is to give a condition to number format  [=0]”Not Selected”. This display Not Selected whenever the value in a cell is 0, else it will use the General formatting settings. Here are a couple of good sources to learn more about Custom Number Formatting:

Office Help Ozgrid Six Things Custom Number Formatting can do for you.

Creating a Dependent Drop Down List in Excel. Creating a Drop Down List with Search Suggestion Functionality. Creating Multiple Drop Down List without Repetition.