What you're changing and where to find it
A drop-down box in Excel is a cell that shows a small arrow you can click to pick from a list of preset options. To change it, you need to access the Data Validation feature, which controls what appears in that list and how the box behaves. The drop-down itself lives in the Data menu on the ribbon at the top of your screen.
Before you start, click on the cell containing the drop-down you want to change. If you want to change the same drop-down in multiple cells at once, select all of them together by clicking the first one, then holding Ctrl and clicking the others. Once you have the right cell or cells selected, you're ready to open the validation settings.
Key Takeaways
- Click the cell with the drop-down, then go to the Data menu and select Data Validation to open the settings window.
- The List field shows your current options; edit them directly there or point to a range of cells that contain your list.
- You can change what appears in the drop-down, add or remove options, or switch to a different type of validation altogether.
- If you delete the source list by accident, the drop-down will stop working until you rebuild it or point to a new list.
Opening the Data Validation dialog
Click on the cell containing the drop-down you want to change. At the top of the screen, find the Data tab in the ribbon and click it. Look for the Data Validation button — it is usually in the middle-right area of the Data ribbon, sometimes labeled as Validation or Data Tools depending on your Excel version.
Click Data Validation. A dialog box will open with several tabs: Settings, Input Message, and Error Alert. The Settings tab is where you change what options appear in your drop-down. If the dialog does not open, make sure you clicked on a cell that actually contains a drop-down; if the cell is empty or contains plain text, Data Validation will open but will not show existing settings.
Editing the list of options
In the Settings tab, look at the Allow field near the top. It should already be set to List. Below that, you will see a Source field or List field — this is where your current options live. If the list is short, you may see the options typed directly in the field, separated by commas or line breaks. If the list is long, you may see a cell range instead, like $A$1:$A$10, which means the options come from cells A1 through A10.
To change the options, click in the Source or List field and edit the text directly. If you typed the options as a list, you can add new ones, remove old ones, or rearrange them. Separate each option with a comma and a space, or put each on its own line — Excel accepts both. If your options are stored in cells elsewhere on the sheet, you can change the cell range instead; for example, if you moved your list to column B, change $A$1:$A$10 to $B$1:$B$10.
Once you have made your changes, click OK. The drop-down will now show your new options the next time someone clicks the arrow.
Switching from one list to another
If you want to stop using a typed list and instead pull options from cells on your sheet, change the Source field from a text list to a cell range. For example, if you have a list of department names in cells D2 through D8, type $D$2:$D$8 in the Source field. The dollar signs lock the range so it does not shift if someone copies the drop-down to another cell.
The reverse also works: if your options are currently stored in cells but you want to type them directly into the validation settings instead, copy the options from the cells, paste them into the Source field separated by commas, and click OK. This is useful if you want to delete the source cells without breaking the drop-down.
Changing the drop-down behavior and appearance
In the Settings tab, you can also control whether the drop-down appears as a button you must click or whether it shows automatically when someone selects the cell. Look for a checkbox labeled Show dropdown arrow or In-cell dropdown — if it is checked, the arrow appears; if unchecked, the cell looks normal until someone types or clicks. Most drop-downs have this checked.
You can also set whether someone is allowed to type a value that is not on the list. Look for Allow blank or Ignore empty — check this if you want to let the cell stay empty. If you want to force someone to pick from the list and reject anything else, go to the Error Alert tab, set the Style to Stop, and write a message that explains what went wrong.
Removing or replacing a drop-down entirely
If you want to delete the drop-down and turn the cell back into a normal text cell, select the cell, open Data Validation again, and click the Clear All button at the bottom of the Settings tab. This removes all validation rules from that cell. The cell will no longer show a drop-down arrow.
If you want to replace the drop-down with a different type of validation — for example, allowing only numbers between 1 and 100 — change the Allow field from List to Number, then set your minimum and maximum values. This removes the drop-down and replaces it with a rule that checks what someone types.
Troubleshooting common problems
If you change the options in the Source field but the drop-down still shows the old list, make sure you clicked OK to save your changes. If the drop-down shows an error message when you click it, the source range may no longer exist — for example, if you deleted the cells that contained your list. Open Data Validation again and either rebuild the list or point to a new range of cells.
If the drop-down arrow does not appear even though you have Data Validation turned on, check the In-cell dropdown checkbox in the Settings tab. If the cell is very narrow, the arrow may be hidden; widen the column and the arrow should appear. If you are working in a shared workbook or a protected sheet, you may not be able to change the validation settings — ask whoever set up the protection to unlock it first.
Frequently Asked Questions
Can I change a drop-down in multiple cells at the same time?
Yes. Select all the cells that contain the drop-down by clicking the first one, then holding Ctrl and clicking the others. Open Data Validation, make your changes, and click OK. The change will explore to all selected cells at once.
What if I want the drop-down to show different options depending on what is in another cell?
This requires a named range and an indirect formula, which is more advanced. In the Source field, type =INDIRECT(A1) where A1 is the cell that determines which list to show. You will need to create named ranges for each possible list first — ask your IT department or a spreadsheet informed for help with this setup.
Can I add a drop-down to a cell that does not have one yet?
Yes. Click on the empty cell, go to Data > Data Validation, set Allow to List, type or paste your options in the Source field, and click OK. The drop-down will appear when ready.
What happens if I delete the cells that contain my drop-down list?
The drop-down will stop working and show an error when clicked. Open Data Validation, point the Source field to a new range of cells with your options, or type the options directly into the Source field, then click OK.
Can I sort the options in my drop-down alphabetically?
If your options are stored in cells, sort those cells alphabetically first, then the drop-down will show them in that order. If you typed the options directly in the Source field, rearrange them manually by editing the text, separating each with a comma or line break.