Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • Count rows that contain specific values in Excel
  • Two-way lookup with VLOOKUP in Excel
  • How to get address of named range in Excel
  • How to calculate two-way lookup VLOOKUP in Excel Table
  • How to use Excel OFFSET function

Data Analysis Examples

  • Conflicting Multiple Conditional Formatting Rules in Excel
  • Excel Bar Chart
  • What-If Analysis: Scenarios and Goal Seek in Excel
  • Excel Pie Chart
  • How to combine 2 or more chart types in a single chart in Excel

Data Validation Examples

  • Prevent invalid data entering in specific cells
  • Excel Data validation allow uppercase only
  • Excel Data validation number multiple 100
  • Excel Data validation specific characters only
  • Excel Data validation must contain specific text

Prevent Duplicate Entries in Excel

by

This example teaches you how to use data validation to prevent users from entering duplicate values.

1. Select the range A2:A20.

2. On the Data tab, in the Data Tools group, click Data Validation.

3. In the Allow list, click Custom.

4. In the Formula box, enter the formula shown below and click OK.

Explanation: The COUNTIF function takes two arguments. =COUNTIF($A$2:$A$20,A2) counts the number of values in the range A2:A20 that are equal to the value in cell A2. This value may only occur once (=1) since we don’t want duplicate entries. Because we selected the range A2:A20 before we clicked on Data Validation, Excelautomatically copies the formula to the other cells. Notice how we created an absolute reference ($A$2:$A$20) to fix this reference.

5. To check this, select cell A3 and click Data Validation.

As you can see, this function counts the number of values in the range A2:A20 that are equal to the value in cell A3. Again, this value may only occur once (=1) since we don’t want duplicate entries.

6. Enter a duplicate invoice number.

Result. Excel shows an error alert. You’ve already entered that invoice number.

 

Note: to enter an input message and error alert message, go to the Input Message and Error Alert tab.

Post navigation

Previous Post:

Prevent invalid Product Codes or ID Numbers in Excel

Next Post:

Reject Invalid Dates in Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others
  • REPLACE, REPLACEB functions: Description, Usage, Syntax, Examples and Explanation
  • How to extract nth word from text string in excel
  • Join Text Strings Using Concatenate or ‘& Operator’ in Excel
  • Clean and reformat telephone numbers using SUBSTITUTE function in Excel
  • How to count specific characters in a range in Excel
  • DATEVALUE function: Description, Usage, Syntax, Examples and Explanation
  • How to Calculate Age in Excel
  • Series of dates by day
  • NETWORKDAYS function: Description, Usage, Syntax, Examples and Explanation
  • How to calculate next scheduled event in Excel
  • PV function: Description, Usage, Syntax, Examples and Explanation
  • How to calculate annual compound interest schedule in Excel
  • Calculate payment periods for loan in Excel
  • SYD function: Description, Usage, Syntax, Examples and Explanation
  • COUPDAYBS function: Description, Usage, Syntax, Examples and Explanation
Acronyms, Abbreviations, Initialism & What They Stand For
© 2021 xlsoffice. All Rights Reserved | Teal Smiles