Lecture Notes: Rules for Better Data Cleaning


Video | Introduction | Types of Dirty Data | Cleaning Rules | Step-by-Step Cleaning Process | Sources | Back to The Data Analysis Process

In this video, a data analyst defines data cleaning, explains why it matters, and walks through common examples of dirty data.


Introduction

Data cleaning is the process of finding and fixing problems in data so that it is accurate, consistent, and complete.

Problems in a dataset are called dirty data. A common example is one person recorded several ways: “Alex Martin,” “Alexander Martin,” “Alex M.” A spreadsheet or database treats each version as a different person. Other columns, such as date of birth, can confirm the records belong to the same person, and the names can then be made consistent.

Cleaning is Step 3 of The Data Analysis Process. It matters for three reasons:

  • Accuracy. Stakeholders make decisions from the data. Errors in the data lead to errors in those decisions.
  • Efficiency. Clean data is faster to sort, group, analyze, and chart.
  • Trust. Stakeholders rely on the analyst’s data being accurate and complete. Clean data supports that trust.

Cleaned data is rarely perfect. The goal is data that is reliable enough to use, with each change recorded.


Types of Dirty Data

Most dirty data falls into a handful of types.

Type What It Looks Like Typical Fix
Inconsistent entries The same thing written different ways: abbreviations, punctuation, spelling (“St.” vs. “Street”) Standardize with Find and Replace
Duplicates The same record entered more than once, exactly or with small differences Confirm each pair, then delete the extra row
Missing data Blank cells where a value belongs Fill from another source, or leave blank and log it
Mixed formats Numbers, dates, or phone numbers entered in different styles; letters in place of digits (a lowercase l for a 1); numbers written as words Retype in one consistent format
Hidden characters Extra spaces or non-printing characters that are invisible on screen but make two entries different Trim whitespace
Inconsistent structure Mixed capitalization; some entries include parts others leave out Standardize the format
Incomplete data An entry missing key parts, such as an address with no number or city Complete from another source, or log it
Outliers Values far outside the usual range, often typing errors Check against other sources before changing

Cleaning Rules

Cleaning changes the data. These rules keep every change safe, deliberate, and traceable.

  • Work in a copy. Leave the original file untouched. Why: The original is the only way back if a change goes wrong.
  • Name tabs as you go. Right-click a tab > Rename. Use names that say what the tab holds, such as Raw Data, Change Log, or Cases by Date. Why: Clear names make the workbook easy to navigate and the work easy to follow.
  • Keep a Change Log. Record every change in a Change Log tab. Why: Changes can be checked, explained, and reversed.
  • Use the automatic tools to find problems, then fix them by hand. Why: Automatic tools miss near-duplicates and can change more than intended.
  • Check the source before changing a value. Why: A value that looks wrong may be correct. Another column, note, or record often settles it.

Rows, columns, and values.

Action What It Does When to Use It
Delete a row Removes the whole record Only for a confirmed duplicate
Clear a value Empties one cell; the record stays A value proven wrong with no replacement. Log it.
Edit a value Replaces a cell’s contents; the record stays Trimming, standardizing labels, fixing numbers stored as text, filling blanks
Delete a column Removes a field from every record Rarely. Hide the column instead (right-click > Hide column).
Edit or clear values in a column Changes values; the field stays Standardizing a whole column

Step-by-Step Cleaning Process

The same six steps work in Google Sheets and Excel. Only the menus differ.

The process runs from the moment you receive the data to a finished chart: make your own copy, set up the sheet, find problems, fix them, analyze with pivot tables, and chart the finding. Each step includes a short reason, so the purpose of every action is clear. Follow the version for your platform:


Step-by-Step Cleaning Process: Google Sheets

From the moment you receive the data to a finished chart.

1. Make Your Own Copy

  1. Copy the file. Open the shared link, then File > Make a copy. Give the copy your name (for example, Cholera Data – J. Smith). Why: The original stays untouched. If a change goes wrong, the starting data is still available.
  2. Add a Change Log tab. Click + at the bottom left to add a tab and name it Change Log. In row 1, type five headings: Row or ID, Column, Old Value, New Value, Reason. Why: Every change is recorded in one place, so it can be checked, explained, or reversed.

2. Set Up the Sheet

  1. Label the columns. If the data has no headings, insert a row at the top (right-click row 1 > Insert 1 row above) and name each column. Why: Sorting, cleanup tools, and pivot tables use the headings to identify each column.
  2. Freeze the header row. View > Freeze > 1 row. Why: Sorting rearranges every row that is not frozen. An unfrozen header gets sorted into the data like any other row.
  3. Format the header. Bold it and add a background color. Why: The labels stay visually separate from the data as you scroll.
  4. Size the columns. Right-click a column letter > Resize column. Make columns wide for long entries and narrow for short ones. Why: Values cut off at the cell edge hide problems such as typos and extra words.
  5. Make cells readable. Select all (Ctrl+A), then Format > Wrapping > Wrap and Format > Alignment (top, left). Widen very full columns. Zoom out with View > Zoom if needed. Why: Long entries, such as notes, can be read without clicking into each cell.

