When working with spreadsheets including lot of data, it is very important to quickly find required information. Excel allows quick search and filtering necessary data by the special tool – Filter.
- How to add Filter in Excel?
- Filter used for multiple columns
- Text filters
- Number filters
- Date filters
- Filter by Color
- How to remove Filter in Excel?
- File with examples to download
How to add Filter in Excel?
To use Filter in Excel you need to select any cell in data range you want to filter and choose Filter command which can be found in Data tab on the Ribbon:
After this action you can see, that small arrows appeared in top row of your data:
In this example, we will filter data for Vienna only.
To do that, click on the arrow in first row of column “City”. By default, Filter in Excel chooses all values in the column, so if you want to select Vienna only, you need to click Select All to clear selection of all items and then choose required one – in this case it’s Vienna:
Filter used for multiple columns
Filtering in Excel is possible for more than one column in the same time. To do that, you just need to repeat steps from previous example for any columns you want.
In example below, there are two columns filtered in the same time. London in column A and July in column C:
Tip:
You can make Filter option window bigger or smaller if needed. To do that, click the bottom right corner of the window and drag it to get desired window size:
Text, number and date filters
Text filters
Text filters is additional filtering option, which allows to find data including specific text strings. This option allows to find records which:
- equals or not equals to given character/text;
- begins with or ends with specific character/text;
- contains or does not contain given characters or text strings.
In example below, I want to filter all data excluding Berlin. I need to use Does Not Contain option and enter “Berlin” (in this example I can also use Does Not Equal option):
As a result, all rows for Berlin are hidden:
Important note:
Text Filters is available only if data in the column include data formatted as text. In other cases, except of Text Filter you can see different options such as Number or Date Filters.
Number Filters
For columns including number data, Excel allows to use special option – Number filters. It is very similar to Text filters explained above, however it includes more filtering options:
- Equals or Does Not Equals to specific value;
- Greater/Less Than or Greater/Less Than or Equals to given value;
- Values Between two other ones;
- Top 10 values in given range;
- Above or Below Average for given data range.
Example for Number Filters – filtering values between 5000 and 10000:
Date Filters
Date Filters is similar tool to Text and Number Filters, but this is available in columns including date information. Among these three tools, Date Filters has the biggest variety of possible filtering options:
Filter by Color
If data in your spreadsheet are formatted manually or by conditional formatting, Excel allows you to filter data based on their formatting style, for example font or cell color. It can be done by using Filter by Color option:
How to remove Filter in Excel?
After applying Filter to specific column in your spreadsheet, you may want to clear the Filter and see all, unfiltered data. For this purpose, you need to use Clear Filter option in given column:
If you used Filter for more than one column and want to remove all of them, you need to use Clear command which is located in Data tab of the Excel Ribbon:
Tip:
To apply or clear filter in quicker way, you can use keyboard shortcut CTRL + SHIFT + L