FILTER function


Are you having trouble with your data while sharing? Are your data’s getting cluttered? Want to get rid of them? Here’s everything you will know to resolve the problem using this exquisite feature called Unique Records in Advanced filter. This article will guide you the steps on how to get Unique Records using Advanced Filter.

Unique Records using Advanced Filter – Introduction

In most cases, when you share large datasets often the data gets repeated and therefore results in showing duplicate values. The unique record only option filters all these duplicate values and ensures to display the original data. You can also generate a list of unique records in a given column. The following steps will guide you with the same.

Step 1: In the below dataset, you can see that the last two rows have been repeated and are the same as the 4th and 12th rows. Thus, showing duplicate values.

Unique Records using Advanced Filter

Step 2: Go to the Data tab >> select Advanced.

How to get Unique Records using Advanced Filter 1

Excel throws the Advance filter box.

unique records

Step 3: Check Copy to another location from the Action section.
Step 4: In the List range, enter the range of your dataset.

How to get Unique Records using Advanced Filter 2

Step 5: In the Copy to box, select the sheet in which you want the unique records to be displayed.
Step 6: This is an important step. Check the unique records only box at the below.

How to get Unique Records using Advanced Filter 3

Step 7: In the below image you can see that the last two rows are removed. And, the data is now original without any duplicate values. The last two rows with duplicate value have disappeared.

How to get Unique Records using Advanced Filter 4

Practice Files

To read more related articles click here

To watch exciting videos and tutorials on Excel visit our YouTube channel.

Looking for specific information? Are large datasets making it difficult to find the information? Thankfully, Excel made it easy for us to narrow down the search with the help of a massive tool Filter or AutoFilter. This article will help you understand the role of Filter in Excel.

tutorial of filer in excel

What is Filter in Excel?

  • Filter in excel is the easiest way to display the information which is relevant to us from the large dataset and hides all other data from our view.
  • Excel can filter rows by format, value, or based on given criteria.
  • By applying a filter, we can even copy, edit, print only the visible cell /rows without changing the entire list.

Key Difference between Excel Filter and Excel Sort –

Generally, many of us think filter and sort are the same, but there is an immense difference between them. The following are the key difference

  1. Excel Filter – when we filter our data it shows only the data which we need, remaining data are temporarily hidden from our view. For example to display only “alphabets” from the list (4,2,8, a, b,1,9) to (a,b).
  2. Excel Sort – when we sort our data, the entire array /table is rearranged, for example arranging a list in ascending order (8, 2, 3) to (2, 3, 8).

Common ways to add a filter in Excel:

  1. Be on the data, Go to Data Tab>> Click Filter under Sort & Filter option.
filter button on data tab
     2.   Be on the data, Go to Home Tab >> Click Sort & Filter > Filter.
shot & filter option

   3.   Press Ctrl +Shift +L key to add or remove the filter.

Pro tip: Ensure that datasets have a header row with the column names. A filter cannot be applied without a heading.

How to apply a filter in Excel?

  1. Be on the column where you want to filter and click, the drop-down arrow.
    Shortcut Key: Press Alt+ Down arrow key to display the drop-down list.
  2. Uncheck the Select all box.
  3. Check the boxes for filtering data and click OK.
filter in excel process

Note: It also allows you to filter multiple columns.

How to use a filter in excel?

Excel Filter provides a bundle of advanced tools that helps to filter specific data types such as text, numbers, and dates.
Let’s have a quick look at each option and see the steps to create a filter that suits your data type.

Text filters –

To filter a column based on the text-value, Excel filter provides plenty of advanced options such as

  1. Text filters that begins with or ends with a specific character(s).
  2. Text filters that contains or does not contain a given character or word.
  3. Text filters that equal or does not equal a specified character that exactly matches.

Excel enables the Text Filters option only when your column contains text values. The below image shows the entire text filter option.

text filters in excel

For example, let’s filter the rows which contain the “Keyboard” word.

  1. In the drop-down menu, select the text filters that Contains.
    you can select any option as per requirement.
contains option in text filters

2.   A Custom AutoFilter dialogue box pops-up on your screen. In the box to the right of the filter, you can either enter the text, copy-paste text, or select the text from the dropdown lists and click OK.

custom autofilter dialogue box with contains option