3. Find Problems

  1. Review the cleanup suggestions. Data > Data cleanup > Cleanup suggestions. Write down what it flags, then fix those problems by hand in Step 4. Why: The tool gives a quick list of likely problems, but it misses some, and applying its fixes automatically can change more than intended.
  2. Sort each column. Right-click a column letter > Sort sheet A to Z (or Z to A). Why: Sorting puts similar values next to each other. Variant spellings sit side by side, text entries sort after numbers, and blanks fall to the bottom.
  3. Find duplicates by ID. Sort by the ID column and compare rows with the same ID. Why: Near-duplicates can differ by a word in a note, which automatic duplicate tools treat as a different row.
  4. List every label in a column. Build a quick pivot table on the column (see Step 5). Why: A pivot table lists each distinct entry once. Two entries that look identical are either spelling variants or hide extra spaces.

4. Fix Problems

  1. Remove duplicates. Select the data. Data > Data cleanup > Remove duplicates. Check Data has header row, then check only the identifying columns (for example, ID, name, address). Matching rows are deleted. Why: A duplicate row counts the same record twice. Checking only the ID columns catches near-duplicates that differ elsewhere.
  2. Trim whitespace. Select all. Data > Data cleanup > Trim whitespace. Why: An invisible space at the start or end of an entry makes it count as a different value.
  3. Standardize labels. Edit > Find and replace (Ctrl+H). Check Match case and Match entire cell contents, and set Search to the column’s range. Replace one variant at a time. Why: Variants split one group into several. Matching the entire cell keeps the tool from changing text inside longer entries.
  4. Fix numbers stored as text. Sort the column A to Z. Retype each text entry (“two,” “3 ill”) as a number. Why: Sums skip text, so totals come out low without any warning.
  5. Fill blank cells. Blanks sort to the bottom. Fill each one from another source when one exists. Otherwise, leave it blank and log it. Why: A blank counts as zero in a total.
  6. Check outliers. Sort the column Z to A. Check each extreme value against other columns and notes before changing it. Why: One typing error, such as an extra zero, can outweigh many correct values.
  7. Log each change in the Change Log tab as you make it.

5. Analyze with Pivot Tables

A pivot table is a separate table that summarizes selected columns of a dataset and leaves the original data unchanged. It groups the rows by the values in one column, such as location, and calculates a total for each group from a number column, such as cases. The result is one row per group. Changing the grouping column produces a new summary from the same data, which allows different explanations to be compared quickly.

  1. Create the pivot table. Click a cell in the data. Insert > Pivot table > New sheet > Create. Why: A pivot table groups rows and totals them in one step, which would otherwise take many sorts.
  2. Choose a grouping. In the editor, Rows > Add and pick a column (for example, a location).
  3. Choose the totals. Values > Add and pick the number column. Confirm Summarize by is SUM.
  4. Sort by total. Under Rows, set Sort by to the SUM field and Order to Descending. Why: The largest groups appear at the top.
  5. Test another grouping. Remove the Rows field (X) and add a different column. Why: Comparing groupings shows which one concentrates the totals.
  6. Compare over time. Put a date column in Rows, a grouping column in Columns, and the number column in Values. To show one group alone, add the grouping column to Filters instead. Why: A pattern over time is a second line of evidence beside the totals.
  7. Keep a copy. Right-click the pivot tab > Duplicate. Why: Each copy stays a working pivot table that can be changed to show something else.
  8. Reopen the editor by clicking a cell in the pivot table, or the Edit button below it. Pivot tables update when the data changes.

6. Chart the Finding

  1. Select the pivot table and choose Insert > Chart.
  2. Pick the chart type in the editor’s Setup tab. Use a column or bar chart to compare groups and a line chart for change over time. Smooth line chart curves the line.
  3. Keep a smoothed line in view. Customize > Vertical axis > set Min to a small negative number. Why: A smoothed line can dip below zero between points and get cut off at the bottom of the chart.
  4. Title the takeaway. Customize > Chart & axis titles. Why: The title tells the reader what the chart shows.

Step-by-Step Cleaning Process: Excel

The same procedure, with Excel’s menus. Excel only marks steps with no Sheets equivalent.

