VBA & Macro Problems

How to Prevent Duplicate Values in an Excel Dependent Dropdown

Maira Mehtab Sep 22, 2026 869 views

Question details

The user wants to prevent duplicate entries, such as serial numbers, from being selected in a dependent dropdown list built with OFFSET and MATCH functions.

Product: Excel
Device & OS: not provided
Scenario: Selecting data from a dependent dropdown list where unique values are required.
Observed behavior: The dependent dropdown allows the same serial number to be selected multiple times, requiring a method to either highlight or actively block duplicate selections.

Before you start

Ensure your dependent dropdown lists are functioning correctly using OFFSET and MATCH before applying duplicate prevention methods. If you plan to use a VBA script, remember to save a backup of your workbook as a Macro-Enabled Workbook.

Solution 1

Highlight Duplicate Values Using Conditional Formatting

Use conditional formatting to visually flag duplicate selections in your dropdown column, allowing users to manually correct their entries.

While this method does not strictly prevent users from making a duplicate selection, it offers a code-free way to identify when a serial number has been chosen more than once.

Select the Target Column

Highlight the column containing your dependent dropdowns (for example, Column R) where you want to check for duplicates.

Apply Conditional Formatting

Navigate to the Home tab on the ribbon, click on Conditional Formatting, and hover over Highlight Cells Rules.

Set Duplicate Values Rule

Select 'Duplicate Values' from the menu. Choose a formatting style (such as Light Red Fill with Dark Red Text) and click OK. Any duplicate selection will now be instantly highlighted.

Solution 2Recommended

Block Duplicates Completely Using a VBA Macro

Implement a Worksheet_Change event in VBA that actively monitors the dropdown column, warns the user, and automatically clears the cell if a duplicate is detected.

Advanced Spreadsheets Made Easy

Manage Dropdowns and Macros Seamlessly with WPS Office

WPS Spreadsheet offers robust support for complex data validation, dynamic arrays like OFFSET and MATCH, and full VBA macro compatibility. You can easily build and secure dependent dropdowns without performance lag.

1. Download and Install: Get WPS Office for free from the official website and open your existing Excel workbook.
2. Enable Macros: Go to the Developer tab and ensure macros are enabled so your duplicate-blocking scripts run correctly.
3. Test Your Dropdowns: Interact with your dependent dropdown list to see the conditional formatting or VBA warnings take effect instantly.

Fully compatible with Microsoft Excel's VBA scripts and Worksheet_Change eventsNative support for advanced lookup functions like OFFSET, MATCH, and INDEXFree, lightweight, and highly responsive interface for complex data tasksSeamless format compatibility with .xlsx and macro-enabled .xlsm files

Frequently Asked Questions

Can I use standard Data Validation instead of VBA to prevent duplicates?

Standard Data Validation can prevent duplicates using a Custom formula like =COUNTIF(R:R, R1)=1. However, since your cell already uses a List rule for the dependent dropdown (using OFFSET/MATCH), you cannot apply two data validation rules to the same cell simultaneously, which is why VBA is required for strict prevention.

Why isn't my Worksheet_Change macro triggering?

This usually happens if macro security settings are blocking the script, or if Application.EnableEvents has been set to False in your VBA environment. Restarting the application or running a quick script to set Application.EnableEvents = True will fix it.

Will a Worksheet_Change macro slow down my spreadsheet?

No, as long as the macro is written efficiently. By adding a line like 'If Intersect(Target, Range("R:R")) Is Nothing Then Exit Sub', the macro will only execute its checks when a change is made specifically in the dropdown column, causing no noticeable performance drop.

Excel & Data Problems