3.   This Feature display cells which contains “Keyboards”.

custom autofilter result box

Filter column with Multiple criteria 

1. Goto text Filters >> Does not contain

does not contain option in text filters

2. A custom filter dialogue box pops up on your screen under that select And/Or button depending on your need, whether both or either criterion should be true
3. Select the comparison operator from the drop-down for the second criteria, type or select the value text from the drop-down lists. In this example, we will filter rows which “Does not contains” Keyboard and Home theatre.

custom autofilter with does not contain and does not end with

4. This function applies a filter by specified criteria.
The output looks like the below image, if you see clearly under the rows section there will be double lines which indicate some of the rows are hidden due to the applied filter.

result of custom autofilter

How to create a filter with wildcard characters?

The following are the wildcard characters that can be used for creating a filter.

Wild characters Description Example
? (Question mark) Used to match the single Character ?one filters “done” and “gone”
* (asterisk) Used to match any sequence of characters Mobile* filters “Mobile phone” and “Mobile charger”
followed by *, ?,or ~
Used to get the cell which contains a question mark, asterisk, or tilde. When~? filters “When?”

Number Filter 

Number filter helps to manipulate numeric data in a variety of ways as follows:

  1. Filters a number i.e. equal or not equal to the given number.
  2. Filters a number which is greater than, less than, or between the specified numbers.
  3. Filters top 10 or bottom 10 values.
  4. Filters above average or below average values.

The below image is the overview of the Number filter.

number filters option

For example, to filter the data that contains the amount less than 2,000.

  1. Be on the numerical data to enable number filter, then click the dropdown in the column header, and point to number filters.
  2. In the drop-down menu, select the Number filters >> less than.
number filters option with less than

3. A Custom AutoFilter dialogue box pops-up on your screen. In the box to the right of the filter, you can either enter the number, copy-paste number, or select the number from the dropdown list and click OK.

custom autofilter dialogue box for less than

4. With the help of the specified criteria filter results in the output as below.

custom autofilter output

Date Filters 

Excel date filter provides a bunch of choices that allows you to filter records for a certain period.

By default, Excel data filter will group all dates in a given column with a hierarchy of years, months, and days. Date filters allow to display or hide data for a day, week, month, quarter, year, before or after a specified date, or between two dates. The Below image represents the entire date filter options.

date filter option

Generally, Excel filter by date works with a single click. For instance, to filter rows containing records for the current month, click Date filter>>This Month.

Assume that you own a computer accessories shop in the center of the city and you want to know the items sold between 1 st to 5th April of 2019. This can be done by applying a date filter, below are the steps to be followed

  1. Be on the date columns to enable date filter, then click the dropdown in the column header, and point to date filters.
  2. In the drop-down menu, select the Date filters >> Between.
between option in date filter

3. A Custom AutoFilter dialogue box pops-up on your screen. By default, Excel suggests using “is after or equal to” and ” is before or equal to” comparison operators. (Here, I have given 1-apr-19 to 5-apr-19)and click OK.

custom dialogue box for date filter
date filter output

Filter by colour

Excel can filter your data if it is formatted manually or through conditional formatting. The following are the most commonly used format in excel.

  1. Filter based on cell colour.
  2. Filter based on font colour.
  3. Filter based on cell icon.

How to copy data after applying filter?

  1. Select any filtered Column or cell and then press Ctrl + A to which selects filtered data including column header.
  2. Press Alt +; to select only visible cells ignoring hidden rows or Go to Home tab >> Editing group >> Find & Select > > Go to Special> Visible Cells only.
  3. Now you can copy and paste the visible data.

How to remove the filter?

To remove all filters from the worksheet.

Go to the Data tab > Sort & Filter group and click Clear or press Shift+ Ctrl+ L to apply or remove the filter.

filter removing option

Filter Function 

If you are quite familiar with Excel functions, you can use the filter function to filter the data based on the criteria.
In the following example, we use the formula = FILTER(B4:D18,C4:C18=G3,) to return all records for a wireless keyboard.
You can FILTER based on multiple criteria with the help of (*) operator.

filter function

To know more on Excel go through our Articles

Don’t miss out on free sessions on Excel and more. To register Click here.

Subscribe to our YouTube channel for regular updates.