When we talk about records, statistics, formulas, spreadsheets, etc., the main program for this task is Microsoft Excel, and even knowing how to use it is a requirement to get a job in some places.
You can download Excel from its official website or buy the full Microsoft Office package somewhere.Let’s talk today Duplicate data in records and/or lists The information we carry in our spreadsheets, how to search for, verify and avoid them.
How to verify that data is actually duplicate in Excel?
Whether it’s because we’re tracking our accounts, or because we’re transcribing a list with a range of values, there’s bound to be some duplication of data, either erroneous or intentional.For whatever reason, if we want to find these equal data If it’s a short list, we can do it manually, but if it’s a long list, it’s better to use one of the Excel functions that can help us with this task.
- Open the relevant file for which you want to check for duplicate data.
- Select all cells, there are two ways to do this.
- If they are specific cells, Ctrl+click each cell to select it.
- Conversely, if it’s a large group, Shift-click the first and last boxes, which will select all boxes in that range.
- Then look for the Conditional Formatting option in the Home tab.
- A submenu will appear, click «Highlight Cell Rules» or «Highlight Cell Rules», then click «Duplicate Values».
- This will open a dialog where you will format (or highlight) all cells with duplicate values.
- Click OK after selecting the format type and Excel will apply it to all cells with duplicate values.
This method is very useful when it comes to the very long lists we mentioned so that we can avoid duplicate records in the Excel spreadsheet.
What formula should we use to validate our data?
Another excellent formula for us to keep good data records and check for duplicates is Use the formula =COUNT.IF, where, specifying a range and some criteria, Excel will show us the number of cells with the criteria we specified. Here is a short guide to its use.
=COUNTIF Usage Guidelines
When you have an Excel file open, Go to the function bar, above your spreadsheet. You must write the following: =COUNT.IF(range, condition), in the range section you must write from which cell you want the function to apply to, and in the condition, write the condition you want Excel to take Account: A value, a color, a word, or a specific letter.
It should be noted that the program does not distinguish between upper and lower case letters in «Criteria», press «Enter» and it will automatically display the number of cells in the range you said that meet that criteria.
Another way is to do Click on the «fx» symbol There you write «COUNT.IF», then when you accept, Excel will open a dialog where you also have to enter the range and criteria, but not manually because by selecting the corresponding cell, the program will automatically detect it . This way you can count cells with unique values in duplicates.
What are the options in Excel that allow me to modify, delete and clear records?
When we say Microsoft Excel is a very powerful program, we’re saying it as truthfully as possible, it’s not a simple track and list program. Well, there are tons of things that can be done, and the same program can also help us automate those things, like removing or deduplicating data in Excel.
In addition to this, there can be an option called VBA Excel, which comes from the release of the first version of the program, and as the program itself is updated and improved The Microsoft Office suite has been modernized. This option is great for making records and tables within the program, in addition to using the other methods we’ve mentioned in this article to control duplicate data.
but most importantly Excel VBA function It will allow you to have full control over all the data you have in the form/registration, either manually or automatically by creating a specific button that you can modify at any time. Of course, implementing this option requires knowing some code, as VBA Excel can use it.