In this post we will see how we could restrict a user to enter value between a minimum and maximum value in excel.
Cases such as inputting values from users for some metrics for which we know the minimum and maximum limits, we could restrict user to enter values in a cell within those limit.
Suppose there is a metric X and its permissible value could only be between 150 and 500 so in this case we expect the user to fill the value between 150 and 500.
We will see Data Validation feature in excel by which we will restrict user to fill a value within permissible limits in excel.
Suppose in cell A1 we have metric name and in cell B2, we want users to fill a value between 150 and 500.
Step 1
Select cell B2 and got o Data Validation under Data tab in excel as shown in the fig below.
Step 2
Now select the “Custom” value from the drop down as shown in the pic below.
Step 3
Enter the formula =AND(B2>=150,B2<=500) in the formula bar shown below.
As we want user to enter a value between a ranges of 150 and 500 that why we have given AND condition that the value should be between 150 and 500 including the extremes.
So the condition in the formula will make sure that the cell will only allow values that are within the minimum and maximum values that are decided for that particular cell in excel.
After entering the formula select OK.
Also if you want you could set an error message that will be displayed when the user will enter a value outside the defined range, to do that go to Error Alert tab as shown in the pic below.
Step 4
Now if you want to apply the same data validation format to a range of cells please refer to Step 6 here.
Hope this helped.
