Excel Data Validation Tips and Troubleshooting (2023)

Drop Down Opens With Blank Selected

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.

Cause: Blank Cells in Source List

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.

Fix: Use a Dynamic Source List

Instead of leaving blank cells in the source list, use a dynamic source list that will adjust automatically, when you add or remove items.

1. The best option is a named Excel table
2. Another option is a dynamic named range, based on a formula

1. Named Excel Table

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.

2. Dynamic Named Range with Formula

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:

• =OFFSET(Prices!$B$2,0,0,COUNTA(Prices!$B:$B)-1,1)

Missing Items in Drop Down

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!

Cause: Drop Down Based on Static List

Some drop downs are based on a static list, using a specific range, such as

• =AdminLists!$B$2:$B$4

If a new item is entered in cell B5, it won't appear in the drop down

Fix: Change Data Validation Source

To fix the missing item problem, follow these steps:

• Select the data validation cells
• On the Excel Ribbon's Data tab, click Data Validation
• On the Data Validation Settings tab, change the range address* in the Source box, to include the new items
• Click OK, to complete the change.

*Name in Source Box

Instead of an address, you might see a name in the Source box, such as:

• =RegionList

To fix that:

• On the Excel Ribbon's Formulas tab, click Name Manger.
• Select the name in the list
• In the Refers To box, change the address, to include the new items
• Click the check mark, to complete the change, then close the Name Manager

Prevent: Use Dynamic Source Lists

To avoid the problem of missing items with static lists, use dynamic lists instead. There are 2 ways to set those up:

1. Named Excel table
2. Dynamic named range, based on a formula

See the videos above, that show the steps for both options

Active Cell Only

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:

• Click the Developer tab on the ribbon, and click Insert
• Click the Combo Box in the Form Controls
• On the worksheet, drag to add a combo box in the size that you want.
• Right-click the combo box, and click Format Control
• In the Input Range box, enter the name or address of the list
• Click OK

Hidden Objects

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:

• Click the File tab on the ribbon, and click Options
• Click the Advanced category
• Scroll down about halfway, to the section, Display Options for This Workbook .
• In the setting, "For Objects, show:", click All
• Click OK

Dropdown Option

In the Data Validation dialog box, you can turn off the option for a dropdown list. To turn it back on:

1. Select the cell that contains a data validation list
2. On the Ribbon, click the Data tab
3. Click the top of the Data Validation button, to open the dialog box
4. In the Data Validation window, go to the Settings tab
5. Add a check mark to the In-cell dropdown check box
6. Click the OK button

Excel 2013 Windows 8

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:

1. Select the cell with the data validation list
2. Click outside of the Excel window (e.g. click on the Desktop, or click in your browser window)
3. Click on the Excel window, and the arrow will appear, and you can select an item from the list.

Freeze Panes

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.

• Without frozen panes
• 
  With frozen panes

Corruption

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:

1. On the Ribbon, click File, and then click Open
2. Click Computer, then click Browse
3. Select the file with the missing data validation arrows
4. At the bottom of the Open windown, click the arrow at the right of he Open button
5. Click Open and Repair
6. When prompted, click Repair.

Deleted by Macro

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.

• If the data validation arrow is visible when you run this macro, it will be deleted too, along with other shapes on the worksheet.

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 visibleDim sh As ShapeDim ws As WorksheetSet ws = ActiveSheetFor Each sh In ws.Shapes sh.DeleteNext shEnd Sub

Valid Entries Not Allowed

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.

Cause: Delimited List

You might see this error if the list is based on a delimited list, that is typed into the Data Validation dialog box.

Fix: Exact Entry

Delimited lists are case sensitive, so enter data in one of these ways to prevent the problem:

• Choose from the drop down list
• Type an entry that exactly matches the upper and lower case letters in the delimited list

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.

Invalid Entries Are Allowed

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.

--Blank Cells in Source List

--Error Alert Turned Off

Blank Cells in Source List

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

Cause: Blank Cells in Named Range

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

Fix: Turn Off Ignore Blank

To turn prevent invalid entries, if the named range has blank cells:

1. Select the cell that contains a data validation list
2. Choose Data|Validation
3. On the Settings tab, remove the check mark from the Ignore blank box.
4. Click OK

Error Alert

If the Error Alert is turned off, users will be able to type any entry, without receiving an error message.

Fix: Turn Error Alert On

To turn the alert on:

