How to filter data in Google Sheets

Spreadsheets hold a lot of power. While we spend more time than we’d care to admit calculating simple restaurant tips with our smartphones, spreadsheets can crunch complex formulas and huge data sets instantly. Even companies that use expensive software for their financials likely have employees who still use spreadsheets for at least some of their everyday responsibilities.

That’s because spreadsheets aren’t just powerful — they’re also versatile.

Still, many of us find it challenging to accomplish what advanced users would consider fairly simple tasks. Take filtering, for example — this is what you do when you need to pare down large data sets. You’ve probably heard of filtering, but have you actually used the feature in a spreadsheet? Probably not.

We’re going to change that today. Below, we walk through exactly how to filter data in Google Sheets. (If you’ve used Microsoft Excel, Google Sheets should feel familiar.) After you learn how to filter data, keep reading for a few tips to get the most out of your data filtering experience.

How to filter data in Google Sheets

When you have a large amount of data — like a 1,000-row customer list — you may want to view only a subset of rows. For instance, you may be interested in customers in your local area. That’s where filtering comes in — it can narrow down an exhaustive number of rows (like thousands) to something more manageable (like 25).

Google Sheets actually offers two methods for filtering: You can filter data for anyone viewing the spreadsheet or filter just for you — in this case, everyone else sees the spreadsheet unfiltered.

One situation where universal filtering is useful is when you want your entire sales team to focus on customers who have reached a certain stage of the sales funnel. Follow these steps to filter data in Google Sheets for every viewer:

1. Select your desired range of cells.
2. Click Data > Create a filter.
3. Use the Filter icon at the top of the range to see your filter options.

Alternatively, you can filter data just for you. You can use this option, for instance, when you’re interested in customers from a specific location. Here’s how to do it:

1. Click a cell (or range of cells) with data.
2. Select Data > Filter views > Create a new filter view.
3. Enter a name for your filter in the Name box just above the spreadsheet. You can then find the filter view anytime by selecting Data > Filter views.
4. Use the Filter icon in a column header to see the filter options. Your view will save automatically as you make changes.
5. Click OK after you’ve made your filter choices.
6. Repeat the previous two steps for each column you want to filter.

Google Sheets also has a help page with instructions on how to filter data.

Data filtering tips

Now that you know how to filter data in Google Sheets, check out the following tips on filtering data from avid Sheets users:

Tip no. 1: Use data validation

“Pairing your filtering efforts with data validation can add additional power to your spreadsheet,” says Mike Miller, editor in chief at Wilderness Times. This feature ensures that all cells use an exact match for entered data.

For example, if you’re developing a budget for your business, you may want all cells in the “Type” column to be entered as either “Revenue” or “Expense.” Data validation forces these entries to adhere to those exact spellings, so you (or another contributor) aren’t entering typos like “Revneu” or “Epence.” This helps you filter accurately.

Tip no. 2: Share your filtered views 

“The best part of filtered views is the ability to share them with other contributors,” says Muhammad Khan, digital marketing strategist at PureVPN. This allows you to have more informed discussions with colleagues or give your manager a quick way to see an issue you’ve identified in a given data set.

You can share a filtered view by opening it, copying the URL, and then sending the URL to the appropriate party.

Tip no. 3: Avoid color overload

“Opening up a rainbow-colored spreadsheet that requires a one-on-one tutorial before using it is not fun,” says Aaron Anderson, link building strategist at Linkpitch. He recommends you skip using colors to categorize data, especially if there are several categories in use. Instead, just add a “Category” column and enter the category names accordingly.

Background photo created by chevanon – www.freepik.com