When we talk about records, statistics, formulas, spreadsheets and more, the main program for this task is Microsoft Excel, even knowing how to use it is a requirement to get jobs in some places.
You can download Excel from its official site or by purchasing the complete Microsoft Office package somewhere. Today we will be talking to you about duplicate data in the records and/or lists that we carry in our spreadsheet, how to find them, validate them and avoid them.
Index( )
- How is it validated in Excel that a data is actually duplicated?
- What formula should we use to verify our data?
- Guide to using =COUNTIF
- What is the option in Excel that allows me to modify, delete and clean records?
How is it validated in Excel that a data is actually duplicated?
Whether it’s because we’re keeping track of our accounts or because we’re transcribing a list with a series of values in it, there’s bound to be some duplicate data either by mistake or on purpose. Regardless of the reason, if we want to search for these same data we can do it manually if it is a short list, but if it is a long one, it is best to use one of the Excel functions that will help us with this.
- Open the file in question where you want to check for duplicate data.
- Select all cells, there are two ways you can do this.
- If they are specific cells hold down Ctrl and click on each of them to select it.
- Instead, if it’s a large group, hold Shift click the first and last box, this will select all the ones within that range.
- Then in the “Home” tab look for the “Conditional Formatting” option.
- A submenu will be displayed, click on “Highlight cell rules” or “Rules to highlight cells” and then on “Duplicate Values”.
- This will open a dialog where you will format (or highlight) all cells that have duplicate values.
- Click on “OK” after you have chosen the format type, Excel will apply this to all those cells that have duplicate values.
This method is quite useful when it comes to an extremely long list as we mentioned and in this way we can avoid duplicate records in our Excel spreadsheet.
What formula should we use to verify our data?
Another excellent formula that is convenient for us to keep a good record of our data and check for duplicate data is with the formula =COUNT.IF , which, specifying a range and certain criteria, Excel will show us the number of cells with that criteria that we specify. Here is a short guide to its use.
Guide to using =COUNTIF
When you already have your Excel file open, go to the functions bar, above your spreadsheet. You must write the following: =COUNT.IF(range, criteria), in the range section you must write from which cell to which cell you want the function to be applied and in criteria, write the condition that you want Excel to take into account: a value, a color, a word or a specific letter.
It should be noted that the program does not differentiate between uppercase or lowercase letters in “Criteria”, press “Enter” and it will automatically show you the number of cells within the range that you said that meet that criteria.
Another way to do it is by clicking on the “fx” symbol and there you write “COUNT.IF” and then on accept, Excel will open a dialog box in which you must also enter the Range and the Criterion, but not manually because by selecting the corresponding cells, the program will detect it automatically. This way you can count the cells with unique values among the duplicates .
What is the option in Excel that allows me to modify, delete and clean records?
When we say that Microsoft Excel is a very powerful program, we say it with all possible truthfulness, it is not a program simply to keep track and make a list. Well, in it it is possible to carry out a large number of actions and that, in addition, the same program helps us to do them automatically, such as removing or eliminating duplicate data in Excel .
In addition to this, it is also possible to have an option called VBA Excel, which has come from the release of the first version of the program and has been updated and improved as the Microsoft Office package itself has been modernized. This option is perfect for making records and forms within the program, in addition to using the others that we have mentioned in this article to control duplicate data.
But the most important thing about this is that the Excel VBA function will allow you full control of all the data you have in your form/registration, either manually or even by creating a specific button to execute it automatically and which, You can always modify it whenever you want. Of course, to implement this option, it is necessary to know some code, since VBA Excel works with this.
