How to add data validation in excel

Answer

Data validation is a feature that restricts the type of data a user enters in a cell. The feature protects structure, improves accuracy, and maintains consistency across worksheets. Data validation can restrict entries to lists, numbers, dates, text length, and custom rules.

How to add data validation in Excel

To add data validation in Excel, follow the steps below for Windows and macOS.

Windows

1. Select the cell or range.

2. Go to the Data tab on the Ribbon.

3. Select Data Validation in the Data Tools group.

4. Choose a validation type from the Allow dropdown.

5. Set criteria in the Data and Value fields.

6. Add an input message in the Input Message tab when needed.

7. Add an error alert in the Error Alert tab to control the message shown when invalid data is entered.

8. Select OK.

Mac

1. Select the cell or range.

2. Open the Data menu.

3. Select Validation.

4. Choose a validation type in the Allow dropdown.

5. Adjust criteria in the Settings section.

6. Add messages in the Input Message and Error Alert tabs.

7. Select OK.

Types of data validation rules

1. List validation

List validation restricts entries to predefined values that appear in a dropdown. Examples include product categories or department names.

Steps:

1. Select the range.

2. Open Data Validation.

3. Choose List.

4. Enter values separated by commas or reference a range.

5. Select OK.

2. Number validation

Number validation sets rules for whole numbers or decimals. Examples include quantities, limits, and ranges.

Steps:

1. Select the range.

2. Open Data Validation.

3. Choose Whole Number or Decimal.

4. Define criteria such as between, greater than, or less than.

5. Enter numeric limits.

6. Select OK.

3. Date validation

Date validation limits entries to a specific period. Examples include due dates or scheduling ranges.

Steps:

1. Select the range.

2. Open Data Validation.

3. Choose Date.

4. Define criteria and set start and end dates.

5. Select OK.

4. Text length validation

Text length validation controls the number of characters. Examples include codes, identifiers, or character limits in form fields.

Steps:

1. Select the range.

2. Open Data Validation.

3. Choose Text Length.

4. Set criteria such as equal to, less than, or between.

5. Enter character limits.

6. Select OK.

5. Custom validation

Custom validation uses formulas to define rules when advanced conditions are needed.

Steps:

1. Select the range.

2. Open Data Validation.

3. Choose Custom.

4. Enter a formula.

5. Select OK.

How to show a message when users select a validated cell

1. Open Data Validation.

2. Select the Input Message tab.

3. Turn on Show input message when cell is selected.

4. Enter a title and message.

5. Select OK.

How to show an error alert when invalid data is entered

1. Open Data Validation.

2. Select the Error Alert tab.

3. Choose an alert style: Stop, Warning, or Information.

4. Add a title and message.

5. Select OK.

How to remove data validation

1. Select the range.

2. Open Data Validation.

3. Choose Clear All.

4. Select OK.

How to copy data validation to another range

1. Select a cell with validation.

2. Copy the cell using Ctrl+C (Windows) or Command+C (Mac).

3. Right-click the target range.

4. Choose Paste Special.

5. Choose Validation.

6. Select OK.

How to find all cells with data validation

1. Select any cell.

2. Press Ctrl+G.

3. Select Special.

4. Choose Data Validation.

5. Select OK.

Yes · 0 No · 0 Add Comment · 0

Add ·

Report