Top 10 Tips for Cleaning Data in Google Sheets

Essential Tips for Reliable Data

Quick Checklist

Clean, well-structured data is essential for accurate analysis and decision-making. Flookup Data Wrangler makes it easier to standardise, validate and automate your data cleaning workflows in Google Sheets.

This article covers essential data cleaning tasks such as validation, deduplication, standardisation and automation, helping you get the most out of Google Sheets and Flookup Data Wrangler.

While it does not cover every aspect of this robust platform, it provides valuable insights that will enable you to carry out a majority of your data cleaning tasks effectively.

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.

How to Use Forms

• To create a survey in Forms, see How to use Google Forms.
• Send survey responses to a new spreadsheet: In Forms, on the Responses tab, click "More" and then "Select response destination".
• Send survey responses to an existing spreadsheet: In Forms, on the Responses tab, click "More" and then "Select response destination". Navigate to Select existing spreadsheet > Select. Choose your spreadsheet and click "Select".

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.

For data that is already entered inconsistently, Flookup's =NORMALIZE() function can standardise existing text entries by removing punctuation, unwanted words and diacritical marks, bringing messy data into a consistent state without manual cell-by-cell editing.

How to Restrict Data

1. In Sheets, open a spreadsheet.
2. Select the column where you want to add the options.
3. Click Data > Data 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. Optionally, 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. To see the choices, optionally click the arrow in a cell under the column.

3. Validate Email Addresses

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

How to Validate

1. In Sheets, open a spreadsheet.
2. Select the column that will contain the email addresses.
3. Click Data > Data validation.
4. Next to Criteria, select Text > contains.
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 "user@example.com".
8. Click "Save".

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.

After combining data from multiple sources, Flookup's =FLOOKUP() function can reconcile records across the merged datasets, identifying customer or product entries that appear under different names in different source sheets.

How to Combine Data

1. In Sheets, open a spreadsheet.
2. In an empty cell, enter =IMPORTRANGE.
3. In parentheses, add the following specifications in quotation marks and separated by a comma.
4. Press Enter.
5. Click "Allow access" to connect the two spreadsheets.

5. Find and Update Data

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

How to Find and Update

1. In Sheets, open a spreadsheet and click Edit > Find 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. Optionally, refine your search by selecting 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 "Find > Replace" and to replace all instances, click "Replace all".

With these search and update tools, you can keep your data accurate and up-to-date with minimal effort.

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 two columns: Last name and First name.

How to Split Data

• 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 > Split 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 Data > Split 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.

How to Swap Rows and Columns

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".
5. For example: To transpose rows 1 and 2 of columns A through E, type: =TRANSPOSE(A1:E2) Inside the appropriate cell.

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.

How to Remove Duplicates

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

Google Sheets' native tool only catches exact duplicates. For near-duplicates like "Acme Corp" versus "Acme Corporation", use Flookup's =DEDUPE() formula to catch records that are similar but not identical using adjustable similarity thresholds.

Leveraging Flookup for Advanced Data Cleaning

While the tips above cover the basics, Flookup Data Wrangler extends Google Sheets with fuzzy matching, semantic matching and automation features that handle the cases native tools cannot:

• Fuzzy deduplication: Use =DEDUPE() for fuzzy deduplication, with configurable threshold control.
• Text standardisation: The =NORMALIZE() formula removes punctuation, stop words and diacritics in a single cell formula.
• Cross-sheet matching: =FLOOKUP() reconciles records across separate sheets or workbooks using fuzzy lookup.
• Scheduled cleaning: Flookup's Schedule Functions run deduplication and standardisation on an hourly or daily cadence without manual intervention.

For a comprehensive guide on how advanced tools transform data cleaning, refer to Custom Functions Documentation.

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.

For more thorough cleaning beyond whitespace, Flookup's =NORMALIZE() function also removes punctuation marks, unwanted words and diacritical marks in a single pass. Useful when importing data from external systems with inconsistent formatting.

How to Remove Spaces

1. In Sheets, open a spreadsheet.
2. Select the data range that you want to remove extra spaces in.
3. Click Data > Trim whitespace.
4. Nonbreaking spaces are not 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.

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.

By mastering these shortcuts, you will save time and streamline your data cleaning process.

Frequently Asked Questions

What is the most important data cleaning step in Google Sheets?

Start by removing duplicate records, as duplicates affect every downstream analysis. Use Google Sheets' built-in Remove Duplicates tool or Flookup's fuzzy deduplication to identify both exact and near-duplicate rows before performing any other cleaning operations.

How can I quickly identify inconsistent data in Google Sheets?

Use conditional formatting to highlight blank cells, outliers and values that deviate from expected patterns. Data validation rules can also flag entries that do not conform to specified formats. For more advanced detection, Flookup's profiling tools surface inconsistencies across columns automatically.

Should I clean data in the original sheet or a copy?

Always work on a copy of your data. Keeping the original intact allows you to verify that cleaning operations did not introduce errors and provides a fallback if mistakes occur. Create a dedicated "cleaning" sheet or workbook for each round of data cleaning.

You Might Also Like

• Fuzzy Matching Algorithms Explained
• A Guide to Data Cleaning in Google Sheets
• Custom Functions Documentation
• How to Automate Data Cleaning in Google Sheets With Macros