How to Prevent Duplicate Entries in Columns in Excel

This post will guide you how to prevent duplicate entries in one or multiple columns in Excel. How do I use Data Validation feature to prevent duplicate values in columns in Excel.

Prevent Duplicate Values


If you want to prevent duplicate values when entering data into your cells or columns in Excel, you can use the Excel’s Data Validation feature to reject duplicate entries.

Assuming that you want to prevent duplicate values in range B1:B4 when entering values into cells, you just do the following steps:

#1 select the range of cells in which you want to prevent duplicate values. Such as: B1:B4

prevent duplicate entries1

#2 go to DATA tab, click Data Validation command under Data Tools group. And select Data Validation menu from the drop down menu list. And the Data Validation dialog will open.

prevent duplicate entries2

#3 click the Settings tab in the Data Validation dialog, choose Custom from the Allow list box, and then enter the following formula into the formula box. And click Ok button.

=COUNTIF($B$1:$B$4,B1)=1

prevent duplicate entries3

 

#4 click the Error Alert tab, enter the text “Duplicate Value” in the Title text box. And enter a meaning full description in the Error message text box. Such as: “The value is a duplicate value, and it is not valid”. Click Ok button.

prevent duplicate entries4

#5 you can enter a duplicate value in range B1:B4, the value will be rejected. And when it happens, just click Cancel button to clear the error message and re-enter a valid value.

prevent duplicate entries5

prevent duplicate entries6

How to Limit Data Entry in a Cell in Excel

This post will guide you how to limit cell entries to specific length and number of digits in Excel. How do I limit data entry to allow only text or only numbers entered into in a cell using Data Validation option in Excel. How to limit entries to numeric values in Excel.

Assuming that you want to limit data entry for a range of cells in your worksheet, such as: only allow to enter text values or numeric values in the selected range of cells. How to do it. You can refer to the following introduction.

Limit Data Entry to Allow Only Text


If you want to only allow entering Text values in the selected range of cells, you can use the Data Validation feature to achieve it. Just do the following steps:

#1 select the range of cells that you want to limit data entry.

limit data entry in cell1

#2 go to DATA tab, click Data Validation command under Data Tools group. And the Data Validation dialog will open.

limit data entry in cell2

#3 click Settings tab, and select Custom from the Allow drop down list box. And then type the following formula based on the ISTEXT function in Formula Text box. And click Ok button.

=ISTEXT(A1)

 limit data entry in cell3

This formula will check if the cell value is a text value or not, if TRUE, it returns TRUE, otherwise, returns FALSE, and the Data Validation rules will be triggered.

Note: A1 is the first cell in your selected range.

limit data entry in cell4

Limit Data Entry to Allow Only Numeric Values


If you want to only allow entering numeric values in the selected range of cells, you can use the Data validation feature in combination with the ISNUMBER function to achieve the result. Do the following steps:

#1 select the range of cells that you want to limit data entry.

limit data entry in cell1

#2 go to DATA tab, click Data Validation command under Data Tools group. And the Data Validation dialog will open.

limit data entry in cell2

#3 click Settings tab, and select Custom from the Allow drop down list box. And then type the following formula based on the ISNUMBER function in Formula Text box. And click Ok button.

=ISNUMBER(A1)

limit data entry in cell5

The ISNUMBER function returns TRUE when cell value is a number and returns FALSE if not.

Note: A1 is the first cell in your selected range.

limit data entry in cell6

Limit Maximum Cell Value


If you want to limit data entries are not greater than or euqal to a given value (200) in a range, just do the following steps:

#1 Select the range of cells that you want to limit maximum cell value.

limit data entry in cell1

#2 go to DATA tab, click Data Validation command under Data Tools group. And the Data Validation dialog will open.

limit data entry in cell2

#3 click Settings tab in the opening Data Validation dialog, select Whole number in the allow drop down list box. And select less or equal to from the Data drop down list box. And then enter a numeric value 200 into text box of Maximum. Click OK button.

limit data entry in cell7

limit data entry in cell8

Video: Limit Data Entry

Related Functions


  • Excel ISNUMBER function
    The Excel ISNUMBER function returns TRUE if the value in a cell is a numeric value, otherwise it will return FALSE.The syntax of the ISNUMBER function is as below:= ISNUMBER (value)…
  • Excel ISTEXT function
    The Excel ISTEXT function used to check if a value is text. If so, returns TRUE; if the text is not text, the function will return FALSE.The syntax of the ISTEXT function is as below:=ISTEXT(value)…

Create Dynamic Drop Down List

This post will guide you how to create a dynamic drop down list in Excel. How do I quickly create a dynamic drop down list in Excel.

Create Dynamic Drop Down List


Assuming that you want to create two drop down list in your current worksheet, and when choose a value in one drop down list, and the values in another drop down list will be updated automatically. How to achieve it. Let’s see the below detailed steps.

#1 select the range of cells A1:B1, go to FORMULAS tab, click Define Name command under Defined Names group. The New Name dialog will open.

create dynamic drop down list1

#2 type one Name in the Name text box. Then click OK button.

#3 select the range of cells A1:A4 to create another defined name.

create dynamic drop down list3

#4 select the range of cells B1:B4 to create one more defined name.

