Excel: Creating a Drop Down List with Values on a Different Sheet

Simple step by step guide on how to set up a drop down list in excel
Català - Castellano - Deutsch
In many situations we might want to have a cell in Microsoft Excel where only certain values from a list are allowed. The best way to do this is to create a drop down list with the closeable values. To do that, follow these simple steps.

1. On a separate sheet (you can do it on the same one as well) create the list of values you want to appear in the drop down list. Put one in each cell.
2. Drag select all those cells and enter a Name on the Name box (upper right near the formula bar). For this example we could use Listofvalues01 for example. use no spaces.
3. Then select the cell (or multiple cells) where you want that drop down list to appear.
4. With the cell or cell selected, go to the "Data" tab on the ribbon, and select Data validation -> Data Validation
5. Go to the settings tab
6. On "Allow" select "Lists" and check "ignore blank" and "in-cell drop down".
7. On "Source" type =Listofvalues01 or whatever name you gave to the list of values on step 2.
8. Click OK and go to the cell. An arrow should appear next to it and if you click on it you will see the list of closeable values

See the image below for a graphical explanation of this process.



Alternatively you could have the values on the same sheet where the drop down list appears. If you do so, you can avoid step 2, and on Step 7 instead of entering =Listofvalues01 you could simply select the cells where the list of values is (on the same sheet).

In Excel 2010, you can use this alternative method even on values being on a different sheet, but be aware that the drop down list will not work if the file is opened by a 2007 or previous version of Excel, so this option is not recommended. Either use the Namerange option (the one described in the 8 steps), or have the values in the same spreadsheet.

Tweet