1. Make Your Own Copy

  1. Download the file. From the shared Google Sheets link, File > Download > Microsoft Excel (.xlsx). Open it in Excel and use File > Save As to save it with your name. Why: The shared original stays untouched, and your copy can be edited freely.
  2. Add a Change Log tab. Click + next to the tabs and name the new tab Change Log. In row 1, type: Row or ID, Column, Old Value, New Value, Reason. Why: Every change is recorded in one place, so it can be checked, explained, or reversed.

2. Set Up the Sheet

  1. Label the columns. If the data has no headings, right-click row 1 > Insert and name each column. Why: Sorting, filters, and PivotTables use the headings to identify each column.
  2. Freeze the header row. View > Freeze Panes > Freeze Top Row. Why: The headings stay visible while you scroll, and the header row is clearly separate from the data.
  3. Format the header. Bold it and add a fill color. Why: Excel and readers both recognize it as a header row.
  4. Size the columns. Double-click the right edge of a column letter, or Home > Format > AutoFit Column Width. Why: Values cut off at the cell edge hide problems.
  5. Make cells readable. Select all (Ctrl+A), then Home > Wrap Text and set alignment in the Alignment group. Widen very full columns. Zoom out with View > Zoom if needed. Why: Long entries can be read without clicking into each cell.

3. Find Problems

  1. Sort each column. Click a cell in the column. Data > Sort A to Z (or Z to A). Why: Similar values sit together, so variants, text entries, and blanks stand out. Excel has no cleanup suggestions tool.
  2. See every value in a column. Data > Filter, then click the column’s arrow. Why: The list shows each distinct entry once, so variants are easy to spot.
  3. Find duplicates by ID. Sort by the ID column and compare rows with the same ID. Why: Near-duplicates can differ by a word, which automatic tools treat as a different row.

4. Fix Problems

  1. Remove duplicates. Click in the data. Data > Remove Duplicates. Click Unselect All, then check only the identifying columns. Excel keeps the first row of each match and deletes the rest. Why: A duplicate counts the same record twice.
  2. Trim whitespace. Excel only: there is no Trim menu command. In an empty column, enter =TRIM( plus the first cell of the column to clean (for example, =TRIM(I2)) and fill it down. Copy the results, then Home > Paste > Paste Values over the original column. Delete the helper column. Why: An invisible space makes an entry count as a different value.
  3. Standardize labels. Select the column. Home > Find & Select > Replace (Ctrl+H) > Options. Check Match case and Match entire cell contents. Replace one variant at a time. Why: Matching the entire cell keeps the tool from changing text inside longer entries.
  4. Fix numbers stored as text. Sort the column A to Z and retype each text entry as a number. Why: Sums skip text, so totals come out low.
  5. Fill blank cells from another source when one exists. Otherwise, leave them blank and log it. Why: A blank counts as zero.
  6. Check outliers. Sort Z to A and check extreme values against other columns and notes. Why: One typing error can outweigh many correct values.
  7. Log each change in the Change Log tab as you make it.

5. Analyze with PivotTables

A pivot table is a separate table that summarizes selected columns of a dataset and leaves the original data unchanged. It groups the rows by the values in one column, such as location, and calculates a total for each group from a number column, such as cases. The result is one row per group. Changing the grouping column produces a new summary from the same data, which allows different explanations to be compared quickly.

  1. Create the PivotTable. Click a cell in the data. Insert > PivotTable > New Worksheet > OK.
  2. Choose a grouping. In the PivotTable Fields pane, drag a column to Rows.
  3. Choose the totals. Drag the number column to Values. If it shows Count of, click its arrow > Value Field Settings > Sum. Why: Excel counts instead of sums when a column contains any text.
  4. Sort by total. Right-click a total > Sort > Sort Largest to Smallest.
  5. Test another grouping. Drag the Rows field out and drag in a different column.
  6. Compare over time. Date column in Rows, grouping column in Columns, number column in Values. Use Filters to show one group.
  7. Refresh after cleaning. Excel only: PivotTable Analyze > Refresh (Alt+F5). Why: Excel PivotTables do not always update when the data changes.
  8. Keep a copy. Right-click the tab > Move or Copy > check Create a copy.

6. Chart the Finding

  1. Click the PivotTable and choose PivotTable Analyze > PivotChart.
  2. Pick the chart type. Column or bar to compare groups; line for change over time.
  3. Smooth the line (optional). Double-click the line > Format Data Series > paint bucket icon > check Smoothed line.
  4. Keep a smoothed line in view. Select the vertical axis > Format Selection > Axis Options > set Minimum to a small negative number. Why: A smoothed line can dip below zero and get cut off.
  5. Title the takeaway. Click the chart title and type the finding.

Sources & Further Reading

Everything referenced above, if you want to go to the originals.