Images loading below. While you wait, can you answer this Excel related tip? Guess the answer then click SEE ANSWER to see how it can be done.

1. What is the shortcut to ONLY select the visible cells? Guess, then click 'See Answer' as it can be done.
2. How do you take all the formatting from chart (see 1 below) and PASTE it onto another chart (2)? Guess and then see how it can be done with 'See Answer'



Remove duplicates NOT WORKING if number formats different

Be careful when using the Remove Duplicates tool in Excel. Remove duplicates DOES NOT work if number formats are different. It is very literal in that even if the format is different it will assume it is not a duplicate. So the numbers 10 and 10.00 will be seen as unique and not deleted. However, conditional formatting DOES see the correct items as duplicates (ignores the formatting).

YouTube Remove duplicate not working in Excel

When not finding duplicates is likely to be a problem

This shouldn’t be a problem if the data is from a system as they tend to be consistent in how they format each column. However, if it is based on human entry or a copy/ paste from another system/ source, it may well NOT delete all the true duplicates.

Remove duplicates NOT seeing duplicates

As shown below (1) is what we effectively want the Remove duplicates tool to see, however it may look like (2) or (3), especially if a human has been involved or data from different sources are put together. Normally Excel ignores formatting and looks at the truth of the number, but the Remove Duplicates tool does not, and can appear as if it is not working.

Remove duplicate NOT WORKING

If we run the Remove Duplicates tool on the 3 blocks, this is what is left behind. Notice that the only difference in (2) and (3) is the way some of the numbers are formatted.

Does Conditional Formatting duplicates have the same problem?

Simple answer is NO. Below, using Conditional Formatting Duplicate values, in (2) and (3) you will see that all 4 cells are considered duplicates even though formatting is different.

Solution- Make all the formats the same

If you use Remove Duplicates, perhaps consider formatting the first row the way you want it and then using the format painter to make all of them the same. Remove Duplicates is more likely to get it correct if you do this.

Go to the next duplicate in Excel

Excel not recognizing duplicates

Excel formula to Rank list with duplicates

Numbers seen as text in Excel

Data scrubbing using Excel course