EasetipsNtricks

Understand Google Sheets

Tips to add & import data in Google Sheets

Use Forms for automatic data entry

Instead of manually entering survey responses in a spreadsheet, use Forms. Create your survey in Forms and send responses instantly to a spreadsheet in Sheets. The spreadsheet is linked to your survey, so new responses appear in real time.

Learn how

Create a survey in Forms:

To create a survey, see Get started with Forms.

Send survey responses to a new spreadsheet:

  1. In Forms, on the Responses tab, click More and thenSelect response destination.
  2. Select Create a new spreadsheet.
  3. (Optional) To change the name, enter a new one.
  4. Click Create.

Send survey responses to an existing spreadsheet:

  1. In Forms, on the Responses tab, click More and thenSelect response destination.
  2. Select Select existing spreadsheetand thenSelect.
  3. Select your spreadsheet and click Select.

To open your spreadsheet from Forms, click View responses in Sheets . The spreadsheet opens in a new window. To go back to Forms, click Formand thenShow summary of responses or go back to the Forms window.

Use BigQuery data

You can access, analyze, visualize, and share billions of rows of data from your spreadsheet with Connected Sheets, the new BigQuery data connector.

  • Collaborate with partners, analysts, or other stakeholders in a familiar spreadsheet interface.
  • Ensure a single source of truth for data without additional .csv exports.
  • Streamline collaboration using delegated access.

Learn how

To understand how Connected Sheets works, you can explore connected sheet examples with public data. 

  1. On your computer, open the Google Sheets template gallery.
  2. Choose one of the connected sheet Examples.

Learn more at the Google Docs Editors Help Center

Compile data from separate spreadsheets

If you keep data in separate Google Sheets, you can copy a range of data from one spreadsheet to another with the IMPORTRANGE function.

For example, you may track quarterly sales data for a product in a different spreadsheet for each region. To combine all that quarterly sales data, copy the data from each region’s spreadsheet into a single spreadsheet using IMPORTRANGE.

Learn how

Import data from another spreadsheet

  1. In Sheets, open a spreadsheet.
  2. In an empty cell, enter =IMPORTRANGE.
  3. In parenthesis, add the following specifications in quotation marks and separated by a comma*:
    • The URL of the spreadsheet in Sheets. Copy and paste the URL from the spreadsheet that contains the data you want to import.
    • The sheet name (optional) and the range of cells to import.
    *Note: If you are in a Spanish-speaking country, use the semicolon as a separator instead of the comma.
  4. Press Enter.
  5. Click Allow access to connect the 2 spreadsheets.

