How to avoid duplicate Excel files?

Published on : 28 October 20213 min reading time
Excel is one of the most powerful tools for managing and editing data. However, like all software, it can also display errors. Among the most frequent ones are duplicates. These are cells or ranges that are automatically displayed in duplicate in an Excel file without having been made in the first place. Fortunately, it is possible to remove or avoid them by following a few steps…

Step 1: Identifying duplicates

Duplicates can become a real problem when you are working on large files. They could distort the real value of the data, as some calculations or cells are displayed in duplicate. 

It is therefore important to remove them as soon as possible. But before doing so, it is necessary to check them and determine whether they are really duplicates or just identical data, which you have created. To do this, you can proceed with these steps:

First, you will select the data you wish to analyse by right-clicking on a cell and stretching the small rectangle that appears on it. Next, you will click on “Conditional Formatting”, found under the “Home” section of the Excel toolbar. 

Choose ‘Cell highlighting rules’ and then ‘Duplicate values’; cells with duplicate values will then be highlighted. You can then decide whether you want to delete them or not. If you are having difficulties, you can also turn to professionals like goaland.com. To visit their website, you can click on the following link: www.goaland.com.

Step 2: Deleting duplicates

Now it is time to delete the duplicates. To do this, you first have to select the range you wish to analyse. Next, click on the “Data” tab on your spreadsheet toolbar. When you get to the section, click on the “data tools” option, then on “delete duplicates”. The duplicates will disappear immediately. If there are no duplicates, Excel will also notify you.

Step 3: Avoiding creating duplicates

Are you tired of deleting duplicates in Excel all the time? Then you can use this method to avoid their creation in the first place.

The first thing you have to do is to select the column in which you want to prevent duplicates. In our case, we will take A1:A7.  Then go to Data/Data tools/Data validation. Click on “option” and choose “customise” in “allow”. Add a formula as follows =NB.SI(A:A:A1)=1 . The formula will automatically be applied to the other cells (A2, A3, etc.). Press OK to confirm the change. Whenever a duplicate appears, Excel henceforth issues an error notification. Note that you can even customise your alert message.

Plan du site