How to use filter in Excel?

Filters in Microsoft Excel

Excel is a tool for data analysis and calculations. For better use of analyzing data we use many features and out of these features, the filter is one of the most important and most used tools. A filter helps to extract certain data out of the given data. This data can be categorized in any form like, text, number, date or fill color. The use of filters in Excel plays an important role whenever we want to extract certain information from different fields of data.

The data used for filter must have the given details to apply filter and use them properly: -

·         The data must have first-row mentioning the title of the columns, these titles work as a field name.

·         Every column must have data of the same type, i.e. either text, date (with proper date format – mm/dd/yyyy or similar as per your system format), or number.

·         There must not be any gaps in the rows/blank rows, else filter is not applied to the rows appears after blank rows.

How to apply a filter to the data?

To apply a filter to the data, we must select the data first, or we can simply click anywhere in the data and then select any one option mentioned below: -

1.       Home Tab -> Editing Group -> Sort & Filter Option -> Filter

2.       Or, Data Tab -> Sort & Filter Group -> Filter

3.       Or even we can use short cuts like

a.       Ctrl + Shift + L (for versions above 2003)

b.       Or even, Alt + D, then F then again F (for all versions)

As I told you, columns of data are divided into text, date or number formats, filters are applied in the same format at the heading of the table.

Text Filter

Text Filter helps in selecting/deselecting checkmarks from the list and pick only selected item(s) to show in data. We can also apply selection filters based on the order of value related to given constraints like, Equals, Does Not Equal, Begins With, Ends With, Contains, Does Not Contain, etc.

Date Filter

Date Filter helps in selecting/deselecting checkmarks from the list and pick only selected item(s) to show in data. We can also apply selection filters based on the order of value related to given constraints like, Equals, before, after, between, Day wise (today, tomorrow, yesterday), week wise (next week, this week, last week), Quarter-wise (next quarter, this quarter, last quarter), Year wise (next year, this year, last year), etc.

 

Number Filter

Number Filter helps in selecting/deselecting check marks from the list and pick only selected item(s) to show in data. We can also apply selection filters based on the order of value related to given constraints like, equals, does not equal, greater than, greater than or equals to, less then, less than or equals to, between, top 10, above average, below average, etc.

Every drop-down arrow at the filter heading can be opened in further two ways: -

1.       Click by mouse/touchpad

2.       Selecting the heading cell and then press (Alt + Down arrow)

Note: you can even select/deselect check marks with keyboard using the space bar.

Color Filter

A color filter is applied to those records which are marked out by applying foreground color or background color or both by selecting rows. This type of filter is visible in all types of filters (text, date or number), this comes under the option, “Filter By Color”

Removing Filters

To remove the filter from any field, you can click on the option “Clear filter from – FieldName”. This option is visible in all drop-down of every filtered column heading.

I hope this small step by step instruction will help you understand filters easily. I have mentioned both ways of applying filters, using tab options, and also short cut. Do tell me in the comment section, which option suits you best and why.

If you like the content, also comment about that and encourage me to write more topics related to excel.

If you want to learn by reading a book you can also buy this book. This is my recommendation.