Free Microsoft Excel 2013 Quick Reference

Drop down arrow Results

Hi All!

From the Excel Toolbar - Data/Validation - I'm successfully using a validation list [drop down arrow] to be able to choose from a list and have whatever value I choose entered into the cell. So far so good. Everything working fine.

Here's the thing though, the list I'm referencing is a column with 50 rows. Sometimes there are many items in the column - up to 50, and sometimes only 3 items depending on other variables in the spreadsheet.

Because the list is referencing the entire 50 rows in the column (only way I know how to do it), during times when the column has only 3 items (3 rows), the drop down still shows a bunch of blank spaces (like 47 of them - tons of white space with a scroll bar window that runs down beyond where one can see), which is awkward (strange looking).

How do I program the validation list to only show as many cells within a range that have actual values within the cell, or where <> "" ? Or, in other words, to leave all blank spaces found in the list = NOT SHOWN.

I'd like to do this without having to utilize any Macro code.

Thank you!

Is there an easy way to change the drop down arrow that is next to a list? Iw ant to chnge either size or style or make it stand out more.

When creating a specified list on the same worksheet and in the same workbook
and having checked the in-cell drop-down option, the validated cell does not
show the in-cell drop-down arrow. I have tried the tools>customize option and
still nothing, I even tried in a new workbook and still nothing. Can anybody
assist please.......

Hi all,

I created several drop-down lists using Data, Validate,... The drop-down
arrows are not visible unless the cells are selected. Is this the default
and can it be changed or am I stuck with the "invisible" drop-downs? I
would like to have all the drop-down arrows visible at all times so it will
be obvious to a user that they will be using drop-downs.

I was also wondering if a drop-down can be disabled ("grayed out") as a
result of a conditional test?


I have created a LIST from a column of data ( the column includes a header).
The list works ok however the list "drop-down arrow" that appears on the
right when I select the column header cell only appears when that header
cell is selected... I would like to see this arrow at all times even when
the header cell is not selected.

Value your suggestions,

in Excel, the color of the autofiler drop-down arrow is navy blue, and
apparently hard coded. It is VERY difficult to make a difference between the
black and navy blue for such tiny arrows, and I can waste quite some time
finding out in big spreadsheets the filtered columns. An easy "low hanging
fruit" way to increase customer satisfaction would be to change the hard
coded color to a lighter one (eg. red or yellow), or even better to allow
users to choose the color they want (think about partially color blinded
people for instance...). If everybody fed up with this limitation sends an
email to Microsoft, could this increase chances to see a Microsoft patch to
solve the problem????

I have a data entry sheet used for various forms by numerous people. I have drop down windows with standard info.

How would I make the drop down window show the drop down arrow all the time and not just when you click on that cell?

I have what looks like drop down arrows at the end of a row. I do not know
how they got there and would like to delete them. Wat are they and how do I
delete them?

I'm using data validation to create a drop down selection menu containing a list of four hyperlinks. My problem is that the drop down list selection arrow does not appear unless you click on the cell containing the list. I need the drop down arrow to continuously appear so my users know that there are more options to select.

User forms DO NOT owrk because I am using hyperlinks. I have learned that I cannot use a hyperlink with my selection list.

The goal is to have a user immediately see a drop down menu selection visible at all times regarles of the cell he/she has seleted. Once the user selects a hyperlink from a list of four, he/she will be automatically redirected to the hyperlink.

The key is to have the drown down selection box and arrow appear at all times so that the users intuitively knows that he/she has options.

Hope I am clear. Any suggestions will be appreciated.

In the attached spreadsheet NamesReference.xls, cell A1 of Initial Quality worksheet has a drop down that apparently references the Namelist? "FieldeList" which is on the List worksheet, cells A1-A14.

I understand how to set up the FieldeList Nameslist, but by examining cell A1 of the Initial Quality worksheet, how can you determine that this reference exists, or how do you establish such references to Namelists, with the drop-down arrows?

I posted this entry in another forum days ago, and got no response, so I was hoping someone here might have the answers -




I was wondering if you can increase the length of a drop down list.
I create a list of say 100 items
create a list name for the 100 items
Then I use data validtation list to create a drop down box.
When I click on teh drop down box I Currently see only the first 8 items. To see any more I have to use the scroll bar.
Question: Is there way to make more items visible when you first click on the drop down list, without havingto use the scroll bar?
This way would be alot faster as you just click on the item without having to scroll down.

A related question: Is there a shortcut key to make the drop down list appear without having to use the mouse on the drop down arrow?

Thanks for your help.



I want to add a drop down box in my word document.

I've attempted use of the "Drop-Down Form Field", which gives me a grey box. But not any type of arrow to scroll through entries.

I thought that I might be able to search the internet for tutorials on how to do this task, but I'm not finding anything that I can use.

The end-result I am going for is an "Authorized By" field, that lists several people that I can select a drop down arrow and scroll through, to select the person that makes the authorization.

