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:
- Select a table cell, and click on the “Filter” command in the “Data” tab.
- Click on the header arrow of the column on which to apply the filter.
- 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.
- Set the filter criteria according to the search objective. From then on, rows that do not meet the criteria are hidden.
- 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.
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.
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.
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.
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.
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.
- The user fills in the filter criteria in a separate cell of the data table.
- 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).
- 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.
- 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.
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.
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.
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.
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)).
To take it a step further, download these Excel chart templates and use it to format your data and highlight your results.