Excel Data Cleaning Checklist Before Sharing or Importing a File

When to Use This Checklist

Use this checklist before sending a spreadsheet to a client, importing it into a database or reporting tool, building a dashboard from it, or using it as the basis for a financial decision. The goal is not to make every file perfect — it is to remove the common problems that silently create wrong reports, broken imports, or misleading totals.

For best results, save a copy of the original workbook first. Clean the copy, keep notes about what changed and why, and compare row counts before and after each step. A count mismatch you cannot explain is a warning sign that something unexpected happened.

1. Audit Formulas First

Before editing any data, run a formula audit. Editing a spreadsheet before understanding its formula structure can break formulas that depended on the original layout. A formula audit surfaces:

• #REF! errors: A formula references a cell or range that no longer exists — usually from a deleted row, column, or sheet.
• #VALUE! and #NAME! errors: Mismatched data types or misspelled function names, often introduced during copy-paste from another workbook.
• Hidden sheets: Many workbooks have sheets hidden from view that still feed calculations in the visible sheets. Deleting visible rows without checking what the hidden sheets reference can break the model.
• Broken external links: Links to other workbooks that no longer exist at the referenced path. These silently use stale cached values until the link target reappears.
• Hardcoded numbers inside formulas: Values like =Revenue*0.23 where 0.23 is a tax rate with no label. These are fragile because changing the rate requires finding every formula that contains it.
• Overwritten formula cells: Cells that should contain a formula (based on the surrounding pattern) but were overwritten with a static value — common after copy-paste operations.

Use the Spreadsheet Auditor for a fast first pass. It checks all of these in one step and explains every issue in plain English with the exact cell address.

2. Remove Blank Rows

Blank rows split tables into disconnected regions. Excel filters, pivot tables, VLOOKUP, SUMIF, and most database import tools treat a blank row as the end of the table — everything below it is ignored. This is one of the most common causes of incomplete imports and wrong totals.

The distinction that matters: a blank row is a row where every cell is empty. A row with a missing value in one or two columns is not blank — it may represent a valid record where some fields were not collected. Deleting non-blank rows with missing values is a data decision, not a cleaning step.

Before removing blank rows, check whether any of them are used as visual separators between sections of a report. If so, consider whether the report structure needs to be flattened into a proper data table first. Use the Remove Blank Rows tool to preview the count of rows that would be removed before downloading the cleaned file.

3. Check Duplicates and Unique Values

Duplicate rows inflate totals, double-count customers, and create misleading reports. They appear most often from: copy-paste from a second source, accidental double-import from a system export, or multiple people editing the same file and merging their versions manually.

A duplicate check requires defining what “duplicate” means for your data. In a customer list, it might mean the same email address. In an order table, it might mean the same Order ID. In a transaction log, it might mean the same combination of Date, Customer, Amount, and Category. Choosing the wrong key columns will either miss real duplicates or incorrectly flag legitimate records.

Unique-value checks are useful for validating category lists, region names, product codes, status fields, and lookup keys. A category field that was supposed to have 5 values but shows 8 — with “North ”, “north”, and “NORTH” appearing as separate entries — is a labelling problem (see step 4). Use Duplicate Row Finder and Extract Unique Values for these checks.

4. Normalise Labels

Inconsistent labels are invisible in a spreadsheet but catastrophic in a report. North, NORTH, north, North (trailing space), and Nort h (accidental space) are five distinct values in a pivot table or GROUP BY query, even though they mean the same thing.

Common normalisation steps:

• TRIM: Removes leading and trailing spaces — the most common source of invisible label mismatches.
• PROPER / UPPER / LOWER: Standardises case across a column.
• Find and Replace: Fixes known variants — for example, replacing “UK” and “U.K.” and “United Kingdom” with a single canonical form. Use whole-cell match in Find and Replace to avoid unintended partial replacements.
• Remove non-printing characters: Pasted text from PDFs, emails, and web pages often contains non-breaking spaces (CHAR(160)) or other invisible characters that look identical to regular spaces but are treated differently by formulas and imports.

After normalising, re-run a unique-value check on the columns you changed to confirm the category count is what you expect.

5. Standardise Dates and Numbers

Dates and numbers are the two most common carriers of hidden format problems in Excel. The cell may display correctly but the underlying value may be text — which means SUM returns 0, COUNTIF misses rows, and sorting puts dates in text order rather than chronological order.

To detect text-formatted numbers: try multiplying the value by 1 in a helper column. If the result is a number, the original was stored as a number. If you get a #VALUE! error, it was text. The ISNUMBER function is also reliable: =ISNUMBER(A2) returns FALSE for text-formatted numbers.

