Zoom drop down list excel
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. Show
NOTE: If you need help with setting up a drop down list, go to the Excel Drop Down Lists page Drop Down Opens With Blank SelectedWhen you click the arrow to open a drop down list, 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 ListIn 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 ListInstead of leaving blank cells in the source list, use a dynamic source list that will adjust automatically, when you add or remove items.
1. Named Excel TableTo 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 FormulaTo 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:
Missing Items in Drop DownIf 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 ListSome 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 Fix: Change Data Validation SourceTo 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:
Prevent: Use Dynamic Source ListsTo 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 Missing ArrowsOccasionally, 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: Active Cell OnlyOnly the active cell on a worksheet will display a data validation dropdown arrow. 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:
Hidden ObjectsIf 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:
Dropdown OptionIn the Data Validation dialog box, you can turn off the option for a dropdown list. To turn it back on:
Excel 2013 Windows 8In 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:
Freeze PanesThe 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.
CorruptionIf 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:
Deleted by MacroIf 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 SubValid Entries Not AllowedIf 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 ListYou might see this error if the list is based on a delimited list, that is typed into the Data Validation dialog box. Fix: Exact EntryDelimited 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. Invalid Entries Are AllowedEven 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 ListIf 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 RangeIn 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:
Error AlertIf the Error Alert is turned off, users will be able to type any entry, without receiving an error message. Fix: Turn Error Alert OnTo 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. Item Limit in Drop Down ListThere 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 Drop Down Font Size and List LengthThe data validation font size and list length can't be changed. Font Size
List Length
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 SheetNOTE: 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: Drop Down List With SymbolsIf 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 FontThe drop down list always shows Tahoma font. It is NOT affected by the formatting in either:
Fix: Use Tahoma SymbolsIf 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. Show the Drop Down List
Scroll Through the List ItemsMouse
Keyboard
Drop Downs on a Protected SheetCells with drop down lists cannot be changed if:
NOTE: In Excel 2000 and earlier versions,
Drop Downs and Change EventsIn 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 WiderThe 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 SubTo add this code to the worksheet:
Make Drop Down Appear LargerThe 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 ListBoxTo 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 SettingIf 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. Temporarily Change Zoom SettingTo 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 selectedIf 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 SubTo add this code to the worksheet:
Zoom in when specific cells are selectedIf 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 SubZoom in when any cell with a data validation list is selectedThe 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 SubGet the Sample FilesBlank 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 Don't Miss Our Excel TipsDon't miss my latest Excel tips and videos! Click OK, to get my weekly newsletter with Excel tips, and links to other Excel news and resources. More TutorialsData 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 |