Working with Data · ~2 min read

Excel Data Validation: Drop-Down Lists and Rules

Create drop-down lists and restrict what can be typed in a cell.

Video link coming soon
What you will learn

Step by step

1. Intro

Welcome back. When several people fill in the same sheet, they type things in different ways. In this lesson, you will use Data Validation to control what can be typed in a cell.

You will build a drop-down list, and a rule that only accepts numbers in a range.

Excel Data Validation: Drop-Down Lists and Rules: Intro
Intro

2. A drop-down list

Here is a task list. The Status column should only ever say Open, Done or Late. Without rules, someone will type finished, or done with a typo, and your filters and counts break.

Select C2 to C6. On the Data tab, open Data Validation, with Alt, A, V, V.

In Allow, choose List. In Source, type the choices, separated by commas: Open, Done, Late. Then click OK.

Click C2. A small arrow appears next to the cell. Click it, or press Alt and the down arrow, and pick Done.

Excel Data Validation: Drop-Down Lists and Rules: A drop-down list
A drop-down list

3. The error message

Now try to type something else. In C3, type finished, and press Enter.

Excel refuses it: the value does not match the rules of this cell. Press Escape to cancel, and the cell stays as it was.

Excel Data Validation: Drop-Down Lists and Rules: The error message
The error message

4. A number rule

Next, the Hours column. Nobody should log zero hours, or more than forty. Select D2 to D6, and open Data Validation again.

In Allow, choose Whole number. Leave Data set to between. Type one as the minimum and forty as the maximum, and click OK.

Test it. In D2, type fifty, and press Enter. Excel stops it. Press Escape, then type eight instead, and press Enter. That one is accepted.

Excel Data Validation: Drop-Down Lists and Rules: A number rule
A number rule

5. Input message

Rules work best when people know them in advance. The Input Message tab of the same dialog shows a small tip as soon as someone clicks the cell, for example: enter whole hours, from one to forty.

One warning: validation checks typing, but values that are pasted from elsewhere can slip past it. Use Circle Invalid Data on the Data tab to find them.

Excel Data Validation: Drop-Down Lists and Rules: Input message
Input message

6. Recap

Let's recap. Data Validation controls what goes in a cell. A List rule gives you a drop-down. A Whole number rule sets limits. And Excel blocks anything that breaks the rule.

In the next lesson, you will make cells change color on their own, with Conditional Formatting.

If there is something you want to learn next, write it in the comments. Your requests decide what we teach in the next tutorials. Thanks for watching, and see you in the next one.

Excel Data Validation: Drop-Down Lists and Rules: Recap
Recap
What do you want to learn next? Tell us in the comments on YouTube: your requests decide the next tutorials.

← Previous tutorial: Remove Duplicates in Excel (Find and Delete Repeats)
Next tutorial: Conditional Formatting in Excel (Highlight Cells Automatically) →