Function Problems

How to Restrict Data Entry to Only One of Multiple Cells in Excel

Olivia Miller Oct 14, 2026 870 views

Question details

The user needs to restrict data input across five specific cells so that only one of the cells can accept a value, rejecting entries in the other four.

How to Allow Data Entry in Only One of Multiple Cells in Excel

Product: Excel
Device & OS: not provided
Scenario: Setting up a form or worksheet where a user must provide input in only one specific field out of several disjointed options (e.g., cells E15, H15, K15, N15, and Q15).
Observed behavior: Once data is entered into one of the designated cells, any attempt to type a value into the remaining designated cells should be blocked by a validation error.

Before you start

Before applying these data validation rules, ensure the target cells are currently empty, as validation rules only restrict new data entries and will not automatically delete existing overlapping data.

Solution 1Recommended

Use Custom Data Validation with the COUNTA Function

Apply a custom formula in the Data Validation settings to count non-empty cells in your selection and block input if the count exceeds one.

By combining the Custom Data Validation feature with the COUNTA function, you can dynamically monitor a specific group of cells. The COUNTA function counts the number of cells that are not empty. By setting the rule to only allow a maximum count of 1, Excel will immediately reject any subsequent entries in that defined group.

Select the target cells

Hold down the 'Ctrl' key on your keyboard and click on each of the specific cells you want to restrict (e.g., E15, H15, K15, N15, and Q15).

Open Data Validation

Navigate to the 'Data' tab on the Excel ribbon, locate the 'Data Tools' group, and click on 'Data Validation'.

Set the validation criteria

In the Data Validation dialog box, go to the 'Settings' tab. Click the 'Allow' drop-down menu and select 'Custom'.

Enter the custom formula

In the 'Formula' input box, enter the formula: =COUNTA($E15,$H15,$K15,$N15,$Q15)<=1. Make sure to use the exact cell references for your specific worksheet.

Apply the rule

Click 'OK' to save and apply the validation rule. Test it by entering data into one cell, then trying to type into another restricted cell.

Use Custom Data Validation with the COUNTA Function

Pro Tip: You can customize the error message that appears when someone tries to enter a second value. In the Data Validation dialog box, go to the 'Error Alert' tab, type a descriptive Title and Error message, and click OK.

WPS Spreadsheet Solution

Easily Manage Data Validation in WPS Spreadsheet

WPS Spreadsheet provides a highly compatible and intuitive interface for applying complex data validation rules, including custom formulas like COUNTA. You can easily restrict data entry across multiple cells exactly as you would in Microsoft Excel, but entirely for free.

1. Select your cells: Open your workbook in WPS Spreadsheet, hold the Ctrl key, and click the cells you want to restrict (e.g., E15, H15, K15, N15, Q15).
2. Access the Validation tool: Navigate to the 'Data' tab on the top ribbon and select 'Validation'.
3. Choose Custom settings: In the Settings tab of the prompt, select 'Custom' from the Allow dropdown menu.
4. Input the formula: Type your formula =COUNTA($E15,$H15,$K15,$N15,$Q15)<=1 in the Formula box and click 'OK'.

100% compatible with Microsoft Excel formulas and validation rulesIntuitive Data Validation menu for quick setup and custom error alertsLightweight application with fast processing for large datasets

microsoft office alternative - wps office

Frequently Asked Questions

Can I apply this validation to a continuous range instead of separate cells?

Yes. If your target cells are next to each other, you can use a continuous range in the COUNTA formula. For example, select cells A1 through A5, open Data Validation, choose Custom, and enter =COUNTA($A$1:$A$5)<=1.

Why is my Data Validation rule not deleting existing duplicate data?

Data Validation only restricts new data being typed into the cells. If the cells already contained data before you applied the rule, Excel will not automatically delete it. You must clear the cells manually before the rule can work properly.

How do I remove the data validation rule later?

Select the cells that have the rule applied, navigate to the Data tab, and click Data Validation. In the bottom left corner of the dialog box, click the 'Clear All' button, then click OK.

Does this formula work if a user copies and pastes data into the restricted cell?

Standard Data Validation can be bypassed if a user copies and pastes a cell over the restricted cell. To prevent this, users would need to paste 'Values Only' or the worksheet would require VBA macros to strictly enforce validation against pasting.

Excel & Data Problems