To make it easier to people to enter data in your Excel workbook, you can create drop down lists in the cells, by using Excel data validation. These lists will also help prevent invalid entries in your worksheets.
![drop down list of product names on worksheet drop down list of product names on worksheet](https://contexturesblog.com/wp-content/uploads/2010/10/datavallist05.png)
Drop Down List Video Update
A couple of years ago, one of the first videos that I uploaded to YouTube showed how to create data validation drop down lists in Excel 2003.
I’ve just finished a new version, for Excel 2007 and Excel 2010, and you can see it at the end of this blog post. The new video includes a couple of shortcuts that will make it even easier to create those drop down lists.
Name the Item List
When creating drop down lists, you can use a list of items from a different worksheet, as long as you give a name to that list of items first.
Here’s the quickest way to name the list of items:
- Select the items
- Click in the Name Box
- Type a one-word name, and press Enter.
Create the Drop Down Lists
After you’ve named the source list, you can add the drop down lists, by following these instructions:
- Select all the cells where you want the drop downs.
- On the Excel Ribbon, click the Data tab, then click the Data Validation command.
- In the Data Validation dialog box, under Allow, select List.
- Then, click in the Source box, and press F3 on your keyboard, to open the Paste Name box. (Quicker than typing the name)
- Click on the Name that you want to use in the drop down list, then click OK
- An equal sign and the name will appear in the Source box.
- Click OK, to create the drop down lists.
Watch the Data Validation Video
To see the steps for creating drop down lists with data validation in Excel 2007 and Excel 2010, watch this short video.
__________
Hi Debra,
I tend to use slicers for this now. Same process as you describe, create a List, name it. Then insert a Pivot Table with the column heading as a row field. Then create a slicer from the Pivot Table.
Why slicers instead of drop-downs? Slicers work in Excel Services. That means you can share the workbook in Office Live, and you can even embed a workbook in a web page using skydrive (or SharePoint if you have that).
Hi Debra,
Thank you for a most interesting blog overall.
Ed Ferrero mentioned slicers, please excuse my ignorance but what are they and how are they used.
Thanks
Kanti
Hi Debra,
Please ignore my question, just Googled Slicers and learnt that they are in Excel 2010, I am working with 2007.
thanks for a great website.
You ought to use OFFSET to define the named region, using something like =OFFSET($A$1,0,0,COUNTA($A:$A),1). This way, if you add or delete a product then the named region will change automatically. If you put the title of the named region in the first cell (i.e., $A$1 in the above example), then you’ll have to adjust the Offset to something like =OFFSET($A$1,1,0,COUNTA($A:$A)-1,1).
Great tip, Debra.
I tried using a structured reference to an Excel 2007 (or 2010) Table column (like “=Table2[Products]”) as my data validation list, but got an error message. It seemed like that would be a really handy way of making an auto-expanding list, but I guess it’s a no-go. I tend not to use the OFFSET function because it’s volatile (I think). Maybe I shouldn’t worry about that, though. Maybe the Slicer thing is a better way to go. I haven’t really worked with them at all, but perhaps it’s time.
Dear Toad, i believe you are using format tables.
That’s loads better than the offset method, what you need to do is,
=indirect(“Table2[Products]”)
you missed the indirect().
enjoy.
@Ed, thanks for the Slicers tip, and for describing why you use them.
@Kanti, you’re welcome, and glad you found the answer
@Eric, thanks, and I often use the OFFSET method, but was going for something quick and easy in this example.
@Toad, Tables are a good idea, and although you can’t reference it directly, you could create a name based on the table column, e.g. ProdList: =PriceTable[Product]
Then, use the ProdList name as the List source in data validation.
@Ed: How do you ensure only one item is selected on the slicer?