Skip to main content

How to limit cell entry to numeric value or a list in Excel?

Sometimes you design a workbook, and you want to control what people can enter in worksheet. For example, you can limit cell entry to a range of numeric value or a specific list. This article will show you the ways to limit cell value entries in Excel quickly.


Limit cell entry to whole numbers or numbers in a given range

You can set numeric value entries with specific limitation with following steps:

1. Select the range that you will set the numeric value entries, and click the Data > Data Validation. See screenshot:

2. In the opening Data Validation dialog box, go to the Setting tab, and:

(1) Select the Whole number item from the Allow drop down box. See the first screenshot below:
  
(2) Select one item from the Data drop down box according to your needs. See the second screenshot above:
(3) Input specific numbers for limitation in Maximum/Minimum/Length box according to your needs.

3. Click OK button.

This method is only able to set limitation for numeric values entered in cells. And the following method will guide you to set limitation for text entries.


Limit cell entry to any numeric value (whole/decimal numbers) or prevent from certain characters

If you have Kutools for Excel installed, you can apply its Prevent Typing feature to limit cell entry to numeric values, letters, or other special characters as you need, and also support to prevent from typing numeric numbers, letters, or specific characters in Excel.

Kutools for Excel - Packed with over 300 essential tools for Excel. Enjoy a full-featured 30-day FREE trial with no credit card required! Download now!

Limit cell entry to whole numbers or decimal numbers only:

Prevent from typing letters in the specified range:

Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy a full-featured 30-day FREE trial with no credit card required! Get It Now


Limit cell entry to a list of text value entries

You can set limitation for text entries with following steps:

1. Preset all texts that other users can enter in cells, saying preset a list of name in A2:A10 as following screen shot show:

2. Select the range that you will set limitation for text entries in cells, and click the Data > Data Validation.


Limit cell entry to a list of text value entries

You can set limitation for text entries with following steps:

1. Preset all texts that other users can enter in cells, saying preset a list of name in A2:A10 as following screen shot show:

2. Select the range that you will set limitation for text entries in cells, and click the Data > Data Validation.

3. In the Data Validation dialog box, go to the Setting tab, and:
(1) Select the List item from the Allow drop down list;
(2) Check the In-cell dropdown option;
(3) Specify the preset source. In this case, it is the name list in A2:A10.

4. Click the OK button.

Then select one cell in the range with text entries limitation, it adds an arrow besides the active cell. Click the arrow , it shows a drop down list with preset name list. You can select one name to fill active cell easily. See screenshot below:


You can also set Input Message for numeric or text cell entries limitation, and set Error Alert for limitation as well.


arrow blue right bubble Limit entering duplicates in one column/list with one click

Sometimes, you may be required to enter unique values in a column or a list. You can get it done with only one click by Kutools for Excel’s Prevent Duplicates utility.

Kutools for Excel - Packed with over 300 essential tools for Excel. Enjoy a full-featured 30-day FREE trial with no credit card required! Download now!

Select the column or list that you will limit entering duplicates, and then click Kutools > Prevent Duplicates. From now on, when duplicate entries occur in the specified column or list, it will warn you immediately:

Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy a full-featured 30-day FREE trial with no credit card required! Get It Now


Demo: only allow or prevent from typing specified characters in Excel


Kutools for Excel: Over 300 handy tools at your fingertips! Start your 30-day free trial with no feature limitations today. Download Now!

Easily Limit Cell Entry to Whole / Decimal Numbers Only in Excel



Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy a full-featured 30-day FREE trial with no credit card required! Get It Now

Relative article:

How to limit characters length in a cell in Excel?

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

Description


Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
Comments (12)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
what if i want the cell show me the max digit that i enter \because if i do restrict ,it will show me icon that i dont want
This comment was minimized by the moderator on the site
I have a case where I want to customize the column with data limit and alpha numeric only i am trying many ways but not able to find solution
This comment was minimized by the moderator on the site
Hi, did you try the second method listed in the article? To limit a column with only alpha numeric input, you can first select the column, then go to Kutools tab, find Prevent Typing. In the drop-down list, select Prevent Typing. Then select the third option: Allow to type in these chars: copy the value below: abcdefghijklmnopqrstuvwxyz.0123456789. Now only alpha numeric characters are allowed to enter in the column.
This comment was minimized by the moderator on the site
I'm trying to do something simple...limit the maximum value in a range of cells. I follow your instructions using data validation...but it does not work...it does not limit the maximum value I put in the window.
This comment was minimized by the moderator on the site
Hi, to limit the maximum value in a range of cells, please follow the first method listed in the artcile. In the Data Validation dialog box, under Allow, you can select Whole number or Decimal according to your needs; Under Data, you should selection less than or equal to. And then set Maximum value.
This comment was minimized by the moderator on the site
HI, I want to insert data in excel as below. How? 1 2 1 2 1 2 1 2
This comment was minimized by the moderator on the site
how it could be possible that i assigned a limit "250" , and a cell show only 200 in this cell and above value than 200 show in other cell
This comment was minimized by the moderator on the site
how do I limit the value of a cell depending on another cell? For example users can select either Kg or Lbs for units of weight, and the limit depends on the units selected.
This comment was minimized by the moderator on the site
sir my work book i fill value 5000 automatic convert 50 4000 40 . i want not convert 5000 = 5000 and 4000 = 4000 ?
This comment was minimized by the moderator on the site
Thanks so much, really appreciate the help. :lol:
This comment was minimized by the moderator on the site
THANK YOU SO MUCH YOU DID MY WORK GO EASY.................... :lol:
This comment was minimized by the moderator on the site
The content is detailed with full explanations.
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations