Value constraint is a feature in Excel that allows you to limit the values that can be entered into a cell. This feature can be used to prevent users from entering invalid or unwanted data.
Excel is one application that is often used to process data, both numeric, text, and date data. However, sometimes we need to limit the input values that can be entered into a specific cell so that the data we enter is valid and matches the criteria we want.
For example, we want to limit cells that can only be filled with numbers between 1 to 10, or can only be filled with certain text, or can only be filled with date format. How to do it?
One of the features that we can use to limit input values in Excel cells is “Data Validation”. Data Validation is an Excel feature used to create data validation criteria in cells or ranges so that data inputted into cells or ranges must meet these criteria. If the data entered does not match the criteria, then Excel will display an error message or error alert.
By using Data Validation, we can create various kinds of data validation criteria, such as:
- Whole Number: to limit cells can only be filled with whole numbers in a certain range.
- Decimal: to limit cells can only be filled with decimal numbers in a certain range.
- List: to limit cells can only be filled with selected values from a predefined list or list.
- Date: to limit cells can only be filled with date format in a certain range.
- Time: to limit cells can only be filled with time format in a certain range.
- Text Length: to limit cells can only be filled with text with a certain number of characters.
- Custom: to delimit cells by using a specific formula or formulas.
In this article, we will discuss how to use Data Validation to limit input values in Excel cells with some example cases. Let’s get started!
How to Limit Excel Cells Can Only Be Filled with Numbers Only
One case that often happens is that we want to limit Excel cells to only be filled with numbers, without any other characters such as letters, symbols, or spaces. This is useful to prevent data input errors, for example, if we want to calculate the average of students’ grades, then we must ensure that cells containing student grades contain only numbers.
To limit Excel cells can only be filled with numbers, we can use Data Validation with the type Whole Number or Decimal. Here are the steps:
- Select (select) cells or ranges that you want to limit input to numbers only. For example, we want to limit column B which contains student grades to only be filled with numbers.
- Click the “Data” menuon the Excel ribbon, then click “Data Validation” in the “Data Tools” group. Alternatively, we can also use the keyboard shortcut Alt + A + V + V.
- In the Data Validation dialog box, select the “Settings” tab, then select “Whole Number” or“Decimal” in the “Allow” drop-down list. The difference between Whole Number and Decimal is that Whole Number only allows whole numbers, while Decimal allows decimal numbers.
- In the “Minimum” and “Maximum” boxes, enter the range of number values allowed to be entered into the cell. For example, if we want to limit a student’s score between 0 and 100, then enter 0 in the Minimum box and 100 in the Maximum box. If we don’t want to limit the range of number values, then leave the Minimum and Maximum boxes blank.
- Click “OK” to apply the Data Validation.
After that, try entering the values into column B. If the values entered are numbers in the specified range (for example between 0 and 100), then Excel will accept the input. However, if the value entered is not a number or outside the specified range, then Excel will display an error message such as the following:
We can change this error message to our liking, by clicking the “Error Alert” tab in the Data Validation dialog box, and then filling the “Title” and “Error Message” boxes with the text we want.