create dynamic drop down list4

#5 create the first drop down list, go to DATA tab, Click Data Validation command under Data Tools group. And select Data Validation item from the pop up menu list. The Data Validation dialog will open.

create dynamic drop down list5

#6 click Settings tab, select List from the Allow drop down list box. And type the formula =Names in the source text box. Click Ok button.

create dynamic drop down list6

#7 the first drop-down list has been created in Cell C1.

create dynamic drop down list7

#8 click Cell D1 to select this cell, go to DATA tab, Click Data Validation command under Data Tools group to create the second drop down list. Click Settings tab, select List from the Allow drop down list box. And type the formula =indirect(C1) in the source text box. Click Ok button.

create dynamic drop down list8

#9 choose one value from the first drop down list, and you will see that the values in the second drop down list will be updated.

create dynamic drop down list9

Data Validation for Specified Text only

If you want to check if the values that contain a specified text string in one cell, you can use a combination of the FIND function and ISNUMBER function as a formula in the Data Validation.

Data Validation for Specified Text only

​Suppose that you want to check that if the values in column B contain the specified text string “@gmail.com”, IF True, then return TRUE, otherwise, it returns FALSE.

You can use the following formula:

=ISNUMBER(FIND("@gmail.com",B1))

Data Validation for Specified Text only1

In the above formula, the FIND function will locate the position of the searched text string “@gmail.com” in Cell B1. If the text string is searched, then return the starting position of the searched text string in cell B1. If not, it will return the Excel #VALUE! Error.

The returned results of the FIND function will be as the first argument of the ISNUMBER function. So if the FIND function returns the correct numeric position, then the ISNUMBER function returns TRUE. Otherwise, returns FALSE.

Last, you can add the above formula into Data Validation as rules to B1:B3.

Data Validation for Specified Text only1

Data Validation for Specified Text only1

When you adds or change the values in range B1:B3, the DATA validation will be triggered.

Data Validation for Specified Text only1


Related Functions

  • Excel Find function
    The Excel FIND function returns the position of the first text string (substring) from the first character of the second text string.The FIND function is a build-in function in Microsoft Excel and it is categorized as a Text Function.The syntax of the FIND function is as below:= FIND  (find_text, within_text,[start_num])…
  • Excel ISNUMBER function
    The Excel ISNUMBER function returns TRUE if the value in a cell is a numeric value, otherwise it will return FALSE.The syntax of the ISNUMBER function is as below:= ISNUMBER (value)…

Excel Basics

 MS Excel Basics

This section will introduce the basics of Excel, such as: Excel Window, Ribbon, Workbook, Worksheet, Cells, Templates, Data Validation, print ..etc.

1. Excel window

when you start the excel application, excel window will appear and this section will let you learn the various important parts of excel window.

2. Excel Ribbon

The Ribbon is the strip of buttons and icons located above the work area. and it is organized into various tabs, such as: File, Home,Insert, Page Layout, Formulas, Data, Review, and View. while clicking on a tab, you will saw all of program’s features and commands. this section will guide you how to customize the Ribbon.

3. Excel Workbook

The workbook is a file that contains one or more worksheets , by default, the excel will create a blank workbook that contains 3 worksheets. this section will guide you how to create a new workbook or open an existing workbook or close a workbook.

4. Excel Worksheet

when you open an new workbook or start excel application, it will create 3 new blank worksheets. this setion will explain you how to create/insert/delete/move/copy/rename a  worksheet.

5. Excel Cells

A Cell can be used in a formula to hold differnt types of data like Numbers, Dates, Currency, Text..  this section will guide you how to format cells /set the cell type/create or change a cell reference…

6. Excel Rows

How do I insert a new row in excel? How do I delete a row in excel? How to delete multiple rows in Excel workbook? How to hide a row from being displayed in excel? How to unhide the hidden rows in excel? This section will guide you how to Insert/Delete/Hide/Unhide/Sizing columns in Microsoft Excel.

7. Excel Columns

How do I insert a new column in excel? How do I delete a column in excel? How to delete multiple columns in Excel workbook? How to hide a column from being displayed in excel? How to unhide the hidden columns in excel? This section will guide you how to Insert/Delete/Hide/Unhide/Sizing columns in Microsoft Excel.

8. Excel Ranges

Excel Ranges is a collection of cells (two or more cells). Thispost will guide you how to use excel ranges from the below 5 topics:

9. Excel Templates

Excel template is also a workbook, the only different is that it can be as a template or module to create other similar workbooks. there are many free excel templates in the internet or you can create your own excel template.

10. Excel  Data Validation

You can use Data validation to control what can be entered into a cell. such as: you can restricts the values allowed in a cell.

11. Excel Protect

How do I encrypt an excel file in excel 2013? How to encrypt excel workbook? How to encrypt an excel file with a password in Microsoft excel? This section will guide you how to encrypt excel file, protect workbook and protect worksheet in excel.

12. Excel print

How do I print a worksheet in excel? How do I print a workbook in Microsoft Excel 2013? How to quick print a excel work sheet? How to print a selection excel range? How to change the print setting to print the excel file base on your request. This section will explain you how to print a worksheet/ change the page scaling setting.