How to Limit Data Entry in a Cell in Excel

This post will guide you how to limit cell entries to specific length and number of digits in Excel. How do I limit data entry to allow only text or only numbers entered into in a cell using Data Validation option in Excel. How to limit entries to numeric values in Excel.

Assuming that you want to limit data entry for a range of cells in your worksheet, such as: only allow to enter text values or numeric values in the selected range of cells. How to do it. You can refer to the following introduction.

Limit Data Entry to Allow Only Text


If you want to only allow entering Text values in the selected range of cells, you can use the Data Validation feature to achieve it. Just do the following steps:

#1 select the range of cells that you want to limit data entry.

limit data entry in cell1

#2 go to DATA tab, click Data Validation command under Data Tools group. And the Data Validation dialog will open.

limit data entry in cell2

#3 click Settings tab, and select Custom from the Allow drop down list box. And then type the following formula based on the ISTEXT function in Formula Text box. And click Ok button.

=ISTEXT(A1)

 limit data entry in cell3

This formula will check if the cell value is a text value or not, if TRUE, it returns TRUE, otherwise, returns FALSE, and the Data Validation rules will be triggered.

Note: A1 is the first cell in your selected range.

limit data entry in cell4

Limit Data Entry to Allow Only Numeric Values


If you want to only allow entering numeric values in the selected range of cells, you can use the Data validation feature in combination with the ISNUMBER function to achieve the result. Do the following steps:

#1 select the range of cells that you want to limit data entry.

limit data entry in cell1

#2 go to DATA tab, click Data Validation command under Data Tools group. And the Data Validation dialog will open.

limit data entry in cell2

#3 click Settings tab, and select Custom from the Allow drop down list box. And then type the following formula based on the ISNUMBER function in Formula Text box. And click Ok button.

=ISNUMBER(A1)

limit data entry in cell5

The ISNUMBER function returns TRUE when cell value is a number and returns FALSE if not.

Note: A1 is the first cell in your selected range.

limit data entry in cell6

Limit Maximum Cell Value


If you want to limit data entries are not greater than or euqal to a given value (200) in a range, just do the following steps:

#1 Select the range of cells that you want to limit maximum cell value.

limit data entry in cell1

#2 go to DATA tab, click Data Validation command under Data Tools group. And the Data Validation dialog will open.

limit data entry in cell2

#3 click Settings tab in the opening Data Validation dialog, select Whole number in the allow drop down list box. And select less or equal to from the Data drop down list box. And then enter a numeric value 200 into text box of Maximum. Click OK button.

limit data entry in cell7

limit data entry in cell8

Video: Limit Data Entry

Related Functions


  • Excel ISNUMBER function
    The Excel ISNUMBER function returns TRUE if the value in a cell is a numeric value, otherwise it will return FALSE.The syntax of the ISNUMBER function is as below:= ISNUMBER (value)…
  • Excel ISTEXT function
    The Excel ISTEXT function used to check if a value is text. If so, returns TRUE; if the text is not text, the function will return FALSE.The syntax of the ISTEXT function is as below:=ISTEXT(value)…

You might also like:

Comments

So empty here ... leave a comment!

Leave a Reply

Your email address will not be published. Required fields are marked *

Sidebar