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

Get FREE Advanced Excel Exercises with Solutions!

Restricting data entry in Excel cells is essential for ensuring accuracy, preventing errors, and maintaining data consistency. By restricting, you can reduce the risk of typos, protect formulas, and adhere to standards. Thus it enhances the overall integrity and security of your spreadsheet.

In this Excel tutorial, you will learn how to restrict data entry in cell.

The following dataset contains the names of students in a column. We’ll restrict the data entry in the Project Code column.

2 Simple Methods to Restrict Data Entry in Excel Cell

Here are the 2 methods to restrict data entry in Excel cell:

Using Data Validation

The Data Validation feature allows you to define rules and restrictions on the type and range of data that users can enter into a cell. It helps ensure data accuracy and consistency by validating user input based on specified criteria.

Here are the 8 cases of using Data Validation to restrict data entry in Excel cell:

Case 1: All Types of Data Entry Restriction

To restrict all types of data:

  1. Select the cell range C5:C9.All Types of Data Entry Restriction
  2. Go to the Data tab > Data Tools group > Data Validation drop-down > Data Validation.All Types of Data Entry RestrictionThe Data Validation dialog box appears.
  3. Go to the Settings tab and select the option Text length from the Allow drop-down list.All Types of Data Entry Restriction
  4. Choose equal to from the Data drop-down list and type 0 in the Length field.All Types of Data Entry Restriction
  5. Go to the Error Alert tab and type Restrict Data Entry in the Title field.
  6. 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.
  7. Click OK.
  8. Select cell C5 and type 1 in that cell.
  9. Press Enter.

You’ll get an error warning box.

Read More: How to Check for Data Entry Errors in Excel

Case 2: Limit Data Entry to Allow Only Number

To restrict data entry to allow only numbers:

  1. Select the cell range.
  2. Go to the Data tab > Data Tools group > Data Validation drop-down > Data Validation.
  3. The Data Validation dialog box appears. In the box:
    • Go to the Settings tab, and select the option Custom from the Allow drop-down list.
    • In the Formula field type the formula: =ISNUMBER(C5:C9)
      This formula checks whether the values in the range C5 to C9 are numbers.
    • In the Error Alert tab, insert the desired title and error message.
    • Click on OK.

    Limit Data Entry to Allow Only Number

  4. Type ASD in cell C5.Limit Data Entry to Allow Only Number
  5. Press Enter.

You will see an error message box.

Case 3: Limit Data Entry to Allow Whole Numbers of Specific Range

To allow whole numbers of specific range:

  1. Select the cell range.
  2. Go to the Data tab > Data Tools group > Data Validation drop-down > Data Validation.
  3. The Data Validation dialog box appears. In the box:
    • Go to the Settings tab.
    • Select the option Whole number from the Allow dropdown list.
    • Select between from the Data drop-down list.
    • Put the value 1 in the Minimum field and 100 in the Maximum field.Allow Only Whole Number
    • Go to the Error Alert tab.
    • Insert Title and Error message.
    • Click OK.Allow Only Whole Number
  4. Type the value 125 in cell C5.
  5. Press Enter.

As a result, an error message box appears.

Case 4: Decimal Numbers in Specific Range Allowed

To allow decimal numbers in specific range:

  1. Select the cell range.
  2. Go to the Data tab > Data Tools group > Data Validation drop-down > Data Validation.
  3. The Data Validation dialog box appears. In the box:
    • Go to the Settings tab.
    • From the Allow drop-down list, choose Decimal.
    • Select between from the Data drop-down list.
    • Fill in the Minimum and Maximum fields with the values 1 and 100, respectively.
    • In the Error Alert tab, insert the desired title and error message.
    • Click OK.

    Only Decimal Numbers Allowed

  4. Select cell C5. Type a 122.8 in that cell.
  5. Hit Enter.Only Decimal Numbers Allowed

You’ll see an error message box.

Case 5: Restrict Data Entry to Allow Only List

To restrict data entry to a list:

  1. Select the cell range.
  2. Go to the Data tab > Data Tools group > Data Validation drop-down > Data Validation.
  3. The Data Validation dialog box appears. In the box:
    • Go to the Settings tab.
    • Select the option List from Allow drop-down list.
    • Additionally, type a list of values in the Source.
    • Click on OK.

    Restrict Data Entry to a List

    A drop-down icon appears beside the selected cells. You can only insert values in the cells from the list. Otherwise, it will show an error.Restrict Data Entry to a List

  4. Now, type 23R in cell C5. The value is not in the defined list.
  5. Press Enter.

You will see an error message box.

Case 6: Limit Data Entry to a Date Range

