Sometimes an individual might need to specify some Excel cells to a certain text or number format and limit the number of digits. Data Validation tool can help greatly to achieve this goal. In this article, we will demonstrate how to set limit in Excel cell in 5 simple ways.
In this article, we will learn 5 simple ways to set limit in Excel cell. We will use the following dataset for this purpose. The dataset contains various types of cell entries such as text, date, time, etc.
1. Setting Limit to Numerical Entry
In the beginning, we will demonstrate how you can limit cells to numeric entries only. We can limit cells to both whole numbers and decimals which are discussed below.
1.1 Whole Number
In this method, you will learn how to limit cells to a Whole Number using the Data Validation tool. Read the following steps.
Steps:
- First of all, select all the data from the Days Absent column to limit it to whole numbers only.
- Next, go to the Data tab.
- Then, click on the Data Validation tool. A pop-up box will appear.
- In the pop-up, select the Whole number in the Allow.
- In the Data option, select any of the options that will help you set your desired limit or criteria. In this example, we will select the between option.
- For the between option, you will have to choose a minimum and maximum number. We chose 10 as the minimum and 90 as the maximum value.
- Now, this cell will only allow values between 10-90. Any value out of this range will show an error message.
- Press OK.
- Now let us try to input a value of more than 90. We wrote 91 in cell E5 and it is showing that this value doesn’t match the criteria.
- Now let’s try a value less than 10. It is again showing the error message when we try to input 9.
- We can modify the error message as well as show input criteria while the cell is selected. To do that, click on the Input Message tab in the Data Validation pop-up.
- Then change the Title and Input message.
- Make sure that the Show input message box is checked.
- To change the error message, click on the Error Alert tab.
- Now you can change the Style, Title and Error message as you want.
- In the following figure, when you select cell E5, the Input Message will appear.
- If you put a wrong value in the cell, it will show the Error Alert you created.
Read More:Â How to Use Excel Formula Not to Exceed a Certain Value
1.2 Decimal Numbers Only
Now we will show you how you can limit cells to decimal numbers. The steps are given below:
Steps:
- First, select all the cells you want to set the limit to decimal numbers.
- Then go to the Data Validation tool from the Data Tab. A pop-up box will open.
- Select Decimal in the Allow option and choose any suitable criteria in the Data menu.
- Here, we chose not between. It only allows value which is not between the specified range.
- We chose the not between range 2500-3000.
- It means that any value out of this range will be accepted.
- For example, if we try to write the value 2550 which is in the range, an error message will pop up.
Read More: How to Make a Data Validation List from Table in Excel
2. Set Limit to Text Length
In this part of the article, you will see how to set up a limit to Text length. Follow the procedures below.
Steps:
- To begin with, select all the cells of the employee name. We will limit the text length to 10 in these cells.
- To do that, go to the Data Tab.
- Then select Data Validation from the ribbon. This will open a pop-up.
- In the pop-up, select Text length in the Allow option and choose any convenient criteria in the Data.
- We selected less than option and chose the maximum value as 10. This will allow text with less than 10 characters only.
- To see that it works, write any name containing more than 10 characters in the selected cells.
- We wrote Williamson which has more than 10 characters. Therefore, it shows that the data doesn’t match the input criteria.
3. Limiting Cell Entry to a List
We will now limit cell entries to only the data of a list. Follow the steps to learn how to do it.
Steps:
- First, select cells B5 to B12.
- Then go to the Data Validation tool from the Data Tab.
- In the Data Validation window, select List in the Allow.
- Then click on the arrow of the source cell.
- Now select the range of the list you want to specify. We specified the range from B5 to B12.
- Then select OK.
- Now if we type any name that was on the specified list, the cell will accept it. Here, we wrote the name Zack in cell B9 which was on the specified list. Therefore, the cell accepted it.
- On the other hand, when typed the name Bill which was not on the list, an error message popped up.
Read More: How to Use Data Validation List from Another Sheet
4. Limiting Entry Except for Date Values Only
Sometimes we need only dates in certain cells. If we want those cells to accept only Date format, we can do this by using Data Validation tool.
Steps:
- First of all, select the cells under Entry Date.
- Go to the Data Tab and from there click on the Data Validation tool.
- Then select Date in the Allow option.
- Choose any criteria in the Data option.
- We chose greater than and wrote the Start date as 01-01-2001.
- That means any date before the specified date will be rejected.
- For instance, if we select C5 cell and type 07-11-2000 which does not meet the criteria, a pop-up will open and show an error.
5. Limiting Cell Entry to Time
Now we will limit our cells to Time format. To do that, we will follow the steps below.
Steps:
- In the beginning, use your mouse to select cells from D5 to D12.
- Next, click on the Data Validation tool from the Data Tab.
- In the Data Validation pop-up, select Time in the Allow option and choose any criteria according to your need.
- We selected between criteria and chose the Start time and End time as 9:00:00 AM and 11:00:00 AM. This will allow the time between 9 AM and 11 AM.
- Now click OK.
- To see that it works, let us try to input a wrong value in cell D5.
- We wrote 8:50:00 AM in the cell and an error message popped up.
Things to Remember
- Choose the limit criteria properly or you won’t get your desired results.
- You can choose Custom in the Allow option of Data Validation if you need customized criteria.
Download Practice Workbook
Download this practice workbook for practice while you are reading this article.
Concluding Remarks
Thanks for making it this far. I hope you find this article useful. Now you know 5 different methods to calculate tenure in years and months in Excel. Please let us know if you have any further queries and feel free to give us any recommendations in the comment section below.
Related Articles
<< Go Back to Data Validation in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
