--- title: "Excel Tip: How to Prevent Data Duplication In an Excel Worksheet" description: In this tip we explain how you can prevent duplicate data from being captured in an Excel worksheet by using custom data validation. image: https://www.equationtech.us/hs-fs/file-460345912-png/images/1-15-2014_3-55-37_pm.png --- [866.436.3530](tel:+8664363530) | [** Customer Portal Login](https://na.myconnectwise.net/support/index.htm?Company=equationtech) [Speak With an Expert](https://www.equationtech.us/speak-with-an-expert) [![Equation Technologies - Sage Business Partner | San Diego](https://www.equationtech.us/hs-fs/hubfs/Redesign_Images/logo.png?width=230&name=logo.png "Equation Technologies - Sage Business Partner | San Diego")](https://www.equationtech.us/) - [Sage Services](https://www.equationtech.us/sage-services) - Products We Support - [Sage 300](https://www.equationtech.us/sage-services/sage-300-cloud) - [Sage Intacct](https://www.equationtech.us/sage-services/sage-intacct/) - [Sage CRM](https://www.equationtech.us/sage-services/sage-crm) - [Sage Payroll](https://www.equationtech.us/sage-services/sage-payroll) - [Sage HCM](https://www.equationtech.us/sage-services/sage-hcm-software) - [Sage FAS](https://www.equationtech.us/sage-services/fixed-assets) - [Sage Intelligence](https://www.equationtech.us/sage-services/sage-intelligence) - [Sage Enterprise Intelligence](https://www.equationtech.us/sage-services/sage-enterprise) - Services We Offer - [Software Selection](https://www.equationtech.us/software-solution-selection) - [Implementation](https://www.equationtech.us/sage-services/erp-implementation) - [Customization](https://www.equationtech.us/sage-services/customization) - [Support](https://www.equationtech.us/sage-services/support) - [Training](https://www.equationtech.us/sage-services/sage-training) - [Sage Cloud](https://www.equationtech.us/sage-services/hosting) - [Upgrades](https://www.equationtech.us/sage-services/upgrades) - [Extend Sage](https://www.equationtech.us/sage-integrations) - [Magento eCommerce](https://www.equationtech.us/sage-integrations#magento) - [ADP](https://www.equationtech.us/en/sage-300-sage-intacct-payroll-powered-by-adp) - [Aloha POS](https://www.equationtech.us/sage-integrations#aloha) - [Salesforce](https://www.equationtech.us/sage-integrations#salesforce) - [TimeClockPlus](https://www.equationtech.us/timeclockplus) - [Other Solutions](https://www.equationtech.us/other-solutions) - Industries - [Distribution](https://www.equationtech.us/distribution-management-software-solution) - [Education Nonprofit](https://www.equationtech.us/education-nonprofit-accounting-software) - [Financial Services](https://www.equationtech.us/financial-services-software) - [Franchise](https://www.equationtech.us/franchise-management-software) - [Healthcare](https://www.equationtech.us/healthcare-finance-software) - [Hospitality](https://www.equationtech.us/hotel-restaurant-accounting-software-solutions) - [Metal Processors & Distributors](https://www.equationtech.us/erp-for-metal-industry-businesses) - [Nonprofit](https://www.equationtech.us/nonprofit-accounting-software) - [Professional Services](https://www.equationtech.us/professional-services-software) - [SaaS & Technology](https://www.equationtech.us/erp-software-for-technology) - [Resources](https://www.equationtech.us/sage-software-resource-library) - [Technology Blog](https://www.equationtech.us/technology-blog) - [Training](https://www.equationtech.us/sage-services/sage-training) - [Online Videos](https://www.equationtech.us/sage-300-erp-accpac-tutorial) - [Case Studies](https://www.equationtech.us/case-studies) - [About](https://www.equationtech.us/about-us) - [Meet The Team](https://www.equationtech.us/management-team) - [Partners](https://www.equationtech.us/partners) - [Careers](https://www.equationtech.us/careers) - [Contact](https://www.equationtech.us/contact) This is a search field with an auto-suggest feature attached. - There are no suggestions because the search field is empty. # Excel Tip: How to Prevent Data Duplication In an Excel Worksheet ![technology blog](https://www.equationtech.us/hs-fs/file-460345912-png/images/1-15-2014_3-55-37_pm.png) **twitter **linkedin **facebook Data duplication in Microsoft® Excel® can lead to a host of irregularities; so it is a good idea to prevent it. In this tip we explain how you can prevent duplicate data from being captured in an Excel worksheet by using custom data validation The COUNTIF function will be added to data validation the option to prevent duplicate data entries. A key benefit of using this method is that it prevents data duplication before it is captured. Other methods will only allow you to highlight and remove duplicate values after your data has been captured. Example exercise – How to prevent the entry of duplicate account codes. Note: Download the[ sample workbook ](http://www.sageintelligence.com/Email%20Resources/Images/Preventing%20Duplicates%20Using%20Custom%20Data%20Validation.xlsx)to practice this exercise. Applies to: Microsoft® Excel® 2007, 2010 and 2013 The screen shot below is used in this example: ![1 15 2014 3 55 37 PM](https://www.equationtech.us/hs-fs/file-460345912-png/images/1-15-2014_3-55-37_pm.png)   1. Select cells A3:A15 2. Select **[Data Validation](http://blog.sageintelligence.com/excel-tips-tricks/2011/06/data-validation/)** as per the screen shot below. ![1 15 2014 4 04 07 PM](https://www.equationtech.us/hs-fs/file-457280513-png/images/1-15-2014_4-04-07_pm.png)   3. Enter the data in the same format as shown below. ![1 15 2014 4 05 06 PM](https://www.equationtech.us/hs-fs/file-458711111-png/images/1-15-2014_4-05-06_pm.png)   4. To add an input message select the **Input Message** tab and enter the appropriate message. ![1 15 2014 4 05 54 PM](https://www.equationtech.us/hs-fs/file-460342617-png/images/1-15-2014_4-05-54_pm.png)   5. To add a custom error message, select the **Error Alert** tab and enter the appropriate message. ![1 15 2014 4 06 35 PM](https://www.equationtech.us/hs-fs/file-458711411-png/images/1-15-2014_4-06-35_pm.png) 6. Click **OK** 7. Select cell **A3** and enter the account code “ACC001”. 8. Select cell **A4** and re-enter the same account code “ACC001”. 9. The **custom error message**, entered in point 5, will be displayed. 10. Select **Retry** and enter the account code “ACC002”. 11. The value will be accepted as shown below.   ![as](https://www.equationtech.us/hs-fs/file-457281468-png/images/as.png)     **Now get out there and brag about your new fancy Excel skills!**   **For more Excel Tips,[ subscibe to the Equation Technolioges Blog](https://www.equationtech.us/sage-accpac-blog/)!**   ## Need Help Now? [![Request a Call](https://hubspot-no-cache-na2-prod.s3.amazonaws.com/cta/default/41683/0d27fa16-1614-41b4-9caf-f9597d262c41.png)](https://hubspot-cta-redirect-na2-prod.s3.amazonaws.com/cta/redirect/41683/0d27fa16-1614-41b4-9caf-f9597d262c41) Products We Support - [Sage 300](https://www.equationtech.us/sage-services/sage-300) - [Sage Intacct](https://www.equationtech.us/sage-services/sage-intacct/) - [Sage CRM](https://www.equationtech.us/sage-services/sage-crm) - [Sage HRMS](https://www.equationtech.us/sage-services/sage-hrms) - [Sage FAS](https://www.equationtech.us/sage-services/fixed-assets) - [Sage Intelligence](https://www.equationtech.us/sage-services/sage-intelligence) - [Sage Enterprise Intelligence](https://www.equationtech.us/sage-services/sage-enterprise) - [Sage Financials](https://www.equationtech.us/sage-services/sage-live) Services We Offer - [Software Selection & Licensing](https://www.equationtech.us/sage-services/erp-software-selection) - [Implementation](https://www.equationtech.us/sage-services/erp-implementation) - [Customization](https://www.equationtech.us/sage-services/customization) - [Support](https://www.equationtech.us/sage-services/support) - [Training](https://www.equationtech.us/sage-services/sage-training) - [Hosting](https://www.equationtech.us/sage-services/hosting) - [Upgrades](https://www.equationtech.us/sage-services/upgrades) Resources - [Technology Blog](https://www.equationtech.us/technology-blog) - [Training](https://www.equationtech.us/sage-services/sage-training) - [Sage Software Video Tutorials](https://www.equationtech.us/sage-300-erp-accpac-tutorial) - [Case Studies](https://www.equationtech.us/case-studies) About - [Meet The Team](https://www.equationtech.us/management-team) - [Certifications & Partners](https://www.equationtech.us/partners) - [Careers](https://www.equationtech.us/careers) - [Contact](https://www.equationtech.us/contact) - [San Diego Location](https://www.equationtech.us/sage-300-support-san-diego) - [Orange County Location](https://www.equationtech.us/sage-300-support-orange-county) - [Seattle Location](https://www.equationtech.us/sage-300-support-seattle) [![Equation Technologies - Sage 300 Authorized Partner](https://www.equationtech.us/hs-fs/hubfs/Redesign_Images/logo-footer.png?width=220&name=logo-footer.png "Equation Technologies - Sage 300 Authorized Partner")](https://www.equationtech.us/) **Equation Technologies** United States: 533 2nd Street Encinitas, CA 92024 Canada: #301 - 220 Brew Street Port Moody, BC V3H 0H6 Phone: 866.436.3530  •  E-mail: [info@equationtech.us](mailto:info@equationtech.us) Equation Technologies ©2016 [Contact Us](https://www.equationtech.us/contact)  | [Privacy Policy](https://www.equationtech.us/privacy-policy) ```json { "@context" : "http://schema.org", "@type" : "Article", "author" : { "@type" : "Person", "name" : "EQ Tech" }, "dateModified" : "Fri, Feb 27, 2015", "datePublished" : "Fri, Feb 27, 2015", "description" : "Sage 300 (formerly Sage Accpac) blog with informative articles in reference to financial software processes and procedures including payroll taxes, training, product information, custom development.", "headline" : "Excel Tip: How to Prevent Data Duplication In an Excel Worksheet", "image" : { "@type" : "ImageObject", "height" : 800, "url" : "//cdn2.hubspot.net/hub/41683/file-460345912-png/images/1-15-2014_3-55-37_pm.png", "width" : 800 }, "mainEntityOfPage" : { "@id" : "https://www.equationtech.us/sage-accpac-blog/bid/103739/excel-tip-how-to-prevent-data-duplication-in-an-excel-worksheet", "@type" : "WebPage" }, "publisher" : { "@type" : "Organization", "logo" : { "@type" : "ImageObject", "height" : 60, "url" : "https://www.equationtech.us/hs-fs/hubfs/Redesign_Images/logo.png?t=1474998725496&width=230&name=logo.png", "width" : 600 }, "name" : "Equation Technologies Inc." } } ```