A little about Excel Filter
- Your worksheet may contain lots of data and sometimes it’s only a tedious task if you are required to find/go through only a few rows out of hundreds or thousands.
- Thanks to Excel’s built-in functionality, you may filter data and return only desired rows or columns based on certain criteria.
- For example, you want to get only rows where the country is the USA.
- In this tutorial, you will learn how to apply the filter to get the subset of data in various versions of MS Excel.
How to filter data in Excel?
Follow these steps to filter the data in Excel:
Select any cell in the worksheet/data set that you want to filter.
Go to the “Data” tab in the ribbon
Find the “Sort and Filter” group and press “Filter” as shown below
You should see a dropdown arrow in the first row that contains the headers.
As you press any dropdown, you will see unique values based on the data in that column.
In our example sheet, we have a “products” worksheet and a header with two possible values:
In Stock or Out of Stock
You can see below, upon clicking the dropdown, it gives us the option to filter the results based on the “In Stock” or “Out of Stock” values.
As I checked the “Out of Stock” to see which products were not in stock and pressed the “Ok” button, it showed only those products that were ‘Out of Stock’.
How to remove that filter?
Don’t worry, your actual data is in place while viewing the subset of data. In order to bring the whole data back, just do this:
There, press the “Clear” button and the filter should be removed.
In order to remove the dropdown arrows in the column headers, press the “Filter” button as well.
Filtering the data based on numbers
Excel offers plenty of options to filter data based on the type of content in the cell as well.
For example, we have a “Price” column and want to filter the data and get those records whose price is greater than $2. This is how you can do it:
Select any cell in the range of cells or table.
Go to “Data” –> “Sort & Filter” and press “Filter”.
Click on the Number column dropdown (Price for our example), “Number Filters” and there you can see many options. For example,
| Equals |
| Does Not Equal |
| Greater Than |
| Greater Than Or Equal To |
| Less Than |
| Less Than Or Equal To |
| Between |
| Top 10 |
| Above Average |
| Below Average |
As I selected the “Greater Than” option and entered the value 2:
The result of the data is shown below:
Similarly, you may apply other filters like narrowing down the data on the basis of the price whose value is greater than or equal to $5.
Similarly, the Sales sheet shows only those records whose number of sales exceeds 50 and so on.
An example filtering text column
Just like numbers, you may narrow down the data based on the text columns. The “Filter” pop-up gives the following options for text filtration:
- Equals
- Does Not Equal
- Begins With
- Ends With
- Contains
- Does Not Contain
For the example, I am going with the “Begins With” option and entered the letter “T”:
This is the result:
The Date Filters
Just like the text and number filter options, Excel gives lots of options for filtering the data based on date columns.
So, if you have a date column and choose to Filter the data by “Data”, “Sort & Filter” and selecting the date column dropdown, you can see the following options:
Select the appropriate option and filter the data based on the date column.