Can any one either explain what I am missing?
Or at least point me towards some good tutorials?

I do not have knowledge about VB, or programming languages.

Thank you!

Help!! I don't know what the h### happened, but for some insane reason the arrow that allows a user to select from a drop down list no longer appears. The cell validation, etc. is correct. I even created a new validation and list location. No arrow ?? The only thing I have done recently is to experiment with a scroll limiting instruction in my macro. I tried it, didn't like it, so I deleted the line. Don't even know if the two are connected.


Hey I'm just really starting to get into excel so if this is a dumb question don't hang me but... after u make a drop down menu how can you get the drop down arrow that displays the options to be permanently displayed rather than being diplayed only when the cell in question is highlighted????? thanks - ryan

I found this code for an auto-complete function from this website:
It works well, but see the web example before using it. You have to name a range MyList with your potential values for it to work.

Currently, you have to click the drop-down arrows to see the potential entries. I want to make it so that after the user enters the first 3 characters it will automatically "drop-down" and show the potential entries. This will save a trip to the mouse and let the user see whether their entry is in the system.

Dim i As Long 
Sub KeyEventOn() 
    For i = 65 To 90 
        Application.OnKey "{" & i & "}", "'MyValidation """ & i & """'" 
End Sub 
Sub KeyEventOff() 
    For i = 64 To 90 
        Application.OnKey "{" & i & "}" 
End Sub 
Sub MyValidation(ByVal KeyCode As Long) 
    Dim strText As String, strList As String 
    If Not TypeOf Selection Is Range Then Exit Sub 
    strText = Selection.Value & Chr(KeyCode) 
    strList = MakeArr(strText) 
    Selection.Value = strText 
    If strList = "False" Then 
        With Selection.Validation 
            .Add 3, 1, 1, Formula1:=strList 
            .IgnoreBlank = True 
            .InCellDropdown = True 
            .ShowInput = True 
        End With 
    End If 
End Sub 
Function MakeArr(ByVal strChr As String) As String 
    Dim a As Variant 
    a = [MyList].Value 
    For i = LBound(a) To UBound(a) 
        If InStr(1, a(i, 1), strChr, vbTextCompare) = 1 Then 
            MakeArr = MakeArr & a(i, 1) & Chr(&H2C) 
        End If 
    If MakeArr  "" Then 
        MakeArr = Left(MakeArr, Len(MakeArr) - 1) 
        MakeArr = "False" 
    End If 
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I am using data validation (a list) to restrict entries users can make to a range of cells. I have opted to have an 'in cell' drop down arrow display as the user clicks in each cell that is the subject of this list validation.

Trouble is when the user clicks this arrow it has the annoying habit of highlighting the last entry in the list (a blank row), giving a new user the impression that there is nothing in the list, and forcing an experienced user to scroll up the list to see the full range of values.

Does anyone know if there is a way to display the value at the top of the list by default when the drop arrow is clicked?

Many thanks...


This is a straightforward Excel question, although the answer may not be straightforward.

I have a spreadsheet with a number of columns regulated by Data Validation.

They are linked to lists with DV, which normally generates a nice drop-down box when you click on the cells.

I inserted a column into the spreadsheet, and now DV doesn't generate a drop-box anymore! In any of the columns! The "In-cell dropdown" box is still checked in my Data Validation screen, and the cells are still bound by validation to only conform to the lists.

Can anyone think of why these boxes would have disappeared? I've shut down Excel and re-opened the file in case it was a momentary glitch. I've deleted the inserted column... In short, I've tried everything I can think of.
Even if I remove Data Validation from the columns, and re-instate it with the "In-cell dropdown" box checked, I still don't get my drop-boxes.

I would be totally happy to send my complete file to anyone who wants it (although it's a bit big, as I have to send the directory structure if you don't want to get an error, about 1 MB), but anyhow - I'm reasonably desparate to get the drop-boxes back in.

Any thoughts would be appreciated.

I need to have one cell that will have a drop down: ie.

Select one: Test tools

Basically, I want the person to press the down arrow (which will be at the right corner of the cell, and have all of the choices appear for the person to select one. I do not know what you call this. I searched the help feature, but nothing matches up with this. Thanks.

I have a spread sheet that I created for techs to enter data in using validation. On of the techs was messing around with the workbook (dont know what she did, did not protect the protected) and my in-cell dropdown menus will no longer work. The validation is still there and information can be typed if it matches whats in the menu, but the drop down arrow and list is gone. Input message is still there. Same situation applies when I create a new validation in another cell on the same drop down list. However, I can create a correctly working validation on the other sheets in the same workbook. Help!!!

I have been working with a file that is on a network drive. When some cohorts open the file, it opens fine with a drop down arrow in the cell that has data validation lists attached. When one person opens the file, those drop downs are not there.

Is there some sort of Excel set up that can resolve this issue?

What would cause this?

Any help would be VERY much appreciated

No luck finding an answer? You could always try Google.