Data Validation in Excel

Apply Data Validation to Range of Cells in Excel – 21 Steal

Data validation is the application of a required restriction to a cell or a cell range. After doing so, if a user enters any restricted value into any cell of data, it will give a reminder-like message without accepting that value. Here, only unrestricted value can be accepted.

Getting Started With Data Validation to Range of Cells

On the data validation dialogue box, we have 3 options. They are 1. Settings, 2. Input Message, and Error Alert. These are all explained underneath below the picture.

Apply Data Validation to Range of Cells in Excel - 21 Steal 1

Basics to Apply Data Validation to Range of Cells:

  • Select the range of cells that you want to apply the rules
  • On the “Data tab”, click “Data validation” to open a dialogue box
  • On the “Settings” tab, under the “Allow” box, select “Whole number”
  • In the “Data” box, select any option you want to perform. In this case I am selecting the first option called “Between”
  • Enter 20 as the greater than or equal value (Or select 20 as the the greater than or equal value from different range as in the picture above)
Apply Data Validation Rules to Range of Cells
Apply Data Validation Rules to Range of Cells
  • Now switch to “Input Message” tab, and type title and message as input message
Apply Input Message to Data Validation
Apply Input Message to Data Validation
  • Again switch to “Error Alert” tab, then select any error alert “Style”, and then type title and message as “Error Alert”
Apply Data Validation to Range of Cells in Excel - 21 Steal 2
  • Now if you select any cell that you have applied data validation criteria, an “Input Message” will show as in the picture below
Apply Data Validation to Range of Cells in Excel - 21 Steal 3
  • Now try to enter less than the minimum value 20 as in the picture below, then, it will also show an “Error Alert” without accepting the values.
Apply Data Validation to Range of Cells in Excel - 21 Steal 4
  • In the same way, try to enter greater than 20 value based on the given condition, it accepts.

Getting Started with Allocation of All Data Validation Settings

Any value:

Any value is the first and default validation criterion and doesn’t apply any validation rules to the cells.

Whole number:

Once you have applied the rule to the range of cells, it allows only whole numbers with restriction based on the given condition.

How to apply restrictions to whole numbers:

  • Select the range of the cells that you want to apply the rules
  • On the “Data tab”, click “Data validation” to open a dialogue box
  • On the “Settings” tab, under the “Allow” box, select “Whole number”
  • In the “Data” box, select any option you want to perform. In this case I am selecting the first option called “Between”
  • Enter 20 as the minimum value and 25 as the maximum value (Or select 20 as the minimum decimal value and 25 as the maximum decimal value from other cells already filled)
  • Now set “Input Message” and “Error Alerts” (as explained in “Basics to data validation to range of cells”)
  • Now try to enter less than the minimum decimal value 20 and greater than the maximum value 25, then it will show an error warning without accepting the values.
  • In the same way, try to enter the numbers between the minimum and the maximum values (20, 21, 22, 23, 24, and 25) based on the given condition, it accepts.
See also  How to Freeze Panes in MS-Excel- 2022's Secret

Decimal:

Once you have applied the rule to the range of cells, it allows only decimal numbers with restriction based on the given condition.

How to apply restrictions to Decimal:

  • Select the range of the cells that you want to apply the rules
  • On the “Data tab”, click “Data validation” to open a dialogue box
  • On the “Settings” tab, under the “Allow” box, select “Whole number”
  • In the “Data” box, select any option you want to perform. In this case I am selecting the first option called “Between”
  • Enter 0.1 as the minimum decimal value and 0.5 as the maximum decimal value (Or select 0.1 as the minimum decimal value and 0.5 as the maximum decimal value from other cells already filled)
  • Now set “Input Message” and “Error Alerts” (as explained in “Basics to data validation to range of cells”)
  • Now try to enter less than the minimum decimal value 0.1 and greater than the maximum decimal value 0.5, then it will show an error warning without accepting the values.
  • In the same way, try to enter the decimal numbers between the minimum and the maximum values (0.1, 0.2, 0.3, 0.4, 0.5) based on the given condition, it accepts.

List:

Use drop-down list to pick an item from a list of data that you create. Continue Reading >>

Date:

Once you have applied the rule to the range of cells, only date with restriction based on the given condition are allowed.

  • Select the range of the cells that you want to apply the rules
  • On the “Data tab”, click “Data validation” to open a dialogue box
  • On the “Settings” tab, under the “Allow” box, select “Date”
  • In the “Data” box, select any option you want to perform. In this case I am selecting the first option called “Between”
  • Enter 02/05/2020 as the start date (minimum) and 02/05/2021 as the end date (maximum). (Or select 02/05/2020 as the start date and 02/05/2021 as the end date from other cells already filled)
  • Now set “Input Message” and “Error Alerts” (as explained in “Basics to data validation to range of cells”)
  • Now try to enter less than 02/05/2020 as the start date (in ) and less than 02/05/2021 as the end date, then it will also an error warning without accepting the date.
  • In the same way, try to enter the dates between the minimum and the maximum values (start date and end date) based on the given condition, it accepts.

