Creating a basic filter in three steps

A filter in Google Sheets lets you hide rows that don't match what you're looking for, so you see only the data you need. You create one by selecting your data, clicking a button, and then choosing what to show or hide. The filter stays in place until you remove it, and it doesn't delete anything — it just hides rows temporarily.

Here's how to set one up. First, click any cell in the table you want to filter. You don't need to select the whole table — Google Sheets will figure out where your data starts and stops. Then go to the Data menu at the top and click Create a filter. You'll see small dropdown arrows appear in the header row of your table.

Click any of those dropdown arrows to open the filter menu for that column. You'll see a list of all the values in that column, with checkboxes next to each one. Uncheck the boxes for anything you want to hide, then click OK. The rows with those values disappear from view, and a blue funnel icon appears in the column header to show a filter is active.

Key Takeaways

  • Click any cell in your data, then go to Data menu and select Create a filter to add dropdown arrows to your header row.
  • Click the dropdown arrow in any column, uncheck the values you want to hide, and click OK to filter that column.
  • You can filter multiple columns at once — each filter works together to show only rows that match all your choices.
  • Filters hide rows but don't delete them, and you can remove a filter anytime by going to Data menu and clicking Remove filter.
  • Use the search box inside a filter menu to quickly find a specific value when a column has hundreds of entries.

Filtering by text, numbers, and dates

The way you filter depends on what kind of data is in the column. For text columns — like names or categories — you see a simple list of all the unique values, and you check or uncheck the ones you want. For number columns, Google Sheets offers extra options like "greater than" or "between," which let you set a range instead of picking individual numbers.

To use a number filter, click the dropdown arrow in a number column and look for the option that says Filter by condition. A menu appears with choices like "greater than," "less than," "between," and "equals." Pick the one you need, type in your number or numbers, and click OK. For example, if you have a sales column and want to see only sales over $500, you'd pick "greater than" and type 500.

Date columns work the same way. Click the dropdown, choose Filter by condition, and you'll see options like "is after," "is before," and "is between." Pick your condition and enter the date. Google Sheets accepts dates in most common formats, so you can type "January 15" or "1/15/2024" and it will understand.

Using multiple filters at the same time

You can filter more than one column at once, and the filters stack on top of each other. If you filter the "Region" column to show only "West" and also filter the "Status" column to show only "Completed," you'll see only rows where the region is West AND the status is Completed. All other rows stay hidden.

To add another filter, just click the dropdown arrow in a different column and make your choices. The blue funnel icon appears in every column that has an active filter. If you want to change or remove one of the filters, click its dropdown arrow again — your previous choices are still there, and you can adjust them.

If you filter so strictly that no rows match all your conditions, Google Sheets shows you an empty table with just the header row. This usually means you've hidden everything by accident. Click the dropdown arrows to check your filters, or go to the Data menu and click Remove filter to start over.

Clearing and removing filters

To turn off a single filter without removing all of them, click the dropdown arrow in that column and select Clear. All the checkboxes will recheck themselves, and all the rows in that column will reappear. The filter still exists — you're just showing everything again.

To remove the filter completely and get rid of the dropdown arrows, go to the Data menu and click Remove filter. This deletes all your filter settings and returns the sheet to normal. Your data doesn't change — you're just removing the filtering tool.

Sorting while a filter is active

You can sort your data while a filter is on, and the sort only affects the rows that are currently visible. Click the dropdown arrow in any column and look for the sort options at the top of the menu — usually "Sort A to Z" or "Sort Z to A" for text, and "Sort ascending" or "Sort descending" for numbers. Click the one you want, and the visible rows rearrange.

The hidden rows stay hidden and don't move. So if you filter to show only "West" region and then sort by sales amount, you're sorting only the West region rows. This is useful when you want to see your filtered data in a specific order — like the highest sales in the West region first.

Common mistakes to avoid

The most common mistake is forgetting that a filter is on and thinking data has been deleted. If rows suddenly disappear, check the Data menu — if Remove filter is an option, a filter is active. Click it to see all your data again. Your information is still there.

Another mistake is filtering the wrong column by accident. If you're looking for a specific value and can't find it, check all the columns with blue funnel icons. One of them might be hiding the rows you need. Click each dropdown to see what's filtered out.

A third issue happens when your header row isn't actually a header — if the first row of your table contains data instead of column names, Google Sheets might treat it as a header anyway and hide it when you create a filter. If this happens, go to the Data menu, click Remove filter, then select your actual header row before creating the filter again.

Frequently Asked Questions

Can I filter by color or formatting?

Yes. Click the dropdown arrow in any column and look for Filter by color. You'll see all the colors used in that column, and you can choose to show only cells with a specific color or background. This works if you've manually colored cells or used conditional formatting.

What happens to formulas when I filter?

Formulas that reference the filtered column will still calculate based on all the data, including hidden rows. If you want a formula to calculate only the visible rows, you need to use a different function like SUBTOTAL, which ignores hidden rows by default.

Can I save a filter so it appears every time I open the sheet?

Yes. Once you create a filter and set it the way you want, just save the sheet normally. The next time you open it, the filter will be there with the same settings. If someone else opens the sheet, they'll see the filter too, but they can change it without affecting your saved version.

How do I filter for empty cells?

Click the dropdown arrow in the column and look for an option that says "Empty" or shows a blank checkbox. Check only that box and click OK. All rows where that column is blank will appear, and all rows with data in that column will hide.

Can I filter text that contains a specific word?

Yes. Click the dropdown arrow and select Filter by condition. Choose "contains" from the menu, type the word you're looking for, and click OK. All rows where that column contains that word will show, even if the word is part of a longer phrase.