Introduction to Cleaning Data in Google Sheets

This article serves as a comprehensive guide to numerous data cleaning procedures that can be performed using Google Sheets. While it does not cover every aspect of this robust platform, it does provide valuable insights that will enable you to carry out a majority of your data cleaning tasks effectively.

SOURCE: Google. (2019). "Top 10 tips to clean up data". [Article] Google Workspace Learning Center. Available at: [Accessed 18 March 2023]

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 to use forms

To create a survey in Forms, see Get started with Forms.

Send survey responses to a new spreadsheet:

Send survey responses to an existing spreadsheet:

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 to restrict data

3. Validate email addresses

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

Learn how to validate

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 to combine data

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 to find and update

You can make your search case-sensitive, find exact matches, use regular expressions, or search within formulas.

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 to split data

Paste and split data:

Split existing data:

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 to swap rows and columns

For example: To transpose rows 1 and 2 of columns A through E, type: =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 to 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 to remove spaces

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 to use keyboard shortcuts

To see a complete list of keyboard shortcuts, open Google Sheets and press "ctrl + /" if you are using Windows or Chrome OS, or "⌘ + /" if you are using macOS.

For the Visual Learners - Courtesy of Google

Understanding Data Cleaning

Named Functions Tutorial


Google Sheets offers a robust platform for data cleaning tasks, with a variety of features designed to streamline and enhance the process.

From automatic data entry with Forms, restricting data entry with lists, to combining data from several sheets into a single sheet, Google Sheets provides a comprehensive suite of tools for efficient data management.

On the other hand Flookup Data Wrangler enhances these capabilities by offering additional functions such as highlighting and removing duplicates, fuzzy matching, and more. By leveraging these tools and techniques, users can effectively clean their data, reducing errors and improving overall data quality.