The best tool to process data and get good results is still Excel. But it is possible that at the time of processing that data you duplicate or repeat some. If this has happened to you, we recommend you read this article, where we explain how you can prevent the repetition of data in Excel , how to detect duplicate data and how to eliminate it.
Index( )
- What are the steps to prevent repetition of data in Excel?
- Validation criteria
- Application of the formula
- Error message
- How to highlight existing duplicate data?
- What method is used to eliminate duplications?
What are the steps to prevent repetition of data in Excel?
One of the Office programs is Excel, which allows you to easily analyze data treatment, tabulations and obtain a reliable record of results . With Excel you can count the cells that have text, but when working in an Excel spreadsheet, it may be the case that duplicate records are inserted, altering the information worked on the sheet.
Fortunately, when a record is entered in a table within the Excel sheet, this tool can prevent a duplicate record from being entered in the same table, that is, data that is already inserted. That is why we are going to show you the steps to prevent the repetition of data.
Validation criteria
The first step to follow is to validate the data because this will allow you to have control over what you should and can do in the Excel sheet. In addition, it will allow you to enter values other than those validated, showing a warning on the screen, if someone tries to do so. The validation criteria is very important because it establishes conditions to avoid repeating the data that you have already entered in the cells.
To proceed to validate the data in your cell, you must first select the table data to validate. For example, the data from table A. Next, on the toolbar, click on the Data tab and within it click on Data validation . Within this dialog box you must select a validation criterion by clicking on Customize.
Application of the formula
When working on a very large sheet within Excel, it is very convenient to avoid data duplication, because entering some data repeated will alter the result of all your work, which would result in a headache. That’s why Excel allows you to avoid all this complication.
After opening the Data Validation dialog box and selecting the customize criteria, the next step is to apply the formula. This formula is generated according to the data and table that you want to validate to apply the criteria. And it must be entered in the corresponding field within the dialog box, you proceed to enter a formula for example: =COUNT.IF(A:A,A1)=1 taking into account the values of that cell.
Subsequently, you must press OK to apply the configuration, finish the process and generate the Data Validation. As you can see, Excel allows you to insert formulas in a text box to perform an action.
Error message
After having carried out the data validation process, this makes it possible to generate an error message if you try to add a repeated data, that is, one that has already been entered into the table. Which is very advantageous, because it saves you the work of having to manually review each data, when applying the validation, the system will warn you with that error message.
In other words, if someone tries to enter any data again, the system will immediately detect the number of times that data appears, allowing it not to be repeated and a warning notification will be displayed. You can easily configure this error message in the same data validation window, and you write the message that you want Excel to show if you try to enter a duplicate and click OK to finish the whole process.
How to highlight existing duplicate data?
The repetition of data is frequent and especially when working on a large data sheet, any mistake made, such as entering repetitive data, can alter the result of the work. Therefore, in addition to validating the data of the cells or the entire spreadsheet, you can also have Excel highlight existing duplicate data for you.
A good option because that way, you can easily identify that data that is repeated on the sheet and be able to correct it. The Excel tool has functions that allow the cells where these duplicate data appear to be highlighted in color. To apply this process you must choose the rows or columns where you want to highlight the duplicate data.
Once you have chosen the cells, click on the Conditional Format option located on the Excel toolbar. A menu with several options will be displayed, click on Rules to highlight cells and then select the Duplicate values option.
This action will highlight duplicate data in the selected cells and fields, displaying the cells in one color. In the Duplicate values box, you can choose the format of the duplicate data and the color to highlight it.
What method is used to eliminate duplications?
Once you have located the cells where you have duplicate data, you must proceed to eliminate it so that your work is not hindered. The method used to eliminate duplication is the following: using the Remove Duplicates option , it is a very easy method to apply because it will make it possible for the repeated data that you have in your Excel table to be permanently eliminated.
You must choose the cells with the duplicate values or data that you want to remove. Then, click on Data and then click on the Remove duplicates option. Just below the Column option, proceed to Deactivate the column where the repeated data appears , click OK.
Before performing this process, it is recommended to make another file in another spreadsheet by copying the original data, so that you do not lose the information if you accidentally delete the wrong data. So you can remove duplicate data in Excel.