1. Select the cell that contains a data validation list
2. Choose Data|Validation
3. On the Error Alert tab, add a check mark to the Show error alert after invalid data is entered box.
4. Click OK

Circle Invalid Data Problem

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.

Item Limit in Drop Down List

There are limits to the number of items that will show in a data validation drop down list:

• The list can show up to show 32,767 items from a list on the worksheet.
• If you type the items into the data validation dialog box (a delimited list), the limit is 256 characters, including the separators.

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

Drop Down Font Size and List Length

The data validation font size and list length can't be changed.

Font Size

• The font in the data validation drop down list is Tahoma, size 8.
• The cell formatting does not affect the font in the drop down list.

List Length

• The drop down list shows a maximum of 8 items at a time.
• Scroll up or down to see additional items

NOTE; With programming, you can temporarily zoom the worksheet, to make the data validation font size appear larger. See the section for workarounds, to .

Source List on Different Sheet

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:

• name the list on the other worksheet, then refer to the named range, as described here: Excel Data Validation
• If the list is in a different workbook, you can use the technique described here: Use a List from Another Workbook

Drop Down List With Symbols

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.

Cause: Drop Down Font

The drop down list always shows Tahoma font. It is NOT affected by the formatting in either:

• The source list cells OR
• The cell with the drop down list

Fix: Use Tahoma Symbols

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:

1. On the worksheet, select a cell where you want to start the list of symbols
2. Press the Alt key, and on the number keypad, type a number for the symbol that you want to insert. A few examples are shown in the list below, and you can experiment to find other symbols.
   Note: To see all the codes, go to the Alt Codes List in Wikipedia.



3. Press Enter, and enter other symbols in the cells below. In the list shown above, the Alt key was used with numbers 30, 29 and 31, to create a list with up and down arrows, and a two-headed arrow.

To create a drop down list with the symbols:

1. Select the cell where you want the drop down list
2. On the Ribbon's Data tab, click Data Validation
3. From the Allow drop down, select List
4. Click in the Source box, and on the worksheet, select the cells with the list of symbols, then click OK

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.

Show the Drop Down List

• Mouse: Click the cell's arrow
• Keyboard: Press Alt + Down Arrow

Scroll Through the List Items

Mouse

• Press the arrows at the top or bottom of the scroll bar, for continuous scrolling
• Click the arrows at the top or bottom of the scroll bar, to scroll one item at a time
• Drag the scroll box up or down
• Click above or below the scroll box, to move up or down one page
• Press above or below the scroll box, for continuous page scrolling

Keyboard

• Press the Up or Down Arrows keys, for continuous scrolling
• Tap the Up or Down Arrows keys, to scroll one item at a time
• Tap the Home or End key, to go to the top or bottom of the list
• Tap the Page Up or Page Down key, to move up or down one page
• Press the Page Up or Page Down key, for continuous page scrolling

Drop Downs on a Protected Sheet

Cells with drop down lists cannot be changed if:

• cell is locked
• sheet is protected

NOTE: In Excel 2000 and earlier versions,

• You can change the selection in a data validation dropdown, if source list is on the worksheet.
• If the list is delimited (typed in the data validation dialog box), the selection can't be changed.

Drop Downs and Change Events

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 doesnot 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.

Make Drop Down Temporarily Wider

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:

1. Right-click on the sheet tab, and choose ViewCode.
2. Copy the code, and paste it onto the code module.
3. Change the column reference from 4 to match your worksheet.

Make Drop Down Appear Larger

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

Use Combo Box or ListBox

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).

Permanently Change Zoom Setting

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.

• To make the drop down list easier to read, adjust the worksheet's zoom to 120%, or another setting that you prefer.
• Then, to make the rest of the worksheet look "unzoomed", reduce the font size in the worksheet cells, and make the columns narrower

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.

Temporarily Change Zoom Setting

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

Zoom in when specific cell 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:

1. Right-click on the sheet tab, and choose ViewCode.
2. Copy the code, and paste it onto the code module.
3. Change the cell reference from $A$2 to match your worksheet.

Zoom in when specific cells are selected

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 

Zoom in when any cell with a data validation list is selected

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 IfexitHandler: Application.EnableEvents = True Exit SuberrHandler: GoTo exitHandlerEnd Sub 

Get the Sample Files

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

More Tutorials

Data Validation Basics

Create Dependent Drop Down Lists

Letter Headings in Drop Down List

Data Validation Criteria Examples

Data Validation Tips

Data Validation With Combo Box