Excel drop down list with color symbols

Very Creative

Interesting trick. This inspires me to try this trick using comment and I am quite happy with the result.
Is there a way I can put a screen shot here?

This is what I did:
Insert Comment, and Display it of course
In the comment box, leave nothing but
Adust the comment box to a size of the row height and a width of a default dropdown
Move it and align it to just the next cell to the right
Format the comment box with a grey fill
Last but not least, send the comment box to back
Apply the data validation to the cell as usual.

What do you think?

Awesome Tip Jon. I will add this trick to my blog //www.exceltoxl.com.

Very very clever. Great solution.

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]

[] with drop down lists only show an arrow when selected, so Jon Acompora created a workaround, to always show an []

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

//www.myonlinetraininghub.com/excel-factor-21-hyperlink-triptych

Cheers,

Mynda

Thank You! very much.

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?

Like a charm. That is awesome. Thank you very much!

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.

This will cause the dropdown to appear right away without clicking on the arrow a second time:

Thank you!

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.

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.
What am I doing wrong?

Great tip. How about this?
1] Screenshot [or snip] the real button
2] Crop it cleanly in Paintbrush, then save as a PNG format
3] Paste the picture in on the sheet wherever needed [no need to worry about column widths, fonts, etc
4] Add hyperlink to the button-picture to the input cell [so that the real button appears.

Hello,
Do you know how I can move the drop down button to the left side of the cell?
Thank You

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?
Thanks

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?

Can any body please help me solving this issue regarding DV
In 2003, I was able to select arrow key of DV by clicking anywhere in cell,
but in 2010, I am forced to go to arrow key at right most part of cell.
Why this happens?

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.

Hi Jon,
Ive got a file with drop down list where could see it in other laptop but I could not see the drop down list at all in my laptop, any reason for that and what could be the solution?
thanks
George

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.

Hello,

Is there any way I can click this Drop Down List Arrow using only the keyboard, and not the mouse?

Thanks

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.

Quality posts is the important to interest the viewers to visit the website, thats what this web
site is providing.

Wow that was unusual. I just wrote an really long comment but
after I clicked submit my comment didnt appear.

Grrrr well Im not writing all that over again. Regardless, just wanted to say wonderful
blog!

Whats up everyone, its my first pay a visit at this web page,
and paragraph is really fruitful in support of me, keep up posting such articles or reviews.

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
SendKeys %{DOWN}
End Sub

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 ?

Hello,
Im trying to make a data sheet and I want to have some drop down menu options but I want to have them blank until we input that line so that my pivot tables on other sheets wont use those lines to do the pie charts until they have all the data in them. Is this possible?
Thank you, Deb

This was really helpful, thank you!

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

Thank you author. this topic was really helpful to me and hope our community will get benefits.

thnks for sharing these tutorials.

This was really helpful. Thank you so much!

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.