To limit data entry to a range of dates:

  1. Select the cell range.
  2. Go to the Data tab > Data Tools group > Data Validation drop-down > Data Validation.
  3. The Data Validation dialog box appears. In the box:
    • Go to the Settings tab.
    • Select Date from the Allow drop-down list and choose between from the Data drop-down list.
    • Type the starting date and ending date of your task in the Start date and End date fields respectively.
    • Click OK.

    Limit Data Entry to a Date Range

  4. Type a date outside the specified range.Limit Data Entry to a Date Range
  5. Press Enter.

You’ll get an error message box.

Case 7: Restrict Data Entry to Specific Time Range

To restrict data entry to the specific time range:

  1. Select the cell range.
  2. Go to the Data tab > Data Tools group > Data Validation drop-down > Data Validation.
  3. The Data Validation dialog box appears. In the box:
    • Go to the Settings tab.
    • Select Time from the Allow drop-down list and choose between from the Data drop-down list.
    • Set the value 9:00:00 AM in the Start time field and 5:00:00 PM at the End time.
    • Click OK.

    Restrict Data Entry to Allow Time

  4. Type 6:00:00 PM in cell C5.Restrict Data Entry to Allow Time
  5. Press Enter.

You’ll get an error message box. You get this message because the value of 6:00:00 PM is outside the defined range.

Read More: How to Record Time of Data Entry in Excel

Case 8: Restrict Data Entry to Allow Only Text

To restrict data entry to allow only text:

  1. Select the cell range.
  2. Go to the Data tab > Data Tools group > Data Validation drop-down > Data Validation.
  3. The Data Validation dialog box appears. In the box:
    • Go to the Settings tab.
    • Select the option Custom from the Allow drop-down list.
    • In the Formula field type the following formula: =ISTEXT(C5:C9)
      This formula checks whether the values in the range C5 to C9 are text.
    • Click OK.

    Restrict Data Entry to Allow Only Text

  4. Type the numeric value 222 in cell C5.Restrict Data Entry to Allow Only Text
  5. Press Enter.

An error message box will appear.

Protecting Excel Worksheet

Protecting a worksheet in Excel is essential to prevent unintentional changes to cell contents, and maintain the integrity of formulas and data structure. This feature helps ensure data accuracy and restrict unauthorized modifications.

To restrict data entry by protecting the Excel worksheet:

Step 1: Unlock Entire Worksheet

  1. Click on the triangle icon in the left corner to select the entire worksheet.
  2. Right-click and select the option Format Cells from the Context Menu.Protect Worksheet to Restrict Data Entry in Excel Cell
  3. The Format Cells dialogue box appears. There, go to the Protection tab.
  4. Uncheck the option Locked.
  5. Click OK.Protect Worksheet to Restrict Data Entry in Excel Cell

Step 2: Lock Specific Range of Cells

  1. Select the cell range B5:B9.
  2. Right-click and select the option Format Cells.Protect Worksheet to Restrict Data Entry in Excel Cell
  3. Go to the Protection tab in the Format Cells dialogue box.
  4. Check the option Locked.
  5. Click OK.Protect Worksheet to Restrict Data Entry in Excel Cell

Step 3: Protect Worksheet

  1. Go to the Review tab > Protect group > Protect Sheet.Protect Worksheet to Restrict Data Entry in Excel Cell
  2. The Protect Sheet dialog box appears. There, type a password to unprotect the sheet.
    Here, we are using 1234.
  3. Click OK.Confirm Password dialog box will appear.
  4. Re-enter the password.
  5. Click OK.
  6. Select cell B5. Start typing in any value.Protect Worksheet to Restrict Data Entry in Excel Cell

You will get a warning box instantly.

Download Practice Workbook

You can download the practice workbook from here.

Conclusion

This article showed 2 simple methods to restrict data entry in an Excel cell. Use Data Validation to restrict data entry to the desired data type. Protect the worksheet to prevent any changes in the sheet. Leave a comment for further queries.

Frequently Asked Questions

Why would I want to restrict data entry in Excel cells?

Restricting data entry helps ensure accuracy, prevent errors, and maintain consistency in your spreadsheet. It allows you to enforce specific rules and criteria for data input.

Can I protect specific cells or ranges in Excel from being edited?

Yes, you can protect specific cells or ranges by locking the cells and then protecting the sheet. This is useful for preserving formulas or restricting changes to critical data.

Can I restrict data entry based on a predefined list of options?

Yes, you can create drop-down lists in Excel, allowing users to choose from predefined options. This helps standardize data entry and reduces the risk of input errors.

Related Articles


<< Go Back to Data Entry in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mukesh Dipto
Mukesh Dipto

Mukesh Dipto is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a superhero tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including Rhino3D, Maxsurf C++, AutoCAD, HTML, CSS, JavaScript, and WordPress. He got his B.Sc in Naval Architecture & Marine Engineering from BUET, and now he's switched gears, working as a content developer. In this role, he... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo