How to Restrict Input Values in Excel Cells

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:

1. 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.
2. 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.
3. 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.
4. 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.
5. 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.

How to Limit Excel Cells to Only Be Filled with Specific Text

Another possible case is that we want to limit Excel cells can only be filled with certain text, for example, city name, country name, or product name. This is useful to ensure that the data we enter is consistent and there are no typos.

To limit Excel cells that can only be filled with certain text, we can use Data Validation with List type. Here are the steps:

1. Select (selection) cells or ranges that you want to limit input to only certain text. For example, we want to limit column C which contains city names can only be filled with predefined city names.
2. Click the “Data” menu on the Excel ribbon, then click “Data Validation” in the “Data Tools” group. Alternatively, we can also use the keyboard shortcut Alt + A + V + V.
3. In the Data Validation dialog box, select the “Settings” tab, then select “List” in the “Allow” drop-down list.
4. In the “Source” box, enter a list of texts that are allowed to be entered into cells, separated by commas. For example, if we want to limit the city name to only be filled with Jakarta, Surabaya, Bandung, or Yogyakarta, then enter Jakarta, Surabaya, Bandung, and Yogyakarta in the Source box. Alternatively, we can also select the range that contains the text list, for example, A1:A4.
5. Click OK to apply the Data Validation.

After that, try entering text into column C. If the text entered is one of the specified text lists (e.g. Jakarta, Surabaya, Bandung, or Yogyakarta), then Excel will accept that input. However, if the entered text is not one of the specified text lists, then Excel will display the error message as before.

In addition, Excel will also display a small arrow to the right of the cell that has the Data Validation type List. If we click the arrow, then Excel will display a list of text that we can choose to fill the cell. This is useful to make it easier for us to select the appropriate text without having to type it.

How to Restrict Excel Cells to Only Fill with Date Format

Another case that may occur is that we want to limit Excel cells can only be filled with date format only, for example, to fill birth data, transaction data, or other data related to time. This is useful to ensure that the data we enter is valid and matches the date format we want.

To limit Excel cells can only be filled with date format, we can use Data Validation with Date type. Here are the steps:

1. Select (select) cells or ranges that you want to limit input to date format only. For example, we want to limit column D which contains birth data to date format only.
2. Click the “Data” menu on the Excel ribbon, then click “Data Validation” in the “Data Tools” group. Alternatively, we can also use the keyboard shortcut Alt + A + V + V.
3. In the Data Validation dialog box, select the “Settings” tab, then select “Date” in the “Allow” drop-down list.
4. In the “Minimum” and “Maximum” boxes, enter the date range allowed to be inserted into the cell. For example, if we want to limit birth data between January 1, 1990, and December 31, 2020, then enter 1/1/1990 in the Minimum box and 31/12/2020 in the Maximum box. If we don’t want to limit the date range, then leave the Minimum and Maximum boxes blank.
5. Click OK to apply the Data Validation.

After that, try entering dates into column D. If the dates entered are within the specified range (e.g. between January 1, 1990, and December 31, 2020), then Excel will accept those inputs. However, if the date entered is outside the specified range, then Excel will display the error message as before.

In addition, Excel will also display a small calendar to the right of the cell that has Data Validation type Date. If we click the calendar, then Excel will display a selection of dates that we can choose to fill in the cell. This is useful to make it easier for us to choose the appropriate date without having to type it.