How to Clean Data in Google Sheets

Working with messy data is one of the most common frustrations in Google Sheets. You download a report, and instead of clear, ready-to-use information, you get a chaotic jumble of inconsistent names, extra spaces, and mixed-up date formats. This article provides a practical guide to the essential tools and techniques for cleaning your data directly within Google Sheets, so you can spend less time wrangling spreadsheets and more time finding insights.

What is Data Cleaning? (And Why Should You Care?)

Data cleaning, or data scrubbing, is the process of identifying and correcting - or removing - inaccurate, incomplete, duplicate, or improperly formatted data within a dataset. It's not the most glamorous part of data analysis, but it's arguably the most important. If you try to analyze messy data, you're going to get messy, unreliable results.

Clean data is the foundation for everything that comes next. It ensures:

Accurate Calculations: Formulas like SUMIF or COUNTIF can fail or give wrong answers if your data is inconsistent (e.g., trying to count "USA," "usa," and "U.S.A." as separate categories).
Reliable Reports: Your charts and pivot tables will accurately reflect reality, giving you the confidence to make informed decisions.
User Trust: When you share a report, your team needs to trust that the underlying information is correct.

Spending a little time cleaning your data upfront saves you from major headaches and flawed conclusions down the line. Think of it as tidying up your workshop before starting a new project - it makes the actual work smoother and more effective.

Start Here: Common Data Messes in Google Sheets

Before you can fix the problems, you have to know what to look for. Most data issues fall into a few common categories. A quick scan of your sheet will likely reveal some of these usual suspects:

Hidden Spaces: Extra leading or trailing spaces (e.g., " John Smith " instead of "John Smith") are invisible to the eye but can wreak havoc on filters and formulas.
Inconsistent Capitalization: People might enter data as "susan," "Susan," or "SUSAN." To your spreadsheet, these are three different entries.
Mixed Data Types: Numbers stored as text are a classic issue. They won't work in mathematical formulas and can sort incorrectly. You might also see dates formatted as plain text ("Sept 01, 2023") instead of a true date value.
Duplicate Records: Having the same person, order, or transaction listed multiple times can inflate your totals and skew your analysis.
Unwanted Characters: Sometimes, when you copy-paste data from a webpage or another app, strange non-printable characters or line breaks get included. These can create chaos in your cells.
Inconsistent Naming: Similar to capitalization, this involves variations in spelling or terminology, like "United States," "USA," and "U.S." for the same country.

Your Data Cleaning Toolkit: Essential Google Sheets Functions

Formulas are your best friends for cleaning data at scale. The typical workflow involves creating a new "helper" column next to the messy one, applying a cleaning formula, and then copying the clean values back over the original data. Let's look at the most useful functions.

1. Tidy Up with TRIM() and CLEAN()

These two functions are often the first step in any cleaning process.

TRIM() removes all extra spaces from text - leading spaces, trailing spaces, and multiple spaces between words - leaving only a single space between words.
CLEAN() removes all non-printable ASCII characters. These are the weird invisible characters that sometimes sneak in from copy-pasting.

You can even nest them together for a one-two punch. If your messy text is in cell A2:

=TRIM(CLEAN(A2))

This formula applies both functions at once, giving you a clean slate to work with.

2. Standardize Case with PROPER(), UPPER(), and LOWER()

Once your spacing is fixed, tackling inconsistent capitalization is next.

LOWER(text): Converts the entire text string to lowercase (e.g., "John Smith").
UPPER(text): Converts the entire text string to all caps (e.g., "JOHN SMITH").
PROPER(text): Capitalizes the first letter of each word, which is perfect for names (e.g., "John Smith").

For example, if you have a column of names in A and they're all over the place (JANE doe, john smith), you can standardize them in column B with:

=PROPER(A2)

3. Split a Column with SPLIT()

Ever get a "Full Name" column when what you really need is "First Name" and "Last Name"? The SPLIT() function is built for this. It splits text around a specified character or string (the "delimiter").

If cell A2 contains "John Smith," you can split it into two columns using a space as the delimiter:

=SPLIT(A2, " ")

Google Sheets will automatically populate the name into two adjacent cells ("John" in the current cell and "Smith" in the one to its right). This also works for splitting product IDs from names using a dash or splitting comma-separated tags.

4. Pinpoint Replacements with SUBSTITUTE()

The SUBSTITUTE() function is a lifesaver for making targeted changes. It replaces existing text with new text in a string.

