- Create a filter in Google Sheets, step by step
- The four ways to narrow a column
- The catch: a normal filter changes the sheet for everyone
- Filters versus filter views at a glance
- Filtering by condition with a custom formula
- Why your filter might look wrong
- Filtering on your phone
- What I'd do
- Frequently asked questions
To filter in Google Sheets, select your data, then click Data and Create a filter. A filter icon appears in each header cell: click it to filter by values, by condition or by colour. If you share the sheet, use Data and Create filter view instead, so your filter doesn't change what everyone else sees.
My client lists in Google Sheets run to hundreds of rows, and I only ever need a slice of them at once: the week's bookings, the people who haven't replied, the leads from one event. That's what a filter is for. It hides the rows you don't want right now without deleting anything, and you can switch it off in a click.
Create a filter in Google Sheets, step by step
- Open your spreadsheet on a computer and click any cell inside your data, or select the exact range you want to filter.
- Click Data, then Create a filter. You can also right click a cell and choose Create a filter.
- Look at your header row. Each column now has a small filter icon on the right.
- Click the icon on the column you want to narrow down and pick how you want to filter (more on the options below).
- Click OK. The rows that don't match are hidden, not deleted.
- To switch it off, click Data, then Remove filter.
Google adds a nice touch here: once a filter is on, the bottom right of the screen shows how many rows are being displayed out of the total. It's a quick sanity check that you haven't hidden more than you meant to.
The four ways to narrow a column
| Option | What it does | Good for |
|---|---|---|
| Filter by values | Untick the entries you want to hide | Showing one client, one status or one product line |
| Filter by condition | Rules such as text contains, date is after, greater than, or your own custom formula | Dates, numbers and anything with a pattern |
| Search | Type to find an entry in the values list | Long columns where scrolling the tick list is painful |
| Filter by color | Show rows with a chosen text or fill colour | Sheets where you colour-code follow-ups |
Filter by color is handy if you highlight rows by hand, and Google says it works with conditional formatting colours too. It doesn't work with alternating colours, so banded rows won't show up as a filter option.
The catch: a normal filter changes the sheet for everyone
This is the bit most people miss. Google's help page is clear that when you add a filter, anyone with access to your spreadsheet sees that filter too, and anyone who can edit can change it. So if you filter a shared team sheet down to your own rows, your colleague opens it and wonders where half the data went.
The fix is a filter view. Click Data, then Create filter view, set up your sort and filters, and click Save View. A filter view only applies to your view of the spreadsheet. Several people can use different filter views at the same time, you can name each one, and you can share a view with someone when you want them to see exactly what you see.
Filters versus filter views at a glance
| Question | Plain filter | Saved view |
|---|---|---|
| Who sees it | Everyone with access | Only you, unless you share it |
| Can you save several | One per range | Yes, and you can name them |
| Works with view-only access | No | Yes, as a temporary view that isn't saved |
| Best for | Your own sheets | Shared team or client sheets |
One small limit worth knowing: you can't change the order of filter views once you've made them. If you build a lot of them, give them clear names so the list still makes sense.
Filtering by condition with a custom formula
When ticking values isn't enough, choose Filter by condition and pick Custom formula is. You write a formula that returns true for the rows you want to keep. For example, if column C holds a status, a formula along the lines of =C2="Booked" keeps only the booked rows. Start the reference on your first data row, not the header, and Google applies it down the column.
If formulas aren't your thing, the built-in conditions cover most everyday jobs: text contains, text starts with, date is before or after, is empty and so on.
Want AI doing the heavy lifting in your marketing?
I build the systems that handle the boring 80 percent, so you get your week back. Done properly, with the human kept in.
Why your filter might look wrong
- Blank rows inside your data. If you clicked one cell rather than selecting a range, a gap can stop the filter at the wrong place. Select the full range and create the filter again.
- Old settings hanging around. Google saves filter settings even if the data you filtered on has been removed. If results look odd, remove the filter from the range and start fresh.
- Formulas pointing at filtered cells. Sorting a filtered range doesn't rewrite your formulas. Google's example is that =B2 stays =B2 even when B2 sits inside the filtered range.
- The option is greyed out. Google notes that Create a filter is unavailable under certain conditions, so check you're not inside a protected or special range.
Filtering on your phone
You can open filtered sheets in the Google Sheets app and the hidden rows stay hidden. For building or tweaking filters, though, I find a computer far easier: the filter menus and the filter view controls are much more comfortable with a mouse. If you're doing your weekly admin in Sheets, it's worth one sit-down session to set up the filter views you'll reuse.
What I'd do
If the sheet is only yours, use a plain filter and remove it when you're done. If anyone else touches the sheet, and with client lists someone usually does, build filter views and name them by the job, such as 'Follow up this week' or 'Event leads'. It takes two minutes and saves the awkward 'where's my data gone?' message. If you want the list in a set order before you filter it, my guide on sorting in Google Sheets covers that, and freezing your header row keeps the filter icons in view as you scroll.
Frequently asked questions
How do I filter in Google Sheets?
Select your data, click Data, then Create a filter. Click the filter icon in a header cell and choose by values, condition, search or colour.
Can other people see my filter in Google Sheets?
Yes. A normal filter shows for everyone with access, and editors can change it. Use a filter view if you want a filter only you see.
How do I remove a filter in Google Sheets?
Click Data, then Remove filter, or right click any cell and choose Remove filter. Your data comes back untouched.
Can I filter a Google Sheet I can only view?
Yes. If you only have view permission, Sheets creates a temporary filter view that only you can use, and it isn't saved.
Can I filter by colour in Google Sheets?
Yes. Filter by color shows rows with a chosen text or fill colour, including conditional formatting colours, but not alternating colours.
