Basic Excel Business Analytics - Clean & Transform Data - Formulas, Flash Fill, Power Query, Text To Columns
ExcelIsFun via YouTube
Overview
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.
Taught by
ExcelIsFun