The process of sifting through large amounts of data can be a time consuming one, not to mention there would be times where subtle errors are made without being discovered until the entire data recording procedure was completed. One of the most common errors that are made in the process of data inputting would be duplicate entries, and fortunately enough, Microsoft Excel comes built in with little functions that enable it’s users to locate and fix errors quickly. Here’s how to stop duplicate entries with a nifty trick known as Excel Data Validation:
- Assess information
Excel data validation is a function that is best performed when there is a data range present. Assess the data that have been inputted in the Excel Workbook. Can they be further organised or classified? Where would the Excel data validation function take effect? One thing to note about Excel data validation is that the function will send information to Excel in such a way that it prompts the user with messages when data identical to previous entries are detected, so that data input will remain completely unique.
- Data validation
Select the cells to use the Excel data validation function on, and locate the function under the Data tab. Choose validation or make use of ALT+D+L on keyboard to open up the dialog box for data validation. The dialog box enables users to make changes to the validation criteria, set up error alerts, customise an input message as well as apply the changes to all other cells. These settings and the available criteria provided in the dialog box are sufficient to keep track of data. However, in order to prevent duplicate entries, users will have to use a formula.
- Customise
Using the ‘Validation Criteria’ tab, locate the custom option under the allow section. Locate the formula section and make use of the COUNTIF formula so that Excel knows to detect identical data and warn the user. The COUNTIF formula should look like this: =COUNTIF( B:B , B1)=1, depending on the column and rows the changes are applied to. Upon completion, an input message can be made to remind users, but that particular step can be forgone since the essential focus would be the error alert.
- Error Alert
Under the error alert tab, users can select the style of the error alert, rename it’s title as well as create an error message. Be sure to label the title clearly so that it could be understood with no chance of misinterpretation. One of the most recommended error styles to choose would be “Stop”. Once all the amendments have been made, click on Okay to apply the Excel data validation function to the relevant sections.
- Test
In order to ascertain that the excel data validation function has been applied accurately, a test is recommended before continuing with the project. Try out entering new data into another cell and take note of whether the data is accepted without any error, this should be the case since this data input does not previously exist anywhere in the selected range. Try entering one of the former data inputs, this should trigger the error message that was set for duplicate data entries since this data has already existed in the range.
If successfully executed, this should be the expected result. Click retry or cancel to remove the error message and continue on with your excel tasks! While the formula section requires a bit of understanding and experimentation, the excel data validation function is the best feature to use when it comes to making sure all data input is unique. With consistent practice, this could become second nature and save the user from having to manually sift through tons of data for error, thereby allowing users to create more accurate reports from the data compilation.
