How to Add Filter in Excel (4 Simple Methods)

Consider the following data as a product price list. We have added a filter to the data. The drop-down icons at the right-bottom corners of the column headers signal that the Filter command is added to this range. We can filter by values inside each column.

how to add filter in Excel

Why Add a Filter in Excel?

Adding filters in Excel can be beneficial to:

  • Manage large datasets;
  • Visualize specific data segments;
  • Identify and address data inconsistencies;
  • Avoid duplicate entries;
  • Easy data extraction.

4 Methods to Add Filter in Excel

Method 1 – Adding a Filter from the Data Tab

  • Select any cell within the range.
  • Go to the Data tab, choose the Sort & Filter group, and click on Filter.
  • adding filter in ExcelYou will see that arrow icons are shown beside the column headers.

filter icon in Excel

  • Click the drop-down icon.
  • Select Number Filters and pick Between. The selected column contains numbers. So, the Number Filters option is visible here.

selecting filter icon

  • The Custom Autofilter dialog box will appear.
  • Type the filter criteria and select OK. We filtered the values from 10-30 in the Quantity column.

defining number range

  • You will see that the Quantity values are shown for only the given range. Here, you will notice that the rows are hidden.

filtered rows

Method 2 – Adding a Filter from the Home Tab

  • Select any cell within the range.
  • Go to the Home tab, choose Sort & Filter, and select Filter.

adding filter from the Home tab

  • Click on the drop-down icon of your preferred column. We have selected the Category column to apply filters.
  • Select the item based on which you want to filter the data. You can select multiple items as well.
  • Click the OK button.

selecting text filters

  • The data is filtered based on the selected criteria.

filtered rows

Method 3 – Adding a Filter from the Context Menu

  • Select any cell within the range and right-click on it.
  • Go to Filter and select Filter by Selected Cell’s Value.

Right-click to Add Filter in Excel

  • Click on the Filter icon.
  • Select the item based on which you want to apply the filter. We selected the item Pasta from the Category column.
  • Click on OK.

selecting text filters

  • You will see only the products under the Pasta category.

filtered rows

Method 4 – Using a Keyboard Shortcut

  • Select any cell within the range.
  • Press Ctrl + Shift + L and the Filter option will be added to your data.

using keyboard shortcut

Download the Practice Workbook

Frequently Asked Questions

How to filter multiple columns in Excel?

To filter multiple columns in Excel, click on the filter arrow for each column you want to filter, then choose your filtering criteria for each column.

How to remove filter in Excel?

To remove a filter in Excel:

  1. Select any cell within the range.
  2. Go to the Data tab > Sort & Filter group > Filter.

How to filter blank cells in Excel?

To filter blank cells in Excel:

  1. Click on any cell within the range.
  2. Go to the Data tab > Sort & Filter group > Filter.
    This will add filter arrows to the headers of your data columns.
  3. Click on the filter arrow in the header of the column you want to filter.
  4. In the filter drop-down menu, uncheck the Select All option.
  5. Scroll down and check the box next to Blanks.

<< Go Back to Filter in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Mrinmoy Roy
Mrinmoy Roy

Mrinmoy Roy, a dedicated professional with a BSc in Electronics and Communication Engineering from Khulna University of Engineering & Technology, Bangladesh, brings over two years of expertise to the ExcelDemy project. As a prolific contributor, he has authored around 180 articles, showcasing his deep knowledge and passion for Microsoft Excel, Data Analysis, and VBA. His unwavering commitment to continuous learning, combined with versatile skills, renders him well-suited for roles in data management and spreadsheet solutions. He has interest... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF