# Data Validation and Named Ranges

Course: [Excel](https://www.devtutorials.ai/learn/excel) · Section: Data Management
Source: https://www.devtutorials.ai/learn/excel/data-management/data-validation

> Restrict numbers, dates, and text, create drop-down lists, and create and use named ranges. Then clean up a register in the Chapter 5 project.

When several people type into a sheet, mistakes can happen: an age of `250`, a date typed as `next Tuesday`, or `Sales`, `sales`, and `Sales Dept` for the same department. **Data validation** sets rules for what can be typed, and **drop-down lists** let people pick instead of type. **Named ranges** give ranges friendly names you can use in lists and formulas. In this lesson, you will set number, date, and text rules, create and edit drop-down lists, and create and use named ranges. At the end, you will clean up a club register in the Chapter 5 project.

Open the **Excel sandbox** and follow along. Validation is on the **Data** tab.

## Setting up a rule

Every validation rule is set the same way:

1. Select the cells the rule should apply to.
2. Go to **Data** > **Data Validation**.
3. In **Allow**, choose the kind of data, then set the details.
4. Click **OK**.

If someone types something that breaks the rule, Excel shows an error and rejects it.

## Number restrictions

Choose **Allow: Whole number** (no decimals) or **Decimal**, then choose a condition:

- **Between** `1` and `100` for a percentage score.
- **Greater than or equal to** `0` for a quantity.

Now `150` or `-3` is rejected.

## Date restrictions

Choose **Allow: Date**, then choose a condition such as **Between** a start and end date, or **Greater than** today’s date for a delivery date. Text like `tomorrow` is rejected, which also keeps your dates easy to sort and filter.

## Text restrictions

Choose **Allow: Text length** to limit how many characters can be typed, for example **Equal to** `10` for a 10-digit phone number, or **Less than or equal to** `50` for a short comment.

In the validation dialog, the **Input Message** and **Error Alert** tabs let you write a friendly hint like "Enter a score from 1 to 100" that appears when the cell is selected, and a clear message when something is rejected.

## Create drop-down lists

A **drop-down list** shows an arrow in the cell. Clicking it shows the allowed choices.

1. Select the cells, such as **C2:C20**.
2. Go to **Data** > **Data Validation**.
3. Choose **Allow: List**.
4. In **Source**, type the choices separated by commas: `Small,Medium,Large`.
5. Click **OK**.

Each cell now has an arrow. People can pick a size instead of typing it, so every entry is spelled the same way.

## Edit drop-down lists

To change the choices, select the cells again, open **Data Validation**, edit the **Source** (for example, add `,Extra Large`), and click **OK**.

To remove validation, open the dialog and click **Clear All**.

For a list that changes often, do not type the choices into the rule. Put them in cells on the sheet, and point the list at those cells, ideally through a **named range**. Then you update the list by editing the cells.

## Named ranges

A **named range** is a range with a name, like `Sizes` for **F2:F4** or `TaxRate` for **F1**. Names are easier to read and remember than addresses.

**To create one:**

- Select the range, click in the **Name Box**, type a name such as `Sizes`, and press **Enter**. Or:
- Go to **Formulas** > **Define Name**, type the name, check the range, and click **OK**.

Names cannot contain spaces (use `Tax_Rate` or `TaxRate`) and cannot look like a cell address (`AB12` is not allowed).

## Using named ranges

Once a range has a name, use it anywhere you would use the address:

- **In formulas:** `=B2*TaxRate` instead of `=B2*$F$1`. A name always points to the same place, so it works like an absolute reference.
- **In functions:** `=SUM(Sales)` instead of `=SUM(B2:B50)`.
- **In drop-down lists:** set the list **Source** to `=Sizes`.
- **To jump:** pick the name from the **Name Box** list to select that range.

Manage your names in **Formulas** > **Name Manager**, where you can edit what a name refers to, rename it, or delete it.

## Common mistakes

| Mistake | What happens | How to fix it |
|---|---|---|
| Rule applied to only one cell | The other cells accept anything | Select the whole column range and apply it again |
| Spaces in a list source like `Small, Medium` | Choices show with a leading space | Use `Small,Medium,Large` with no spaces |
| Name with a space, like `Tax Rate` | Excel rejects the name | Use `TaxRate` or `Tax_Rate` |
| Pasting into validated cells | Pasted values skip the rule | Paste carefully, or check again with validation |
| List source pointing at the wrong range | Wrong or empty choices | Edit the Source or the named range |

## Exercise

Click **Reset** in the sandbox header. Type:

- **A1**: `Name`, **B1**: `Age`, **C1**: `Size`
- **E1**: `Sizes`, **E2**: `Small`, **E3**: `Medium`, **E4**: `Large`

Then do each task and press **Check**.

## Chapter project

Put Chapter 5 together. You will clean up a club register: fix a spelling mistake everywhere, sort it, filter it, and add a drop-down so future entries stay tidy.

## What to remember

- **Data** > **Data Validation** sets rules: whole numbers, decimals, dates, text length, or a list.
- **List** validation creates a drop-down; separate choices with commas, no spaces.
- Edit or clear a rule from the same dialog.
- **Named ranges** give ranges names like `Sizes` or `TaxRate`: create them in the Name Box or with **Define Name**.
- Use names in formulas, functions, and list sources: `=Sizes`.

That is the end of Chapter 5. In Chapter 6, you will manage worksheets and work with data across several sheets.