Function Problems

How to Limit Excel Drop-Down List Selections to 15 Uses

Tauseeq Magsi Oct 21, 2026 870 views

Question details

The user needs to restrict the number of times a specific option can be selected from a drop-down list to a maximum of 15 uses.

Product: Spreadsheet
Device & OS: not provided
Scenario: Assigning limited-capacity resources like training dates or venues where each drop-down option has a maximum usage limit.
Observed behavior: The user wants to implement a restriction using COUNTIF or COUNTIFS in Data Validation to prevent users from exceeding the 15-use limit for specific drop-down options.

Before you start

Ensure your drop-down list source data is set up in a separate range and identify the exact column where users will be making their selections.

Solution 1Recommended

Use Custom Data Validation with the COUNTIF Function

Apply a custom data validation rule using the COUNTIF function to restrict how many times a single item can be selected in a given range.

By combining Excel's Data Validation feature with the COUNTIF function, you can dynamically count how many times an option has been chosen. When the count exceeds your specified limit (e.g., 15), the system will prevent the user from making that selection.

Select the target range

Highlight the cells where you want the drop-down list and restrictions to apply, for example, cells G2 through G99.

Open Data Validation

Navigate to the Data tab on the top ribbon and click on Data Validation.

Set validation criteria

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

Enter the COUNTIF formula

In the Formula box, enter `=COUNTIF($G$2:$G$99,G2)<=15`. Ensure the range is locked with dollar signs so the rule applies consistently across the selected column.

Configure the Error Alert

Switch to the Error Alert tab, choose 'Stop' as the style, and type a custom warning message like 'This date or venue has reached its 15-use limit.' Click OK to apply.

Use Custom Data Validation with the COUNTIF Function

Adding Drop-Down Functionality: A custom validation rule alone does not display a drop-down arrow. To have both a drop-down list and a usage limit, you must set the Allow rule to 'List' first to create the drop-down, then use a helper column or VBA, OR instruct users to type the value while custom validation checks the limit.

Manage Data Validation Easily

Effortlessly Limit Drop-Down Choices in WPS Spreadsheet

WPS Spreadsheet fully supports advanced Data Validation and COUNTIF formulas, allowing you to easily manage capacity limits, training assignments, and inventory quotas without complex workarounds.

1. Open WPS Spreadsheet: Launch WPS Office and open the workbook containing your resource assignment list.
2. Access Data Validation: Select your input cells, navigate to the Data tab, and click Validation.
3. Apply Custom Rule: Select 'Custom' from the criteria list and input your COUNTIF formula to cap selections at 15.
4. Set Alert Message: Click the Error Alert tab to provide a clear explanation for users when a venue or date hits full capacity.

Fully compatible with Microsoft Excel data validation rules and formulas.Intuitive Data tab for quick drop-down list management.Customizable error alerts to guide users when limits are reached.Lightweight, fast, and free spreadsheet software for everyday tasks.

microsoft office alternative - wps office

Frequently Asked Questions

Can I limit different drop-down options to different maximum numbers?

Yes, but it requires a more complex formula. You can set up a reference table with the options and their specific limits, then use a combination of COUNTIF and VLOOKUP in your custom data validation formula to check against individual limits.

Why is my custom validation formula not working for empty cells?

Ensure that the 'Ignore blank' checkbox is selected in the Data Validation dialog box. If it is unchecked, Excel might evaluate empty cells in a way that triggers a false validation error.

What happens if a user copies and pastes a value instead of selecting from the drop-down?

Copying and pasting a cell can overwrite and bypass standard Data Validation rules. To strictly enforce limits and prevent copy-pasting over rules, you may need to protect the worksheet or use VBA macro scripts.

Can I use COUNTIFS instead of COUNTIF to apply limits based on multiple conditions?

Yes. If your limit depends on multiple criteria (e.g., limiting a venue choice only for a specific date), you can use the COUNTIFS function in the custom validation formula: `=COUNTIFS(range1, criteria1, range2, criteria2)<=15`.

Excel & Data Problems