Adding a filter takes three clicks and turns a static list into something you can search

A filter in Excel lets you hide rows that don't match what you're looking for, so you see only the data you need. You don't delete anything — you just temporarily hide it. To add a filter, select any cell in your data table, then go to the Data tab at the top and click Filter. Excel will add dropdown arrows to the header row of your table. Click any dropdown arrow to choose which rows to show or hide.

Filters work best when your data has a clear header row — the first row that names each column. If your first row contains actual data rather than labels, Excel may treat it as a header anyway, so check before you filter. Once a filter is active, you can click the dropdown arrow in any column to sort A to Z, Z to A, smallest to largest, or by specific values you select.

Key Takeaways

  • Click any cell in your data, then go to Data > Filter to add dropdown arrows to your header row.
  • Click a dropdown arrow and uncheck the values you want to hide; checked values are the ones that show.
  • You can filter by multiple columns at once — each filter narrows down what the others show.
  • Filtering hides rows but does not delete them; remove the filter to see everything again.
  • If your data has no header row, add one before filtering, or Excel will treat your first data row as headers.

Selecting your data before you filter

Excel is flexible about where you click before you turn on the filter. You can click the header cell, any cell in the middle of your data, or even a cell below it — as long as Excel can see a rectangular block of data, it will filter the whole thing. The safest approach is to click a cell inside your table, then click Data > Filter. Excel will automatically detect the edges of your data.

If your data is scattered across the sheet with gaps or irregular spacing, Excel may not detect it correctly. In that case, select the exact range you want to filter first — click the top-left cell, hold Shift, and click the bottom-right cell — then turn on the filter. This tells Excel exactly which rows and columns belong to your table.

Using the filter dropdown to show and hide rows

Once the filter is on, each header cell shows a small dropdown arrow. Click the arrow in any column to open a list of all the unique values in that column. Next to each value is a checkbox. Checked values are currently visible; unchecked values are hidden. To hide a row, uncheck the value. To show it again, check the value.

At the top of the dropdown list, you'll see All, which lets you quickly check or uncheck everything at once. Below that is a search box where you can type to find a specific value — useful if your list has hundreds of entries. At the bottom are sorting options: Sort A to Z, Sort Z to A, Sort Smallest to Largest, or Sort Largest to Smallest, depending on whether your column contains text or numbers.

Filtering by multiple columns at the same time

You can filter on more than one column, and each filter works together with the others. For example, if you filter the City column to show only "Boston" and the Status column to show only "Active", Excel will display only rows where the city is Boston AND the status is Active. Rows that are Boston but not Active will be hidden, and rows that are Active but not Boston will also be hidden.

Each column you filter shows a small funnel icon in its dropdown arrow to remind you that a filter is active on that column. If you want to remove a filter from one column but keep filters on others, click the dropdown arrow in that column and select All to show all values again. The other filters stay in place.

Sorting filtered data

Once you've filtered your data to show only what you need, you can sort it. Click the dropdown arrow in any visible column and choose Sort A to Z, Sort Z to A, Sort Smallest to Largest, or Sort Largest to Smallest. Excel will rearrange the visible rows in that order. The hidden rows stay hidden and don't move — they're not part of the sort.

If you sort by one column and then sort by another, the second sort overwrites the first. To sort by multiple columns at once — for example, by City first, then by Name within each city — use the Data > Sort button instead of the filter dropdown. That opens a dialog where you can add multiple sort levels.

Removing a filter or clearing all filters

To turn off the filter completely and show all rows again, go to Data > Filter and click it again. The dropdown arrows disappear, and all hidden rows reappear. Your data is unchanged — filtering never deletes anything.

If you want to keep the filter dropdowns but show all rows, click the dropdown arrow in any column and select All. Then do the same for every other column you've filtered. This unchecks all the hidden values and shows everything, but the filter buttons stay active so you can filter again without going back to the Data menu.

Common mistakes and how to avoid them

The most common mistake is forgetting that a filter is active. You'll look at your data and think rows are missing, when really they're just hidden. If you copy data while a filter is on, Excel copies only the visible rows, not the hidden ones. This is usually what you want, but it can surprise you if you weren't aware the filter was there. Check the column headers for the funnel icon to see if a filter is active.

Another mistake is trying to filter data that has no header row. If your first row contains actual data — like a customer name or a date — Excel will treat it as a header and won't let you filter it. Add a header row above your data first. You can also accidentally filter out your header row itself if you select it as part of your data range. When you select your data, make sure the header row is included but not treated as data.

Frequently Asked Questions

Can I filter by more than one value in the same column?

Yes. Click the dropdown arrow in that column, uncheck All, then check only the values you want to see. For example, you could show only rows where the Status is "Active" or "Pending" by unchecking "Closed" and "On Hold". All other columns' filters still explore.

What happens to my data when I filter it?

Nothing is deleted or changed. Filtering only hides rows temporarily. When you remove the filter, all rows come back exactly as they were. You can filter and unfilter as many times as you want without affecting your data.

Can I filter by a date range?

Yes. Click the dropdown arrow in a date column and you'll see options like Date Filters or Filter by Date. This opens a submenu where you can choose ranges like "This Month", "Last Quarter", or "Between [date] and [date]". You can also manually uncheck individual dates you don't want to see.

Why can't I see the filter dropdown arrows?

The filter may not be turned on. Go to Data > Filter to turn it on. If the arrows still don't appear, make sure you clicked a cell inside your data table first. If your data has no header row, add one — Excel needs headers to know where the filter should go.

Can I filter and then copy only the visible rows?

Yes. When a filter is active, selecting and copying data copies only the visible rows, not the hidden ones. This is useful when you want to extract a subset of your data into a new sheet or document. Make sure the filter is set up the way you want before you copy.