Others

How to Limit Excel Table Entries to Specific Values and Maximum Count

Maira Mehtab Sep 28, 2026 869 views

Question details

The user needs to restrict input in an Excel column to four specific text values (L1, L2, L3, L4) and ensure the combined total of these entries does not exceed 15.

Product: Excel
Device & OS: not provided
Scenario: Enforcing strict data entry rules to prevent typos for downstream processing (like Power Query) and capping the total number of allowed entries across the dataset.
Observed behavior: Without proper data validation, users can enter invalid text (e.g., L6) or exceed the required maximum count of 15 entries without triggering an error.

Before you start

Ensure you know the exact range of cells you want to apply the restriction to (e.g., G8:G197), and make sure the top-left cell of the selection is the active cell before applying the formula.

Solution 1Recommended

Use Custom Data Validation with AND, OR, and COUNTIF Functions

Apply a custom data validation formula to simultaneously restrict input to specific values and cap their total combined count.

To enforce two rules at once (allowing only specific text values AND limiting their total count), you must use a Custom Data Validation formula. By combining the AND, OR, and COUNTIF functions, Excel will evaluate both conditions before allowing the user to input data.

Select the target range

Highlight the cells where you want to apply the restriction, for example, G8:G197. Ensure that G8 is the active cell in your selection.

Open Data Validation

Go to the 'Data' tab on the ribbon and click on 'Data Validation' in the Data Tools group.

Set the Validation Criteria

In the Settings tab of the Data Validation dialog box, click the 'Allow' drop-down menu and select 'Custom'.

Enter the custom formula

In the Formula box, paste the following formula: =AND(OR(G8="L1",G8="L2",G8="L3",G8="L4"),(COUNTIF($G$8:$G$197,"L1")+COUNTIF($G$8:$G$197,"L2")+COUNTIF($G$8:$G$197,"L3")+COUNTIF($G$8:$G$197,"L4"))<15)

Apply the rule

Click 'OK'. The selected range will now only accept 'L1', 'L2', 'L3', or 'L4', and will reject entries if the combined count reaches the limit of 15.

Adjusting the Count Limit: If you want to allow exactly 15 entries, you may need to change '<15' at the end of the formula to '<=15', depending on how your specific dataset evaluates the count during data entry.

Advanced Data Validation in WPS Spreadsheet

Restrict Cell Entries and Set Limits Using WPS Spreadsheet

WPS Spreadsheet fully supports advanced custom data validation formulas, allowing you to use AND, OR, and COUNTIF functions to strictly control data entry and prevent errors in your datasets.

1. Select your range: Open your workbook in WPS Spreadsheet and select the column range (e.g., G8:G197).
2. Access Data Validation: Navigate to the 'Data' tab on the top ribbon and select 'Validation'.
3. Choose Custom Validation: Choose 'Custom' from the 'Allow' drop-down menu.
4. Input the formula: Paste the combined =AND(OR(...), (COUNTIF(...)<15) formula into the Formula box.
5. Confirm and apply: Click 'OK' to immediately enforce the criteria across the selected range.

Fully compatible with Microsoft Excel data validation formulas and rules.Prevent typos and standardize data for accurate Power Query processing.User-friendly interface for setting up custom error alerts and input messages.Lightweight, fast, and completely free to use.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my data validation formula return an error when I apply it?

This usually happens if the active cell during selection does not match the relative cell reference in your formula (e.g., using G8 in the formula while G9 is the active cell). Always ensure the first cell of your selection is the active one.

Can I change the maximum allowed entries to a number other than 15?

Yes, simply change the '<15' at the end of the custom formula to your desired limit, such as '<20' or '<=10', depending on the maximum number of entries you want to permit.

How do I add a custom error message when the limit is exceeded?

In the Data Validation dialog box, switch to the 'Error Alert' tab. Check the box for 'Show error alert after invalid data is entered', choose the 'Stop' style, and type your custom title and error message before clicking OK.

Does this formula prevent users from copy-pasting invalid data?

Standard data validation in Excel and WPS Spreadsheet does not prevent users from pasting invalid data over the cells, which overrides the validation rule. To strictly enforce this, you would need to protect the worksheet or use VBA macros to block paste operations.

Office Quick Solutions