It becomes a difficult task to manage large amount of data in a spreadsheet especially when it comes to searching for various data. Filtering data in Excel enables us to retrieve the desired information based on different criteria. This helps in narrowing down the data by hiding or excluding unwanted records in our worksheet. Filtering can be done on numbers, texts, background color and much more. In this article, you will learn how to apply both Auto Filter and Advanced Filters.
Where to find the Filter Menu?
Before you start Filtering data you need to first understand where this tool is present in MS-Excel. The Filter option is present at two places in Excel.
Firstly, Filter option is present in the Home tab itself with the Sort option in the Editing section.
Secondly, in the Data tab, we have the Filter option in the Sort & Filter section.
Using the Filter Option
You can select the filter option from either of the above places.
Whenever the filter option is selected, relevant data is displayed i.e. data according to particular criteria is displayed. If the filter is removed, then the entire data is displayed again.
Let us understand the concepts in detail with the help of an example.
Using AutoFilter Option
Have a look at the data before applying the filter.
In order to use an AutoFilter, click on the Filter option in the DATA tab. On clicking the filter option it will be highlighted in the DATA tab and the first row (or the header row) of our table will change in appearance. They will now have arrowheads by their side as in a drop-down list. Check for yourselves in the example below.
These arrowheads will help to filter out data with all the ease. On clicking these arrowheads, various options will be displayed to choose from. The options are:
The first two options are for sorting the data alphabetically or in the reverse order respectively. You also have an option to Sort the data by Color.
You will be displayed Number Filters or Text Filters depending on the type of field of the arrowhead you select.
At the last part of the list, we have a long checklist which displays the unique options present in the selected column. A tick (√) will mean we want to see the selected data. Unchecking the item will not display it on screen once we press the OK button. Have a look at the example below.
If you check only Computers and Engineering options, then the following records will be displayed.
Have a look at the arrow beside Stream column. It changes and this depicts that a filter in this column has been applied to the data.
NOTE: – Clicking on Select All will select all the options. Clicking on it again will uncheck all the options. You have to select at least one option in the checklist as MS-Excel will deactivate the OK button if we uncheck all the options on the menu.
Using Text Filters
You can further add details to the filter by using the Text Filter options. On selecting a column with text or character data, Text Filters with various options like Equals, Does not equal, Begins With, Ends with, Contains, Does not Contain and Custom Filter are displayed.
Let’s take up an example. Suppose we want to search for names which start with a particular letter say ‘A’ from our long list of data. What we can do is select the Begins With option from the above list. On selecting this, the following dialog box will be displayed.
Here, we will write the letter with which we want our names to begin in this example we will write A. On clicking OK, we get the following result.
We can specify two criteria as well. For example: if we want to display names beginning with an A or L we will do as follows.
Select the Begins With option from the list, write the criteria as shown in the picture below.
We will use OR as it will check for either criterion to be true. In AND, it will display only rows matching both the criteria’s. Here is the output on clicking OK.
An example using AND:
If we want to see data of students whose names start with ‘A’ and do not end with an ‘S’, we will do as follows in the Custom AutoFilter dialog box.
Here is the output on clicking OK.
Take note that data of student name as Andrews is not displayed here.
Now, let’s take another example where you want to search for a name which has the second character as ‘a’, then you need to specify the constraints as follows:
‘?’ represents a single character from the set of alphabets.
‘*’ represents any number or combination of characters from the set of alphabets.
Hence, in the above example, all names beginning with any character but second character as ‘a’ and ending in any combination of characters will be displayed on the screen. Have a look at the output.
In a similar fashion number filters and date filters can also be used. Let us move to number filters first.
Using Number Filters
On selecting a column with numeric data, Number Filters with various options like equals, does not equals, greater than, less than, between etc are displayed.
Let us take a simple example. Let us view all students whose percentage was between 70 and 80 percent. For this, we will select the between option. Do the following in the dialog box displayed to you.
The following output is displayed on clicking OK.
You can make use of other options in a similar fashion. Go ahead and try them too. Now, let us have a look at the date filters.
Using Date Filters
On selecting a column with dates in it, Date Filters with various options like equals, before, after, between, this week, next month, this year, last quarter etc are displayed on the screen.
Let us take an example. Suppose we want to see data of students who were born in the month of April, we will do as follows:
Select the Date Filters -> All dates in the period -> April.
On clicking April, all records with this criterion will be displayed on the screen. Have a look at the output.
In order to get back all the original data, we need to clear the filters we have applied. We can do this in two different ways.
In order to clear the filters, we get an option of Clear Filter from “Stream” (the column name on which the filter was applied). On clicking this option the filter applied on this column will be cleared.
Alternately, you can click on the Clear button in the Sort & Filter section on the Data Tab.
Using Advance Filter Option
If you still want a more specific search then Excel provides us with Advanced Filters. On clicking Advanced, Excel will display you with yet another set of filter options.
You will find this option in the Sort & Filter section in the DATA tab.
- Before clicking on this button, you need to write the Search Criteria. For doing so, we will write the criteria at any place in the worksheet. For example:
- Now, you need to select a cell where you want to apply the filter. On clicking Advanced, a dialog box will be displayed. The dataset will automatically be selected based on the cell selected by you. Alternately you can select or specify the dataset on your own too. The dialog box which will be displayed is as follows:
List Range specifies the range of the data on which the filter needs to be applied on. In this example, the range is from cell A1 to cell E11.
Criteria Range specifies the Search Criteria based on which the results are to be evaluated.
- Now, select the Criteria Range from the worksheet.
- On clicking OK the following result will be displayed on the screen.
For Multiple AND Criteria
For selecting rows pertaining to all the criteria’s, we specify the criteria’s as follows:
Click on advanced, select the data range and Criteria range like:
Click OK. Now notice the result.
Students having a percentage above 85% and of commerce stream are only displayed.
For Multiple OR Criteria
For selecting rows pertaining to either of the two criteria’s, we specify the criteria’s as follows:
Click on advanced, select the data range and Criteria range like:
Click OK. Now have a close look at the result.
Students having a percentage below 70% are displayed along with all the students of commerce stream. Here, either of the criteria is evaluated as true and the results matching them are displayed `on the screen.
Final Words to Sum it up
Hope we were able to clear all your concepts regarding the use of filters in MS-Excel. Now, go ahead and search for the required data from the long list maintained in an Excel spreadsheet. This tutorial would have made your Excel skills a lot better for sure.