How to Limit Cell Value in Excel (7 Suitable Examples)

Sometimes it becomes necessary to receive input from users in Excel cells and it is important to ensure that the entered value by the user is valid. In order to achieve this, the Data Validation tool can be used to limit cell values in Excel. In this article, we will explore seven different examples to use the Excel Data Validation tool to limit cell value to a particular type of data.

Download Practice Workbook

Download this practice workbook to exercise while reading this article.

Introduction to Data Validation in Excel

How to Launch Data Validation Dialogue Box:

• Click on the Data tab and go to Data →  Data Tools →  Data Validation
• As a result, the Data Validation dialogue box will open.

Types of Data Validation:

Data Criteria:

Custom Input Message:

In the Input Message tab, you can customize the input message to be displayed. If you check the box of Show input message when cell is selected, you will see the input message if the cell is selected. An input message has two parts. Title and Input message.

Custom Error Alert:

Error alert is displayed when a wrong type of value is entered. You can customize the message that is to be shown as an error alert. Error Alert has three parts. Style, Title, and Error message.

How to Limit Cell Value in Excel: 7 Suitable Examples

In this article, we will demonstrate seven examples in Excel to limit cell value. We will use the following dataset for this purpose.

1. Limit Cell Value to Numeric Entry in Excel

In the first example, we will limit cell values to numeric entries only. We will explain restricting values to whole number, decimal, and number in a given range.

1.1 Limit Cell Value to Whole Number

First, we will limit cells E5 to E12 to the whole number following the steps given below.

• First of all, select the range E5:E12 and open the Data Validation dialogue box.
• Then select the Whole number in Allow
• After that, check the box of Ignore blank.
• Choose anything you like in the Data We chose less than and set the Maximum value to 80. It will only allow values less than 80.
• Click on OK to close the dialogue box.

• Now insert any value other than a whole number and you will get an error message.

1.2 Limit Cell Value to Decimal

In Excel, we can also limit cell value to decimals. We will allow the range F5:F12 to accept decimal values. That means this range will be able to accept both integers and decimal entries. The steps to achieve this goal is described below.

• First, select cells F5 to F12.
• Then in the Data Validation box, choose Decimal in Allow
• Don’t forget to check the box of Ignore blank.
• Then select greater than and set the Minimum value to 2000.
• After that, press OK.

• Now if you insert data that is not a numeric value, an error message will appear.

1.3 Limit Cell Value to Number in a Given Range

To limit cell value to number in a given range, you need to select between in the Data option after choosing Whole number or Decimal in Allow option. Then set the Minimum and Maximum values to specify the range. In this example, the specified range is 5 to 80.

If I insert a value outside this range, it will not be accepted by the selected cells.

2. Limit Cell Value to List

In this example, we will limit cell value to a predefined List without drop-down in Excel. We will use the range B5:B12 as the list. We will limit the range G5:G12 to this list. Follow these steps to do so.

• After selecting cells G5 to G12, go to the Data Validation dialogue box.
• Select List in the Allow option and leave the In-cell dropdown box unchecked.
• Then select range B5:B12 as Source.
• Finally, press OK to continue.

• Now try to insert a name that is not present in the range B5:B12.
• As a result, you will get an error message.

3. Restrict Cell Values to Drop-down List in Excel

Now you will learn to restrict cell values to Drop-down lists in Excel. We will use the range B5:B12 as the list. We will limit the cells G5 to G12 with this data validation. The process is explained below.

• First, select range G5:G12 and open the Data Validation
• Choose List in Allow option and make sure the In-cell dropdown box is checked.
• Then select range B5:B12 as Source and press OK.

• Now if you select any cell in the G5:G12 range, a dropdown with the list will appear. You will only be able to choose a value from the list.

4. Restrict Cell Value to Date in Excel

In this example, we will restrict cell value to Date only in Excel. We will follow the steps given below to do so.

• First, select cells where you want to apply data validation. We selected cells C5 to C12 in this example.
• Select Date in Allow option from the Data Validation dialogue box.
• Then choose less than or equal to in the Data option and set an End date.

• Now if you enter any other data other than date or a date after 01-01-2006, an error message will appear.

5. Limit Cell Value to Time

We will now show you how to limit cell value to Time in Excel. In this example, we will restrict the range D5:D12 to time. We will follow these steps to do so.

• First, select cells D5 to D12 and open Data Validation.
• Select Time in the Allow option and choose not between in the Data
• Finally, set a Start time and End time.

• If the entered time is within the Start and End Time, it will show an error.

6. Limit Cell Value to Text Length in Excel

In this example, you will see how to limit cell value to Text Length in Excel. We will restrict cells B5 to B12 to text length 8 which means if a text length or character length is 8 or more, the cells won’t accept the value. The procedure is discussed below.

• First of all, select the range B5:B12 and open the Data Validation dialogue box.
• Select Text Length in Allow option from the Data Validation dialogue box.
• Then select less than and set the Maximum value to 8.

• Now if you enter “Henderson” which is 9-letter text, the cell won’t accept it.

7. Restrict Cell Value Using Formula

In the last example, we will restrict cells B5 to B12 text using a formula. We will use the Custom type Data Validation for this purpose. Follow these steps to learn how to do it.

• First, select cells B5 to B12 and open Data Validation.
• Select Custom in Allow
• Then type the following formula.

• This formula will allow the cells to only accept text.
• Press OK to close the Data Validation.

• If you select any cell of the range B5:B12, a custom input message will appear that says Only text entry is allowed.

Frequently Asked Questions

• How do I set minimum and maximum values in Excel?

In the Data Validation dialogue box, select Whole number or Decimal in Allow option. Then select between in the Data option. After that, set Minimum and Maximum values as you like.

• How do you limit a value to 100 in Excel?

Go to the Data Validation dialogue box and select Whole number or Decimal in Allow option. Then select less than or equal to in the Data option. After that set the Maximum value to 100.

Things to Remember

• Choose the appropriate type of data validation based on the requirement.
• Don’t forget to give proper cell references while working with a custom formula.

Conclusion

Thanks for making it this far. I hope you found this article helpful. In this article, we have demonstrated seven examples to limit cell value in Excel. We have described limiting cell values to Whole number, Decimal, number in a given range. We have also covered how to limit cell values to List with or without dropdown. Moreover, we have explained steps to restrict cell values to Date, Time, and Text Length. Restricting cell values using custom formula is included in the article as well. If you have any queries or recommendations regarding this article, feel free to let us know in the comment section below.