Bottom Line: The drop-down arrow (icon) for a data validation list disappears when another cell is selected. This technique will make the drop-down arrow permanently visible on the worksheet, even if the user selects a different cell.
Skill Level: Intermediate
Video: Drop-down List Arrow Always Visible
Mr Excel Podcast #1816 – Other solutions by Bill Jelen
Problem: The Drop-down List Arrows Disappear
Drop-down lists in a cell (also known as validation lists) are a great way to make your Excel model interactive. When a user selects the cell that contains a drop-down list, a small icon appears to the right of the cell. Clicking on this icon allows them to make a selection from a list.
The problem is that the drop-down icon arrow disappears when the user selects a different cell. This means the user won’t necessarily know that there is a drop-down list for them to choose from.
Solution: Create a Fake Drop-down Icon
One possible solution is to create a fake drop-down icon in the cell to the right of the cell that contains the validation list.
You can do this by placing a Wingdings 3 character in the cell to the right. Then format the cell to look like like a disabled drop-down arrow icon.
Here are the steps to create the icon:
- Select the cell to the right of the cell that contains a validation list.
- Go to the Insert tab on the ribbon, press the Symbol button.
- On the Symbol window, choose “Wingdings 3” from the Text drop-down.
- Find the symbol that looks like the down-arrow. Character code 128, or letter “q”.
- Press the Insert button, then the Close button.
- Format the cell with the following properties:
- Border Color = Grey, Border Width = Single
- Fill Color = Light Grey
- Font Color = Grey
This will create the drop-down icon in the cell and give it a disabled appearance.
When the user selects the cell to the left that contains the list, the real drop-down icon will appear.
Please see the video above for further details and instruction.
Add a Hyperlink to the Fake Drop-down
I received an awesome suggestion from the TheCric1 on the YouTube video comments about an alternative to the Input Message for the fake cell.
The suggestion was to use put a hyperlink in the fake drop-down cell that points to the cell to the left that contains the data validation.
This means when the user clicks the fake drop-down cell, the cell with the validation list will automatically be selected and the user can select from the list. The great part about this is that the user does not have to move the mouse cursor to select the cell to the left.
This article contains more info and a video on how to keep the drop-down arrow always visible.
I updated the sample file with this solution as well, and you can download it below.
It basically uses the HYPERLINK function in the fake drop-down cell. Here is the formula that you can insert into the fake drop-down cell.
Choose Wingdings 3 as the font and change the formatting of the cell to give it the look of a disabled grey button.
The cell that contains the fake icon will probably fake the user at first. They are likely to click on the cell that contains the fake icon to select from the list. I’ve done it several times… 🙂
To help prevent this, I added an Input Message to the fake icon cell. When the user selects this cell, a message will appear that tells them to “select the cell to the left to activate the drop-down menu.”
To create the data validation Input Message:
- Go to the Data tab in the ribbon, then press the Data Validation button.
- Select the Input Message tab.
- Click the checkbox: “Show input message when cell is selected”.
- Type a message in the Input Message: box.
Copy & Paste to Other Cells
Once the fake icon cell is setup you can copy and paste it to other cells in your spreadsheet.
You can also download the example file and copy it directly into any of your Excel files.
Download the file that I used in the video, and copy/paste the fake drop-down icon into your file.
Here is another solution based on a question from Paul in the comments. This method uses a macro and a shape that looks like the drop-down button. When the user clicks the shape, the cell to the left of it is selected and the drop-down list is opened. This uses the VBA SendKeys method which is NOT always reliable. See the notes in the file and macro code for more details.
The biggest drawback of this technique is that the cell to the right of the validation list cell must be blank, and also be about 18 pixels wide.
If your drop-down validation lists are in an Excel Table, then you will have to insert a blank column.
Now that you know this trick, you can plan on how you will incorporate it into your model at design time.
- How to Create Dependent Drop-down Lists Without Named Ranges
- How to Create Drop-down Validation Lists by Mynda Treacy at MyOnlineTrainingHub.com
- Mr Excel Podcast #1816 contains a few other creative solutions to this problem.
- Excel Tables – Learn some other benefits of using Excel Tables.
Please leave a comment below with any questions or suggestions. Thank you!