For example: To import cells A1 through C10 from sheet 1 of the abcd123abcd123 spreadsheet, you enter: =IMPORTRANGE(“https://docs.google.com/spreadsheets/d/abcd123abcd123”, “sheet1!A1:C10”

Learn more at the Google Docs Editors Help Center

Create reports from Analytics data

Want to get insights from customers so you can make better business and marketing decisions? Use Google Analytics to see how visitors find and use your site—and keep them coming back. Then, use Analytics add-on to import your data into Sheets. After your data is in Sheets, you can clean it up, create graphs, and share your findings with your team.

Learn how

Get the Analytics add-on

  1. If you haven’t already, sign up to start using Analytics.
  2. Open Sheets.
  3. Click Extensionsand thenAdd-onsand thenGet add-ons.
  4. Search for the Google Analytics add-on and click Install.
  5. Click Continue.

Create an Analytics report

  1. Open a spreadsheet in Sheets.
  2. Click Extensionsand thenGoogle Analyticsand thenCreate new report.
  3. Enter your information and click Create Report. Your analytics data appears in a new spreadsheet.
  4. For help, see Google Analytics Spreadsheet Add-On.

Track stock prices or earnings

Monitor stock prices or earnings per share with the GOOGLEFINANCE function in Google Sheets.

  • See revenue trends and changes.
  • Compare prices and earnings with major competitors.
  • Predict future stock trends and directions.

Learn how

  1. In Sheets, open a spreadsheet.
  2. In an empty cell, type =GOOGLEFINANCE.
  3. In parenthesis, add any of the following, separated by a comma:
    • A ticker symbol in quotation marks.
    • (Optional) The attribute you want to show, such as price, in quotation marks.
    • (Optional) A start and an end date preceded by DATE and followed with the numerical date in parenthesis.
    • (Optional) Daily or weekly frequency in quotation marks.
  4. Press Enter.

For example: To list the Google stock price, taken at the same time daily for 2019, you type: =GOOGLEFINANCE(“GOOG”, “price”, DATE(2019,1,1), DATE(2019,12,31), “DAILY”)

Learn more at the Google Docs Editors Help Center

Find and update data

Searching for data and updating it doesn’t have to be time-consuming. You can quickly find and update text or numbers in Sheets.

Learn how

  1. In Sheets, open a spreadsheet and click Editand thenFind and replace.
  2. Next to Find, enter the text or numbers that you want to find.
  3. Next to Replace with, enter the new data.
  4. Next to Search, choose the sheets that you want to search.
  5. (Optional) To refine your search, select additional options.
    You can make your search case-sensitive, find exact matches, use regular expressions, or search within formulas.
  6. Choose an option to replace the data:
    • To replace instances one at a time, click Findand thenReplace.
    • To replace all instances, click Replace all.

Protect data from being updated

If you don’t want people to change the content in a spreadsheet, you can protect it. This shouldn’t be used as a security measure. People can print, copy, paste, and import and export copies of a protected spreadsheet. Only share spreadsheets with people you trust.

Tip: If “Protected sheets and ranges” isn’t visible, you’re likely in Microsoft Office editing. To use sheets and ranges protection, convert your file to Google Sheets. Learn about Microsoft Office editing and how to convert Microsoft Office files.

Learn how

Important: When you protect a sheet, you can’t:

  • Simultaneously lock the formatting of cells and allow users to edit input values
  • Protect data with a password
  1. Open a spreadsheet in Google Sheets.
  2. Click Data and then Protect sheets and ranges. A box will open on the right.
  3. Click Add a sheet or range or click an existing protection to edit it.
  4. To protect a range, click Range. To protect a sheet, click Sheet.
    • Range: To change or enter the range you’re protecting, click the spreadsheet icon and highlight the range in the spreadsheet.
    • Sheet: Choose a sheet to protect. If you want a set of cells to be unprotected in a sheet, check the box next to “Except certain cells.”
  5. Click Set permissions or Change permissions.
  6. Choose how you want to limit editing:
  • To show a warning when anyone makes an edit: Select “Show a warning when editing this range.” It doesn’t block people from editing, but they’ll see a message asking them to confirm if they really want to make an edit.
  • To choose who can edit the range or sheet: select “Restrict who can edit this range.” Choose:
    • Only you: Only you (and the owner if you’re not the owner) can edit the range or sheet.
    • Only domain: If you use Google Sheets for work or school, only people in your domain can edit the range or sheet. This option is only available when everyone in your domain can edit the spreadsheet.
    • Custom: Only the people you choose can edit the range or sheet.
    • Copy permissions from another range: Reuse the same permissions you set up on a different set of cells or sheet.
  1. Click Save or Done.

To see protected cells, click View and then Show and then Protected ranges. A striped background will appear over the cells.

Learn more at the Google Docs Editors Help Center

Automate tasks with macros

Save time by automating repetitive tasks with macros in Google Sheets. Say you need to format new data imports or build the same chart across multiple sheets of quarterly data. Repeating the same steps manually can take hours. Use a Sheets macro to record these actions and easily use them again without having to write code.

Learn how

  1. On your computer, open a spreadsheet at sheets.google.com.
  2. At the top, click Extensions and then Macros and then Record macro.
  3. At the bottom, choose which type of cell reference you want your macro to use:
  • Use absolute references: The macro will do tasks on the exact cell you record. For example, if you bold cell A1, the macro will only ever bold cell A1 regardless of which cell you clicked.
  • Use relative references: The macro will do tasks on the cell you select and its nearby cells. For example, if you record bolding cells A1 and B1, the macro can later be used to bold cells C1 and D1.
  1. Complete the task you want to record. When you’re done, click Save.
  2. Name the macro, create a custom shortcut, and click Save.
  • Note: When you create a macro in Google Sheets, an Apps Script is created. To edit this Apps Script, at the top, click Tools and then Script editor.

To perform a macro, click Extensions and then Macros and then the macro you want.

Learn more at the Google Docs Editors Help Center

Do more with add-ons & scripts

Use ready-made add-ons to do more with Gmail and Google Docs, Sheets, Slides, and Forms. You can also create your own scripts in any of the Docs editors with Google Apps Script.

  • Add Google Analytics to your spreadsheet.
  • Add a thesaurus to your document.
  • Add form responses in Docs to create a poll.
  • Add interactive questions to your presentation.
  • Manage sales and customer relationships directly in Gmail.
  • Add custom menus and windows with Google Apps Script.

Learn how

Install an add-on

  1. On your computer, open a document, spreadsheet, or presentation.
  2. Click Extensions and then Add-ons and then Get add-ons.
  3. Point to an add-on to find a short description. To find a full description, click the add-on.
  4. To install the add-on, click Install and then Continue.
  5. For most add-ons, a message will appear requesting access to data that the add-on needs to work. Read the message, then click Allow.
  6. After the add-on installs, click Done.

Create a script

  1. In Docs, Sheets, Slides, or Forms, open a document, spreadsheet, presentation, or form.
  2. If you’re using Docs, Sheets, or Slides, click Extensionsand thenApps Script.
  3. If you’re using Forms, in the top-right corner, click Moreand thenScript editor.
  4. Create your script.

For help, see Overview of Google Apps Script.

Learn more at the Google Docs Editors Help Center

Tips to format & clean up data

Add your logo to an invoice

Make your spreadsheets look consistent and professional by putting your organization’s logo at the top of invoices. Just add the image to the top row. Set the row to “freeze” so it remains visible as you scroll. 

Learn how

Add an image to a spreadsheet

  1. On your computer, open a spreadsheet in Google Sheets.
  2. Click the cell where you want to add an image.
  3. Click Insert and then Image. 
  4. Select to place your image in the cell or over the the cells. Cells with an image cannot also have text.
  5. Choose an image or take a snapshot.
  6. Click Open or Select.

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

 

Add checkboxes to spreadsheets

Use checkboxes to make Google Sheets more interactive. You can use checkboxes with charts, filters, pivot tables, and formulas.

  • Check off items in a to-do list
  • Track project tasks
  • Take attendance
  • Create dynamic charts

Learn how

  1. On your computer, open a spreadsheet in Google Sheets.
  2. Select the cells you want to have checkboxes.
  3. In the menu at the top, click Insert and then Checkbox.
  4. To remove checkboxes, select the checkboxes you want to remove and press Delete.

Learn more at the Google Docs Editors Help Center

Standardize data entries with lists

Reduce the chance of data-entry errors by limiting choices in Sheets. For example, if you have a status column, you can give choices, such as Done, In Progress, and Not Started. You specify the options and they appear in a drop-down list in each cell in the column.

Learn how

Create a list for data validation

  1. In Sheets, open a spreadsheet.
  2. Select the column where you want to add the options.
  3. Click Dataand thenData validation.
  4. Next to Criteria, select List of items.
  5. Enter the valid options separated by commas.
  6. Make sure the Show dropdown list in cell box is checked. 
  7. Select Show warning or Reject input to specify what happens if someone enters an invalid option.
  8. (Optional) To show a message to assist with validation, check the Show validation help text box and enter a message. For example: Please enter a valid value (Done, In Progress, Not Started).
  9. Click Save.
  10. (Optional) To see the choices, click the arrow in a cell under the column.

Validate email addresses

If your data involves entering email addresses, reduce entry errors by validating the email format in Sheets.

Learn how

Use data validation for email addresses

  1. In Sheets, open a spreadsheet.
  2. Select the column that will contain the email addresses.
  3. Click Dataand thenData validation.
  4. Next to Criteria, select Textand thencontains.
  5. In the text box next to contains, enter @.
  6. Select Show warning or Reject input to specify what happens if someone enters an invalid option.
  7. (Optional) To show a message to assist with validation, select Show validation help text and enter a message. For example: Please enter a valid email address, such as xxx@xxx.xxx.
  8. Click Save.

Split data into columns

You can split clearly defined data, such as text separated by commas, into separate columns in Sheets. For example, a single column with Last name, First name data can be split into 2 columns: Last name and First name.

Learn how

  1. On your computer, open a spreadsheet in Google Sheets.
  • If the data’s already in the sheet, select the cells you want to split.
  • If the data isn’t in the sheet yet, paste it.
  1. At the top, click Data and then Split text to columns.
  2. To change which character Sheets uses to split the data, next to “Separator” click the dropdown menu.
  3. To fix how your columns spread out after you split your text, click the menu next to “Separator” and then Detect automatically.

Note: After you paste the data, you can click Paste Paste and then Split text to columns.

Swap rows and columns

If you want to rotate what you have in columns to rows, or vice versa, you can do that using the TRANSPOSE function in Sheets. For example, you might want to swap column headings with row headings.

Learn how

  1. In Sheets, open a spreadsheet.
  2. In an empty cell, type =TRANSPOSE.
  3. In parentheses, enter the references to the rows or columns that you want to transpose.
  4. Press Enter.

For example:

To transpose rows 1 and 2 of columns A through E, you enter: =TRANSPOSE(A1:E2).

Space rows & columns evenly

In Sheets, you can resize all rows, or all columns, to use the same amount of space.

Learn how

Evenly space rows & columns

  1. On your computer, open a spreadsheet in Google Sheets.
  2. Select the rows or columns you want to resize.
    Tip: To select all rows and columns in the sheet, click the button in the top left corner of the sheet.
  3. To resize rows:
    1. Point to a row border on the numbered row bar. The pointer changes to a vertical arrow.
    2. To change the height of selected rows, drag the row border in the row bar.
  4. To resize columns:
    1. Point to a column border on the column-heading bar. The pointer changes to a horizontal arrow.
    2. To change the width of selected columns, drag the column border in the heading bar.

Automatically expand cells to show content

In Sheets, you can format cells to automatically expand to show content. You can also set content to flow into adjacent empty cells without expanding cell size.

  1. On your computer, open a spreadsheet in Google Sheets.
  2. Select the cells you want to format.
  3. Click Formatand thenWrapping, then select an option:
    • Overflow—Allow content to flow into adjacent empty cells. Overflow saves vertical space and shows as much content as possible.
    • Wrap—Automatically expand cells to show all content. Wrap lets you view all content in selected cells.
    • Clip—Hide content that doesn’t fit in existing cells. Clip shows only the content that fits in the cell.

Remove duplicate data & spaces

Duplication errors and spaces are a common problem that can be costly and easy to miss, especially in big data. 

  • Remove unwanted, duplicate data. Cells with identical values but different letter cases, formatting, or formulas are considered duplicates.
  • Remove extra leading spaces, trailing spaces, or excessive spaces from your data. This helps reduce problems when searching for data strings.

Learn how

Remove duplicate data

  1. In Sheets, open a spreadsheet.
  2. Select the data range that you want to remove duplicate data in.
  3. Click Dataand thenRemove duplicates.
  4. Select which columns to include and whether the data has headers.
  5. Click Remove duplicates.
  6. In the status window, click OK.

Remove extra spaces

  1. In Sheets, open a spreadsheet.
  2. Select the data range that you want to remove extra spaces in.
  3. Click Dataand thenTrim whitespace.
    Nonbreaking spaces aren’t trimmed.

Format currencies in your spreadsheet

If you’re working with international teams or clients, you can add international currencies to your financial spreadsheets in Google Sheets.

Learn how

  1. On your computer, open a spreadsheet in Google Sheets.
  2. Highlight the data you want to format.
  3. Click Format and then Number.
  4. Click Custom currency.
  5. Search in the menu text box to select a format. You can also add your own custom currency format into the text box.
  6. Click Apply.

You can also change a few properties about the currency (for example, how many decimal places to show) by clicking the drop-down menu in the right corner of the input box and choosing a desired option.

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.

  1. On your computer, open a spreadsheet in Google Sheets.
  2. Click Data and then 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.
  • 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.
  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 and then 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
  3. To find the filter options, go to the top of the range and click Filter Filter list.
    • Filter by condition: Choose conditions or write your own custom formulas.Custom formula examples
      • 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 and then 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 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 Formatand thenConvert 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   and thenexisting 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 and then 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 and then 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 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 and then 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 and then Chart and then 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 and then Link and then Select a range of cells to link Grid. 
  4. Select the range of cells you want to link to.
  5. Click Ok and then 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