Google Sheets and Excel Data Cleaning Checklist: Standardize Imports Before Reporting
data cleaningExcelGoogle Sheetsdata qualityreportingspreadsheet workflow

Google Sheets and Excel Data Cleaning Checklist: Standardize Imports Before Reporting

SSpreadsheet.top Editorial Team
2026-11-19
7 min read

Use this Excel and Google Sheets data-cleaning checklist to standardize imports, validate totals, and prepare reliable dashboards and reports.

Imported data can look complete while still producing unreliable totals, filters, and dashboard results. This Google Sheets and Excel data cleaning checklist gives you a repeatable way to preserve the original export, standardize the working data, validate transformations, and document changes before the information reaches a report.

Overview

A clean spreadsheet is not necessarily one with no blank cells. It is a dataset with consistent structure, clear definitions, valid values, and traceable changes. Those qualities matter whether you are preparing a sales report, a cash flow forecast, an inventory sheet, or a KPI dashboard.

The safest workflow separates three layers:

  1. Raw layer: the untouched export from a CRM, accounting system, form, or other source.
  2. Clean layer: a working copy where formats, labels, missing values, and records are standardized.
  3. Report layer: formulas, pivot tables, charts, and dashboard views built from the cleaned data.

Do not clean directly over the original import. Save the source file or worksheet with a date and source name, such as CRM_export_2026-11-05. Protect or restrict editing on that tab if the file is shared. This gives you a reference point when a total changes or a transformation needs to be reversed.

Before changing anything, define the intended output. Decide what one row represents, identify the required columns, and write down the fields used in calculations. A sales table might use one row per opportunity, while an invoice table might use one row per invoice. Mixing those levels of detail can create duplicate totals even when every individual value appears correct.

What to track

Use the following checklist before connecting an imported range to a dashboard or business spreadsheet template.

1. Duplicate records

First decide what makes a record unique. It may be an order ID, invoice number, customer-and-date combination, or another key. Do not remove duplicates simply because two rows look similar; repeated transactions may be legitimate.

In Excel, use Data > Remove Duplicates only after keeping a copy of the source and selecting the correct identifying columns. In Google Sheets, =UNIQUE(A2:H) can create a separate deduplicated view, but it does not determine whether repeated rows are valid business events. For a stronger check, add a helper column that counts occurrences of an ID, for example =COUNTIF($A$2:$A$1000,A2).

2. Blank and missing values

Separate intentional blanks from missing information. A blank delivery date may mean an order has not shipped; a blank revenue value may indicate an incomplete export or a non-revenue record. Avoid replacing every blank with zero. Zero is a number with a meaning, while an empty cell may mean “unknown,” “not applicable,” or “not yet entered.”

Track the number of blanks in important fields, especially IDs, dates, quantities, prices, owners, and status categories. Use filters or formulas such as =COUNTBLANK(D2:D1000). Create a short data dictionary that states how each missing-value condition should be handled.

3. Dates and times

Imported dates may appear as true spreadsheet dates, text strings, or serial values. Test them by changing the format or using a helper check. A real date should sort chronologically and support functions such as YEAR, MONTH, and DATEDIF where appropriate.

Standardize one display format, such as YYYY-MM-DD, but remember that formatting alone does not convert text into a date. For text dates, functions such as DATEVALUE may help when the source format is recognized. Check whether the source includes times or different time zones before grouping records by day or month.

4. Numbers stored as text

Values such as revenue, quantity, margin, and account balances must be numeric before they are used in calculations. Warning indicators include left-aligned numbers, totals that exclude visible values, currency symbols embedded in text, and commas or decimal marks that do not match the workbook’s locale.

Use a separate conversion column first. Depending on the import, VALUE, NUMBERVALUE, or a controlled find-and-replace may be appropriate. Check negative values, parentheses, percentage signs, and currency codes rather than stripping symbols indiscriminately.

5. Labels, categories, and naming conventions

