Siegel, Here Are Your Articles for Wednesday, November 10, 2021
Is this email not displaying correctly?
View it in your browser .
Our Website Contact Us About Us Services Tutorials Acumatica
Share Save

Preventing Duplicate Entries in Excel Spreadsheets


It's often difficult to police a user's actions within an Excel spreadsheet, unless you're aware of Data Validation. This feature is tucked away on Excel's Data menu, and allows you to create a variety of input rules. This article will demonstrate how to use Excel's COUNTIF function to trigger an error prompt if a user attempts to enter the same value in a specific area of a spreadsheet.

As shown in the figure:

  1. Select the range of cells that you wish to prevent duplicates within.
  2. Choose Data Validation from the Data menu.
  3. Choose Custom from the Allow list within the Data Validation dialog box.
  4. Enter the following formula:


Change $A$2:$A$40 to match your desired range of cells. Make sure to include the dollar signs around the range of cells, while A2 should be the address of the first cell in the range, but without dollar signs.

  1. Activate the Input Message tab of the Data Validation dialog box.
  2. Provide a title for the prompt that you wish to appear when the user clicks on a cell that you're restricting.
  3. Provide an input message to document the restriction.
  4. Activate the Error Alert tab of the Data Validation dialog box.
  5. Provide a title for the error prompt that should appear if the user enters a duplicate value.
  6. Provide an error message that should appear if the user enters a duplicate value.
  7. Click OK to close the Data Validation dialog box.
  8. Test your work by entering a duplicate value.

To remove Data Validation, click on any cell that has a restriction in place, and then choose Data Validation from the Data menu. Click the Apply These Changes To All Other Cells With the Same Settings checkbox, and then click the Clear All button.


Share Save

Your Comments

Siegel Solutions Inc
Siegel Solutions Inc
(781) 487-7000
144 Gould Street Suite 205
Needham, MA 02494
Friend Me on Facebook
Follow Me on Twitter
Connect with me on LinkedIn
Saved Articles
Comments and Feedback
Refer A Friend
Your Privacy
Our firm provides the information in this e-newsletter for general guidance only, and does not constitute the provision of legal advice, tax advice, accounting services, investment advice, or professional consulting of any kind. The information provided herein should not be used as a substitute for consultation with professional tax, accounting, legal, or other competent advisers. Before making any decision or taking any action, you should consult a professional adviser who has been provided with all pertinent facts relevant to your particular situation. Tax articles in this e-newsletter are not intended to be used, and cannot be used by any taxpayer, for the purpose of avoiding accuracy-related penalties that may be imposed on the taxpayer. The information is provided "as is," with no assurance or guarantee of completeness, accuracy, or timeliness of the information, and without warranty of any kind, express or implied, including but not limited to warranties of performance, merchantability, and fitness for a particular purpose.
Powered by
Copyright © IndustryNewsletters All rights reserved.

This email was sent to:

Mailing address: 144 Gould Street Suite 205, Needham, MA 02494