Home » Computers & Electronics » PCs » Computer Software

Easy Data Entry With Excel Drop Down Lists

5 Comments

11 Like

Like 11

Pin It

Ranked #3,380 in Computers & Electronics, #61,528 overall

Create a Drop Down List in Excel

Add drop down lists to your Excel workbooks, and it will be easier to enter data.

For example, with a drop down list of products in an order form, customers click on the product that they want, instead of typing. This helps reduce data entry errors, such as typing mistakes.

To create the drop down lists, you'll use, Data Validation, a very useful Excel feature.

For Excel 2007, watch the video below, to see the steps, or follow the written instructions.

If you're using Excel 2003, there's a tutorial video for that version, beneath the written instructions

Contents at a Glance

Create a Drop Down List in Excel
Step 1: Create a List of Items for the D...
Step 2: Select Cells for the Drop Down L...
Step 3: Create the Drop Down List
Step 4: Use the Drop Down List
Data Validation Tips

Create a Drop Down List in Excel

This video shows the steps for creating a drop down list in Excel, by using Data Validation.

For more Data Validation examples and tips, please visit the Contextures website -- Excel Data Validation page.

If you prefer written instructions, follow the steps shown below this video.

Create Drop Down List in Excel Worksheet Cell: by contextures | video info

60 ratings | 32,255 views

curated content from YouTube

Step 1: Create a List of Items for the Drop Down

The first step in creating a drop down list is to type a list of items on the worksheet. In this example, you'll type a list of clothing items in cells E1:E5, on Sheet1. Later, you'll create the drop down lists on the same worksheet.

Arrange the list in the order you want the items to appear in the drop down list. In our sample, the list is: Coat, Dress, Shirt, Pants, Hat.

Step 2: Select Cells for the Drop Down List

Now you're ready to create the drop down list. In this example, we are designing an order form. Customers might want to order several clothing items, so we'll add drop down lists in cells A2:A6.

1. Select cells A2:A6
2. On the Ribbon, click the Data tab
3. Click the Data Validation button
Note: If you're using Excel 2003, click the Data menu, then click Validation.

This will open the Data Validation dialog box.

Step 3: Create the Drop Down List

In the Data Validation dialog box, you'll enter the settings for your drop down list.

1. Click on the Settings tab.
2. In the Allow box, select List.
3. Click in the Source box, then on the worksheet, select cells E1:E5, where your typed list is entered.
4. Click OK, to create the drop down list.

Step 4: Use the Drop Down List

When you select a cell that contains a drop down list, an arrow will appear at the right side of the cell. Only the active cell will display an arrow. When you move away from the cell, its arrow will disappear.

To use the drop down lists:

1. Select a cell that contains a drop down list.
2. Click on the arrow at the right side of the cell
3. In the drop down list, click on an item to select it.

Data Validation Tips

Data Validation Tips: Excel Data Validation Tips and Quirks

My Pivot Table Books on Amazon

Create a Drop Down List in Excel 2003

In this short video, see how you can use a named list to create a drop down list on a different sheet. There are detailed instructions at www.contextures.com/xlDataVal01.html

Drop Down List in Excel: by contextures | video info

166 ratings | 182,356 views

curated content from YouTube

More Excel Lenses

Excel
Easy Data Entry With Excel Drop Down Lists
Improve Excel Sheets By Naming Cells
How To Create a Christmas Planner in Excel
Clean Up an Excel List
Color Cells That Are Too High or Low
Date Formulas in Excel
Organize Your Data in Excel 2007
Add a Picture in an Excel Comment
How to Create an Excel Dashboard
Find Free Excel Help Online
Contextures - Excel Tips Website
Debra Dalgleish - Excel Tips

Pivot Tables
Excel Pivot Table Videos
Block Pivot Table Changes
You Need Pivot Tables!

Microsoft Excel on Amazon

Microsoft Excel on eBay

My Excel Tips Blog

Reader Feedback

Reply

kiayadr Aug 16, 2011 @ 8:19 pm | delete

I love the information in this article please post more for us to read. I like the content and how we can learn from this. Welcome to wholesale Pandora Beads, [url=http://www.pandorajewelrydiy.com]Pandora Beads[/url] enjoy your stay!

Reply

WebaliciousGuides Jun 5, 2011 @ 9:14 am | delete

This is a very useful technique - thanks for sharing it. It's a lot easier to use drop-downs rather than entering text manually and risking typos.

Reply

dfrye1 Apr 15, 2011 @ 6:55 pm | delete

The more I learn about excel, the more I love it. I use excel at work on a daily basis and am continually amazed at its power. When I discovered the amazing sumproduct function, I was thrilled and rarely create a spreadsheet without using it somewhere in my calculations.

I often miss some of the other functions and abilities of excel, such as data validation, because of the way I work. It is good to catch snippets from others to continue to build my experience level in excel. I am grateful for your tips.