Dropdown lists from validation in Excel - Real Business Scenario

Scenario Mode

👤 Your Role: You are an office assistant managing inventory orders.

📋 Request: Your manager wants a simple order form where staff can select product names from a dropdown list to avoid typing errors.

📊 Data: You have a list of products with their codes and prices in a table.

🎯 Deliverable: Create an order form with a dropdown list for product selection using data validation.

Sample Data

Product Code	Product Name	Price
P001	Notebook	2.50
P002	Pen	1.20
P003	Stapler	5.00
P004	Marker	1.80
P005	Folder	3.00
P006	Envelope	0.50
P007	Paper Clips	0.10
P008	Highlighter	1.50

1

Step 1: Create a named range for the product names to use in the dropdown list.

Select cells B2:B9 (Product Name column), then go to Formulas > Define Name, name it 'ProductList'.

Expected Result

Named range 'ProductList' refers to B2:B9.

2

Step 2: Set up the order form with a cell for product selection.

In a new sheet or area, label cell A2 as 'Select Product' and select cell B2 for dropdown.

Expected Result

Cell B2 is ready for data validation dropdown.

3

Step 3: Apply data validation to cell B2 to create a dropdown list of products.

Select cell B2, go to Data > Data Validation, choose 'List', and enter '=ProductList' in the source box.

Expected Result

Cell B2 shows a dropdown arrow with product names from the list.

4

Step 4: Test the dropdown by clicking cell B2 and selecting a product.

Click the dropdown arrow in B2 and select 'Pen'.

Expected Result

Cell B2 displays 'Pen' after selection.

Final Result

Order Form
-----------------
Select Product: [Pen ▼]

(The dropdown shows all product names from the list.)

✓Dropdown list prevents typing errors by limiting choices to valid products.

✓Named ranges make it easy to manage and update the list of products.

✓Data validation improves order form usability and accuracy.

Bonus Challenge

Add a dependent dropdown list to select the product price automatically after choosing the product name.

Show Hint

Use the VLOOKUP function to find the price based on the selected product name.