How to Restrict Input Values in Excel Cells

How to Limit Excel Cells by Using a Formula or Formula

Another possible case is that we want to delimit Excel cells by using specific formulas or formulas, for example, to fill data that depends on other data, or to create more complex and specific data validation criteria. This is useful for making Data Validation more flexible and dynamic.

To limit Excel cells by using formulas or formulas, we can use Data Validation with Custom type. Here are the steps:

1. Select (select) the cells or ranges that you want to limit input by using formulas or formulas. For example, we want to limit column E which contains student status to only be filled with Pass or Not Pass, depending on the student’s grade in column B.
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 “Custom” in the “Allow” drop-down list.
4. In the “Formula” box, enter the formula or formulas used to limit data input. This formula or formula must return a value of TRUE or FALSE, depending on whether or not the entered data meets the validation criteria. For example, if we want to limit the student status can only be filled with Pass if the student’s grade is more than or equal to 60, and Not Pass if the student’s grade is less than 60, then enter the following formula:
   =IF(B2>=60,E2= “Pass”,E2= “Not Pass” )
   This formula means: If the student’s grade in column B is greater than or equal to 60, then the student’s status in column E must be equal to “Pass”. If not, then the status of the student in column E must be the same as “Did Not Pass”.
5. Click OK to apply the Data Validation.

After that, try entering the text into column E. If the entered text matches the specified formula or formulas (e.g. Pass if the student’s grade is more than or equal to 60, and Not Pass if the student’s grade is less than 60), then Excel will accept that input. However, if the entered text does not match the specified formula or formula, then Excel will display the error message as before.

Conclusion

By using Data Validation, we can limit the input value in Excel cells according to the data validation criteria we want. Data Validation has various types of data validation, such as Whole Numbers, Decimal, List, Date, Time, Text Length, and Custom. By using the type of data validation that suits our needs, we can make the data we enter into Excel more valid and consistent.

Thus the article on how to limit input values in Excel cells by using Data Validation. Hope this article was useful and can help you achieve better rankings on Google. Thank you for reading this article to the end! ?