How to Put a Filter in Excel: A Complete Guide to Sorting and Filtering Data
Filtering in Excel is one of the most practical skills you can develop for working with spreadsheets. Whether you're managing a sales report, tracking inventory, or reviewing a list of contacts, filters let you zero in on exactly the rows you need — without deleting or rearranging anything else.
Here's everything you need to know about how filters work in Excel, the different ways to apply them, and the variables that affect how you'll use them.
What Does a Filter Actually Do in Excel?
When you apply a filter in Excel, you're telling the spreadsheet to temporarily hide rows that don't match your criteria. The data isn't deleted — it's just out of view. This makes filters non-destructive, meaning you can remove or adjust them at any time and your original data stays intact.
Filters work on columns. Each column header gets a dropdown arrow that lets you choose what to show or hide in that column. You can apply filters to one column or multiple columns simultaneously, narrowing your view as precisely as you need.
How to Add a Basic Filter in Excel
Step 1: Select Your Data Range
Click anywhere inside your data table. Excel is usually smart enough to detect the boundaries of your data automatically, but if your table has irregular structure, you may want to manually select the header row first.
Step 2: Turn On the Filter
Go to the Data tab on the ribbon, then click Filter. You'll see small dropdown arrows appear in each of your header cells. Alternatively, you can use the keyboard shortcut Ctrl + Shift + L (Windows) or Command + Shift + F (Mac) to toggle filters on and off quickly.
Step 3: Use the Dropdown to Filter a Column
Click the dropdown arrow on any column header. You'll see a list of options including:
- Sort options (ascending/descending)
- Filter by specific values — checkboxes for each unique entry in that column
- Search box — useful for long lists
- Filter type options — depending on your data type (text, numbers, dates)
Check or uncheck values to control what's visible, then click OK.
Types of Filters Available in Excel 🔍
Excel offers more than just simple checkbox filtering. The filter menu adapts based on what kind of data is in that column.
| Data Type | Filter Options Available |
|---|---|
| Text | Contains, begins with, ends with, equals, does not contain |
| Numbers | Greater than, less than, between, top 10, above average |
| Dates | This week, this month, last year, before/after a date, custom range |
| Custom | Build your own logic with AND/OR conditions |
These are called AutoFilter options — they're built in and available on any standard Excel installation. No extra setup required.
Number Filters in Practice
If your column contains sales figures, you might filter to show only transactions above a certain amount, or use the Top 10 filter to surface your highest-value entries. "Top 10" is a bit of a misnomer — you can actually set it to show any number of top or bottom values.
Text Filters in Practice
For a column containing city names, you could filter to show only rows where the city "contains" a specific string. This is helpful when entries aren't perfectly standardized.
Date Filters in Practice
Date filters are particularly dynamic. Options like "This Month" or "Last Quarter" update automatically based on the current date — useful for recurring reports.
How to Filter by Multiple Columns
You're not limited to filtering one column at a time. Each filter you apply stacks on top of the others, narrowing the result further. For example, you could filter a sales spreadsheet to show only rows where the region is "West" and the status is "Closed" simultaneously.
Each column showing an active filter will display a funnel icon on its dropdown arrow, making it easy to see at a glance where filters are applied.
Using Excel Tables vs. Plain Ranges
If you format your data as an Excel Table (Insert → Table, or Ctrl + T), filters are turned on by default and stay connected to your data automatically as it grows. This is generally the more robust approach for ongoing datasets.
With a plain cell range, filters are applied manually and don't automatically expand when you add new rows below the data. For static datasets this isn't an issue — but for anything you update regularly, tables offer meaningful advantages.
Clearing and Removing Filters
- To clear filters on one column: Click the dropdown arrow, then select "Clear Filter From [Column Name]."
- To clear all filters at once: Go to Data → Clear (in the Sort & Filter group).
- To turn off filters entirely: Click the Filter button again or press Ctrl + Shift + L.
Your data returns to full view immediately. 📊
Variables That Affect How Filtering Works for You
The steps above cover the standard approach, but a few factors shape how filtering behaves in practice:
Excel version matters. Older versions (pre-2010) have limited filter options, while Microsoft 365 and Excel 2019+ include additional capabilities like dynamic arrays that interact with filtered views in specific ways.
Data structure is critical. Filters work best when your data has a clean header row, no merged cells within the data range, and consistent data types within each column. Mixed data — like numbers and text in the same column — can cause filter categories to behave unexpectedly.
File format plays a role too. If you're working in a .csv file opened in Excel, filters work fine, but some formatting behaviors differ from native .xlsx files.
Shared or protected workbooks may restrict what filters users can apply or change, depending on how the sheet permissions are configured.
Excel for the web (the browser-based version) supports basic filtering but has fewer advanced filter options compared to the full desktop application.
How useful any given filter method is depends heavily on what your data looks like, how large the dataset is, and what version of Excel you're working in — which means the right approach looks a little different for every spreadsheet.