WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » How to Filter in Excel?

How to Filter in Excel?

By Jeevan A YJeevan A Y | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

Filter Data in Excel

The filter is the most often used tool in excel especially when we work with a large amount of data. Usually working with a large amount of data is challenging so to deal with relevant information from the large data set we have filter options.

In this article, we will expose you to how to add a filter in excel. This article covers what the filter is, how to apply the filter, how to work with them, and what are the things we need to vary when we work with the filter option.

Filter, as the name suggests, is used to filtering the data as per the criteria are given. We can filter out a particular set of data from the huge database and remove all other elements of the data. For example, if you have city-wise sale data, then by applying a filter, we can filter only “Bangalore” city-data.

How to Filter in Excel 1

Filter Options in Excel

You can download this Filter Excel Template here – Filter Excel Template

Firstly take into consideration the below data set.

How to Filter in Excel Example 1

For this, how do we apply a filter?

To apply the filter, we have several ways; I will show you each one of them now.

Method 1: Apply Filter from FILTER Option under HOME tab

Under the HOME tab, we have a FILTER option. You probably have not noticed this much.

 Example 1.1

Step 1: First, select the data and click on the FILTER option under the editing section.

How to Filter in Excel Example 1.2

Step 2:  As soon as you click on the filter option. It will apply the Filter to the selected data range.

Example 1.3

As we can see, a small drop-down kind of arrow is the filter.

Step 3: Click on the drop-down list to see what the items it has in it are.

How to Filter in Excel Example 1.4.0

I have clicked on the “City” drop-down list, and I can see all the cities which are there in the filter applied data range.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

Step 4: Now, if I want to filter out only “Delhi,” then I have to select only “Delhi” and uncheck all other boxes.

Example 1.5

It will filter out only “Delhi” data.

How to Filter in Excel Example 1.6

Method 2: Filter From Data Tab

We can also apply the filter from the Data tab as well. Under the Data tab, we have a FILTER option.

Example 1.7

Method 3: Apply Filter by Using Shortcut Key

This is my favorite… Without breaking any sweat with a simple keyboard shortcut, we can apply the filter to the data.

The following are shortcuts for the filter–

The shortcut key to apply a filter is SHIFT + CTRL + L

How to Filter in Excel Example 1.19

Another shortcut key to apply a filter is ALT + D + F + F

How to Filter in Excel Example 1.18

Both these keys are toggle key to apply and remove the filter.

How to Apply Filter in Excel?

Working with filter is fun because it almost fits all the needs that we wish to do. As we have seen in the above example, if we want to filter out any particular data, then we just need to select that and unselect the rest of the items.

Similarly, when we try to filter numbers, we can filter out numbers we can use advanced techniques.

Example #1

For example, in the above data, if we want to filter the numbers which are greater than 10000, then open the filter in the invoice value column.

After opening the filter option in that column, go to “Number Filter” >>> here, we see various options; choose the “Greater Than” option.

How to Filter in Excel Example 1.8

Now we will see the below window.

Example 1.9.0

Enter the number as 10000.

How to Filter in Excel Example 1.10

This will filter out numbers that are greater than 10000.

This symbol is the indication that the filter has been applied to the column.

Example 1.11

As we can see in the above image, it has filtered all the numbers >10000.

If you want to filter numbers which are greater than 10000 but less than 20000, then in the same window, choose less than value.

How to Filter in Excel Example 1.12

Now in the below box, enter 20000.

 Example 1.13

It will filter the number which is greater than 10000 but less than 20000.

How to Filter in Excel Example 1.14

Example #2

Filter Data By Typing the Value

If the data is large, then we will get flinty of values when we open the filter. Below is an example of the same.

Example 1.15

In these cases, we can simply type the value that we need to filter. If we want to filter the value “Prd 1,” then we just simply need to type the value in the search box.

How to Filter in Excel Example 1.16

As we can see above, it has filtered only that value from the huge list. We get the following data.

Example 1.17

Things to Remember

Below are the things to remember while applying the filter.

  • If the list is huge, then type the value that you want to filter; as soon as you type the value, it will start filter out all the possible matches.
  • If you want to filter number which is greater than or less than a specific number, then use the “Number Filter” option.
  • If the rows are colored, then you can filter specific colored rows as well by using “Filter by Color.”

Recommended Articles

This has been a guide to How to Filter in Excel. Here we discuss the top 3 methods to add a filter in excel (Filter Option, Data Tab, By and Shortcut Key) along with examples and a downloadable template. You may learn more about excel from the following articles –

  • VBA Filter
  • How to Filter Pivot Table?
  • Advanced Filter in Excel
  • Types of Filters in Power BI
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download Filter Excel Template

Special Offer - All in One Excel VBA Bundle (35 Courses with Projects) View More