A common use case is converting numbers stored as text into actual numbers by removing currency symbols or commas. If A2 contains "$1,250.00," this formula will strip the non-numeric characters:

=VALUE(SUBSTITUTE(SUBSTITUTE(A2, "$", ""), ",", ""))

Here, we nest two SUBSTITUTE functions - one to remove the dollar sign and another to remove the comma. We then wrap the whole thing in the VALUE() function to convert the resulting text string "1250.00" into a true number.

Using Built-in Features for Quick Fixes

Sometimes you don't need a formula. Google Sheets has several powerful menu options designed for data cleanup.

Find and Replace

Simple but effective. Use Ctrl + H (or Cmd + H on Mac) to open the Find and Replace dialog. This is perfect for standardizing categories. For example, you can find all instances of "U.S.A." and replace them with "USA" across your entire sheet in seconds. Use the "Search" dropdown to specify whether to search the whole workbook, just the current sheet, or a specific range.

Remove Duplicates

This feature does exactly what it says. It scans your data and removes entire rows that are exact duplicates.

Select the range of data you want to de-duplicate.
Go to the menu: Data > Data cleanup > Remove duplicates.
A dialog box will appear. If your data has a header row, check the box "Data has header row."
Select the columns you want Google Sheets to check for duplicates. If you want to remove rows where the entire row is identical, select all columns. If you just want to remove rows with a duplicate email address (regardless of another column's data), select only the email column.
Click "Remove duplicates." Sheets will tell you how many duplicate rows were found and removed.

Data Validation: Prevent Messy Data from Happening

The best way to deal with typos and inconsistent entries is to prevent them in the first place. Data Validation lets you do this by creating dropdown menus in your cells.

Let's say you have a "Category" column and you only want users to enter "Marketing," "Sales," or "Engineering."

Create a list of your allowed categories in a separate column or tab (e.g., in cells H1:H3).
Select the cell or range where you want the dropdown to appear (e.g., column C).
Go to Data > Data validation.
For "Criteria," choose "List from a range" and then select the cells with your categories (H1:H3).
Click "Save." Now, anyone trying to enter data in column C will have to choose from your pre-defined list, ensuring total consistency.

A Repeatable 5-Step Data Cleaning Workflow

With all these tools at your disposal, having a structured process will make your life much easier.

1. Make a Copy of Your Data

Never work on your original, raw data. The very first step is always to duplicate your sheet or create a copy of your file. This is your safety net. If you make a mistake, you can always go back to the original source.

2. Remove Duplicates and Blank Rows

Start with the big-picture cleanup. Use the "Remove Duplicates" feature to get rid of redundant rows. Then, sort one of your key columns to group all the blank rows together and delete them in one go.

3. Address Structural "Noise": Spaces and Case

This is where you'll use "helper columns." Create new columns and use TRIM(), CLEAN(), and PROPER() (or UPPER() / LOWER()) to standardize your text fields. Once you have a clean column of data, use Copy and then Paste special > Values only to replace the messy original column with the newly cleaned data. You can then delete the helper column.

4. Standardize Categories and Correct Data Types

Use Find and Replace to standardize text categories (e.g., changing all "CA" and "Calif." to "California"). Use the VALUE() or DATEVALUE() functions to convert text entries into proper numbers and dates that you can use in calculations and charts. You can also change the format of a column directly from the Format > Number menu.

5. Validate Your Work

Once you think everything is clean, do a final check. Use the filter controls on each column and look at the unique values listed. Do you see any oddities you missed? Creating a quick pivot table is another great way to sniff out inconsistencies. If you have a sales column grouped by country, a country named "united states" will stand out immediately if all the others are properly capitalized.

Final Thoughts

Cleaning data in Google Sheets doesn't have to be a nightmare. By combining a handful of powerful formulas with the software's built-in features, you can turn a chaotic spreadsheet into a clean, reliable dataset ready for analysis. Establishing a routine workflow is the key to doing it efficiently and consistently every time.

Of course, the need for manual cleaning often arises from downloading static CSVs or pasting data from different sources. This routine is not only time-consuming but happens every time you need an updated report. As we designed our platform, our goal was to eliminate this step entirely. With Graphed , we connect directly to your live data sources like Google Analytics, Shopify, or Salesforce, so the data is always clean, structured, and up-to-date. Instead of spending hours in a spreadsheet just preparing your data, you can instantly ask questions and build dashboards with fresh data in seconds.