OmniDocData Cleaning Cheat Sheet for Excel Users
A one-page data cleaning cheat sheet for Excel, organized by workflow step, with the functions to use at each stage and a layout you can reuse.
Messy spreadsheets slow down almost every analysis. A customer list has names with trailing spaces, a sales export stores dates as text, and a category column contains "NY", "New York" and "new york " all at once. If you have searched for a data cleaning cheat sheet, you probably want a short reference you can keep open while you work through those problems, not a textbook.
This article gives you a one-page data cleaning cheat sheet for Excel users. It is organized around the order you actually clean data: inspect, fix text, fix types, handle blanks and duplicates, standardize, and verify. Each stage lists the functions and checks that matter most, so you can follow the sheet from top to bottom.
A general formulas list tells you what each function does. A cleaning cheat sheet tells you when to reach for it. The sections below show how to set up the sheet, what belongs in each part, and how to keep a copy you can print or save as a PDF.
What should a one-page data cleaning cheat sheet include? The short answer
A one-page cheat sheet works well when it follows your cleaning sequence and lists one or two functions per problem, with a check at the end of each stage. Keep the raw data untouched and write your cleaning steps in a separate sheet or document.
| Your situation | What to do | Why |
|---|---|---|
| You are starting with a new export | Count rows, check column types, and save an untouched copy | You need a baseline to compare against at the end |
| Text looks right but lookups fail | Run TRIM and CLEAN on the affected columns | Hidden spaces and nonprinting characters break matches |
| Numbers or dates are left-aligned | Convert with VALUE or DATEVALUE, then check the result | Text values do not sum, sort or filter like real numbers |
| The same entity has several spellings | List the variants, then replace them with one standard label | Category counts stay accurate only when labels match |
| You are about to delete rows | Mark duplicates and blanks first, then decide row by row | Removing rows without a record makes errors hard to trace |
📊 The template at a glance
Use this structure for your sheet. Keep each part short so the whole thing fits on one page when printed or saved as a PDF.
| Part | What goes in it | Suggested length |
|---|---|---|
| 1. Inspect | Row and column counts, blank counts, data types | 3-5 bullets |
| 2. Clean text | TRIM, CLEAN, PROPER, SUBSTITUTE | 3-5 bullets |
| 3. Fix types | VALUE, DATEVALUE, number formats | 2-4 bullets |
| 4. Blanks and duplicates | COUNTBLANK, duplicate checks, how to decide | 3-4 bullets |
| 5. Standardize | Label map, find and replace rules | 2-4 bullets |
| 6. Verify | Count and total checks against the raw data | 3-5 bullets |
| Notes | Date of cleaning, source file name, open questions | 2-3 lines |
How to write each part
1. Inspect
Start by counting what you have. Write down the number of rows and columns, and the number of blank cells in the columns you plan to use. Use COUNTA for filled cells and COUNTBLANK for empty ones.
Check whether each column is text or numbers. A quick test is to select a value and see whether it aligns left (usually text) or right (usually numbers). Add a line to your sheet such as "Column [name]: expected [number type], found [text]."
2. Clean text
Hidden spaces are a common cause of failed matches. TRIM removes extra spaces at the start and end of a value and collapses repeated spaces between words. CLEAN removes nonprinting characters that often come from system exports.
Use PROPER only for names and titles, because it can produce odd results for codes or abbreviations. Use SUBSTITUTE to replace a specific character, such as a non-breaking space, with a normal space.
Example line for your sheet: "Apply TRIM and CLEAN to [column name] in a helper column, then paste values back over the original."
3. Fix types
Numbers stored as text will not add up correctly. VALUE converts a text number to a real number. DATEVALUE converts a text date, but it depends on the date format your system expects, so test one value first.
Wrap conversions in IFERROR when you expect some values to fail, and return a label such as "check" so you can find them. Do not hide errors with a blank result.
Example line: "Convert [column name] with VALUE, then confirm that the total of [column name] matches the raw export."
4. Blanks and duplicates
Decide what a blank means before you change it. A blank in a revenue column may mean zero, or it may mean the value is missing. Write the rule on the sheet.
For duplicates, Excel has a built-in removal tool, but run a check first. Use COUNTIF on your key column to flag repeated values, and review them before you delete anything. Keep a note of how many rows you removed and why.
5. Standardize
Build a short label map: one column with the variants you found and one with the standard label. Apply it with find and replace or with a lookup formula, then recount each category.
Example line: "Map [variant] to [standard label]; confirm that the category totals still add up to the total row count."
6. Verify
Verification is the step most cheat sheets skip. Compare the row count after cleaning with the count you wrote down in step 1. Compare key totals, such as the sum of an amount column, with the raw data.
If the numbers differ, check the notes from the earlier steps before you go further. A difference is not always an error, but it should always have an explanation.
Format it in the editor
You can build the sheet in any spreadsheet or document editor. If you want to write it as a document with headings, tables and a notes section, the UniDoc web editor at app.unidoc.top supports Word-compatible editing, so you can work in a familiar structure.
- Open a new document and enter the six stage names as headings, using the heading styles so the outline is easy to scan.
- Add the template table, then fill each part with two to five short bullets or one example line.
- Keep the sheet to one page. Reduce spacing or cut bullets until it fits, rather than shrinking the font below a readable size.
- Add a notes line at the bottom with the source file name and the date you cleaned it.
- When you need a PDF copy, use your word processor's or spreadsheet's save-as-PDF option. If you already have a PDF, the desktop client includes a PDF editor for page operations and text edits, and it can export pages to Word, Excel or PowerPoint.
If you need to install the desktop client, download it from download.omnidoc.top.
Common mistakes
- Cleaning the only copy of the data, so there is no baseline to check against.
- Converting text to numbers without testing one value first, which can turn an entire column into errors.
- Deleting duplicates without a review step, then discovering that two rows were different customers.
- Writing one generic rule, such as "replace blanks with zero," for every column.
- Skipping the final count check because the sheet looks clean.
- Making the sheet longer than one page, which turns it into a manual that nobody reads while working.
❓ FAQ
1. Should I keep my cleaning formulas or paste the results as values?
Keep the formulas in helper columns while you work so you can trace each step. Once you have checked the results, paste values over the original column only if you do not need the formulas to update. Keep the untouched raw data in a separate sheet either way.
2. How is a cleaning cheat sheet different from a list of formulas?
A formulas list is organized by function name, so you have to know which function you need. A cleaning cheat sheet is organized by the problem and the order you solve it in, which makes it easier to follow during a real project.
3. What if my dates use different formats in the same column?
Mixed formats often mean the source combined two systems. Sort or filter the column to find the odd ones out, then convert each group separately. Record which format each group uses in your notes so the conversion is repeatable.
4. Can I share the cheat sheet with my team?
Yes. Save it as a PDF so everyone sees the same layout, and keep the editable version where your team can update the label map and the notes. Date each version so people know which one is current.
📝 Summary
A data cleaning cheat sheet for Excel works well when it follows your workflow: inspect, clean text, fix types, handle blanks and duplicates, standardize, and verify. Keep the raw data untouched, write down each decision, and compare your final counts with the original. Keep the sheet to one page so you will actually use it while you work.
