10 Google Workspace tips to clean up data

Want to get more out of Google apps at work or school?  Sign up for a free Google Workspace trial

Data analysis tools, such as a tablet with a chart and a ruler

" "

Show all instructions | Hide all instructions

1

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.

Select response destination

To open your spreadsheet from Forms, click View responses in Sheets 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.

 

2

Restrict data entry 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
  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.
On a sheet, by Criteria under Data validation, enter List of items, status choices like Done, and check the box

3

Validate email addresses""

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

Learn how
  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.
Enter Text, contains, and @ by Criteria under Data validation on a sheet

4

Combine data from several sheets into a single sheet""

If you have data in separate spreadsheets, you can copy a range of data from one spreadsheet to another. For example, you can track quarterly sales data for a product in a different spreadsheet for each region. To combine all the quarterly sales data, copy the data from each region's spreadsheet into a single spreadsheet in Sheets.

Learn how
  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.
    • 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")

5

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.
Find and replace appears under the File option in the menu

6

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

Paste and split data:

  1. In Sheets, open a spreadsheet and paste the data that you want to split into columns.
  2. Next to the cell where you pasted the data, click Paste formatting Pasteand thenSplit text to columns.
  3. If you want Sheets to detect when a file is formatted using fixed-width, select Detect automatically.

Split existing data:

  1. In Sheets, select the column that contains the data that you want to split.
  2. Click Dataand thenSplit text to columns.
  3. If you want Sheets to detect when a file is formatted using fixed-width, select Detect automatically.

7

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

8

Remove duplicate data""

Duplication errors are a common problem that can be costly and easy to miss, especially in big data. Use Sheets to remove any unwanted, duplicate data. Cells with identical values but different letter cases, formatting, or formulas are considered duplicates.

Learn how
  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.
On a sheet, click Data, then Remove duplicates. Select cells, then click Remove duplicates

9

Remove extra spaces""

When you copy and paste data, sometimes extra spaces are accidentally pasted. Extra spaces can cause problems when searching for data strings. Remove extra leading spaces, trailing spaces, or excessive spaces from your data in Sheets.

Learn how
  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.

10

Clean up data faster with keyboard shortcuts""

Instead of using your mouse, work faster with keyboard shortcuts. For example, you can quickly select, group, or hide rows or columns or format data in cells.

Learn how

Sheets

Was this helpful?
How can we improve it?