Data imported or copied into Excel often requires cleanup before it can be analyzed effectively. Although Excel provides numerous ways to clean and restructure data, repeatedly making the same corrections can consume time and introduce inconsistencies. This session explores approaches for cleaning text, separating and combining information, removing unwanted data, restructuring reports, and creating repeatable cleanup processes with Power Query.
This presentation answers the question:
• How can I reduce repetitive data cleanup decisions in Excel?
About David:
• Background: CPA with 35 years of spreadsheet consulting experience
• Instructor: More than 2,500 live Excel webinars, including monthly Excel webinars for CCH CPELink since 2009
• Author: Eleven books, including Microsoft 365 Excel for Dummies and Exploring Microsoft Excel's Hidden Treasures
Teaching approach:
• Demonstration: Each technique shown first with numbered PowerPoint steps, then demonstrated live in Excel
• Version guidance: Differences in perpetually licensed versions of Excel highlighted during the presentation and in the handouts
• Practice materials: Excel workbook with most examples demonstrated during the session
• Mac support: Follow-up questions from Excel for Mac users answered via email
Who Should Attend
Excel users who regularly clean, reshape, or prepare data and want to reduce repetitive manual cleanup work.
Topics Covered
- Workflows/features covered include:
- Cleaning Text: TRIM, CLEAN, and related text-cleanup techniques
- Transforming Text: Flash Fill for separating and combining information
- Separating Data: Text to Columns and address extraction
- Removing Unwanted Data: Remove Duplicates and Power Query filtering techniques
- Automating Data Cleanup: importing, filtering, changing data types, merging, and splitting with Power Query
- Restructuring Reports: unpivoting data with Power Query
- Refreshing Cleaned Data: query refresh and load settings
Learning Objectives
- Identify the purpose of the TRIM function when cleaning text in Excel
- Determine when Flash Fill can be used to transform text based on a recognizable pattern
- Identify when Text to Columns can be used to separate text into multiple fields
- Determine when Power Query provides a reusable approach to recurring data cleanup
- Recall the purpose of unpivoting data in Power Query
Level
Intermediate
Instructional Method
Group: Internet-based
NASBA Field of Study
Computer Software & Applications (2 hours)
Program Prerequisites
Prior experience working with Excel spreadsheets.
Advance Preparation
None
Instructor
David H. Ringstrom
David H. Ringstrom, CPA, is an author and nationally recognized instructor who teaches over 200 webinars each year. His Excel courses are based on over 30 years of consulting and teaching experience. David’s catchphrase is “Either you work Excel, or it works you,” so he focuses on what he sees users don’t, but should, know about Microsoft Excel. His goal is to empower you to use Excel more effectively. He's currently writing a book titled "Exploring Excel's Hidden Treasures". To learn more about David, you can view his
LinkedIn profile and follow him on
Facebook or
Twitter (@excelwriter).