4 Easy steps using the Advanced Filter to extract unique values in Excel

iStock_000021895605Large_jpgHave you ever found yourself in a situation where you have thousands of duplicate values in a column? Perhaps you were importing client data into Microsoft® Excel® from an accounting system or simply combining data in Excel, but often end up having duplicates values in a given column.

If you frequently experience this problem when working in Excel, in today’s tip we have the solution to help you overcome it. By using the Advanced Filter – Unique Records Only option you can generate a list of unique records in a given column. Follow the steps below on how to remove duplicate values by generating a unique list of Client Number values in a column.


You are welcome to download the workbook to practice.

Applies To: Microsoft® Excel® 2010, 2013 and 2016.

  1. Select the Data Tab, then Advanced under the Sort & Filter
  2. Select copy to another location, then next to List range, highlight the range with duplicates (A1:A22).

N.B. When selecting the list range, ensure that the cell with the list heading is also selected to avoid duplicate values.

  1. Place the cursor next to Copy to: and select cell B1.
  2. Tick the unique records only check box and select

1. ExcelOnSteroids_AdvanceFilter

Unique Client Numbers will now be extracted creating an accurate range of data. This will lead to a more efficient process and quick decision making, as less time will be spent cleaning up your data.