Time:

You can limit allowing the time format into the cells after applying the rule to the range of cells.

  • Select the range of the cells that you want to apply the rules
  • On the “Data tab”, click “Data validation” to open a dialogue box
  • On the “Settings” tab, under the “Allow” box, select “Date”
  • In the “Data” box, select any option you want to perform. In this case I am selecting the first option called “Between”
  • Enter 9:30 as the start time (minimum) and 12:30 as the end time (maximum). (Or select 9:30 as the start time and 12:30 as the end time from other cells already filled)
  • Now set “Input Message” and “Error Alerts” (as explained in “Basics to data validation to range of cells”)
  • Now try to enter 9:30 as the start time (in ) and 12:30 as the end time, then it will show an error warning without accepting the time.
  • In the same way, try to enter the time between the minimum and the maximum (start time and end time) based on the given condition, it accepts.
See also  [Fixed] Excel is Not Recognizing Numbers in Cells - 2022

Text Length:

You can limits allowing number of characters or digits to the range of cells after applying this validation rule.

  • Select the range of the cells that you want to apply the rules
  • On the “Data tab”, click “Data validation” to open a dialogue box
  • On the “Settings” tab, under the “Allow” box, select “Whole number”
  • In the “Data” box, select any option you want to perform. In this case I am selecting the first option called “Between”
  • Enter 2 as the minimum text length and 5 as the maximum text length (Or select 2 as the minimum text length and 5 as the maximum text length from other cells already filled)
  • Now set “Input Message” and “Error Alerts” (as explained in “Basics to data validation to range of cells”)
  • Now try to enter less than the text length and greater than the maximum text length, then it will show an error warning without accepting the text length.
  • In the same way, try to enter the numbers between the minimum and the maximum text lengths (2, 3, 4, and 5) based on the given condition, it accepts.

Custom Data Validation:

It is used for custom formula. Continue Reading >>

Data Rules in Data Validation in MS Excel

Between:

The range of cells that allows values that is only between two sets of numbers or letters.

For Example:

If you want to allow numbers between 20 and 25 to the cells you want, then the cells accept 20, 21, 22, 23, 24, and 25.

Not between:

The range of cells doesn’t accept the values that is between two sets of numbers or letters that you enter.

For Example:

If you don’t want to allow numbers between 20 and 25 to the cells you want, then the cells don’t accept 20, 21, 22, 23, 24, and 25. Other than these accepts all numbers.

Equal to:

The range of cells that accepts the values equal to the numbers or letters you entered.

For Example:

If you want to allow the number which is equal to 20, then the cells accept only 20.

See also  The Draw tab in Word, Excel, and Ppt - 2022's - Hidden

Not equal to:

The range of cells that do not accept values equal to the numbers or letters you entered.

For Example:

If you don’t want to allow the number which is equal to 20, then the cells don’t accept the number 20. Other than the number 20 accepts all the numbers.

Greater than:

The range of cells that accepts the values that is greater than the numbers or letters that you enter.

For Example:

If you want to allow the number or letter which is greater than 2, then the cells accept greater than the number or the letter 2.

Less than:

The range of cells that accepts the values that is Less than the numbers or letters that you enter.

For Example:

If you want to allow the number or letter which is less than 2, then the cells accept less than the number or the letter 2.

Greater than or equal to:

The range of cells that accepts the values that is greater than or equal to the numbers or letters that you enter.

For Example:

If you want to allow the number or letter which is greater than or equal to 2, then the cells accept greater than or equal to the number or the letter 2.

Less than or equal to:

The range of cells that accepts the values that is Less than or equal to the numbers or letters that you enter.

For Example:

If you want to allow the number or letter which is less than or equal to 2, then the cells accept less than or equal to the number or the letter 2.

Other Controlling Tools:

Ignore blank cells: 

When you enter certain value to the blank cell beyond the condition you applied, it doesn’t accept those values besides an error alert message (if ignore blank cells option is ticked). Continue Reading >>

Input Message:

You can show a message to the selected cells you want . click here to navigate on how to set an input message >>

Error Alert:

It shows an error alert message after you enter the invalid data to the cells. click here to navigate on how to set a Error Alert >>

Read Also:

What is Data Validation in Microsoft Excel?

Data validation is the application of a required restriction to a cell or a cell range. After doing so, if a user enters any restricted value into any cell of data, it will give a reminder-like message without accepting that value. Here, only unrestricted value can be accepted.