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

Overview

Save Big on Coursera Plus. 7,000+ courses at $160 off. Limited Time Only!

Grab it

Explore essential techniques for cleaning and transforming data in Excel to prepare for analysis in this 30-minute tutorial. Master the use of formulas, Flash Fill, Power Query, and Text to Columns to enhance data quality and usability. Learn to create better labels using VLOOKUP, remove extra spaces with TRIM and Flash Fill, convert ISO dates to serial numbers, split combined data into separate columns, and extract information from transaction descriptions. Compare the dynamic updating capabilities of formulas and Power Query, and gain practical skills to streamline your data preparation process for more effective business analytics.

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.