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.
- 1 What is Filter in Excel?
- 2 Common ways to add a filter in Excel
- 3 How to apply a filter in Excel
- 4 How to use a filter in excel
- 5 Filter by colour
- 6 How to remove filter
- 7 Filter Function
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.
- Be on the data, Go to Home Tab >> Click Sort & Filter > Filter.
- Press Ctrl +Shift +L key to add or remove the filter.
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.
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.
To filter a column based on the text-value, Excel filter provides plenty of advanced options such as
- Text filters that begins with or ends with a specific character(s).
- Text filters that contains or does not contain a given character or word.
- 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.
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.
- 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.
- This Feature display cells which contains “Keyboards”.
Filter column with Multiple criteria.
1. Goto text Filters >> Does not contain
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.
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.
How to Create filter with wildcard characters
The following are the wildcard characters can be used for creating a filter.
|? (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 contain question mark, asterisk, or tilde.||When~? filters “When?”|
Number filter helps to manipulate numeric data in a variety of ways as follows:
- Filters a number i.e. equal or not equal to the given number.
- Filters a number which is greater than, less than or between the specified numbers.
- Filters top 10 or bottom 10 values.
- Filters above average or below average values.
The below image is the overview of the Number filter.
For example, to filter the data that contains amount less than 2,000.
- Be on the numerical data to enable number filter, then click the dropdown in the column header, and point to number filters.
- In the drop-down menu, select the Number filters >> 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.
4. With the help of the specified crieteria filter results the output as below.
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.
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
- Be on the date columns to enable date filter, then click the dropdown in the column header, and point to date filters.
- In the drop-down menu, select the Date filters >> Between.
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 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
- Select any filtered Column or cell and then press Ctrl + A to which selects filtered data including column header.
- 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.
- 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.
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.
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.