Course sections

Introduction to Microsoft Excel, Lecture 4

Removing Duplicates, Getting unique values and Advanced Filter options

Data duplication can happen when records are entered into the spreadsheet more than once (or you’re combining records from multiple sources). You might also find duplicates for one or more fields, such as a name or state field, with the same data and want to limit the records to unique data for some fields.

Below are the steps for removing duplicate values in Excel:

  1. Select the range of cells, or make sure that the active cell is in a table.
  2. On the Data tab, in the Data Toolsgroup, click Remove Duplicates.
  3. Do one or more of the following:
    • Under Columns, select one or more columns.
    • To quickly select all columns, click Select All.
    • To quickly clear all columns, click Unselect All.

If the range of cells or table contains many columns and you want to only select a few columns, you may find it easier to click Unselect All, and then under Columns, select those columns.

  1. Click OK.

Note: Excel displays a message indicating how many duplicate values were removed and how many unique values remain, or if no duplicate values were removed.

  1. Click

Excel Advanced Filter

As the name suggests – is the advanced version of the regular filter. You can use this when you need to use more complex criteria to filter your data set.

Here are some differences between the regular filter and Advanced filter:

  • While the regular data filter will filter the existing dataset, you can use Excel advanced filter to extract the data set to some other location as well.
  • Excel Advanced Filter allows you to use complex criteria. For example, if you have sales data, you can filter data on a criterion where the sales rep is Bob and the region is either North or South.
  • You can use the Excel Advanced Filter to extract unique records from your data (more on this in a second).

EXCEL ADVANCED FILTER (Examples)

Now let’s have a look at some example on using the Advanced Filter in Excel.

Extracting a Unique list

You can use Excel Advanced Filter to quickly extract unique records from a data set (or in other words remove duplicates). Excel Advanced filter would allow you to select a location to get the unique list. Let’s see how to use advanced filter to get a unique list. Suppose you have a dataset as shown below:

As you can see, there are duplicate records in this data set (highlighted in orange). These could be due to an error in data entry or result of data compilation. In such a case, you can use Excel Advanced Filter tool to quickly get a list of all the unique records in a different location (so that your original data remains intact). Here are the steps to get all the unique records:

  • Select the entire data set (including the headers).
  • Go Data tab –> Sort & Filter –> Advanced. This will open the Advanced Filter dialog box.
  • In the Advanced Filter dialog box, use the following details:
    • Action:Select the ‘Copy to another location’ option. This will allow you to specify the location where you can get the list of unique records.
    • List Range:Make sure it refers to the dataset from which you want to find unique records. Also, make sure headers in the data set are included.
    • Criteria Range:Leave this empty.
    • Copy To:Specify the cell address where you want to get the list of unique records.
    • Copy Unique Records Only: Check this option.
    • Click OK.

This will instantly give you a list of all the unique records.

Using Criteria in Excel Advanced Filter

Getting unique records is one of the many things you can do with Excel advanced filter. Its primary utility lies in its ability to allow using complex criteria for filtering data. Suppose you have a dataset as shown below and you want to quickly get all the records where the sales are greater than 5000 and the region is the US. Here is how you can use Excel Advanced Filter to filter the records based on the specified criteria:

  • The first step when using Excel Advanced Filter with complex criteria is to specify the criteria. To do this, copy the headers and paste it somewhere in the worksheet.
  • Specify the criteria for which you want to filter the data. In this example, since we want to get all the records for the US with sales more than 5000, enter ‘US’ in the cell below Region and >5000 in the cell below Sales. This would now be used as an input in Advanced Filter to get the filtered data.
  • Select the entire data set (including the headers).
  • Go Data tab –> Sort & Filter –> Advanced. This will open the Advanced Filter dialog box.
  • In the Advanced Filter dialog box, use the following details:
    • Action:Select the ‘Copy to another location’ option. This will allow you to specify the location where you can get the list of unique records.
    • List Range:Make sure it refers to the dataset from which you want to find unique records. Also, make sure headers in the data set are included.
    • Criteria Range:Specify the criteria we constructed in the steps above. In this example, it would be F1:I3.
    • Copy To:Specify the cell address where you want to get the list of unique records.
    • Copy Unique Records Only:Check this option.
    • Click OK.

This would instantly give you all the records where the region is the US and the sales are more than 5000.

The above example is a case where the filtering is done based on two criteria (US and sales greater than 5000).

WhatsApp chat