In this tutorial we will know how to filter data in excel, along with creating filters on text values, numbers and dates. Further, we will tackle how to remove filter and how to fix it if ever auto-filter is not working.

What is Filter in Excel?

Filter in Excel is the quickest way to display related data requested in a given time and remove those nonessential information. The thing you can do in your rows is filter by their value, criteria, or by its format. Hence you can print, copy, or edit without rearranging the data.

Additionally, it also allows sorting options on relevant data in a given column. Mainly in sorting text values it can sort from A to Z, sort Z to A or Sort by color.

For sorting numbers, it can sort from smallest to greatest and sort by color. On the other hand sorting dates from oldest to newest, newest and oldest and sorting by color is able to accomplish.

Difference between Filter and Sort

Filter and Sort somehow have similarities however in sorting data in excel the entire table is rearranged. Another thing is there is no data hidden it only presents new order of the given data.

Meanwhile, the filter only displays entries you want to display and hides irrelevant details.

How to use filter in Excel

This time we examine how to use filter features of excel, as we use the example worksheet in order to apply this feature. Let’s how to utilize it:

Include head Row.
To have filtering work right the data should have header row, so you could identify the name of each column. In our sample data our header row is Name, Math, English, Science, Filipino, and Date.
Select Data Tab and Click Filter command.
Also in Home Tab in Editing group, you can access the sort and filter commands.
A drop-down menu will show in the header of column.
Click drop-down arrow of the column you want to filter so in our case we will filter the Math column.
Filter menu will appear
Deselect all the data
Just uncheck the box next to right next before Select all.
Check the box you want to filter
Select the data you want to filter and check the box next to them. Then Click OK. In our example, we checked 75, 83, 85.
The filtered data will appear
The result only displays the data selected to be filtered. So in our case grades only contains 75, 83 and 85 shown in Math column.

How to add a filter in excel

There are 3 ways to add Filter to your workbook.

Take a look at these three ways:

You can add Filter through Data Tab and on the Sort and Filter Group click the Filter command.
On the home tab on the Editing group select Sort and Filter command and click Filter.
Another way to use the keyboard shortcut, just press CTRL+L.

How to remove filter in excel

In removing Filter on the data of your workbook do these following ways:

Go to the Data tab > Sort & Filter group, and click Clear.
On the Home tab in the Editing group select Sort and Filter and click Clear.

how to filter in excel by color

If the data you have contain conditional formatting you can able to filter these data by color. As you click the dropdown arrow it will display Filter menu which you can locate Filter by Color with one or more options depending on your formatting applied.

Specifically, it could be:

Filter by cell color
Filter by font color
Filter by cell icon
- In our example will only display the formatting of the cells in the color red.

how to filter in excel for multiple values

Filter in Excel is cumulative it handles multiple values in filtering. Therefore it is a great help in narrowing data to get a specific result.

Knowing you can filter by color, now let’s add other values which will filter Math and English column wherein Math column will display grades 85 and above and in English column are formatted in red color.

How to create an advanced filter in excel

Excel provides advanced filtering tools which can be used in specific filtering of data, it includes such as text, number, and dates which is advantageous in narrowing and obtaining an exact result.

Filter text values

This filtering tool allows you to filter columns that contain text in something more specific, so here are the advanced options of Text Filters which can help you.

Text Filters cells that begin with or end with characters.
Text Filters that cells contain or does not contain characters or any word specified.
Text Filter that filters exactly equal or not equal to a given character.

This filter automatically appears on Autofilter when clicking the dropdown arrow once your column is composed of text.

To visualize, we are going to look value of names that begin with A.

Click the drop-down arrow in the column heading, and point to Text Filters.
In the drop-down menu, select the preferred filter in the example we selected begin with.
As the Custom AutoFilter dialog box will appear. Then you can customize, in the right next to begin with box, enter the text to be the criteria or select desired option on the list.
Click OK. Then the result will show as below:

Advanced number filters

Aside from the text filters Excel also has Number Filter to manipulate numeric data in various ways.

Number Filters allow to filter equal to or not equal to specific number.
Filter numbers, greater than, less than or between the specified numbers.
Filter top 10 or bottom 10 numbers.
Filter cells with numbers that are above average or below average.

The figure below visualizes what are the number filters available in Excel.

For example, if we only want to display the top 10 grades in science, try to utilize the steps below:

Click the autofilter arrow in the column header, and point to Number Filters.
Select the number filters that exactly meet your requirements, click the top 10 filter.
Then the top 10 Autofilter dialog box will pop up, where you can change choose top or bottom if the result will percent or items.
Click OK.

Advanced date filters

Furthermore, Excel provides Date Filters that let you choose various ways to filter records of a certain period of time easily and quickly such as between two dates, last year or next quarter.

This time for you to understand, we’ll give an example that will only view the grades between December 15 to December 20.

First Select the Data tab and the Filter Command then click the dropdown arrow on the column header of cells.
Select a column you want to filter and click the dropdown arrow. In our example, column M to view the range of dates.
The filter menu will display, hover your mouse to Date filters then the options in filtering dates will appear. Thus in our example, we will select between option.

The custom filter will appear which you can custom the date you prefer then if you are done click OK. Consequently, in our example we entered the date on after or equal to this date and before or equal to this date.

This will display ranges of dates between the two dates.

Filter not working in Excel

If your Filter in Excel is not working probably there is new data inserted outside the range of the filtered cells. Thus to resolve this just re-apply the Filter. If still not working just clear your entire filter on your worksheet and apply it again.

Moreover, if the data you have contains empty cells or rows you must manually select the range of the cells using your mouse, proceed to apply Autofilter. Once you do this the new data will be added to the range of cells filtered.

Conclusion

In conclusion, this article about how to filter in excel taught that there are a lot of ways to make our life easier particularly filtering data. It is easier to find, edit and copy specific data because of this filtering tool provided by Excel.

Thank you for reading 🙂

How to Filter in Excel With Advance Filtering Tool & Example