How to filter in Excel? (Auto filter + FILTER function)

How to filter in Excel? (Auto filter + FILTER function)


Knowing how to filter in Excel is essential to easily and quickly manipulate a table, making sure to preserve the integrity of the data. Manipulation is useful for research purposes on the one hand, for analysis on the other hand, and is of significant interest when the table contains a large number of data. The Microsoft software offers two filtering techniques: the automatic filter and the FILTER function.

> Download: Excel Chart Templates” align=”middle”/>

How to make an automatic filter in Excel?

The automatic filter allows the user to display only the rows of the table that meet the criteria of his choice, temporarily hiding the others. Once the information sought has been obtained, the user removes the filter: the table reappears in its entirety.

To make an automatic filter in Excel:

  1. Select a table cell, and click on the “Filter” command in the “Data” tab.
  2. Click on the header arrow of the column on which to apply the filter.
  3. Choose the textual filter or the relevant numerical filter with regard to the search. Note that the automatic filter in Excel also allows you to filter by color, if the formatting uses text or background colors.
  4. Set the filter criteria according to the search objective. From then on, rows that do not meet the criteria are hidden.
  5. To make the hidden rows reappear, click again on the column header arrow and select “Clear filter”.

Here are examples to understand in pictures how to filter in Excel, and to discover the extent of the possibilities offered by the automatic filter.

See also  Where to Work as an SEO Consultant? (Advertiser, Agency, Freelance)

On this table of customer data, the marketing team wants to highlight customers who are buying online to inform them of a promotion on the brand’s e-commerce site.

automatic Excel customer data filter

The automatic filter must be applied to the “Purchase channel” column. By selecting only the “online” text criterion, only the rows of customers who buy online appear. The marketing team uses the contact data displayed to communicate its promotional offer.

automatic Excel filter criteria

The marketing team wishes to deepen its knowledge of the customer segment under 30 years old. The automatic filter must be applied to the age range column. By entering the condition “less than or equal to”: 30, only the lines of customers aged 30 and under appear, with for each their gender, city, purchasing channel and means of contact.

filter numeric data in excel

Excel offers filters depending on the nature of the data in each column:

  • The column contains textual data: the user can use the automatic filters “Is equal to” or “Is different from”, “Begins with” or “Does not start with”, “Ends with” or “Does not end by”, and “Contains” or “Does not contain”.
  • The column contains numeric data: Excel offers automatic filters for equality and difference, superiority and inferiority ratios, and deviations from the mean of numbers.

This range of conditional filters allows you to perform an advanced search on a large volume of data, in an automated way to avoid analyzing the table line by line.

Operation of the FILTER function

Unlike the automatic filter, the FILTER function does not hide data that does not meet the selected criteria: the original table remains intact, the FILTER function transfers the rows that meet the criteria to another range of the Excel sheet. The user thus simultaneously visualizes his data table and the extraction he performs.

See also  What is a target market and how do you define it?

Unlike the automatic filter, the FILTER function requires mastering the syntax of Excel formulas.

Like the automatic filter, the FILTER function allows you to view rows of data in isolation that meet the user’s criteria.

How to use the FILTER function?

Basic use of the FILTER function

To use the FILTER function, you must choose a filter criterion, then write the formula in the destination cell. The formula works provided you indicate at least two pieces of information: the location of the data table to be filtered, and the column of the table to which to apply the filtering criterion.

Concretely :

  1. The user fills in the filter criteria in a separate cell of the data table.
  2. The user writes the FILTER formula in another cell: this is where the extraction of the filtered data is displayed. The syntax is as follows: =FILTER(location of the table of data to be filtered;column to which to apply the criterion=location of the cell which fills in the filtering criterion).
  3. By clicking on the “Enter” key on the keyboard, the rows of the original table that meet the filtering criterion appear at the destination location, without modifying the original table.
  4. The user can simply modify the filtering criterion, without rewriting the formula: the rows are updated automatically.

Example: on this sales table, the company wants to extract data relating to apple sales.

Excel table of sales before filter

In the data extraction destination location, the FILTER formula is written as: =FILTER(b3:e18;d3:d18=g3;””). Note that the quotation marks allow Excel to understand that if there is no “Apples” value in the data table to be filtered, nothing should be displayed at the destination location. This indication avoids error messages.

FILTER formula syntax in Excel

By clicking on the “Enter” key, the apple sales lines appear. The company knows who sold apples, and knows for each salesperson who sold apples his geographical area and his sales volume.

See also  Internet graphic charter: the essential points

extracting data with the Excel FILTER function

To obtain this data on pear sales, simply replace “Apples” with “Pears” in cell G3 of the Excel sheet: the data extraction is automatically updated.

Filter by multiple criteria

Excel’s FILTER function lets you introduce several filtering criteria to refine the search. In the previous example, the company can thus display the apple sales representatives of the Eastern sector alone, or the sales representatives of all sectors who have sold more than 5,000 apples.

To filter according to several criteria, you must fill in each criterion in a separate cell, then mention each criterion in the formula. The syntax is as follows: =FILTER(data range to filter;(criterion1)*(criterion2)*(criterion3)).

In this example, the company isolates sales reps in the East sector who sold apples.

use multiple filter criteria in excel

Mastering the different syntaxes of the Excel FILTER formula allows you to configure the results in an advanced way. It is possible for example to specify numerical filters, or to introduce enriched conditional filters:

  • To highlight salespeople from the East who have sold more than 5,000 apples, the criterion of the minimum number must be added. The number “5000” is entered in a separate cell of the Excel sheet, cell H3 for example, then the formula is modified as follows: =FILTER(A5:D20;(C5:C20=H1)*(A5:A20=H2 )*(D5:D20>H3)).
  • To obtain the list of salespeople in the East sector and of salespersons who have sold apples, you must use the “+” sign to add the filtering criteria. The formula is: =FILTER(A5:D20;(C5:C20=H1)+(A5:A20=H2)).

advanced multi-criteria Excel FILTER function

To take it a step further, download these Excel chart templates and use it to format your data and highlight your results.New call-to-action

Leave a Comment

Your email address will not be published.