Google Sheets Data Cleaning: Tools, Steps, and Tips

Google does not make a product called Google Excel: its spreadsheet app is Google Sheets, and it includes tools for cleaning data. In Sheets, start with Data > Data cleanup to review cleanup suggestions, remove duplicate rows, or trim whitespace; Excel has comparable tools, plus Power Query for repeatable cleanup workflows.

What people mean by Google Excel

People who search for “Google Excel” usually mean one of two things: cleaning a spreadsheet in Google Sheets, or finding Excel-like cleanup features in Google’s app. Google Sheets and Microsoft Excel are separate products. Sheets runs in a browser and supports collaborative editing; Excel is available as a desktop app, a web app, and mobile apps. Both can import and export common spreadsheet formats, including .xlsx, but features and formatting may not transfer perfectly.

Cleaning means making values consistent and dependable before sorting, analyzing, charting, or sharing them. A contact list might contain extra spaces, inconsistent capitalization, duplicate people, phone numbers stored in different formats, or dates that are actually text. These problems can make a sheet look acceptable while causing filters, counts, and calculations to give misleading results. The right cleanup method depends on whether you need a quick one-time fix or a process you can repeat when new data arrives.

Start with a safe copy and a clear goal

Before changing a sheet, make a copy or duplicate the tab. In Google Sheets, use File > Make a copy for a separate file, or use the sheet-tab menu to duplicate a worksheet. In Excel for Microsoft 365 on Windows, save a copy of the workbook or duplicate the worksheet. This matters because removing duplicates or replacing values can change many cells at once, and undo history is not a reliable long-term backup.

Decide what “clean” means for the data before applying a tool. For a customer table, you may want one row per customer ID, but two people with the same name are not necessarily duplicates. For a transaction table, two identical-looking rows may represent two legitimate purchases. Keep a record of the original row count and identify the column or combination of columns that defines a duplicate.

Check whether the first row contains column headings, and include those headings when you select a table if the tool asks.
Look for blank rows, merged cells, hidden columns, and formulas before sorting or removing data.
Choose a stable identifier, such as an account number, rather than relying only on a person’s name.
Test cleanup on a small copy of the data, then verify the result before replacing the original.

Clean data in Google Sheets

For a typical one-time cleanup, select the relevant range or table, then open Data > Data cleanup. The available commands include Cleanup suggestions, Remove duplicates, and Trim whitespace. Menu labels and available suggestions can change as Google updates Sheets, but the underlying idea is to preview changes and apply only the ones that match your goal.

Review Cleanup suggestions. Sheets can flag issues such as inconsistent formatting or duplicate values and show suggested corrections. Treat suggestions as prompts, not unquestionable instructions: a repeated value may be valid, and a proposed formatting change may not fit your data rules.
Remove duplicates. Select the table, choose Data > Data cleanup > Remove duplicates, confirm whether the range has a header row, and select the columns that define a duplicate. If you select only an email column, rows with the same email can be treated as duplicates even if other columns differ. Review the remaining rows and compare the count with your expected result.
Trim whitespace. Select the cells and choose Data > Data cleanup > Trim whitespace. This can remove leading or trailing spaces and reduce excess spaces that interfere with matching or sorting. It does not decide whether two different-looking spellings refer to the same person, and unusual invisible characters may still need separate handling.
Split combined values. To divide text such as “North,West” into separate columns, select the cells and use Data > Split text to columns. Choose a separator or let Sheets detect one, then check the preview. Make sure the destination columns are empty, since splitting can overwrite cells to the right.

For replacements, use Edit > Find and replace and inspect the options before replacing all matches. A search for “St.”, for instance, could change both “St. Paul” and an unrelated text field. Search within the intended range when possible, and consider whether case-sensitive matching or regular expressions are appropriate for the task.

Standardize values without losing meaning

Trimming spaces is only one part of standardization. Names may have inconsistent capitalization, dates may use different conventions, and phone numbers may contain punctuation. Choose a target format that preserves meaning. A phone number is often best stored as text, because treating it as a number can remove a leading zero or misinterpret a country code. Dates should be real date values, not merely text that looks like a date.

Before changing a column, consider what downstream users expect. If “NY,” “New York,” and “N.Y.” mean the same place, use a deliberate mapping table or controlled list rather than an indiscriminate find-and-replace. Similarly, do not convert product codes to numbers if leading zeros are significant. In Sheets, number format controls how a value displays, while the underlying value determines how it sorts and calculates.

It is also useful to separate correction from interpretation. Correcting an accidental extra space is usually safe; deciding that two different spellings identify the same customer requires a business rule. Keep an “original” column when the source value may need to be audited, and put the cleaned value in a new column until the results have been checked.

Excel options for a Google Sheets-style cleanup

In Excel for Microsoft 365 on Windows, use Data > Remove Duplicates to remove repeated records from a selected range. Confirm whether the first row contains headers, then choose the columns that define a duplicate. Excel keeps the first occurrence in the selected range and removes later matching rows, so sort deliberately if a particular record should be retained. On Mac, the command is also on the Data tab, though the ribbon layout can differ; Excel for the web may present a more compact ribbon.

