Excel: Data cleaning and analysis techniques

Learn to clean and reformat data in excel for analysis using text functions. Address common data issues and apply examples with left, right, find, concatenate, proper, and search.

Download the excel template provided with this module and follow along for hands-on practice in data cleaning and analysis techniques.

Explore how to convert student personal emails into college domain.edu emails using Excel text functions and a reusable formula, enabling automatic generation for about 1000 records.

Convert personal student emails to college domain addresses using excel text functions len, find, left, and concatenate, form a single formula, and replicate across many rows.

Clean system generated records in Excel using the trim and find functions to remove leading spaces and locate hyphens, then split into date, product, price, and quantity for analysis.

Apply trim, find, length, left, mid, and value in Excel to extract date, product id, price, and quantity from hyphen-delimited text, convert dates, and replicate formulas across cells.

Explore illustration three to see how Excel text functions automate classifying qualitative feedback into positive or negative, using the illustration three tab in the provided module.

Use Excel's search function to locate positive and negative keywords in qualitative feedback by returning each keyword's position. Lock references with dollar signs when copying the formula across and down.

The lecture demonstrates using the IFERROR function to replace error results from a keyword search with zero, copying the formula across cells to handle missing keywords in qualitative feedback rules.

Illustration of using the if function in Excel to classify qualitative feedback as positive or negative by summing positive keywords and applying a logical test.

Recap the key text functions used to clean data in Excel, including length, find, left, concatenate, mid, trim, value, search, and if error, for ready-to-analyze datasets.