Recommended Free Tools
iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
Clean dirty data as a controlled transformation, not an edit to the only copy. Preserve an unchanged raw input, document each rule, make risky changes in helper columns or copied queries, test representative records, and publish only after downstream checks pass. That approach lets you correct errors while retaining a clear rollback path for reports, refreshes, formulas, joins, and handoffs.
Why ordinary cleanup breaks working systems
A value that looks wrong in a spreadsheet may be an identifier used by a join, a category expected by a report, or a text field that a refresh step converts to a number. Changing it in place can therefore create failures far from the cell being edited. Typical defects include misspellings, trailing spaces, unwanted prefixes, inconsistent capitalization, duplicates, blank values, malformed dates or numbers, and nonprinting characters. Microsoft lists these examples in its Excel guidance: Top ten ways to clean your data.
The safe mental model is a versioned pipeline:
- Raw: the original file, extract, or table is retained unchanged.
- Transform: explicit, reviewable rules produce a candidate cleaned result.
- Validate: structural, semantic, privacy, and downstream checks compare the candidate with expectations.
- Publish: consumers receive the candidate only after the gate passes.
- Rollback: the raw input, rules, and prior published output remain available.
Cleaning is successful only when the corrected data is more useful without silently changing the contract that existing workflows rely on.
Free tools Windows power users keep installed
One-click scans. No signup required.
1. Preserve a recoverable source before touching a value
Make the source read-only for the duration of the work. For a workbook, save a separate backup before editing. Microsoft Support states:
#1 Best Overall
“Create a backup copy of the original data in a separate workbook.”
For recurring imports, retain the original file or extract in an immutable raw location and write the cleaned result to a different table, file, or folder. Record the source name, extraction time, row count, and any source-system version that is available. Do not rely on a renamed copy that people can overwrite; use permissions or storage controls that make accidental edits difficult.
- Give the raw object a stable identifier or timestamp so a later run can be traced to the exact input.
- Keep the previously published output until the new output has passed validation.
- Store transformation definitions, exception records, and validation results with the run metadata.
- Never use the cleaned output as the next run’s raw input; that compounds changes and removes the ability to distinguish source defects from earlier edits.
2. Understand the shape of the data and its consumers
Make the input structurally predictable
In Excel, arrange records in a table with one consistent type of data per column and no blank rows inside the range. A header should identify the field, and each row should represent the same kind of record. This prevents a cleanup formula or import step from treating a subtotal, note, or blank separator as a data record. The table and backup practices are described in Microsoft’s Excel guidance at support.microsoft.com/en-us/excel/top-ten-ways-to-clean-your-data.
Inventory dependencies before changing a field
For every column under consideration, identify formulas, pivot reports, Power Query steps, database joins, exports, dashboards, and people who consume it. Note the column name, data type, expected format, allowed values, key status, and sort or ordering assumptions. A rename, type conversion, or category merge can be a breaking change even when every resulting value appears cleaner.
| Dependency | What to record before cleaning | Why it matters |
|---|---|---|
| Formulas and workbook reports | Referenced ranges, column names, and expected text or numeric types | A replacement column or type change can return errors or alter calculations. |
| Power Query refreshes | Source step, column names, type steps, joins, and privacy levels | Source changes can make a later step fail during refresh. |
| Database joins | Join keys, uniqueness expectations, collation or case behavior, and null handling | Whitespace, case, or duplicate changes can create missing or multiplied matches. |
| Exports and handoffs | File layout, required headers, delimiters, encoding, and recipient assumptions | A technically valid table may still break a receiving process. |
3. Choose an approach that fits the workflow
No single cleanup function or product is safe for every dataset. Compare the available approach against repeatability, reviewability, scale, compatibility, privacy, and operational ownership.
| Situation | Good starting point | Safeguards and trade-offs |
|---|---|---|
| One-off spreadsheet repair | Excel backup plus helper columns or formulas | Easy to inspect row by row. Keep the original column and review the transformed column before replacing anything. Microsoft’s Excel guidance recommends a separate backup. |
| Recurring file or database import into Excel | Power Query with copied queries or columns during development | Refresh steps are repeatable, but a source change can break a step. Copy uncertain transformations, test them, and preserve privacy settings. See Power Query data-source error guidance. |
| Governed database cleansing with proposed corrections | SQL Server Data Quality Services (DQS) | Knowledge-based suggestions can be reviewed, rejected, or edited by a data steward before export to a new table or file. See Microsoft Learn’s DQS cleansing documentation. |
| Large or highly varied preparation workload | Evaluate the existing cloud or data-engineering architecture, such as AWS Glue or Amazon EMR where appropriate | Match the platform to access controls, data volume and variety, operating skills, and downstream consumers. The AWS guidance does not set a universal size threshold or performance ranking: Data preparation and cleaning. |
4. Isolate transformations instead of overwriting source fields
Excel: add a helper column
Insert a new column beside the original field and apply the proposed transformation there. Give it a name that states both the field and the state, such as CustomerName_clean_candidate. Compare original and candidate values, filter to rows that changed, and have a reviewer inspect exceptions. Only after validation should you copy approved results into the published field, keeping the backup and change record.
Rank #2
For duplicate records, use Excel’s Data > Remove Duplicates operation only on a reviewed copy or after saving the pre-operation state. Decide which columns define a duplicate before running it; two rows that share a name may represent different people, while identical identifiers may indicate an actual duplicate.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
Power Query: copy the query or original column
When a transformation could have negative effects, duplicate the query or preserve the original column and create a derived one. Microsoft advises avoiding significant changes to original columns where possible because later steps can fail when their expected type or name changes. Work in the copied query, refresh it repeatedly, and remove or revise a step when it fails. The procedure and failure behavior are documented in Handling data source errors (Power Query).
Keep the sequence of applied steps visible and meaningful. A type-conversion step should occur only after the text has been normalized enough for the conversion to be deterministic. If a source adds, removes, or renames a column, stop the refresh rather than allowing a guessed substitute to flow into reports.
DQS: treat suggestions as proposals
SQL Server Data Quality Services can generate cleansing suggestions from its knowledge base. A steward can approve, reject, or edit proposed corrections before exporting the result. This is useful when a correction has business consequences, such as mapping several spellings to one legal entity, because the review decision is retained instead of hidden inside a formula. Follow the documented workflow at Data Cleansing – Data Quality Services.
Large workloads: preserve layers
For broad data-preparation workloads, keep ingestion, standardized data, exception records, and published data as separate layers even when an orchestration platform performs the work. AWS describes anonymizing or masking personally identifiable information as part of preparation and cleaning, and points to services such as Glue or EMR for large workloads at AWS Prescriptive Guidance. The correct architecture depends on governance and operational capability, not on a universal row-count cutoff.
5. Write the cleaning rule before writing the formula
A rule should state the defect, the exact substitution, and the values that must remain unchanged. This prevents a broad operation from “fixing” records that only look unusual.
Rank #3
| Defect | Explicit rule example | Do not assume |
|---|---|---|
| Trailing spaces | Remove spaces at the beginning and end of the field; preserve internal spaces in names and addresses. | Every repeated space is an error; formatting may be meaningful in a code. |
| Unwanted prefix | Remove the known source-system prefix only when the value starts with that exact prefix. | Any letters before a number are disposable. |
| Inconsistent case | Standardize case for display labels while retaining the original value for audit or matching. | Case can never matter in a key or password-like field. |
| Misspelling or alias | Map an approved source spelling to a named canonical value; send unrecognized variants to review. | Similar-looking words represent the same entity. |
| Blank or missing value | Leave it missing unless a documented source or business rule supplies the replacement. | Blank means zero, “unknown,” or the most common category. |
| Duplicate row | Define the key columns and a survivor rule before removing or consolidating rows. | Matching names alone prove that two records are duplicates. |
Keep the original value alongside the candidate whenever a human may need to explain the change. For an ambiguous value, create an exception status such as needs_review rather than forcing a low-confidence replacement.
6. Test on representative records, not just easy rows
Build a small test set that contains the patterns a real refresh can deliver:
- Normal, already-clean records to prove they remain unchanged.
- Each known defect, including misspellings, spaces, prefixes, case differences, duplicates, and nonprinting characters.
- Blank and null values, including fields that are optional and fields that are required.
- Boundary values such as the shortest and longest valid strings, zero and negative numbers where allowed, and dates at the supported limits.
- Malformed values that should be rejected or routed to an exception queue.
- Newly observed patterns from the latest source extract.
Run the candidate transformation on this set before applying it to the full input. In Power Query, Microsoft recommends experimenting with a copied query and iterating; if a step fails, remove or revise it rather than hiding the error. The guidance is at Handling data source errors (Power Query).
Record both changed and unchanged rows. An unchanged clean record is a test assertion: a rule that changes it unexpectedly is too broad. A malformed record that passes through without an exception is a missing control.
7. Apply a validation gate before replacement
Validation should compare the candidate output with the raw input and with the expectations of every important consumer. Tailor the checks to the workflow; the following gate covers the common failure modes.
| Check | What to compare | Failure response |
|---|---|---|
| Row preservation | Input and output row counts, plus an explanation for every intentional insert, merge, or removal | Stop publication if rows disappeared or multiplied without an approved reason. |
| Key integrity | Nulls, uniqueness, length, allowed characters, and unchanged key values where required | Restore the original key or route affected records for review. |
| Type conversion | Conversion success rate, rejected values, date interpretation, numeric range, and decimal precision | Keep failed conversions as exceptions; do not coerce them to zero or an arbitrary date. |
| Allowed categories | Values against the controlled list, including newly appearing categories | Approve a new category or retain it in the exception queue. |
| Duplicate behavior | Duplicate counts before and after, grouped by the defined key | Investigate unexpected merges or splits. |
| Referential matches | Join match rates and unmatched keys against reference tables | Check whitespace, case, type, and key changes before publishing. |
| Downstream refresh | Test-copy formulas, queries, reports, exports, and handoffs | Fix the transformation or update the consumer deliberately; never ship a failing refresh. |
| Changed-row review | A sample or complete listing of rows whose values changed | Reject substitutions that are not supported by the written rule. |
Keep the validation result with the run: counts, failed records, reviewer, date, and decision. A green status should mean every required check passed, not merely that the file opened.
Rank #4
8. Handle common defects with targeted operations and known limits
Spaces, prefixes, and case
Normalize only the characters covered by the rule. For example, remove a known leading prefix only when it is present, and trim edge spaces without rewriting meaningful internal punctuation. Preserve the raw value so an operator can compare the before and after. If a field is used as a join key, validate matches after normalization rather than assuming that visual similarity is sufficient.
Do these 3 things before closing this tab:
1Repair Windows errors before they cause bigger problems2Fix the driver behind crashes, sound loss and screen glitches3Clear out junk files and repair common Windows errorsDuplicates
Define the duplicate key and survivor policy first. A duplicate may be an exact repeated row, two records with the same business identifier, or two people who happen to share a name. Keep a record of removed or consolidated rows and the reason. If the workflow cannot tolerate row-count changes, flag duplicates for review instead of deleting them.
Nonprinting characters
Excel’s CLEAN function is not a universal text sanitizer. Microsoft says it removes the first 32 nonprinting characters in 7-bit ASCII, values 0 through 31, but by itself does not remove additional nonprinting Unicode characters such as 127, 129, 141, 143, 144, and 157. See the exact limitation in Microsoft’s CLEAN function documentation. If a match still fails after using a targeted cleanup, inspect the character codes and create a rule for the actual source pattern; do not claim that CLEAN removed every invisible character.
Missing and ambiguous values
Do not turn unfamiliar values into blanks or the most common category merely to satisfy a type check. Keep an explicit missing state and an exception queue. For high-impact data, require a steward to approve proposed substitutions, as the DQS workflow allows at Microsoft Learn.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.9. Protect privacy while queries combine sources
Set Power Query privacy levels to match the sensitivity and trust boundary of each source. Microsoft describes Private sources as isolated and warns that ignoring privacy levels can expose sensitive data because Power Query cannot ensure privacy for merged data. Restrictive isolation can reduce functionality or performance, so document the trade-off instead of bypassing the control for convenience. Configure the setting according to the steps at Set privacy levels (Power Query).
- Restrict access to raw exports and logs that contain personal information.
- Mask or anonymize personal identifiers in development and test environments where feasible.
- Use the least-privileged account that can read the required sources and write the candidate output.
- Document which sources may be combined and who approved the boundary.
- Do not copy sensitive raw rows into screenshots, shared workbooks, or unrestricted exception files.
Privacy is part of workflow safety: a refresh that succeeds by leaking data is not a successful cleanup.
10. Publish only after the gate and keep rollback material
- Freeze the candidate output and validation report so they cannot change while being reviewed.
- Have the owner of the downstream report, query, or handoff approve the changed-row sample and failed-record handling.
- Publish the candidate to a new version or distinct output location first.
- Refresh a test copy of dependent workbooks, queries, joins, and reports; inspect totals, match rates, and visible changes.
- Switch consumers to the new output only after the test refresh passes.
- Retain the raw input, transformation steps, exception queue, validation results, and previous published output for the agreed retention period.
If a later refresh reveals a problem, revert consumers to the previous published output, preserve the failing input and error details, and revise the rule in the isolated transformation layer. Never repair the published file by hand and then discard the evidence; that makes the next run irreproducible.
11. Troubleshoot by symptom
A Power Query refresh fails after a source change
Compare the source schema with the step that failed. Check for a renamed or removed column, a changed data type, an unexpected null, or a privacy-level conflict. Reproduce the problem in the copied query, revise the step or add an explicit exception, and rerun the validation gate. Microsoft’s error guidance is available at Handling data source errors (Power Query).
Row counts changed unexpectedly
Inspect filters, joins, duplicate removal, and type-conversion failures. Compare keys before and after and list every missing or repeated key. Restore the raw input and adjust the step; do not compensate by inserting arbitrary rows or deleting rows until the count looks familiar.
Joins no longer match
Compare the raw and candidate key values, including leading or trailing spaces, case, encoding, nulls, and data types. Check whether a cleanup rule changed a key that was supposed to remain stable. If the business meaning of a key is uncertain, send the records to review rather than broadening the matching rule.
A cleanup rule changes too many values
Filter to changed rows and identify the first pattern that was transformed incorrectly. Narrow the rule to the exact prefix, character, category, or condition; add the discovered counterexample to the test set; and rerun validation. Keep the original column so the correction can be audited.
Privacy controls block a merge
Confirm the classification and trust relationship for each source, then choose an approved privacy level or redesign the merge so sensitive data is not combined unnecessarily. Do not disable privacy warnings simply to make a refresh complete; the isolation may be the control preventing disclosure.
12. A repeatable pre-publication checklist
- Is the original input preserved and protected from edits?
- Are the affected columns, consumers, keys, types, and allowed values documented?
- Is every transformation expressed as a rule with explicit unchanged cases?
- Were risky edits made in helper columns, copied queries, or a separate candidate table?
- Does the test set include clean, malformed, blank, boundary, and newly observed patterns?
- Were row counts, keys, nulls, duplicates, types, categories, joins, and changed rows checked?
- Did a test copy of every important refresh, report, and handoff succeed?
- Are ambiguous records retained in an exception or human-review path?
- Do privacy levels and access permissions match the sensitivity of each source?
- Can the published output be rolled back to the prior version with the raw input and rule history intact?
When all answers are yes, cleaning becomes a controlled, repeatable change rather than an irreversible edit. The result is easier to explain, safer to refresh, and recoverable when the next source file contains a pattern no one has seen before.
Quick wins for a faster PC:
Repair Windows errors before they cause bigger problemsFix Now →Scan for outdated or missing drivers - takes under a minuteDriver Scan →Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