Excel’s Flash Fill can recognize a pattern when you type an example in the neighboring column, then choose Data > Flash Fill or press Ctrl+E on Windows. For example, typing a first name extracted from a full name may prompt Excel to fill the rest of the column. Flash Fill creates values rather than a live transformation, so check the results, especially where names or formats are inconsistent. On Mac, the keyboard shortcut can differ by keyboard and Excel version; use the Data tab’s Flash Fill command if Ctrl+E is unavailable.

For repeatable imports, Power Query is often the stronger option. In Excel for Microsoft 365 on Windows, select a cell in a source table and choose Data > From Table/Range to open the Power Query Editor. There you can remove columns, filter rows, split columns, change data types, trim text, and remove duplicates, then load the result back into the workbook. The transformation steps are saved, so refreshing the query can apply them again when the source data changes. Availability and some features differ by Excel platform and version.

Excel formulas for specific cleanup checks

Formulas are useful when you want to keep the source data intact and show a cleaned result beside it. In Excel for Microsoft 365 on Windows, the formula below handles ordinary surrounding spaces, many nonprinting characters, and nonbreaking spaces that can arrive in copied web data. With raw names in A2:A4 and the formula entered in B2 and filled down, an A2 value containing “ Ana Lee ” (with a nonbreaking space between the names) returns “Ana Lee.”

=TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," ")))

To flag repeated IDs, put IDs in A2:A5 and enter the following in B2, then fill down. If the IDs are 104, 105, 104, and 106, the results are FALSE, FALSE, TRUE, and FALSE: only the second occurrence of 104 is flagged. This identifies repeats without deleting anything, so you can review them first.

=COUNTIF($A$2:A2,A2)>1

These formulas clean or flag values; they do not establish that records are truly duplicates. For example, two transactions with the same ID may signal a data issue, or the ID may legitimately recur in separate systems. Always confirm the rule against the purpose of the dataset before removing rows.

Common problems and how to avoid them

A frequent surprise is that values that look identical do not match. One cell may contain a regular space and another a nonbreaking space; one date may be a number formatted as a date while another is text. Try sorting, filtering, or testing a small sample before assuming a cleanup command failed. If values came from a website or another system, inspect a few raw cells and preserve the original data while investigating.

Another risk is selecting the wrong range. Removing duplicates from only one column can leave other columns out of alignment if the selection does not include complete rows. Conversely, including a unique timestamp or note column in the duplicate check may prevent otherwise repeated records from being recognized. Select the full table and choose only the fields that define sameness.

Finally, beware of automatic type conversions. Spreadsheet apps may interpret strings as dates or numbers, and spreadsheet settings such as locale affect date and decimal conventions. A value such as 03/04/2026 can mean different dates in different regions. Confirm the source convention before converting it, and verify that a sample displays and calculates as intended.

Choosing Sheets, Excel, or a combination

Google Sheets is a practical choice for shared, browser-based lists and straightforward cleanup where several people need to review data together. Its cleanup menu covers common one-off tasks, and collaborators can see changes as they happen. Excel is a good fit when the data already lives in workbooks, when advanced workbook features matter, or when Power Query can turn a recurring cleanup into a refreshable process.

Moving between the two is possible but not seamless. Formulas, cell formatting, pivot tables, charts, protected ranges, and data connections may behave differently when an .xlsx file is opened or exported. If a workbook depends on Excel-specific functionality, retain an Excel copy and verify the exported version rather than assuming the sheets are identical. For routine collaboration, agree on one source of truth so that people do not edit separate copies and create competing versions.

Frequently Asked Questions

Is Google Sheets the same as Excel?

No. Google Sheets is Google’s spreadsheet application, while Excel is Microsoft’s. They share many basic concepts and can exchange common file formats, but menus, features, and some behavior differ.

Where is the remove duplicates tool in Google Sheets?

Select the data, then choose Data > Data cleanup > Remove duplicates. Confirm the header setting and choose the columns that define a duplicate before applying the change.

Does Google Sheets automatically clean my data?

Sheets can offer Cleanup suggestions, but suggestions do not replace review. Check whether each proposed change fits the meaning of your data, and make a copy before applying bulk edits.

How do I remove extra spaces in Google Sheets?

Select the relevant cells and choose Data > Data cleanup > Trim whitespace. If matching still fails, the values may contain unusual invisible characters or inconsistent spelling that needs a separate check.

Can Excel clean data from a file every time I import it?

Yes. Power Query can save transformation steps for a source and apply them again when you refresh the query. This is more repeatable than manually making the same edits after each import.

Should I delete duplicate rows right away?

Not until you know what counts as a duplicate. Compare the relevant identifiers, retain a backup, and inspect flagged rows; repeated names or values can represent legitimate separate records.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.