Class Central is learner-supported. When you buy through links on our site, we may earn an affiliate commission.

Basic Excel Business Analytics - Clean & Transform Data - Formulas, Flash Fill, Power Query, Text To Columns

ExcelIsFun via YouTube

Start learning Write review

Details

Start learning

Provider

YouTube
Pricing

Free Video
Languages

English
Effort

30 minutes
Sessions

Self-Paced
Level

Beginner

Found in

This course demonstrates how to clean and transform existing Excel data using formulas, Flash Fill, Text to Columns, and Power Query. Examples include converting dates, splitting fields, improving labels, extracting transaction details, and comparing dynamic refresh behavior.

Syllabus

) Intro to Import, Clean and Transform Data for this section of the class.
) Use VLOOKUP to create better labels for our data set and for our PivotTable Report with % of Column Totals and a Slicer (Filter).
) Get rid of extra spaces with the TRIM Function.
) Get rid of extra spaces with Flash Fill.
) Formula: Convert ISO Dates like 20140212 (Year, Month, Day) to Serial Number Dates. TEXT function, Custom Number Format “0000-00-00” and add zero (any math operation) to convert number stroed as text back to a number..
) Text To Column: Convert ISO Dates like 20140212 (Year, Month, Day) to Serial Number Dates..
) Power Query: Convert ISO Dates like 20140212 (Year, Month, Day) to Serial Number Dates..
) Formula: Split Region and City from Same Cell. LEFT and SEARCH functions..
) Flash Fill: Split Region and City from Same Cell..
) Text To Columns: Split Region and City from Same Cell..
) Formulas: Get Date and Sales from a transaction description in a single cell. See the MID, SUBSTITUTE, SEARCH and REPLACE functions..
) Power Query: Get Department, Product, Date and Sales from a transaction description in a single cell..
) Compare the dynamic (ability to update when source data changes) aspects of Formulas and Power Query..
) Summary and Conclusion.