Excel Filtering Demystified: Essential Tips & Techniques

What is a Filter in Excel?

Filter or filtering in Excel is a function offered to the users to view the data based on specific characteristics. Out of the overall entry, the filter function allows the selection of cells matching the requirements and offering them as output. The basis of filtering can be single or multiple, and the sequence can also be chosen accordingly. 

The broad classification of filters in Excel categorizes it into two types: the text filter and the number filter. They offer a broad type of filtering options depending on the context. Their subtypes can be common or specific. For instance, ‘equal’ and ‘does not equal’ are common to both, while ‘begins with’ is specific to the text filter, and ‘between’ is particular to the numeric filter. The filtering function is carried out through the three types of logical arguments. These are array, If_Empty, and include.  

We have a table showcasing interior decoration products manufactured by a home decor company. We have listed the customers' demands, the overall sales in a month, and the profit earned. The table is depicted in Figure 1. 

Figure 1: Table depicting information about the home decor company

Filtering a Range of Data

To help you figure out how to filter data in Excel, there are two ways. You can go with a specific cell or a complete table. Either way, the selection of a column is mandatory. Let us begin with a specific cell method followed by filtering in the complete table. 

Select Data

Select any cell within the table of concern. The choice here will determine if you get to opt for a text filter or a number filter. 

Enable Filter

Considering the previous statement, we will take two cases here. Case I will include textual data to allow a choice of the text filter, and Case II will include numeric data to allow a choice of the number filter. 

Case I: Select the A7 cell. 

Case II: Select the C7 cell. 

Regardless of the choice, the filter will be applied to all the columns. Thus arises the importance of the next step. 

Choose a Column for Filtering

Now, choose the column for the application of the filter depending on the requirement. 

Case I

Now, we click on the downward arrow on the ‘Product’ column. You will get the following options as depicted in Figure 2.

Figure 2: Options on clicking the downward arrow on ‘Product’. 

Case II

Now we click on the downward arrow on the ‘Sold Units.’ You will get the following options as depicted in Figure 3. 

Figure 3: Options on clicking the downward arrow on ‘Sold Units’. 

Set Filter Criteria and Apply Filter 

Case I

We can now choose either to view only a specific type of product or apply the text filter. Unclicking any ticked product, as depicted in Figure 2, will simply remove it from the list. Now, let us check the different options available in the ‘Text filter.’ 

We customized a filter depicted in figure 4, and the output is shown in Figure 5. 

Figure 4: Applying customized text filters

We received four products as output. 

Figure 5: Output of customized text filter. 

Case II

Now we will remove this filter by clicking on ‘Product’ and further clicking on ‘Clear Filter from “Product”.’ However, it is possible to further add additional filter over the filter. 

Now, we can choose any column as there are three with numeric data. Let’s consider ‘Requirements.’ We can either choose to see a specific number in this column or apply a numeric filter. Going with the latter one, make the choice as depicted in Figure 6. 

Figure 6: Choosing a numeric filter

Click on ‘OK’ to apply the filter. The output will be as depicted in Figure 7. We see the removal of certain rows in the table. The exact number of removed rows can be seen on the leftmost side, exhibiting the sequence. 

Figure 7: Table with numeric filter ‘between’

Filtering Data in a Table

Previously, we worked on a specific cell and column. The same can be done on the complete table. Considering the previous table, let’s navigate through the process. 

Create a Table

We have a table. Selecting the same and clicking on the filter adds the filter to each header. 

Figure 8: Adding filter on the table

Select Column to Filter and Choose Filter Options

Let us consider the ‘Profit’ column. Choosing the filter ‘top 10’. A drop-down menu will appear to customize the choice further. You can choose between top and bottom and any number such as top 10, 11, 9, and so on. Moreover, the criteria for the top 10, like whether it will be items or percent. 

Figure 9: Dialogue box to customize the filter

Apply Filter

We choose the ‘items’ and click on ‘OK.’ The output will be as depicted in Figure 10. Check the change in numbers on the leftmost side to locate the removed row. 

Figure 10: Applying numeric filter ‘Top 10’

Note: You can also choose specific or multiple required items only by checking the box. 

Conclusion

Excel sheets are an important part of work for professionals handling tasks such as Data Scientists and Analytics. With a wide number of actions performed using the software, familiarity and the ability to lower the time of data handling is critical. 

Helping candidates learn many easier functions along with industry-specific tricks, the Master’s program for Data Scientists from Simplilearn covers much more. The hands-on experience, online training, and the opportunity to participate in hackathons, webinars, masterclasses, and Ask-Me-Anything sessions is a boost for both career and self-development!