Bring your best ideas to life with Gemini for Google Workspace. Get tips & real-life use cases for using gen AI at work.

# Tips to analyze data

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

2. Click Data Filter views Create new 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

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.
3. To see 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"))`
• 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 see the number of rows being displayed out of the total rows in the table.

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

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

## Visualize data with charts

• 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

2. Select the cells you want to include in your chart.
3. Click Insert Chart.

#### Insert a chart in your presentation

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.
5. Click Import.

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

2. Click the cell you want to link.
4. Select the range of cells you want to link to.
5. Click Ok  Apply.

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

## 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

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.

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