Excel 2010 Bible

Chapter 5: Introducing Tables

109

Cross-Reference
For more information about formulas, including the use of formulas in a table column, see Chapter 10. n

Removing duplicate rows from a table .....................................................................

If data in a table was obtained from multiple sources, the table may contain duplicate items. Most

of the time, you want to eliminate the duplicates. In the past, removing duplicate data was essen-

tially a manual task, but it’s very easy if the data is in a table.

Start by selecting any cell in your table. Then choose Table Tools ➪ Design ➪ Tools ➪ Remove

Duplicates. Excel responds with Remove Duplicates dialog box shown in Figure 5.8. The dialog

box lists all the columns in your table. Place a check mark next to the columns that you want to be

included in the duplicate search. Most of the time, you’ll want to select all the columns, which is

the default. Click OK, and Excel weeds out the duplicate rows and displays a message that tells

you how many duplicates it removed.

When you select all columns in the Remove Duplicates dialog box, Excel will delete a row only if

the content of every column is duplicated. In some situations, you may not care about matching

some columns, so you would deselect those columns in the Remove Duplicates dialog box. In the

example shown in Figure 5.8, removing the check mark from all columns except Agent would

result in a table that showed one row per agent — an unduplicated list of all agents.

FIGURE 5.8

Removing duplicate rows from a table is easy.

Caution
It’s important to understand that duplicate values are determined by the value displayed in the cell — not nec-
essarily 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/2011, and the other is formatted to display as May 15, 2011. When removing
duplicates, Excel considers these dates to be different. n