Function Problems

How to Show an Excel Alert When a Duplicate Number is Selected

Steve K Oct 1, 2026 868 views

Question details

The user wants to prevent duplicate number selections in a 16-cell Excel drop-down list and dynamically display the remaining unused numbers without using VBA.

How to Prevent Duplicate Selections in Excel Drop-Down Lists

Product: Excel
Device & OS: not provided
Scenario: Assigning unique numbers from a specific list (1 to 16) to a group of cells where each number can only be used once.
Observed behavior: The user needs an error alert to trigger when a duplicate number is selected and wants a formula-based list showing which numbers are still available.

Before you start

Ensure your target cells are clearly defined (e.g., A1:A16) and decide whether you want the remaining numbers to be displayed on the same sheet or a separate calculation sheet.

Solution 1Recommended

Use Data Validation to Prevent Duplicate Entries

Use a custom COUNTIF formula in the Data Validation tool to restrict users from selecting the same number twice and trigger an alert.

Data Validation is a built-in Excel feature that can stop users from entering invalid data. By combining it with a custom formula, you can ensure that each selection in your range is unique.

Select the target range

Highlight the group of cells where the numbers will be entered, for example, cells A1 through A16.

Open Data Validation

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

Enter the custom formula

In the Settings tab, change the 'Allow' dropdown to 'Custom'. In the Formula box, type =COUNTIF($A$1:$A$16,A1)=1. Ensure you use absolute references ($) for the range and a relative reference for the first cell.

Configure the Error Alert

Switch to the Error Alert tab. Choose 'Stop' as the style, enter a Title like 'Duplicate Number', and an Error message such as 'This number is already used. Please choose another.'

Use Data Validation to Prevent Duplicate Entries

Tip: Using the 'Stop' style ensures the user cannot bypass the warning, forcing them to select an unused number.

Solution 2

List Unused Numbers Dynamically

Use the FILTER and SEQUENCE functions to automatically display a list of numbers that are still available for selection.

Manage Data Easily with WPS Spreadsheet

Prevent Duplicate Data Entries with WPS Spreadsheet

WPS Spreadsheet offers powerful Data Validation tools to easily restrict duplicate entries, helping you maintain data accuracy without writing complex VBA code. It is highly compatible with standard Excel formulas.

1. Open your workbook: Launch WPS Spreadsheet and open the file where you want to restrict duplicates.
2. Highlight target cells: Select the range of cells where users will be entering or selecting numbers.
3. Access Data Validation: Go to the Data tab and click on the Data Validation icon.
4. Apply custom rule: Select Custom, enter your COUNTIF formula, and configure your custom Error Alert message.

Set up custom data validation rules and error alerts with ease.Fully compatible with Microsoft Excel formulas like COUNTIF.Free, lightweight, and features a familiar user interface for seamless migration.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my Data Validation custom formula not work?

Ensure you are using absolute references (like $A$1:$A$16) for the validation range and a relative reference (like A1) for the active cell inside the COUNTIF formula. If both are relative, the validation range will shift as it applies to other cells.

Can I prevent duplicates without using the FILTER function for remaining numbers?

Yes, the Data Validation alert works completely independently. The FILTER function is only required if you want to visually display a dynamic list of the unused numbers on your worksheet.

Does this data validation method work for text entries as well as numbers?

Yes, the COUNTIF Data Validation formula works for any duplicate values, meaning you can use it to prevent duplicate text, names, or dates in a specific column.

Excel & Data Problems