Skip to main content
Back to path

Welcome to a Grasp lesson

Create a free account to keep studying and create your own study path.

Lesson 23

Standardizing Data Formats

Recommended resources

Introduction

Welcome to the next step in our data cleaning journey. In our last lesson, you learned how to split data from a single column into multiple columns, a key skill for structuring raw data. Now that our data has the right structure, we need to ensure the information within each column is consistent.

Today, we will focus on standardizing data formats. This means making sure that similar data entries all look and behave the same way. For a freelance data researcher, presenting a clean, uniformly formatted dataset is a mark of professionalism. It also makes the data much easier to sort, filter, and analyze accurately. We will cover common standardization tasks for text (like names), dates, and phone numbers in both Excel and Google Sheets.

Why Standardization Matters

Before we dive into the "how," let's quickly look at the "why." Datasets you collect will often have a variety of inconsistencies that can cause problems later.

Cleaning Data in Excel | Excel Tutorials for Beginners

The video "Cleaning Data in Excel" by Alex The Analyst provides an excellent overview of the exact issues we'll be tackling.

Watch the segment from 02:24 to 03:41. The narrator points out several common problems in a raw dataset: inconsistent capitalization ("james monroe" vs. "James Monroe"), extra spaces, currency symbols that interfere with calculations, and mixed date formats. These are the classic signs of unstandardized data.

As you can see, these small inconsistencies can make a dataset look messy and unreliable. Let's learn the practical techniques to fix them.

Standardizing Text Data

Two of the most frequent issues with text are inconsistent capitalization and unwanted spaces. Both Excel and Google Sheets use simple functions to solve these problems.

1. Fixing Capitalization and Extra Spaces

Imagine a column of names with a mix of "JOHN SMITH," "john smith," and " John Smith ". Our goal is to make them all look like "John Smith". We can do this with two key functions:

  • PROPER(text): Converts text to "Proper Case," where the first letter of each word is capitalized. This is perfect for names. You can also use UPPER() for all uppercase or LOWER() for all lowercase if a project requires it.
  • TRIM(text): Removes all extra spaces from text, including leading spaces, trailing spaces, and extra spaces between words.

The following video demonstrates how to use these functions in Excel. The process is identical in Google Sheets.

Cleaning Data in Excel | Excel Tutorials for Beginners

This video provides a practical, step-by-step guide to using these functions.

First, watch how to use PROPER() to fix inconsistent capitalization from 05:25 to 06:55. The key idea is to create a new, temporary column to apply the formula. Next, see how the TRIM() function is used to remove hidden or unwanted spaces from 10:53 to 11:40. Pay attention to the explanation of how spaces before, after, or in the middle of text are all handled.

2. Making Your Changes Permanent

After using functions like PROPER or TRIM, the new column contains formulas, not the actual cleaned text. If you delete the original column, your new column will show errors. To finalize your work, you need to replace the formulas with their resulting values. This is done by "pasting as values."

Cleaning Data in Excel | Excel Tutorials for Beginners

This final clip from the same video shows this essential step.

Watch the process from 15:58 to 17:15. The narrator copies the cells containing the formulas, then uses a special paste option to paste only the resulting values back into the original column (or a new one). This "bakes in" your corrections.

To summarize the workflow for text cleaning:

  1. Insert a new column next to the one you want to clean.
  2. In the new column, enter your formula (e.g., =PROPER(A2) or =TRIM(A2)).
  3. Apply the formula down the entire column.
  4. Select the entire new column, copy it (Ctrl+C).
  5. Select the first cell of the column where you want the final, cleaned data.
  6. Right-click and choose Paste Special > Values (in Excel, it's an icon with "123"; in Google Sheets, it's Edit > Paste special > Values only).
  7. Delete the temporary formula column.

Standardizing Dates and Numbers

Inconsistent dates (e.g., 12/25/2023 vs. 25-Dec-2023) or phone numbers (e.g., 5551234567 vs. (555) 123-4567) can prevent proper sorting and filtering. Let's fix these using built-in formatting tools.

1. Standardizing Date Formats

The goal is to apply a single, consistent date format to an entire column.

In Excel:
The process is straightforward. You select the cells and choose a standard format.

Cleaning Data in Excel | Excel Tutorials for Beginners

The "Cleaning Data in Excel" video demonstrates this.

Watch from 13:48 to 15:43 to see how to select a column of dates with mixed formats and apply a uniform "Short Date" format to make them all consistent.

In Google Sheets:
You have similar tools, with the added option of setting a "Locale" to define default formats for the entire file.

Adjusting Date Formats in Google Sheets: A Simple Guide

This guide from SpreadsheetWise clearly explains the two main methods for standardizing dates in Google Sheets.

First, read "Method 1: Using the Format Menu". Focus on the steps to apply a specific format, like Date, to a selection of cells. This is great for quick, targeted changes. Next, read "Method 2: Adjusting Locale Settings". Understand how to use the File > Settings > Locale menu. Changing the locale (e.g., to United Kingdom or United States) is a powerful way to set the default date and currency format for your entire spreadsheet, which is very useful for international projects.

The Custom date and time formats menu in Google Sheets, which you can access from `Format > Number`, allows you to build any date style you need.

2. Standardizing Phone Number Formats

Phone numbers are often entered as a string of digits. Applying a custom number format can make them readable and consistent without changing the underlying numerical value.

In Excel:
Excel's custom formatting tool lets you define a pattern using placeholders.

How to Format Phone Numbers in Excel - Tutorial

This tutorial by "Excel Tutorials by Y. Acosta" is focused specifically on formatting phone numbers.

First, watch the brief introduction from 00:00 to 00:27. It makes an important point: formatting only affects the appearance, not the actual number stored in the cell. Then, skip to the section on custom formats from 02:57 to 04:22. This is the most powerful method. Pay close attention to how 0 or # are used as placeholders to build a format like 000-000-0000.

In Google Sheets:
The process is nearly identical to Excel, using the same placeholder logic.

How to Format Phone Numbers in Google Sheets - LiveFlow

This LiveFlow article provides a concise walkthrough for Google Sheets.

Read the main steps in the article. Focus on the process of navigating to Format > Number > Custom Number Format and entering a pattern like 000-000-0000 into the text box.

Applying a custom format like `"+"#"-"###"-"###"-"####"` in Google Sheets to create a standardized phone number display.

Conclusion

In this lesson, you've acquired a set of essential skills for bringing consistency to your datasets. Standardizing your data is a non-negotiable step in preparing a professional-grade deliverable for any data research project.

Here are the key takeaways:

  • Text Standardization: Use PROPER() for consistent capitalization and TRIM() to remove unwanted spaces. Remember to make these changes permanent with Paste Special > Values.
  • Date Standardization: Use the Format > Number > Date menu in both Excel and Sheets to apply a consistent style. In Google Sheets, you can also set the Locale for file-wide defaults.
  • Phone Number Standardization: Use Custom Number Formats to apply a consistent visual style (e.g., ###-###-####) without altering the underlying numerical data.

We are nearing the end of our data cleaning module. So far, we've tackled errors, duplicates, structure, and formatting. In the next lesson, we will address the final common problem: handling missing values. You will learn strategies to identify and manage empty cells in your dataset to ensure it's complete and ready for analysis.