{"id":192,"date":"2022-08-15T19:20:46","date_gmt":"2022-08-15T19:20:46","guid":{"rendered":"https:\/\/www.bizinfograph.com\/resource\/?p=192"},"modified":"2024-02-08T16:58:59","modified_gmt":"2024-02-08T16:58:59","slug":"how-to-do-filter-in-excel-with-shortcut-methods","status":"publish","type":"post","link":"https:\/\/www.bizinfograph.com\/resource\/how-to-do-filter-in-excel-with-shortcut-methods\/","title":{"rendered":"How to Filter in Excel \u2013 with shortcut methods?"},"content":{"rendered":"

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.<\/p>\n

What is Filter in Excel?<\/strong><\/h2>\n

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.<\/p>\n

How to add filter in excel?<\/strong><\/h2>\n

Step-1:<\/strong> Prepare a data table with data outlined below<\/p>\n

\"Filter<\/p>\n

Step-2:<\/strong> Select the range of data to filter, outlined below<\/p>\n

\"Filter<\/p>\n

Step-3:<\/strong> Select Filter from the Data menu<\/p>\n

\"Filter<\/p>\n

A dropdown arrow will appear to the right of each column header<\/p>\n

\"Insert<\/p>\n

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.<\/p>\n

For example: to view all male employee<\/strong>:<\/p>\n

Select the dropdown arrow appeared in Gender Cell<\/p>\n

\"Insert<\/p>\n

Filter result will be appeared, <\/strong>outlined below (Only Male Employees will be display<\/strong>)<\/p>\n

\"Insert<\/p>\n

How to Clear a filter in Excel?<\/strong><\/h2>\n

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.<\/p>\n

\"Insert<\/p>\n

Also, you can select the option \u201cClear Filter from Gender\u201d<\/strong> from Gender dropdown menu to clear the filter.<\/p>\n

How to apply filter in excel shortcut? \/ How to add filter in excel shortcut?<\/strong><\/h2>\n

Select any cell of data to filter<\/p>\n

Press \u2018CTRL+Shift+L<\/strong><\/p>\n

A dropdown arrow will appear to the right of each column header, outlined below.<\/p>\n

\"Insert<\/p>\n

How to apply multiple filter<\/strong><\/p>\n

For Example:<\/strong> to view all male employee who joined in 1 Jan 2022:<\/p>\n

Step-1:<\/strong> Select the dropdown arrow appeared in Gender<\/strong> Cell<\/p>\n

\"Insert<\/p>\n

How to apply multiple filter<\/strong><\/h2>\n

For Example:<\/strong> to view all male employee who joined in 1 Jan 2022:<\/p>\n

Step-1:<\/strong> Select the dropdown arrow appeared in Gender<\/strong> Cell<\/p>\n

\"Insert<\/p>\n

Filter result will be appeared, <\/strong>outlined below (Only Male Employee will be displayed<\/strong>)<\/p>\n

\"Insert<\/p>\n

Step-2: <\/strong>Select the dropdown arrow appeared in Date of Joining<\/strong> Cell<\/p>\n

\"Insert<\/p>\n

Filter result will be appeared, <\/strong>outlined below (Only Male Employee joined in 01 Jan 2022 will be display<\/strong>):<\/p>\n

\"Insert<\/p>\n

How to filter by text color in excel?<\/strong><\/p>\n

Step-1:<\/strong> Prepare a data table with information outlined below with blue font text color in Age field<\/p>\n

\"Insert<\/p>\n

Step-2:<\/strong>\u00a0 Select the dropdown arrow appeared in Age Cell<\/p>\n

\"Insert<\/p>\n

Filter result will be appeared, <\/strong>outlined below (Only font color blue in age column will be displayed<\/strong>)<\/p>\n

\"Insert<\/p>\n

How to filter by Cell Fill Color or filter by cell color in excel?<\/strong><\/p>\n

Step-1:<\/strong> Prepare a data table with information outlined below with green fill color in Gender field:<\/p>\n

\"Insert<\/p>\n

Step-2:<\/strong>\u00a0 Select the dropdown arrow appeared in Gender Cell<\/p>\n

\"Insert<\/p>\n

Filter result will be appeared, <\/strong>outlined below (Only Cell color Green in age column will be displayed<\/strong>)<\/p>\n

\"Insert<\/p>\n

How to use Custom Auto Filter in Excel?<\/strong><\/p>\n

Step-1:<\/strong> Prepare a data table with data outlined in below:<\/p>\n

\"Insert<\/p>\n

Step-2:<\/strong>\u00a0 Select the dropdown arrow appeared in Date of Joining<\/strong> Cell<\/p>\n

\"Insert<\/p>\n

Custom Autofilter<\/strong> window will be appear, outlined below<\/p>\n

\"Insert<\/p>\n

Step-3:<\/strong>\u00a0 Select the option from Date of Joining dropdown menu. Select equals <\/strong>from dropdown menu appear in left side.<\/strong><\/p>\n

Step-4:<\/strong>\u00a0 Select required date from the right side filed. Select 01 Jan 2022.<\/p>\n

Filter result will be appeared, outlined below (Only employee will display who joined 01 Jan 2022)<\/p>\n

\"Insert<\/p>\n

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<\/strong>. This will provide the desired result to analyze data.<\/p>\n

Note: Some of these conditions apply only to text and some others apply only to numbers.<\/p>\n

What is Advance filter in Excel? How to use Advance Filter in Excel?<\/strong><\/h2>\n

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.<\/p>\n

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.<\/p>\n

Example 1 \u2013 Extracting a Unique list using Advance Filtering<\/strong><\/p>\n

Step-1:<\/strong> Prepare a data table as outlined below<\/p>\n

\"Filter<\/p>\n

Step-2:<\/strong> Select the range of data to filter outlined below<\/p>\n

\"Insert<\/p>\n

Step-3:<\/strong> Select Advance Filter from the Data menu, outlined below:<\/p>\n

\"Insert<\/p>\n

An Advance Filter window will appear, outlined below\"Insert<\/p>\n

In the Advanced Filter dialog box, use the following details:<\/p>\n