Articles

Filter in Excel

Pinterest LinkedIn Tumblr

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 MS Excel.

Filter in Excel 1

What is Filter in Excel?

  • Excel Filter 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

  • 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)
  • 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

  • Be on the data, Go to Data Tab>> Click Filter under Sort & Filter option.
Filter in Excel_01
  • Be on the data, Go to Home Tab >> Click Sort & Filter > Filter.
Filter in Excel_02
  • 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

  • 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.
  • Uncheck the Select all box.
  • Check the boxes for filtering data and click OK.
Filter in Excel 2

Note: It also allows you to filter multiple columns.

How to use a filter in excel

Excel Filter provides a bundle of advanced tools which 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 Text Filters option only when your column contains text values. The below image shows the entire text filter option.

Filter in Excel 3

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

  • In the drop-down menu, select the text filters that Contains.
    Note:
    you can select any option as per requirement.
Filter in Excel 4
  • A Custom AutoFilter dialogue box pops-up in 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.
Filter in Excel 5
  • This Feature display cells which contains “Keyboards”.
Filter in Excel 6

Filter column with Multiple criteria.

1. Goto text Filters >> Does not contain

Filter in Excel 7

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.

Filter in Excel 8

5. This function applies filter by specified crieteria.
The output looks like the below image, if you see clearly under the rows section there will be double lines which indicates some of the rows are hiden due to applied filter.

Filter in Excel 9

How to Create filter with wildcard characters

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

Wild charactersDescriptionExample
? (Question mark)Used to match the single Character?one filters “done” and “gone”
* (asterisk)Used to match any sequence of charactersMobile* filters “Mobile phone”and “Mobile charger”
~(tilde)
followed by *, ?,or ~
Used to get the cell which contain 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.

Filter in Excel 10

For example, to filter the data that contains 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.
Filter in Excel 11

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.

Filter in Excel 12

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

Filter in Excel 13

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.

Filter in Excel 14

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.
Filter in Excel 15

3. A Custom AutoFilter dialogue box pops-up in 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.

Filter in Excel 16
Filter in Excel 17

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.

  • Filter based on cell colour.
  • Filter based on font colour.
  • 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 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 in Excel 18

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 in Excel 19

To know more on Excel go through our Articles

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

Subscribe to our YouTube channel for regular updates.

Related Posts

Write A Comment