Data Cleaning Essentials

The Project: Cleaning Concession Stand Data

To make this practical, we will work on a beginner-friendly data cleaning project. This project uses a real-world scenario: cleaning sales data for a fictitious company, "KAT Concession Supply."

First, let's get acquainted with the project and set up your workspace.

Executing the Cleaning Tasks

Now that you have your own copy of the KAT Concession Supply data, we can begin cleaning. We will follow the requirements listed in the project document you just read, which mirror the "cleaning plan" you learned to create.

1. Adjusting Column Widths

First, a simple but important step for readability. The data is hard to read when it's cramped in the cells. Let's fix that.

1. Select all cells by clicking the empty rectangle in the top-left corner (to the left of column A and above row 1), or by pressing Ctrl+A (Cmd+A on Mac).
2. Hover your mouse over the line between any two column headers (e.g., between A and B) until the cursor changes to a double-sided arrow.
3. Double-click. This will automatically resize each column to fit its content.

This quick trick is a great first step in making any dataset more manageable.

2. Removing Duplicate Records

Duplicate rows are one of the most common and serious data quality issues, as they can lead to inflated counts and incorrect analysis. Let's find and remove them.

1. With all your data still selected (Ctrl+A), navigate to the menu shown in the image above: Data > Data cleanup > Remove duplicates.
2. A dialog box will appear. Check the box for Data has header row.
3. Ensure all columns are selected. This tells Sheets to look for rows that are identical across every column.
4. Click Remove duplicates. Google Sheets will tell you how many duplicate rows it found and removed.

This single action significantly improves the integrity of your data. For a more detailed guide on this process, you can refer to the ModelMonkey article.

3. Cleaning and Standardizing Text

Much of data cleaning involves fixing text. This includes removing extra spaces, correcting capitalization, and replacing incorrect values. We will use a combination of functions and tools to do this.

A. Removing Extra Spaces and Changing Case

In your dataset, the project requires you to clean the Transaction year (original) column and change the Warehouse state (original) to uppercase. We'll use a standard professional workflow involving "helper columns" and three key functions:

• TRIM(): Removes leading and trailing spaces from text.
• CLEAN(): Removes non-printable characters (like line breaks) that can be hidden in data imported from other systems.
• UPPER(): Converts all text to uppercase. (LOWER() and PROPER() are also available for lowercase and title case, respectively).

Let's watch a video that demonstrates this workflow. While the video uses Excel, the functions and the "copy/paste values" technique are identical in Google Sheets.

Now, apply this to your project sheet for the Warehouse state column:

1. Insert a new column to the right of Warehouse state (original). You can call it Temp.
2. In the first cell of your new column (e.g., L2), enter the formula: =UPPER(TRIM(K2)). This will take the value from cell K2, remove any extra spaces, and convert it to uppercase.
3. Click on the cell with the formula. A small blue square will appear in the bottom-right corner. Double-click this square to automatically fill the formula down for all rows.
4. Select the entire Temp column, and copy it (Ctrl+C).
5. Select the first cell of the Warehouse state (edited) column (cell M2).
6. Paste the data as values only by using the shortcut Ctrl+Shift+V (or by right-clicking and selecting Paste special > Values only).
7. You can now delete the Temp column.

Repeat a similar process for the Transaction year (original) column, using the formula =TRIM(CLEAN(C2)) in a helper column and pasting the values into Transaction year (edited).

B. Find and Replace

The project requires you to replace all instances of “coffee” with “cola.” The fastest way to do this is with the Find and Replace tool.

1. Select the columns you want to search (in this case, Columns G and H).
2. Go to Edit > Find and replace (or use the shortcut Ctrl+H).
3. In the "Find" box, type coffee.
4. In the "Replace with" box, type cola.
5. Click Replace all. Sheets will confirm how many instances were replaced.

This tool is incredibly efficient for correcting widespread, consistent errors.

4. Splitting Text to Columns

The project dataset doesn't require this, but it's a critical skill included in our learning outcome. Often, you'll receive data where multiple pieces of information are in a single column, like "John Smith" in a Full Name column. Google Sheets can easily split this.

Let's watch a quick demonstration of the "Text to Columns" feature.

To try this yourself (hypothetically):

1. Select a column containing data you want to split (e.g., a column of full names).
2. Go to Data > Split text to columns.
3. A small box will appear, allowing you to choose the delimiter (e.g., comma, space, or a custom character).
4. Sheets will automatically split the text into the adjacent columns to the right. Be careful, as this will overwrite any data in those columns!

5. Handling Missing Values and Errors

Blank cells and error messages can disrupt analysis. The project requires you to format some columns as currency, but first, you must handle any non-numeric data in those cells.

A common strategy is to replace blanks or errors with a specific value like "N/A", "Unknown", or 0.

For your project, once you have cleaned any errors in columns AE through AI, you can apply the currency formatting as required:

1. Select the columns (e.g., AE through AH).
2. Go to Format > Number > Currency.
3. To change the number of decimal places, use the "Decrease decimal places" or "Increase decimal places" icons in the toolbar.

Conclusion

Congratulations on working through your first full data cleaning project! You have taken a raw, messy file and systematically transformed it into a clean, reliable dataset. This process is the bedrock of all credible data analysis and a highly valuable skill for any data researcher.

Your key takeaways from this lesson are the practical techniques you applied:

• Remove Duplicates: Use the built-in tool to ensure data integrity.
• Standardize Text: Master the helper column workflow with functions like TRIM, CLEAN, UPPER, LOWER, and PROPER, followed by "Paste values only."
• Correct in Bulk: Use Find and Replace for fast, consistent corrections.
• Restructure Data: Split a single column into multiple columns using Split text to columns.
• Handle Blanks and Errors: Fill empty cells and use functions like IFERROR to manage missing or invalid data.

In our next lesson, we will focus on the final step of this project: restructuring the cleaned data into a well-organized final output. This involves arranging columns, ensuring the format is logical, and preparing the dataset for delivery or analysis.