How to Restrict Data Entry in Excel Cell (2 Simple Methods)

Get FREE Advanced Excel Exercises with Solutions!

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.


Download Practice Workbook

You can download the practice workbook from here.


2 Simple Methods to Restrict Data Entry in Excel Cell

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.

2 Simple Methods to Restrict Data Entry in Excel Cell


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).

All Types of Data Entry Restriction

  • In addition, go to Data > Data Validation > Data Validation

All Types of Data Entry Restriction

  • The above command opens a new dialogue box named ‘Data Validation’.
  • Then, go to the Settings Select the option Text length from the Allow drop-down list.

All Types of Data Entry Restriction

  • Furthermore, choose equal to from the Data drop-down list, then type 0 in the Length field.

All Types of Data Entry Restriction

  • 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 Title will show up when a user will try 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.


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.

Allow Only Whole Number

  • Afterward, go to the Error Alert tab.
  • Type the following texts in the Title and Error message field.

Allow Only Whole Number

  • 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.

Only Decimal Numbers Allowed

  • After that, select cell C5. Type a 125 in that cell.
  • Hit Enter.

Only Decimal Numbers Allowed

  • 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.

Restrict Data Entry to a List

  • 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.

Restrict Data Entry to a List

  • 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.

Limit Data Entry to a Date Range

  • Afterward, type a date out outside the defined range.

Limit Data Entry to a Date 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.

Restrict Data Entry to Allow Time

  • After that, type the value 6:00:00 PM in cell C5.

Restrict Data Entry to Allow Time

  • 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.


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.

Restrict Data Entry to Allow Only Text

  • Here the ISTEXT function determines whether a value is text and returns TRUE if it is. Otherwise, it returns False.
  • Moreover, type numeric value 222 in cell C5.

Restrict Data Entry to Allow Only Text

  • 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.

Limit Data Entry to Allow Only Number

Limit Data Entry to Allow Only Number

  • Press Enter.
  • Finally, we will see an error message box like the following image.

Read More: How to Create Data Entry Form in Excel (Step by Step)


Similar Readings


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.

Protect Worksheet to Restrict Data Entry in Excel Cell

  • Then, in the Format Cells dialogue box go to the Protection
  • Uncheck the option Locked.
  • Click on OK.

Protect Worksheet to Restrict Data Entry in Excel Cell

  • Furthermore, select only cells (B5:B9).
  • Right-click on the selected region and select the option Format Cells.

Protect Worksheet to Restrict Data Entry in Excel Cell

  • Next, go to the Protection tab of the Format Cells dialogue box. Check the option Locked.
  • Now, click on OK.

Protect Worksheet to Restrict Data Entry in Excel Cell

  • Afterward, go to the Review Select the option Protect Sheet from the ribbon.

Protect Worksheet to Restrict Data Entry in Excel Cell

  • 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.

Protect Worksheet to Restrict Data Entry in Excel Cell

  • As a result, we will get an alert box like the following image.

Read More: How to Create Data Entry Form in Excel VBA (with Easy Steps)


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. Keep an eye out for more inventive Microsoft Excel solutions in the future.


Related Articles

Mukesh Dipto

Mukesh Dipto

Hi there! I am Mukesh Dipto. I graduated with a bachelor's degree in engineering. Currently, I am working as a technical content writer in ExcelDemy. You will find all my articles on Microsoft Excel on this site. Outside of the workplace, my hobbies and interests include sports and meeting new people. I also enjoy sports. My favorite sports are football (to watch and play) and Badminton (play).

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo