top of page

You are learning Data Validation in MS Excel

How to restrict data entry to a specific list of items using data validation?

There are two main methods to restrict data entry to a specific list of items using data validation in Excel:

Method 1: Using a Typed List

1. Create your list: In a separate area of your worksheet, type each option you want to include in the dropdown list.

2. Select the cell(s): Click on the cell(s) where you want the dropdown list to appear.

3. Data Validation: Go to the "Data" tab and click "Data Validation" in the "Data Tools" group.

4. Set Allow to List: In the "Settings" tab, under "Allow," choose "List" from the dropdown menu.

5. Define the Source: In the "Source" box, type your list items separated by commas. For example, "Apple,Orange,Banana."

6. Additional Options (Optional):
- Ignore blank: Check this box if you want to allow users to leave the cell empty.
- In-cell dropdown: Check this box to display a small down arrow within the cell for easier access to the dropdown list.

7. Click OK: Click "OK" to close the Data Validation window.

Method 2: Using a Named Range

1. Create a named range:
- Select the cells containing your dropdown options.
- Go to the "Formulas" tab.
- Click "Define Name" in the "Defined Names" group.
- Type a clear name for your range (e.g., "FruitList") in the "New Name" box.
- Ensure the correct cell range is displayed in the "Refers to" box.
- Click "OK" to create the named range.

2. Select the cell(s): Similar to method 1, choose the cell(s) where you want the dropdown list.

3. Data Validation: Navigate to the "Data" tab and click "Data Validation."

4. Set Allow to List: In the "Settings" tab, under "Allow," choose "List" from the dropdown menu.

5. Define the Source: In the "Source" box, enter an equal sign (=) followed by the name you created for your dropdown list (e.g., =FruitList).

6. Complete the steps (Optional): Follow steps 6 and 7 from method 1 for any additional options.

7. Click OK: Click "OK" to close the Data Validation window.

Using either method, you'll have a dropdown list allowing users to choose only from the predefined options you specified.

bottom of page