Data Validation - Dropdown List With Symbols

Hi Guys, good to see you again.

We have been seeing about Data Validation and related items over few previous posts. We saw how to apply validation to restrict the users to select only those options you provide as a drop-down  I already said that you cannot change the font that your drop-down list uses. By the way, Excel drop-down lists use the font ‘Tahoma’. This font is normally alphanumeric.

Situation

Recently, I wanted to create a validation in team’s self-appraisal template. There is this field where each team member will have to select what they feel about their performance in the sense is it moved upwards or downwards or no change compared to last quarter. Of course, I can provide the drop-down options using the text. But to be more appealing and creative (J), I wanted to use arrow symbols in the drop-down list showing the directions.

Normally, I use font “Wingdings 3” to get the arrows I want. I type alphabets “p”, “q” and “tu” (without quotes and all in small case) in 3 cells, let’s say in range A1:A3. I then select these 3 cells and change the font to “Wingdings 3”. At this point of time, these 3 cells appear like below.

Though arrow is appearing in the cell, value behind is actually “p” (Notice the formula bar), also see the font above.

Now the next step, I have to create a validation at some other place using these arrows so that a drop-down appears with these options. So let’s do it in cell B1. Remember steps to create drop-down?

Excel 2007 or later

Select cell(s) you want to apply Data Validation and go to Data ribbon > Data Validation. In Data Validation dialogue box, under Settings Tab, select List in Allow: field. Under Source, select range you want. In our example, I select A1:A3.

Excel 2003 or earlier

Select cell(s) you want to apply Data Validation and go to Data menu > Data Validation. In Data Validation dialogue box, under Settings Tab, select List in Allow: field. Under the Source, select range we want. In our example, I select A1:A3.

Check in the cells now. Is it success? Have we successfully created the drop-down using the arrows?

The Problem

Ah. I have the drop-down list but the options are not arrows but alphabets “p”, “q” and “tu”. If I select one the option, the same letter appears in the cell. Wait a minute; this is not what I wanted.

Now, I can format cell B1 to have the font “Wingdings 3” so that when my team selects option “p”, cell actually shows an arrow facing upwards just like below.

Are we successful now? If you say yes, please scroll to top and read situation again. What I wanted was to use arrow symbols in the drop-down list showing the directions. But look above picture, what I have in drop-down are alphabets, not arrows. This alphabets in the drop-down creates confusion among users. So, how to get what I want?

The Solution

We will use something called Special ALT characters to achieve this i.e. working with symbols. Excel supports what is called as Special ALT characters. These are essentially symbols that Excel takes literally but input method to get the symbols is through your keyboard.

Below is the extract of few of the symbols and ALT code for them.

You can download complete list of these codes by clicking here.

If you are working on the same sheet where we have earlier changed font to “Wingdings 3” etc, clear off all the formatting done so that sheet will be in it default condition. Or simply work on a fresh sheet. Follow closely from now on.

1. Select first cell where you want symbol.
2. Make sure that the NumLock key has been pressed to activate the numeric key section of the keyboard.
3. Press the Alt key.
4. While the Alt key is depressed, type the proper sequence (only the numeric part of the ALT code from the table above) of numbers on the numeric keypad. Note that you should use number key pad on the right side of keyboard and should not use the top row in your keyboard where you have numbers. (If you are using a laptop without numeric keypad, check the keys m, j, k, l, u, i, o, 7, 8, 9. You will find numbers noted with a smaller font below the alphabets / numbers, you will have to press Fn key to activate them. One more option is to enable On-Screen Keyboard through Start menu > All Programs > Accessories > Accessibility > On-Screen Keyboard)
5. Release the Alt key, and the character will appear.
6. Repeat the process for all the cells

So to get arrows like how I wanted, I type 30, 31 in cells A1, A2 respectively and 17 & 16 in cell A3. Once this is done, you will be able to see something like in the picture below.

Now notice the font, this will be your default font. Formula bar contains the actual symbol instead of an alphabet. Now go ahead and apply validation in cell B1.

Mission accomplished. Hope you enjoyed this post as much as I did. Happy learning!!