Microsoft Office Tutorials and References
In Depth Information
Duplicate values are determined by the value displayed in the cell — not necessarily the
value stored in the cell. For example, assume that two cells contain the same date. One
of the dates is formatted to display as 5/15/2013, and the other is formatted to display
as May 15, 2013. When removing duplicates, Excel considers these dates to be differ-
ent. Similarly, values that are formatted differently are considered to be different. So
$1,209.32 is not the same as 1209.32. Therefore, you might want to apply formatting to
entire columns to ensure that duplicate rows are not overlooked just because of a
Identifying duplicate rows
If you would like to identify duplicate rows so you can examine them without automatically deleting them,
here's another method. Unlike the technique described in the previous section, this method looks at actual val-
ues, not formatted value.
Create a formula to the right of your data that concatenates each of the cells to the left. The following formulas
assume that the data is in columns A:F.
Enter this formula in cell G2:
Add another formula in cell H2. This formula displays the number of times that a value in column G occurs.
Copy these formulas down the column for each row of your data.
Column H displays the number of occurrences of that row. Unduplicated rows will display 1. Duplicated rows
will display a number that corresponds to the number of times that row appears.
Figure 16-6 shows a simple example. If you don't care about a particular column, just omit it from the formula
in column G. For example, if you want to find duplicates regardless of the Status column, just omit D2 from the