Skip to content
Related Articles

Related Articles

How to Filter Data in Excel?

View Discussion
Improve Article
Save Article
Like Article
  • Last Updated : 24 Mar, 2022

Working with a database is a crucial job. With every data added increases the comprehensiveness to manage records and pick out the needed information from any corner of the database. Keeping the struggle in mind, Excel is a great tool to store and extract data in a hustle-free manner. It is convenient for every data-driven purpose. The filter option provides efficiency in searching the needed data from a huge pile of records. Let’s understand what exactly the filter option is. The filter in excel displays data that is relevant for the users to make decisions. The user applies some criteria and the records get separated wherein required data show up and others disappear. This reduces the burden to scroll each record to find the best match plus saves a lot of time. Excel filter is the most preferred feature among professionals to rely on making crucial decisions.

How to apply a filter in Excel?

The process begins with the drop-down arrow appearing on the headings of each column. There are three methods to enable filter in excel. You can choose the one based on your convenience and needs. Let’s have a glance at each one of them.

1. Filter option in Home tab: Select a cell from the record. Under the Home tab, there’s an option visible as ‘Sort & Filter’. From the drop-down list select ‘Filter’. 

Filter-option-in-Home-tab

 

2. Filter option in Data tab: Select a cell from the record. Under the Data tab, there’s an option visible as ‘Filter’. Click on it and you can see the drop drop-down on each column header. 

Filter-option-in-Data-tab

 

3. With shortcut keys: Select any cell from the record and simply go with any of these methods:

  • Ctrl + Shift + L
  • Alt + A + T
With-shortcut-keys

 

How to Filter Excel data?

To understand the whole mechanism, let’s take an example. A store offers a various range of apparel for both men and women. The record includes details like the Order Number, Date, Customer Name, Item Purchased and Amount paid. Filtering the data is never been easy but excel makes it easy to understand for everyone. To make each concept clear here are some cases for each type of filter option. You can learn to apply them and experiment with options as well.

Case 1: You want to know the purchase history of Marie

If you want to know what all purchases have been made by Marie, you can follow the steps below. This helps in understanding the customer relationship with the business.

Step 1: Click on the arrow near the Customer name  

Step 2: Click on the Select all option.

Step 3: Check on Marie.

The filter is applied to the column which shows the purchase details of Marie.

Final-Result

 

Case 2: You need the sales record for 1st March 2022

In case you need all the sales made on 1st March 2022, apply custom filter options with each step given below. 

Step 1: Click on the arrow near Date. 

Step 2: Hover the cursor on Data Filters.

Step 3: Click on Custom Filters.

Click-on-Custom-Filters

 

Step 4: A window appears. Select the date for which you want to see the records.

Select-the-date

 

The record for the selected date will appear in the sheet.

Record-for-the-selected-date-will-appear

 

Case 3: You want to know the name of the items priced more than 2000.

Here, we are going to discuss finding the records with ‘Greater than’ criteria to show up all records above the specified amount.

Step 1: Click on the arrow near Amount paid.

Step 2: Hover the cursor on ‘Number Filters’ and click on Greater than.

Click-on-Greater-than

 

Step 3: A window will appear. Fill up the amount you want to filter by.

 Fill-up-the-amount-you-want-to-filter-by

 

Sales records with the amount paid more than 2000 will be displayed.

final-Result

 

 

Case 4: You want to filter the records by Hoodie and Shorts

In case, you want to apply a filter on two options from the same column. You can do so by selecting desired options from the text filters. Follow the steps below.

Step 1: Click on the arrow near ‘Customer name’.  

Step 2: Click on ‘Select all’.

Step 3: Check on Hoodie and Shorts.

Check-on-Hoodie-and-Shorts

 

The filter will show the records related to Item Hoodie and Shorts.

 

Remove filters

To remove the filter click on ‘Clear’ from the Data tab or simply press Ctrl + Z. You can apply filters on two columns one by one to get more sorted details.

Remove-filter

We’ve seen how the filter option works for text, numbers, and dates. You can apply multiple filters at the same time for one column. To apply the filter for two or more columns, repeat the steps one by one for each header. More often, Sorting and filtering are used interchangeably but there’s a difference. Sorting is all about arranging the data in a specific order while filtering means extracting the required data from the record. There are unlimited criteria to filter a set of data. Keep exploring and make the best use of this magic feature of excel.

My Personal Notes arrow_drop_up
Recommended Articles
Page :

Start Your Coding Journey Now!