Standardize values such as Open, open, and OPEN when they represent the same status. Also look for trailing spaces, nonbreaking spaces, spelling variations, and inconsistent abbreviations. TRIM removes many extra spaces; CLEAN can remove certain nonprinting characters. Some imports contain characters that these functions do not remove, so test a sample and inspect the result.

Use controlled lists for fields such as department, region, product family, project status, and sales stage. Data validation in Excel and Google Sheets can reduce new variations after the initial cleanup. Keep a mapping table when replacing old labels with approved labels so the change remains auditable.

6. Invalid and out-of-range values

Check for negative quantities where they are not expected, percentages above 100%, dates outside the reporting period, impossible status transitions, and IDs that do not match the source system. These checks should be explicit rather than hidden inside a formula. A small exception report is often more useful than silently correcting questionable values.

Cadence and checkpoints

Run a full cleaning review when you receive a new export format, add a new source system, change a reporting definition, or discover an unexpected dashboard total. For recurring reports, use a lighter checklist at every refresh and a deeper review monthly or quarterly, depending on how often the data changes.

At each refresh, record:

  • the source file name and export date;
  • the row count before and after cleaning;
  • the number of duplicate keys;
  • blank counts for required fields;
  • the date range covered;
  • the number of invalid categories or exceptions;
  • the person who reviewed the result.

Compare control totals with the source system where possible. For example, compare the imported transaction count and total value with the export summary before building a chart. If the clean layer has fewer rows, document whether records were removed, merged, or excluded by a defined filter.

Keep transformation steps visible. A practical workbook may include a Read Me tab, a Mapping tab for old-to-new labels, and an Exceptions tab for records requiring review. This is more reliable than placing many opaque corrections inside a single long formula.

How to interpret changes

A change in a report does not automatically mean the business changed. It may reflect a new export filter, a renamed category, a date conversion issue, or duplicate records. When a dashboard total moves unexpectedly, compare the raw, clean, and report layers in that order.

Start with row counts and key totals. Then inspect the distribution of categories and dates. If revenue falls but transaction count is stable, check numeric conversion, excluded values, and currency handling. If transaction count rises sharply, check duplicate IDs and whether the source began including historical records. If a category disappears, compare the mapping table and validation list.

Use reconciliation checks rather than relying only on visual inspection. Examples include:

  • clean row count = valid rows + excluded rows;
  • total revenue in report = total revenue in clean layer, subject to documented filters;
  • all required IDs are nonblank and unique;
  • all category values belong to the approved list.

Once the data passes these checks, connect it to the appropriate reporting tool. A cleaned sales table can support a sales forecast spreadsheet template, while an inventory export may feed an inventory management spreadsheet. For recurring management reporting, use a small business KPI dashboard template only after its source range has been validated.

When to revisit

Revisit this checklist before every monthly or quarterly reporting cycle, after receiving a new export, and whenever a recurring data point changes unexpectedly. Perform an additional review when a source system adds columns, changes date or currency formats, introduces new categories, or alters the meaning of an existing field.

At least once a year, review the workbook’s formulas, menu instructions, data-validation lists, and compatibility notes for the current Excel and Google Sheets environment. Function behavior, import formats, and interface paths can change, so screenshots and step-by-step directions should not be treated as permanent. Also review whether the cleaning rules still match the business definitions used by your dashboards and forecasts.

For your next refresh, duplicate the workbook, preserve the new raw export, and complete the checklist in order: confirm the row definition, check duplicates, inspect blanks, standardize dates and numbers, normalize labels, validate categories, reconcile totals, and document exceptions. If the cleaned data supports a strategic plan or decision model, link it only after those checks pass. For a final formula and reporting review, use the spreadsheet audit checklist to look for broken links, formula errors, and presentation mistakes.

Related Topics

#data cleaning#Excel#Google Sheets#data quality#reporting#spreadsheet workflow
S

Spreadsheet.top Editorial Team

Spreadsheet Strategy Editors

Senior editor and content strategist. Writing about technology, design, and the future of digital media. Follow along for deep dives into the industry's moving parts.