Tips to analyze data

On this page

• Filter data in a spreadsheet
• Convert data to tables
• Summarize data with pivot tables
• Generate data analysis & insights with Gemini
• Visualize data with charts
• Navigate data with links
• Search large datasets with QUERY
• Make quick calculations with formulas
• Freeze headings when scrolling

               

Want advanced Google Workspace features for your business?

Try Google Workspace today!

 

 

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.

Learn how

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.

1. On your computer, open a spreadsheet in Google Sheets.
2. Click Data Create filter view.
3. Sort and filter the data.
4. To save your filter view, at the top right, click Save View.
5. 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.

1. On your computer, open a spreadsheet in Google Sheets.
2. 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.

   Learn why ”Create a filter” is unavailable

   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.
       1. Go to Format.
       2. Select Conditional formatting.
          • Make sure there are no rules applied.
     • Data validation
       • Check for any data validation rules applied to the sheet.
         1. Go to Data.
         2. 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:
       1. Create a copy of the sheet.
       2. Check if the filter option is enabled in the copy.
          • If it is, you can use the copy of the sheet instead.
3. 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.

     Custom formula examples

     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.
4. 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.

Learn how

Convert existing data to a table

1. On your computer, open a Sheet.
2. Select a range of cells, either empty or with data.
3. On the Menu Bar, click FormatConvert to table.
4. 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

Learn how

1. On your computer, open a spreadsheet in Google Sheets.
2. Select the cells with source data you want to use. Important: Each column needs a header.
3. In the menu at the top, click Insert Pivot table. Click the pivot table sheet, if it’s not already open.
4. 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:
     1. At the top, click Tools Suggestion controls.
     2. Turn off Enable Pivot table suggestions.
5. In the side panel, next to "Values," click Add, then choose the value you want to see over your rows or columns.
6. 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

 

Generate data analysis & insights with Gemini

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.

Learn how

Important: This feature is currently only available via Gemini Alpha and Workspace Labs program.

1. On your computer, open a spreadsheet from Google Sheets.
2. At the top right, click Ask Gemini .
3. 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.

Learn how

Create a chart

1. On your computer, open a spreadsheet in Google Sheets.
2. Select the cells you want to include in your chart.
3. Click Insert Chart.

Learn more at the Google Docs Editors Help Center

Insert a chart in your presentation

1. On your computer, open a document or presentation in Google Docs or Google Slides.
2. Click Insert Chart From Sheets.
3. Click the spreadsheet with the chart you want to add, then click Select.
4. Click the chart you want to add.
   • If you don't want the chart linked to the spreadsheet, uncheck "Link to spreadsheet."
5. Click Import.

Learn more at the Google Docs Editors Help Center

 

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.

Learn how

Link to a range of cells

1. Open a Google Sheet.
2. Click the cell you want to link.
3. Click Insert Link  Select a range of cells to link . 
4. Select the range of cells you want to link to.
5. Click Ok  Apply.

Learn more at the Google Docs Editors Help Center

 

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.

Learn how

1. In Sheets, open a spreadsheet.
2. In an empty cell, type =QUERY.
3. 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.
4. Press Enter.

Learn more at the Google Docs Editors Help Center

 

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

• Tips to clean up data
• Tips for finance 
• Get certified in data analytics

_{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.}