How do I remove duplicates in data validation list in Excel?

Prevent duplicates before they happen by combining a simple function with data validation.

Excel has built-in tools to expose and even delete duplicates, but they work on existing data after the fact. If you want to make sure duplicates never happen in the first place, you might consider using Excel’s data validation feature. This feature checks values as you enter them and depending on the rules you specify rejects or accepts that value. Unfortunately, there’s no built-in validation rule that recognizes a duplicate value, so you’ll need to combine the feature with Excel’s COUNTIF() function.

In this article, I’ll show you how to do this in a Table object using structured referencing and named ranges.

More about Software

• Learn advanced Excel skills for less than $35
• How to use the spill range in Excel
• How to create a fun Fly In effect in PowerPoint
• Google Workspace vs. Microsoft 365: A side-by-side analysis

I’m using Office 365’s Excel 2016 (desktop) on a Windows 10 64-bit system, but both techniques will work in earlier versions and in the browser edition. You can work with your own data or download the demonstration .xlsx and .xls files.

SEE: Windows 10 power tips: Secret shortcuts to your favorite settings (Tech Pro Research)

Extract unique items fordynamic data validation drop down list

How can you extract unique items from a list that contains multiple instances of each item, and use this list in a dynamic dropdown so that any time a new item is added to the list, or, if the list expands, your data validation list is updated?

There are more “manual” ways you can do this– for example, using the Remove duplicates feature, or a Pivot Table.

There are simpler ways to approach this with the use of CSE (CTRL + SHIFT + ENTER), but there are also alternatives to achieve the same result.

This guide will show another approach that does not involve any of these.

Instead, it will be using a combination of Excel’s functions: INDEX, OFFSET, MATCH and COUNTIF.

In this example, we will be taking a data set with different Divisions, along with the Apps that belong to each division.

The aim here is to get a unique list of divisions, which we will then use to create a dynamic dropdown list.

Moreover, this list should be able to expand accordingly when new divisions or apps are added to the data set.

Creating a Drop Down List without Duplicates and Blanks in Excel 365

By

-

0

Nobody wants to see multiple occurrences of values in a drop-down list in Excel. So What is the easiest way to create a drop-down list without duplicates in Excel 365?

We can depend on a helper column. But not the way you were doing in earlier versions of Excel.

Here in Excel 365, things are a little easier.

Table of Contents

• Creating a Drop Down List without Duplicates in Excel 365
  • 1. Creating a Proper Helper Column for Drop-Down List
  • 2. Dynamic Range and No Blanks