MO Lesson 2.10 - Data Cleanup and Automation 2

Learners can:

• identify and select appropriate delimiters for splitting text data
• determine the purpose of removing duplicate data entries
• describe how the Flash Fill identifies patterns in data
• sort the steps to use the Flash Fill tool
• analyze the use of the Split Text to Columns tool

Monday, Wednesday, Friday 2:20-3:05 pm

Warm Up: Each day we will begin with a question or activity to get us started!

Welcome to Microsoft Office Mrs. Vandernick lvandernick@ccaeducate.me (717) 710-3300 extension 11814

The Plan for Today: Unit 2 Lesson: 2.10 Data Cleanup and Automation Goals:

• delimiter
• Flash Fill

Vocabulary

Just like having a perfect shopping list can save your barbecue, mastering these Excel tools can save you time and ensure accuracy in all your data tasks.

• Imagine you are getting ready for a big weekend, and you are responsible for grocery shopping for a family barbecue. You have been given a shopping list, but as you stroll through the aisles, you notice some problems: items are listed multiple times, some essentials are missing, and others are described vaguely. The chaotic list causes you to spend double the time shopping and miss out on key ingredients. The result? A disappointing barbecue with missing items and last-minute rushing to the store.

A Disorganized List...

• The Split Text to Columns tool can be used to help you break down text data that is combined in a single column into separate columns.
• For example, if you have a column containing names and email addresses, you can use this tool to split the names into two separate columns: one for first names and another for email addresses.

1. Select the column or range of cells containing the data you want to split. 2. Go to the "Data" tab in the ribbon. 3. Click on the "Split Text to Columns" button. 4. A wizard will appear, guiding you through the process of splitting the text.

• Three powerful tools in Excel that are extremely useful for data organization include:
• Split Text to Columns
• Remove Duplicates
• Flash Fill

Error Free Data

Error Free Data

• The wizard allows you to specify the delimiter (separator) that separates the data within each cell.
• Common delimiters include commas, spaces, tabs, or custom characters.
• You can also choose the data format for each resulting column (e.g., text, date, or number).

• Separating Full Names: You can split a column with full names (like "John Doe") into two columns: one for first names and one for last names. This helps with sorting, filtering, or analyzing names.
• Splitting Addresses: Addresses have parts like street, city, state, and zip code. Splitting them into separate columns makes it easier to analyze or work with each part.
• Extracting Date Components: You can split a date like "06/01/2024" into separate columns for month, day, and year. This helps with date calculations, filtering, or sorting by specific parts of the date.
• Separating Product Codes: In inventory or product catalogs, product codes may have segments for things like category, size, and color. Splitting these into separate columns helps you analyze and filter products by these attributes.
• Organizing Contact Information: Contact information includes phone numbers, emails, and addresses. Splitting them into separate columns makes it easier to manage and analyze.

Common Scenarios for Applying the Split Text to Columns Tool

Question...

• Removing duplicates is important for data cleaning because duplicates can cause inaccurate analysis and incorrect decisions. Here's why it's essential:
• Data Integrity: Duplicates can create errors and inconsistencies in your data.
• Efficient Management: Duplicates increase the dataset size, making it harder to manage.
• Accurate Reporting: Duplicates lead to incorrect totals and summaries, affecting reports and decisions.

Removing Duplicates

• Data Entry Errors: Human errors during manual data entry.
• Merging Datasets: When combining data from multiple sources, duplicates may arise due to overlapping records.
• Tracking Changes: In some cases, duplicate records are intentionally created to track changes over time.

Where Do Duplicates Come From?

You can easily remove duplicate data to help ensure that your dataset is ready to use.

1. Select the Range of Cells to Check
2. Navigate to the Data Tab and Select Remove Duplicates
3. Choose the Columns to Check for Duplicates
4. Review the Result and Confirm the Duplicate Removal

Steps to Remove Duplicates

Question...

1. Data Formatting: Quickly change data to a uniform style, like making text all uppercase or lowercase, or removing extra spaces.
2. Data Extraction: Pull out specific information from a larger text, like product names, email addresses, or phone numbers.
3. Data Transformation: Change data from one format to another, like converting dates or splitting one column into several columns.
4. Fix messy data by removing unwanted characters, trimming extra spaces, or replacing certain values.

Common scenarios for utilizing Flash Fill:

• Flash Fill is a feature in Microsoft Excel that automatically fills in cells based on patterns it sees in your data. It saves you time and effort, especially when you have a lot of data or repetitive tasks to do.

Flash Fill Tool

The Flash Fill tool can be used two different ways:

1. After entering enough data, Flash Fill recognizes a pattern and suggests how to fill in the rest of the cells. If the suggestion looks good, just press Enter to accept it.
2. If Flash Fill can’t automatically suggest anything due to insufficient data, you can use it manually. Just select your data and then choose the Flash Fill tool to help it find patterns and fill in the rest.

Flash Fill Use...

Question...

Question...

Question...

Match the Excel feature with its primary function.

Question...

Match the Excel feature with its primary function.

Question...

Match the Excel feature with its primary function.

Question...

Today, you learned:

• to identify and select appropriate delimiters for splitting text data using the Split Text to Columns tool,
• to use the Split Text to Columns tool to separate combined data into individual columns;
• the importance of removing duplicate data entries and how it helps maintain data integrity;
• how Flash Fill identifies patterns in data based on the examples provided; and
• to use the Flash Fill tool to automate repetitive data entry tasks efficiently.
• In an upcoming lesson, you will work on an assignment using Excel for budgeting.

Summary