Excel drop down list with color symbols
Modeling, Tables & Data, Tips, Tips & Shortcuts Drop-down List Arrow Always Visible for Data ValidationNovember 25, 2014 70 comments 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. Show
Skill Level: Intermediate Video: Drop-down List Arrow Always VisibleMr Excel Podcast #1816 Other solutions by Bill Jelen Problem: The Drop-down List Arrows DisappearDrop-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 wont necessarily know that there is a drop-down list for them to choose from. Solution: Create a Fake Drop-down IconOne 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:
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-downI 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. =HYPERLINK(#&ADDRESS(ROW(),COLUMN()-1),CHAR(128)) Choose Wingdings 3 as the font and change the formatting of the cell to give it the look of a disabled grey button. Input MessageThe 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. Ive 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:
Copy & Paste to Other CellsOnce 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. DownloadDownload 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. LimitationsThe 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. Additional Resources
Please leave a comment below with any questions or suggestions. Thank you! Previous 3 Ways to Unhide Multiple Sheets in Excel + VBA Tutorial Next How to Create Dependent Cascading Drop-down Lists You may also likeExcel Challenge: Equal Playing TimeThe 7 Wonders of Excel7 Excel Tricks or TreatsExcel Update: Smooth Scrolling Rows and Columns Sumit Bansal says: November 25, 2014 at 7:15 am Very Creative Reply
MF says: November 25, 2014 at 10:35 pm Interesting trick. This inspires me to try this trick using comment and I am quite happy with the result. This is what I did: What do you think? Reply
Abhilash VK says: November 26, 2014 at 4:11 am Awesome Tip Jon. I will add this trick to my blog http://www.exceltoxl.com. Reply
Oz says: November 26, 2014 at 2:33 pm Very very clever. Great solution. Reply
Chris Macro says: November 30, 2014 at 10:19 am Love the creativity in this solution. The one major drawback I see to this is most users when they see a drop down are going to click the arrow icon instead of the cell to the left. This might cause more confusion as clicking the fake drop down will not show the choices for the data validation. Does your solution address this? (I might have skipped over it accidentally) Reply
Excel Roundup 20141201 « Contextures Blog says: November 30, 2014 at 9:01 pm [] with drop down lists only show an arrow when selected, so Jon Acompora created a workaround, to always show an [] Reply Mynda says: December 2, 2014 at 10:56 am Great tip, Jon. A while back I shared a tip from Zoran for formatting the cell containing the data validation list with a custom number format which shows the wingding down arrow at the end of the text. You can see what I mean here under the bonus tip for point 1.: http://www.myonlinetraininghub.com/excel-factor-21-hyperlink-triptych Cheers, Mynda Reply
Phyo Htet says: March 12, 2015 at 10:00 pm Thank You! very much. Reply
Mindy Weller says: August 21, 2015 at 9:48 pm I tried using the formula you noted to create a hyperlink from the fake drop-down icon to the actual dropdown list but I couldnt get it to work. Could you provide more detailed instruction on how to create the hyperlink between cells in the same sheet? Reply Zachary Pick says: November 3, 2015 at 11:51 am Like a charm. That is awesome. Thank you very much! Reply
Monmusu Suu says: December 11, 2015 at 4:29 am A good alternative, though itd get time consuming if you need to do a lot of them, us by using combo boxes. In the developer tab (You might have to enable it by going to file>options>customise ribbon and selecting developer.), click on insert, and under activex Controlls, click on the combo box (second icon from the left, top row), and draw the selection box in the cell you want. You can resize it later. click on properties in the developer ribbon, and you can fill in the range under ListFillRange and the linked cell choose the cell you drew the combo box over. You can link the cell to any cell, but I find hiding the combo box over it looks better. You can change the appearance as well. When finished, if you click on the design mode icon in he developer tab, it will close and the combo box will no longer be movable or resizeable. You can click he design mode icon again to change it, and to bring the box back up to change the linked cells and range you click on properties. Reply
Dmitri Martin says: January 11, 2016 at 11:51 am This will cause the dropdown to appear right away without clicking on the arrow a second time: Private Sub Worksheet_SelectionChange(ByVal Target As Range) On Error GoTo Err1: If Target.Cells.Count = 1 Then If Target.Validation.InCellDropdown = True Then Application.SendKeys ("%{UP}") End If End If Err1: 'do nothing End SubReply
Bogdan says: January 13, 2016 at 5:03 pm Thank you! Reply santosh sharma says: February 11, 2016 at 3:06 am when Creating drop down with combo box also there is one limitation please tell me how to remove this. when i select any option from drop down and save it >> then again re-open this it will not show the saved value. but only blank will show there . i am using combo box so that my drop down should be visible. Reply LD says: April 8, 2016 at 9:29 am Hello, When I used the hyperlink formula =HYPERLINK(#&ADDRESS(ROW(),COLUMN()-1),CHAR(128))for the fake cell, all of the wingding characters appear in the row next to the fake cell. Reply
JF says: May 4, 2016 at 7:09 am Great tip. How about this? Reply
Kristal says: July 20, 2016 at 11:37 am Hello, Reply
Ethan says: September 20, 2016 at 10:29 pm Hey Jon, I have a large workbook of data. A number of columns have data validations lists in them which is feeding from a range on other worksheets. When I save and close out the file, the drop down feature is lost. When I scroll over the cells the drop down arrow no longer appears. The only way to see it is if I right click and select pick from drop down list. Is there any way to get it back to display without having to right click? Reply
Justin says: December 5, 2016 at 3:21 pm Hi Jon, Thanks for this solution! I only have one follow up question: Right now with the hyperlink formula, getting the drop down list to open requires 2 clicks. Once when you click the inactive drop down arrow and again when you click the real drop down arrow. Is there a way to make it so that clicking the inactive arrow will automatically open the drop down list without having to click a second time? Reply
Vipul says: December 25, 2016 at 9:18 pm Can any body please help me solving this issue regarding DV Reply
Nadine says: February 20, 2017 at 12:15 pm Hi Jon, Is there a way to create a drop down menu for a cell that tells specific information instead of creating options to choose and fill in. For instance, I am creating a graph that will highlight all projects that I am working on. To give more specific information about the project I wanted users to be able to click the drop down tab on the project and specific information be viewed. Thanks in advance. Reply
George says: March 14, 2017 at 1:19 am Hi Jon, Reply
leo rizzo says: May 11, 2017 at 10:14 am Hi, is there a way to have a drop down within another drop down? so for example, you click the drop down and have 4 options, when you hover over one option it provides an additional drop down etc. Im trying to create a drop down where you would pick an Office building, when you hover over it, it gives you Floors, when you hover over floors you get Desk numbers to select. Reply Jasmine says: May 16, 2017 at 7:34 pm Hello, Is there any way I can click this Drop Down List Arrow using only the keyboard, and not the mouse? Thanks Reply anthony von says: July 26, 2017 at 10:10 pm the drop down arrow is misplaced. the dropdown arrow shoulb in b4 but it is located in cell c10. it is functional but is confusing.thanks in advance. Reply Alpha Force Testo says: August 30, 2017 at 1:42 pm Quality posts is the important to interest the viewers to visit the website, thats what this web Reply Maddison says: August 30, 2017 at 4:14 pm Wow that was unusual. I just wrote an really long comment but Grrrr well Im not writing all that over again. Regardless, just wanted to say wonderful Reply Illumina SD says: August 31, 2017 at 4:05 am Whats up everyone, its my first pay a visit at this web page, Reply Andy says: February 2, 2018 at 7:47 am Hi, great sharing here. After experimenting, I found a best and the most simple way that automatically select the cell and expand the drop down list at the same time with VBA macro. First create a shape with the text q Wingdings 3 as explained in your video. Then assign a macro. Sub Macro1() Range(Q7).Select Reply jonathan says: May 24, 2018 at 7:25 am Hi Jon, Im trying to insert the hyperlink into the wingdings 3 q fake drop-down cell. How do you do it without over-riding the q ? Reply Deb says: May 31, 2018 at 6:32 am Hello, Reply Susan says: December 6, 2019 at 11:13 am This was really helpful, thank you! Reply maria says: December 26, 2019 at 8:43 am Im looking for the method to insert a black triangle in the bottom right corner of a cell, I used to hover over this triangle and an image would pop up in larger form and once I would retract the hover it would shrink back down to the triangle,l the cell size would never change when i inserted this triangle or image Reply Muhammad Bilal says: April 11, 2020 at 5:32 am Thank you author. this topic was really helpful to me and hope our community will get benefits. Reply Muhammad Bilal says: April 11, 2020 at 6:10 am thnks for sharing these tutorials. Reply LJ says: August 14, 2020 at 10:20 am This was really helpful. Thank you so much! Reply tamara says: November 1, 2020 at 6:00 pm Hyperlink formula works great , but I got the Euro symbol , instead arrow , any suggestions ?Tried a few times, checked with Jons excel sheet .. without results. Reply Search More results... Generic filters Hidden label Exact matches only JOIN US & LEARN EXCELLearn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??" Download the eBook Excel Shortcuts ListLearn over 270 Excel keyboard & mouse shortcuts for Windows & Mac. Excel Shortcuts List Join Our Weekly NewsletterThe Excel Pro Tips Newsletter is packed with tips & techniques to help you master Excel. Join Our Free NewsletterAbout MeHello and welcome! My name is Jon Acampora and I'm here to help you learn Excel. This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me... BLOG CATEGORIES
|