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.
- Select first cell where you want symbol.
- Make sure that the NumLock key has been pressed to activate the numeric key section of the keyboard.
- Press the Alt key.
- 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)
- Release the Alt key, and the character will appear.
- 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!!
