Suppose we have a worksheet with several cells in which we want to block others from entering data. In this tutorial, we will show you how to restrict data entry in an Excel cell. Restriction of data entry becomes necessary when we share our important worksheets with others. Sometimes, we just don’t want others to interrupt our project worksheet. Also, we might need to restrict data entry for specific types of values.
How to Restrict Data Entry in Excel Cell: 2 Simple Methods
Throughout this article, we will show you 2 simple methods to restrict data entry in an Excel cell. To illustrate the methods we will use the following dataset. The dataset contains the names of students in a column. It also has a column that shows the Project Code assigned to each student. This is the column where we want to apply restrictions. We will use this dataset for all the methods of this article.
1. Restrict Data Entry Types in Excel Cell with Data Validation
Data Validation is an Excel feature that allows you to restrict what a user can type into a cell. This tool is very necessary for creating projects where we want to limit the accessibility of users. In this section, we will discuss various types of restrictions applied in Excel cells with data validation.
1.1 All Types of Data Entry Restriction
In this method, we will restrict all types of data entry for a user in an Excel cell. Follow the below steps to perform this method.
STEPS:
- To begin with, select cells (C5:C9).
- In addition, go to Data > Data Validation > Data Validation option.
- The above command opens a new dialogue box named ‘Data Validation’.
- Then, go to the Settings box. Select the option Text length from the Allow drop-down list.
- Furthermore, choose equal to from the Data drop-down list, then type 0 in the Length field.
- Subsequently, go to the Error Alert tab.
- Moreover, type the name in the Title Also, type a message in the Error message box. This message with the Title will show up when a user tries to enter data in specific cells.
- Now click on OK.
- After that, select cell C5. Type 1 in that cell.
- Press Enter.
- Finally, we get an error alert box like the one below.
Read More: How to Check for Data Entry Errors in Excel
1.2 Allow Only Whole Number
In this method, we will allow only a whole number for data entry in an Excel cell. The process of this method is similar to the previous one. To do this we will follow the below steps.
STEPS:
- First, repeat the initial two steps of the previous method.
- Next, go to the Settings tab.
- Then, select the option Whole number from the Allow dropdown list, between from the Data drop-down list.
- Put the value 1 in the Minimum field and 100 in the Maximum field that we want to allow.
- Afterward, go to the Error Alert tab.
- Type the following texts in the Title and Error message field.
- After that, type the value 5 in cell C5.
- Press Enter.
- As a result, we get an error in the following message box.
1.3 Only Decimal Numbers Allowed
This example is similar to the previous example. Here, we will restrict data entry in an Excel cell only to decimal numbers. Let’s see the steps to do this:
STEPS:
- Firstly, open the Data Validation dialogue box like the previous method.
- Secondly, go to the Settings tab.
- Thirdly, from the Allow drop-down list, choose Decimal and between from the Data drop-down list.
- Next, fill in the Minimum and Maximum fields with the values 1 and 100, respectively.
- After that, make changes in the error alert box like the previous method.
- Now, click on OK.
- After that, select cell C5. Type a 125 in that cell.
- Hit Enter.
- Lastly, we can see the error message box in the following image.
1.4 Restrict Data Entry to List
In this method, we will restrict data entry in an Excel cell by defining a list. We will follow the steps of our previous method to do this. The change that we have to make is to select the appropriate option for this method. To do this method follow the below steps.
STEPS:
- In the first place, repeat the first two steps of the first method. This will open the Data Validation dialogue box.
- Next, go to the Settings tab.
- Then, select the option List from Allow drop-down list.
- Additionally, type a list of values in the Source.
- Now, click on OK.
- So, a drop-down icon appears beside the selected cells.
- We can only insert values in the cells from the list. Otherwise, it will show an error.
- Afterward, type 23R in cell C5. The value is not in the defined list.
- Press Enter.
- Finally, we will see an error message like the following image.
1.5 Limit Data Entry to a Date Range
In this method, we will restrict data entry to date in an Excel cell. Then we will limit dates in selected cells. Let’s see the steps to perform this action.
STEPS:
- To begin with, follow the first two steps to open the Data Validation dialogue box.
- Additionally, go to the Settings tab.
- Furthermore, select Date from the Allow drop-down list and between from the Data drop-down list.
- Moreover, type the starting date and ending date of your task in the ‘Start date’ and ‘End date’ fields respectively.
- Keep the error alert messages the same as the previous methods.
- Now, click on OK.
- Afterward, type a date outside the defined range.
- Press Enter.
- Finally, we get an error message box like the following image.
1.6 Restrict Data Entry to Allow Time
We can also restrict data entry based on time in an Excel cell. So, we will not be able to type any time value outside a defined time range. Follow the below steps to perform this method.
STEPS:
- First, open the Data Validation To do this we will follow the first two steps of the first method.
- Next, go to the Settings tab.
- Then, select Time from the Allow drop-down list and between from the Data drop-down list.
- Additionally, set the value 9:00:00 AM in the Start time field and 5:00:00 PM at the End time.
- Set the message in Error Alert like the previous method.
- Click on OK.
- After that, type the value 6:00:00 PM in cell C5.
- Press Enter.
- As a result, we get the following error message box. We get this message as the value 6:00:00 PM is outside our defined range.
Read More: How to Record Time of Data Entry in Excel
1.7 Restrict Data Entry to Allow Only Text
Till now all the restrictions were built in by the Data Validation feature. What if we want to restrict otherwise? In that, we have to apply Custom Data Validation. Suppose in this method, we want to restrict data entry to allow only text values. Let’s see the steps to do this.
STEPS:
- Firstly, repeat the first two steps of the first method. It will open the Data Validation dialogue box.
- Secondly, go to the Settings tab.
- Thirdly, select the option Custom from the Allow drop-down list.
- Furthermore, in the Formula field type the following formula:
=ISTEXT(C5:C9)- Set the messages of the Error Alert tab like the previous methods.
- Click on OK.
- Here the ISTEXT function determines whether a value is text and returns TRUE if it is. Otherwise, it returns False.
- Moreover, type the numeric value 222 in cell C5.
- Press Enter.
- So, we get an error message box like the following one.
1.8 Limit Data Entry to Allow Only Number
In this method, unlike the previous method, we will restrict data entry to allow only numbers in an Excel cell. Go through the following steps to perform this method.
STEPS:
- In the beginning, perform the first two steps of the first method to open the Data Validation dialogue box.
- Next, go to the Settings tab.
- Then, from the Allow drop-down list, select the option Custom.
- Additionally, in the Formula field type the following formula:
=ISNUMBER(C5:C9)- Set the Error Alert tab’s messages in the same way as the previous approaches.
- Click on OK.
- Here, the ISNUMBER function is used to check whether a value is a number or not.
- Type ASD in cell C5.
- Press Enter.
- Finally, we will see an error message box like the following image.
2. Protect Worksheet to Restrict Data Entry in Excel Cell
Using the Data Validation feature is the most convenient way to restrict data entry in an Excel cell. But, we can also do this by protecting our Excel Worksheet. Let’s see the steps to do this.
STEPS:
- To begin with, click on the triangle icon in the left corner to select the entire worksheet.
- In addition, right-click on the selected region. Select the option Format Cells.
- Then, in the Format Cells dialogue box go to the Protection field.
- Uncheck the option Locked.
- Click on OK.
- Furthermore, select only cells (B5:B9).
- Right-click on the selected region and select the option Format Cells.
- Next, go to the Protection tab of the Format Cells dialogue box. Check the option Locked.
- Now, click on OK.
- Afterward, go to the Review tab. Select the option Protect Sheet from the ribbon.
- Type a password in the input field. We are using 1234.
- Click on OK.
- Again, one more dialogue box to confirm the password will appear.
- So, type in the password again in the input field.
- Click on OK.
- After that, select cell B5. Try to type any value in that cell.
- As a result, we will get an alert box like the following image.
Download Practice Workbook
You can download the practice workbook from here.
Conclusion
In conclusion, this tutorial demonstrates 2 simple methods to restrict data entry in an Excel cell. Download the practice worksheet contained in this article to put your skills to the test. If you have any questions, please leave a comment in the box below. Our team will try to respond to your message as soon as possible.
