This page has data validation tips, and shows how to fix Excel data validation problems, such as drop down not working, blank selected, and not showing all items. The videos and written steps below will help you with data validation drop down list troubleshooting in Microsoft Excel.
NOTE: If you need help with setting up a drop down list, go to the Excel Drop Down Lists page
Author: Debra Dalgleish
Common Problems
--Drop Down Opens With Blank Selected
Limitations
--Item Limit in Drop Down List
When you click the arrow to open a drop down list on a data entry sheet, the selection might go to a blank at the bottom of the list, instead of the first item in the list. Why does this happen, and how can you prevent it?
Get the sample file in the download section.
In the example shown above, the drop down list is based on a range named Products. The person who set up the list left a few blank cells at the end, where new items could be added.
When there's a blank cell in the source list, and the cell with the data validation list is blank, the list will open with the blank entry selected.
NOTE: Another problem can occur if there are blanks in the source list --invalid entries might be allowed in the cells.
Instead of leaving blank cells in the source list, use a dynamic source list that will adjust automatically, when you add or remove items.
To see the steps in creating an Excel Table, you can watch this short video. There are written steps on the Create an Excel table page.
To see the steps for setting up a dynamic named range, you can watch this short video tutorial. The OFFSET formula is shown below the video.
The OFFSET formula used in this example is:
If you add new items at the bottom of the source list, those items might be missing when you open the drop down list later.
Here's the most common cause for that problem, and how to fix it. See how you can prevent this common problem too!
Some drop downs are based on a static list, using a specific range, such as
If a new item is entered in cell B5, it won't appear in the drop down
To fix the missing item problem, follow these steps:
*Name in Source Box
Instead of an address, you might see a name in the Source box, such as:
To fix that:
To avoid the problem of missing items with static lists, use dynamic lists instead. There are 2 ways to set those up:
See the videos above, that show the steps for both options
Occasionally, data validation dropdown arrows are not visible on the worksheet, in cells where you know that data validation lists have been created.
This video shows the most common reasons for missing arrows. Written instructions for fixing the problems are below the video.
Here are a few causes of missing arrow for data validation. Click a link to see the details:
Only the active cell on a worksheet will display a data validation drop down arrow, even if there are lots of cells with a data validation rule. To mark cells that contain data validation lists, you can colour the cells, or add a comment.
If you require visible arrows for all cells that contain lists, you can use combo boxes instead of data validation, and those arrows will be visible at all times. To create a combo box:
If objects are hidden on the worksheet, the data validation dropdown arrows will also be hidden.
To make objects visible, use the keyboard shortcut -- Ctrl + 6
Or, follow these steps, to change the Option settings:
In the Data Validation dialog box, you can turn off the option for a dropdown list. To turn it back on:
In you have a linked picture in an Excel 2013 workbook, on Window 8, the data validation arrow might not appear in the active cell, unless you are pressing the mouse button.
As a workaround, follow these steps to make the arrow appear:
The Freeze Panes setting can cause problems with drop down arrows, in all versions of Excel. There were additional problems in Excel 97 and earlier.
In any version of Excel, if a drop down list is in a frozen pane of the Excel window, and the column to the right has been scrolled off screen, the drop down arrow will not be visible.
Thanks to John Constable for this tip.
In Excel 97, if a Data Validation dropdown list is in a frozen pane of the window, the dropdown arrow does not appear when the cell is selected. As a workaround, use Window|Split instead of Window|Freeze Panes
NOTE: This problem has been corrected in later versions.
If none of the above solutions explains the missing dropdown arrows, the worksheet may be corrupted. Try copying the data to a new worksheet or workbook, and the dropdown arrows may reappear.
Or, try to repair the file as you open it:
If you run a macro that deletes all the shapes on a worksheet, it might also delete the drop down arrow for data validation. Thanks to Ed Howland who suggested adding this tip.
For example, the macro below deletes all the shapes on the active sheet.
Safe Macros: To delete other shapes safely, without deleting the data validation arrows, see the macros to delete objects on Ron de Bruin's website.
Sub DeleteShapesALL() 'WARNING: Deletes data val arrow ' if it is visible Dim sh As Shape Dim ws As Worksheet Set ws = ActiveSheet For Each sh In ws.Shapes sh.Delete Next sh End Sub
If you type a valid entry in a cell that has a drop down list, you still might see an error message, stating that "The value you entered is not valid."
For example, this list allows you to choose Yes or No.
However, if you type no, a message says that is not valid.
You might see this error if the list is based on a delimited list, that is typed into the Data Validation dialog box.
Delimited lists are case sensitive, so enter data in one of these ways to prevent the problem:
For example, if you type No, the entry will be accepted, without an error message, because the first letter is upper case, and the second letter is lower case.
Even if create drop down data validation cells, users may be able to type invalid entries.
Here are the most common reasons for this. You can get the sample file in the download section.
If the source list is a named range that contains blank cells, users may be able to type any entry, without receiving an error message. This short video shows one possible solution to the problem, and there are written steps below the video.
Note: This problem with blank cells does not happen if the source list is a range address, e.g. $A$1:$A$10
In the screen shot below, the Manager column has a drop down list with 5 names.
However, if a different name is typed in that column, there is no error alert. The name Bill is not in the list, but was allowed in the cell.
This occurs when a named range is used as the list source, and there is a blank cell anywhere in that named range. In this example, there is a blank cell at the end of the named range, MgrList
To turn prevent invalid entries, if the named range has blank cells:
If the Error Alert is turned off, users will be able to type any entry, without receiving an error message.
To turn the alert on:
If you use the Circle Invalid Data feature in Excel, you might see unexpected results occasionally.
This short video shows potential problems with Dependent Drop Down lists, when Ignore Blank is turned off, and the Circle Invalid Data feature is used.
There are limits to the number of items that will show in a data validation drop down list:
If you need more items than that, you could create a dependent drop down list, broken down by category. There is a sample file on this page: Dependent Drop Down from Sorted List
The data validation font size and list length can't be changed.
NOTE; With programming, you can temporarily zoom the worksheet, to make the data validation font size appear larger. See the section for workarounds, to .
NOTE: This affects old versions of Excel only
In old versions of Excel, if you try to create an Excel data validation dropdown list, and refer to a source list on a different worksheet, you might see an error message:
"You may not use references to other worksheets or workbooks for Data Validation criteria."
To avoid this problem, use one of these workarounds:
If the source list has symbols from a symbol font, such as Wingdings, those symbols will not appear correctly in a data validation drop down list.
The drop down list always shows Tahoma font. It is NOT affected by the formatting in either:
If you want to show symbol characters in a drop down list, use the symbols available in the Tahoma font, such as arrows, circles, and squares.
Get the sample file in the download section.
This video shows the steps to show symbols in a drop down list, and the written instructions are below the video.
To create a list of symbols:
To create a drop down list with the symbols:
You can open the drop down list with either the mouse or the keyboard, and you can scroll through the list with a mouse or keyboard shortcuts.
Cells with drop down lists cannot be changed if:
NOTE: In Excel 2000 and earlier versions,
In Excel 2000 and later versions, selecting an item from a Data Validation dropdown list will trigger a Change event. This means that code can automatically run after a user selects an item from the list.
To see an example, go to the Sample Worksheets page, and under the Filters heading, find Product List by Category, and download the ProductsList.zip file.
In Excel 97, selecting an item from a Data Validation dropdown list does not trigger a Change event, unless the list items have been typed in the Data Validation dialog box. In this version, you can add a button to the worksheet, and run the code by clicking the button. To see an example, go to the Sample Worksheets page, and under the Filters heading, find Product List by Category, and download the ProductsList97.zip file.
Another option in Excel 97 is to use the Calculate event to run the
code. To do this, refer to the cell with data validation in a formula
on the worksheet, e.g. =MATCH(C3,CategoryList,0). Then, add
the filter code to the worksheet's Calculate event. To see an example,
go to the Sample Worksheets page, and
under the Filters heading, find Product List by Category,
and download the ProductsList97Calc.zip file.
The Data Validation dropdown is the width of the cell that it's in, to a minimum of about 3/4". You could use a SelectionChange event to temporarily widen the column when it's active, then make it narrower when you select a cell in another column.
For example, with Data Validation cells in column A:
Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Count > 1 Then Exit Sub If Target.Column = 1 Then Target.Columns.ColumnWidth = 20 Else Columns(1).ColumnWidth = 5 End If End Sub
To add this code to the worksheet:
The font in the data validation drop down list is Tahoma, size 8. There is no setting in Excel to make this font size bigger, so it's easier to read.
If you reduce the zoom setting on a worksheet, the problem is even worse. For example, this screen shot shows the drop down list with a zoom setting of 80%.
There are a few workarounds that you can use to make the data validation font look larger:
1) Use a macro to show a combo box or listbox
2) Permanently increase the sheet's zoom setting (manually)
3) Temporarily increase the zoom setting with a macro
To make the data validation items easier to read, you could use programming, with a combo box or listbox, to show the entries. The font in those can be set to any size, and you can also set them to show more than the default 8 items at a time.
Then, when you double-clicks
on a data validation cell, the combo box or listbox appears, and you can choose
from it. See instructions for adding
a combo box, or showing
a listbox (can be set for single selection or multiple selection).
If you don't want to use macros to adjust the worksheet's zoom setting, this workaround might do what you need. Thanks to John Culley for suggesting this method.
Here's a screen shot of a drop down list with the zoom level at 100%. In row 2, the cells are formatted with Cambria font, size 12.
Here's the same worksheet with the zoom level at 120%. The font in row 2 has been reduced from 12 to 10, so it looks about the same size as it did before.
To make the text appear larger, you can use an event procedure to increase the zoom setting when the cell is selected. (Note: This technique can be a bit jumpy)
There are 3 macro examples below:
-- Zoom when one specific cell is selected
-- Zoom when one of a list of specific cells is selected
-- Zoom when any cell with a data validation list is selected
If cell A2 has a data validation list, the following code will change the zoom setting to 120% when that cell is selected.
Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address = "$A$2" Then ActiveWindow.Zoom = 120 Else ActiveWindow.Zoom = 100 End If End Sub
To add this code to the worksheet:
If several cells have a data validation list, the following code will change the zoom setting to 120% when any of those cells are selected. In this example, cells A1, B3 and D9 have data validation.
Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Cells.Count > 1 Then Exit Sub If Intersect(Target, Range("A1,B3,D9")) Is Nothing Then ActiveWindow.Zoom = 100 Else ActiveWindow.Zoom = 120 End If End Sub
The following code will change the zoom setting to 120% when any cell with a data validation list is selected.
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim lZoom As Long Dim lZoomDV As Long Dim lDVType As Long lZoom = 100 lZoomDV = 120 lDVType = 0 Application.EnableEvents = False On Error Resume Next lDVType = Target.Validation.Type On Error GoTo errHandler If lDVType <> 3 Then With ActiveWindow If .Zoom <> lZoom Then .Zoom = lZoom End If End With Else With ActiveWindow If .Zoom <> lZoomDV Then .Zoom = lZoomDV End If End With End If exitHandler: Application.EnableEvents = True Exit Sub errHandler: GoTo exitHandler End Sub
Blank Selected: This sample file shows an example where a blank item is selected when the drop down list opens: Remove Blanks With Dynamic Range Sample File
Invalid Entries Allowed: This workbook has an example where invalid entries can be typed in the cells with drop down lists: Data Validation Invalid Entries Sample File
Drop Down Symbols: This workbook has an example of a drop down list with symbols in the Tahoma font: Data Validation List With Symbols
Create Dependent Drop Down Lists
Letter Headings in Drop Down List
Data Validation Criteria Examples
Data Validation With Combo Box
Last updated: April 21, 2022 7:26 PM