On this page
Expand all | Collapse all
Filter data in a spreadsheet
If you're working on a spreadsheet in Sheets with other people, you can create a filter view that only changes your view of the data. Or you can create filters that your collaborators can use, as well.
You can save and name multiple filter views for quick access and sorting later. You can also share the views so people immediately see the most relevant information.
- Share different filter view links with different people, so each person sees the most relevant information.
- Save and name multiple filter views for quick access and sorting later.
- Make a copy or create another view with similar rules.
- Don’t have edit access to a spreadsheet and still want to filter or sort? Create a temporary filter view.
Create a filter view only you can see
On a computer, you can filter data so the filtered data only applies to your view of the spreadsheet. Your filter view changes are automatically saved.
- On your computer, open a spreadsheet in Google Sheets.
- Click Data
Create filter view.
- Sort and filter the data.
- To save your filter view, at the top right, click Save View.
- Click Save.
Create a filter collaborators can see
Important:
- When you add a filter, anyone with access to your spreadsheet will find the filter too. Anyone with permission to edit your spreadsheet will be able to change the filter.
- The “Create a filter” option is unavailable under certain conditions. Learn more about these conditions.
- Your filter settings are saved, ensuring consistent filtering even if the data you filter on is removed. To reset filter settings, you can remove the filter from your data range.
- On your computer, open a spreadsheet in Google Sheets.
- To create a filter, select an option:
- Select a range of cells, then click Data
Create a filter.
- Right click on a cell or a range of cells, then click Create a filter.
The option to “Create a filter” can be unavailable due to:
- Formatting
- Check for any merged cells or hidden rows or columns in the sheet. Try to unmerge the cells and unhide the rows or columns.
- Check for any conditional formatting applied to the sheet.
- Go to Format.
- Select Conditional formatting.
- Make sure there are no rules applied.
- Data validation
- Check for any data validation rules applied to the sheet.
- Go to Data.
- Select Data Validation.
- Make sure there are no rules applied.
- If none of the above solutions work, you might not have access. To get access:
- Create a copy of the sheet.
- Check if the filter option is enabled in the copy.
- If it is, you can use the copy of the sheet instead.
- To find the filter options, go to the top of the range and click Filter
.
- Filter by condition: Choose conditions or write your own custom formulas.
Find unique values within a data range
- Select Custom formula is
- Type
=COUNTIF(data_range, data_range)=1
Find text matching “Good” or “Great” within a data range
- Select Custom formula is
- Type
=OR(REGEXMATCH(data_range, "Good"), REGEXMATCH(data_range, "Great"))
- You can filter cells that have valid or invalid data validation rules.
- Filter by values: To hide data points, uncheck the box next to the data point and click OK.
- To create a filter and filter by cell value, right click on a cell then click Filter by cell value.
- Search: Search for data points by typing in the search box.
- Filter by color: Choose which text or fill color to filter by. You can filter by conditional formatting colors, but not alternating colors.
- To remove the filter, select an option:
- Click Data Remove filter.
- Right click on any cell then click Remove filter.
Once filtered, at the bottom right users can find the number of rows being displayed out of the total rows in the table.
Learn more at the Google Docs Editors Help Center
Convert data to tables
In Google Sheets, tables can simplify data creation and reduce the need to repeatedly format, input, and update data by automatically applying format and structure to ranges of data.
Tables are well suited to track and organize information like:
- Project tracking
- Event planning
- Inventory management
There are 2 main parts of a table:
- For each column, you can set the appropriate column type. Your table ensures all data you enter aligns.
- You have access to a unified menu. You can manage table-level settings from the menu and perform actions like create a filter view for your table.
Convert existing data to a table
- On your computer, open a Sheet.
- Select a range of cells, either empty or with data.
- On the Menu Bar, click FormatConvert to table.
- Select the appropriate column type for each column.
Use tables views
With views, you can find the data you care about without impacting what others see on the sheet. You can use views to show or hide specific rows and apply other configurations to manage how to visualize data.
Each view can have its own unique configurations to filter and sort.
- To create new views, click Table menu
, and then select one of the following:
- Create group by view
- This allows you to see rows grouped together based on the field of your choice.
- Create filter view
Note: Temporary views disappear after spreadsheet refreshes.
- To apply existing views, click Table menu existing view name.
Learn more at the Google Docs Editors Help Center
Summarize data with pivot tables
Pivot tables in Sheets help you summarize data, find patterns, and reorganize information. You can add pivot tables based on suggestions in Google Sheets or create them manually. After you create a pivot table, you can add and move data, add a filter, drill down to see details about a calculation, group data, and more.
- Summarize thousands of rows of data, such as sales each year segmented by region.
- Drill down to analyze a region. Use a pivot table to see the total of each region’s sales. Then, get a breakdown of individuals and their sales for that region.
- Try it now: Pivot tables template
- On your computer, open a spreadsheet in Google Sheets.
- Select the cells with source data you want to use. Important: Each column needs a header.
- In the menu at the top, click Insert
Pivot table. Click the pivot table sheet, if it’s not already open.
- In the side panel, next to "Rows" or "Columns," click Add, then choose a value.
- Sometimes, you'll see recommended pivot tables based on the data you choose. To add a pivot table, under "Suggested," choose a pivot table.
- High confidence pivot table suggestions will be automatically inserted upon pivot table creation.
- To turn off pivot table suggestions:
- At the top, click Tools Suggestion controls.
- Turn off Enable Pivot table suggestions.
- In the side panel, next to "Values," click Add, then choose the value you want to see over your rows or columns.
- You can change how your data is listed, sorted, summarized, or filtered. Next to what you want to change, click the Down Arrow
.
Learn more at the Google Docs Editors Help Center
You can use this feature only if your organization supports it. For help, contact your administrator.
You can use Gemini to answer questions about the data in your sheet.
Important: This feature is currently only available via Gemini Alpha and Workspace Labs program.
- On your computer, open a spreadsheet from Google Sheets.
- At the top right, click Ask Gemini
.
- In the side panel, create your own prompt with references from your sheet or generic cell names. Examples of prompts:
- "Identify trends in this table."
- "How can I show regression and prediction of this data?"
- "What analysis can you help me perform with this sheet?"
- "Help me understand month-to-month food prices."
Learn more at the Google Docs Editors Help Center
Visualize data with charts
Summarize data in your spreadsheet with a chart.
- Make your data visual so that others can understand it quickly.
- Create charts based on your data that you can use in reports and presentations.
Navigate data with links
Make it easy to navigate your data In Sheets by adding links. Clicking the link can go straight to a specific range of cells. For example, you could link a sales table summary to the relevant financial data you want to highlight.
Search large datasets with QUERY
When you have large amounts of data in your spreadsheet, it can be tricky to find what you’re looking for. Filter, sort, add, and verify values in your data with the QUERY function in Sheets.
- In Sheets, open a spreadsheet.
- In an empty cell, type =QUERY.
- In parenthesis, add the following specifications separated by a comma:
- Cell range, separated by a colon, to perform the query on.
- Specific query to perform (using Google Visualization API Query Language).
- (Optional) Type a digit for the number of header rows at the top of the data.
- Press Enter.
Make quick calculations with formulas
Want to find the average, maximum, or minimum values in a dataset? Use a function in Sheets to instantly calculate these values. For financial analysis, keep track of specific stock prices and see their changes. Or, predict future stock trends using the GOOGLEFINANCE function.
Learn how at the Google Docs Editors Help Center
Freeze headings when scrolling
Working on a detailed project plan with your team, or entering a large amount of data for your accounts? Keep your column headings fixed in Sheets, so you always know what data you’re viewing.
Learn how: Get started with Sheets for Google Workspace
Related topics
Google, Google Workspace, and related marks and logos are trademarks of Google LLC. All other company and product names are trademarks of the companies with which they are associated.
Create filter view.
Create a filter.
.
Pivot table. Click the pivot table sheet, if it’s not already open.
.