Excel Yourself: Removing duplicates

Excel Yourself

When you import data from other systems into Excel, the data can often contain duplicated entries.

You will need to ensure there are no blank rows or columns within the data so that Excel can automatically determine the extent of the data table.

A column with a header and no data below it is acceptable.

Click any cell within the data table. Click the Data Ribbon tab. In the Data Tools section, click the Remove Duplicates icon. See Figure 1.

Figure 1

Figure 1

In most cases you will tick all the listed columns and click OK.

This ensures that the duplicated rows are identical across all columns. See Figure 2. Row 5 is a duplicate of row 2.

Figure 2

Figure 2


Excel will inform you how many rows have been removed, as per Figure 3.

Figure 3

Figure 3

It doesn’t inform you which rows have been removed. If the result is not what you expected you can undo the operation.

To practice, download a sample spreadsheet
 
Neale Blackwood CPA runs A4 Accounting, providing Excel training, webinars and consulting services.

How-to articles, sample spreadsheets and more

Excel yourself

Combine annual and monthly data in Excel with these simple steps


Making data relationships work

Easy steps to make data Relationships work in Excel


Excel Yourself

Easy tips for matching salary to job class in Excel