What does =B2*TaxRate do if TaxRate names F1?

Multiplies B2 by the word TaxRate

Data Validation and Named Ranges

By the Syncfusion DevTutorials team · Last updated October 20, 2026

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:

Select the cells the rule should apply to.
Go to Data > Data Validation.
In Allow, choose the kind of data, then set the details.
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.

Select the cells, such as C2:C20.
Go to Data > Data Validation.
Choose Allow: List.
In Source, type the choices separated by commas: Small,Medium,Large.
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.

Running club register

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.