For dates, sort oldest to newest and look for anomalies: future dates that should not exist, dates in January 1900 (Excel's default when a date formula fails), mixed formats in the same column (some cells showing DD/MM/YYYY while others show MM/DD/YYYY), and date strings that look like “2026-06-12” but were imported as text rather than date serial numbers.

When importing from CSV or system exports, dates are especially fragile. The safest approach is to confirm the date format of the source system before importing and apply the correct TEXT TO COLUMNS date format during import rather than relying on Excel to guess.

6. Sort Only After Selecting the Full Table

Sorting a single column without selecting all adjacent columns breaks the row-level relationship between fields. The sorted column changes order, but the other columns stay fixed — which means customer names end up with the wrong amounts, dates with the wrong products, and IDs with the wrong records. This is one of the most damaging data mistakes because it is not obviously visible until results are checked against a source system.

The safe approach: always select the entire data table (Ctrl+Shift+End from the top-left cell), or click any cell inside the table and let Excel auto-detect the region. If Excel shows a “Sort Warning” dialog asking whether to expand the selection — always choose “Expand the selection.” Never choose “Continue with the current selection” unless you deliberately want to sort only one column in isolation.

For important files, keep an original ID column and spot-check a few rows after sorting to confirm the fields are still aligned correctly. The Sort Excel tool sorts entire tables and never sorts partial columns in isolation.

7. Review Column Statistics

Column statistics reveal problems that are invisible from looking at individual cells. Before reporting or importing, check the following for each key column:

• Row count vs non-blank count: If a column has 1,000 rows but only 850 non-blank values, 150 rows have missing data. Decide whether that is expected or a problem.
• Min and max: Impossible values (negative ages, future transaction dates, zero prices in a non-zero-price product list) often appear as outliers in the min or max.
• Sum vs expected total: For financial columns, check whether the SUM matches an external control total from the source system. A mismatch of even a single row is a data integrity problem.
• Unique count: A column that should have 12 distinct months but shows 15 has duplicate or misspelled month labels. A column that should have unique customer IDs but shows fewer unique values than rows has duplicate IDs.

These checks do not require additional tools — they can be done with a few formulas in a helper row. But they are often skipped, which is why bad data passes through review undetected.

8. Convert Formats Carefully

When converting Excel to CSV, JSON, XML, or SQL, the conversion process introduces its own risks. A clean Excel file can produce a broken import if the conversion is not configured correctly.

The most common conversion problems:

• Leading zeros stripped: ZIP codes, phone numbers, product codes, and ID numbers that begin with zero lose the zero in CSV conversion unless the column is formatted as text before export.
• Date format changes: A date displayed as “12 Jun 2026” in Excel may be exported as “2026-06-12”, “06/12/2026”, or “42933” (the serial number) depending on the converter and locale.
• Delimiter conflicts: If the data contains commas inside text fields (like “Smith, John”) and the CSV uses commas as delimiters, the field will split incorrectly unless it is enclosed in quotes. Use the Excel to CSV tool's delimiter selection to choose the right separator for your target system.
• Encoding issues: Non-ASCII characters (accented letters, currency symbols, non-Latin scripts) may become garbled if the wrong encoding is used. UTF-8 with BOM is the safest default for Excel compatibility.

Always preview the first few rows of the output before importing into a target system, and check the row count matches the original.

9. Check Hidden Sheets and Hidden Data

Hidden sheets are not the same as deleted sheets. They still exist in the workbook, still feed formulas in visible sheets, and are still included in file size. More importantly, they often contain intermediate calculations, backup data, lookup tables, configuration ranges, and sometimes confidential notes or personal data that was not intended to be shared.

There are two kinds of hidden sheets in Excel. Standard hidden sheets can be unhidden from the right-click menu on any sheet tab. Very hidden sheets (xlSheetVeryHidden) cannot be unhidden from the normal menu — they require VBA or a tool to detect. The Spreadsheet Auditor detects both types and reports them by name.

Hidden rows and columns within a visible sheet are a separate issue. Filtered rows and column groups can hide data that is still included in totals. Before sharing a file externally, clear all filters and expand all column groups so the recipient can see the full dataset — or explicitly note which rows are filtered and why.

10. Create a Final Review Copy

Once the file is cleaned, save a final review copy with a clear name (for example, report_2026-06-12_final.xlsx) and open it from disk as if you were a new recipient. This catches problems that only appear after saving: broken external links that now point to a path that exists on your machine but not the recipient's, external references that cached a value during the session but will prompt on first open, and formatting changes that occur when a file is resaved in a different Excel version.

If multiple people will use the workbook, test it on a clean machine or download it from your file-sharing platform and open the downloaded copy — not the copy you worked on. Shared drives and version control systems sometimes cache intermediate states that do not match what a first-time recipient downloads.

For files used in automated imports, run the import process against the cleaned file in a test environment before using it in production. A clean-looking Excel file can still fail a database import if column order changed, column names were renamed, or the sheet name the import script expects no longer exists.

Quick Reference: 10-Step Checklist

Final Rule

Clean a copy, not the only copy. Keep the original workbook untouched, export the cleaned version with a versioned filename, and compare row and column counts before using the output. If something unexpected changed, the original is still there to diagnose the problem.