Unable to update drop down list

Question

Hi,

I'm trying to update the drop down menu item selection for a group of 12 cells. Normally, I would select the cells and go to Data Menu -> Data Validation. I do not see the Data Validation menu option anywhere. I can select items from the drop down by right clicking on a "Pick From Drop-down List..." I also can't select each of the items from the menu immediately entering the cell with the mouse. I have no idea where the drop down selector is pulling the menu items from. Any tips or ideas for 1) How to make the Data Validation menu appear for these cells and 2) How to make the drop down arrow reappear so I don't have to right click every time I want to select an item?

Answer 1

Hi JC2023. I am an Excel user like you.

I don't know if there is something unusual going on with your situation, but here is what should be the case.

The Data Validation icon is in the Data Tools section of the Data ribbon. Depending on the resolution of your computer's monitor these icons are sometimes labeled, sometimes just an icon like below, and sometimes you have to click on a dropdown for the particular section to see the items in that section (if this is the case look for a dropdown for Data Tools). This is the icon for Data Validation:

Select one or all of the cells that have Data Validation applied and then open the Data Validation dialogue.

The Allow line will tell what kind of data is allowed. The Source line will tell you where it is found. The checkbox for In-cell dropdown is how you indicate that you want to see the dropdown arrow in each cell when it is selected. At times data may also be directly entered in the Source line rather than being taken from a range somewhere else. Once you find the source of the data you should be able to edit the data in the list's location and edit the range in the validation window.

If you do edit the range in the validation window, be sure and checkmark the box at the bottom of the window to Apply these changes to all other cells with the same settings.

If your Data Validation is not working correctly, you might consider clicking on Clear All and re-doing the Data Validation from scratch. Make note of the source first so that you can find it again to set up the new validation. I will also usually save a copy of the file I am working on with a slightly different name (add a 1 or something to the end of the name) so that if something doesn't work right, I can go back to my original file without losing anything.

Reply if you have additional questions or information. Please mark this reply as answered if this solves your question.

Rich~M

Answer 2

Thanks for the try. The main issue is I simply cannot get the selection field under data validation to appear -- everything is greyed out unfortunately. I may have to simply delete and start over. I'm hoping someone else may have more insight into the problem rather than, "Clear All."

This is what I see:

This is what I should see or at least something similar to it:

Answer 3

OK so I found the answer. You are not currently using Data Validation. The "Pick from Drop-down List" that you are clicking on when you right click in a cell is NOT from Data Validation. It is a special feature that creates a variable list based on content in adjacent cells in the column. It gives a unique list of the data from the adjacent cells above or below it in the column. All cells must be adjacent to each other to be included in the list. It will not skip blank cells to include data in the list.

Since the list is actually taken from the current contents of the other cells in the column, it cannot be edited or changed except by including another item in the adjacent cells in the column.

Here is an article I found about it that may help to understand it better. Be appropriately cautious. This is not a Microsoft site:

How to use the "Pick from drop-down list" feature in Excel (spreadsheetweb.com)

If you want to control the content of the available dropdown list, you will need to actually set up the Data Validation with a source list. I usually put my source list on another sheet where it won't interfere with my data and the sheet can be hidden if desired.

Reply if you have additional questions or information. Please mark this reply as answered if this solves your question.

Rich~M