Filter in Excel is a powerful tool that enables users to sift through vast datasets with ease, making data analysis more efficient and insightful. By leveraging the various filtering options available, such as text, number, date, and custom filters, users can quickly identify patterns, outliers, and trends within their data. Whether it’s refining search results, identifying specific criteria, or extracting valuable insights, Filter in Excel streamlines the process and enhances decision-making. With its intuitive interface and robust functionality, Filter in Excel empowers users to navigate complex datasets effortlessly, allowing them to focus on extracting meaningful information. Harnessing the power of Filter in Excel transforms data analysis into a more streamlined and productive process, ultimately driving better outcomes for businesses and individuals alike.
What is Filter in Excel?
Filter is a feature which allows to display data with given criteria. In the other hand, Data which does not meet the given criteria will be hidden from dataset.
How to add filter in excel?
Step-1: Prepare a data table with data outlined below
Step-2: Select the range of data to filter, outlined below
Step-3: Select Filter from the Data menu
A dropdown arrow will appear to the right of each column header
Filter may be applied to each column of dataset. To apply a filter, click the dropdown menu from column header and select the criteria to display.
For example: to view all male employee:
Select the dropdown arrow appeared in Gender Cell
Filter result will be appeared, outlined below (Only Male Employees will be display)
How to Clear a filter in Excel?
To Clear filter and return to the full data table information, select all check boxes from the dropdown menu and click OK or you can deselect the filter option from Data menu, outlined below.
Also, you can select the option “Clear Filter from Gender” from Gender dropdown menu to clear the filter.
How to apply filter in excel shortcut? / How to add filter in excel shortcut?
Select any cell of data to filter
Press ‘CTRL+Shift+L
A dropdown arrow will appear to the right of each column header, outlined below.
How to apply multiple filter
For Example: to view all male employee who joined in 1 Jan 2022:
Step-1: Select the dropdown arrow appeared in Gender Cell
How to apply multiple filter
For Example: to view all male employee who joined in 1 Jan 2022:
Step-1: Select the dropdown arrow appeared in Gender Cell
Filter result will be appeared, outlined below (Only Male Employee will be displayed)
Step-2: Select the dropdown arrow appeared in Date of Joining Cell
Filter result will be appeared, outlined below (Only Male Employee joined in 01 Jan 2022 will be display):
How to filter by text color in excel?
Step-1: Prepare a data table with information outlined below with blue font text color in Age field
Step-2: Select the dropdown arrow appeared in Age Cell
Filter result will be appeared, outlined below (Only font color blue in age column will be displayed)
How to filter by Cell Fill Color or filter by cell color in excel?
Step-1: Prepare a data table with information outlined below with green fill color in Gender field:
Step-2: Select the dropdown arrow appeared in Gender Cell
Filter result will be appeared, outlined below (Only Cell color Green in age column will be displayed)
How to use Custom Auto Filter in Excel?
Step-1: Prepare a data table with data outlined in below:
Step-2: Select the dropdown arrow appeared in Date of Joining Cell
Custom Autofilter window will be appear, outlined below
Step-3: Select the option from Date of Joining dropdown menu. Select equals from dropdown menu appear in left side.
Step-4: Select required date from the right side filed. Select 01 Jan 2022.
Filter result will be appeared, outlined below (Only employee will display who joined 01 Jan 2022)
From the Custom Autofilter window, you can select multiple criteria as required from the dropdown list like equals, does not equal, is after, is before, end with, contains, does not contain. This will provide the desired result to analyze data.
Note: Some of these conditions apply only to text and some others apply only to numbers.
What is Advance filter in Excel? How to use Advance Filter in Excel?
Excel Advanced Filter is the advanced version of the regular filter. You can use this when you need to use more complex criteria to filter your data set.
Excel advanced filter is giving the option to extract the data set to some other location. It is also allowing you to use complex criteria and giving the scope to extract unique records from your data set.
Example 1 – Extracting a Unique list using Advance Filtering
Step-1: Prepare a data table as outlined below
Step-2: Select the range of data to filter outlined below
Step-3: Select Advance Filter from the Data menu, outlined below:
An Advance Filter window will appear, outlined below
In the Advanced Filter dialog box, use the following details:
- Action: Select the ‘Copy to another location’ This will allow you to specify the location where you can get the list of unique records.
- List Range: Make sure it refers to the dataset from which you want to find unique records. Also, make sure headers in the data set are included.
- Criteria Range: Leave this empty.
- Copy To: Specify the cell address where you want to get the list of unique records.
- Copy Unique Records Only: Check this option.
- Click OK
See the outline in below
Advance Filter result will be appeared, outlined below (Only Unique value or records will be copied to Column-I to Column-O)
Note: while using Advanced Filter to get the unique list, make sure you have also selected the header. If you don’t select header, it would consider the first cell as the header.
How to use keyboard shortcut key for Advance Filter
Select the range of data to filter
Press ‘Alt + A + Q’
This will open the Advanced Filter dialog box, outlined below
In the Advanced Filter dialog box, use the following details:
- Action: Select the ‘Copy to another location’ This will allow you to specify the location where you can get the list of unique records.
- List Range: Make sure it refers to the dataset from which you want to find unique records. Also, make sure headers in the data set are included.
- Criteria Range: Leave this empty.
- Copy To: Specify the cell address where you want to get the list of unique records.
- Copy Unique Records Only: Check this option.
- Click OK
See the outline in below:
Application of Filter in Excel
- Filter by Criteria: Excel’s filtering feature allows users to display only the data that meets specific criteria. This helps in isolating relevant information quickly and efficiently.
- Sort Data: Filtering data in Excel enables users to sort it in ascending or descending order based on different attributes such as numbers, dates, or text. This aids in organizing data for better analysis.
- Hide Irrelevant Information: By filtering data, users can hide rows or columns containing irrelevant or unnecessary information, providing a cleaner view of the dataset.
- Explore Data Trends: Filtering allows users to focus on subsets of data, making it easier to identify trends, patterns, or outliers within the dataset.
- Perform Comparative Analysis: Excel’s filtering feature facilitates comparative analysis by allowing users to filter data based on different parameters and compare the results side by side.
- Extract Specific Information: Filtering data enables users to extract specific information or extract data subsets based on predefined criteria, making it easier to generate reports or analyze specific aspects of the dataset.
For ready-to-use Dashboard Templates:
