MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel Data Cleansing Straight To The Point


July 2019

Excel Data Cleansing Straight To The Point

Covers many ways to clean data in Excel, both with and without Power Query

This book helps you decide when to use straight Excel and when to use Power Query.

Bill Jelen, MrExcel

category: Power Query
covers: Excel 365

Product Details
  • 47 Pages
  • Publisher: Holy Macro! Books
  • PDF ISBN: 978-1-61547-150-8

This Straight to the Point guide provides an introduction to data cleansing, which also goes by names such as data munging and data wrangling. Whatever the name, it basically means doing what needs to be done to make data useful and trustworthy. Data cleansing can include the following tasks:

  • Deleting unnecessary headers
  • Deleting summary rows
  • Filling in gaps
  • Flattening a report
  • Merging and appending data from multiple sources
  • Pulling data from source X to complete data in source Y
  • Splitting names from addresses
  • Identifying and deleting duplicate records
  • Converting units of measurement in multiple sources
  • Transposing data so it appears in columns rather than rows
  • INTRODUCTION
    • A Data Cleansing Example
    • Data Cleansing as a Skill
    • The Straight to the Point Ethos
  • CORRECTING NAMES: PROPER CASE
  • COMPARING LISTS: WHAT’S OVER HERE THAT’S NOT OVER THERE?
    • Invitations and Responses (Match It All Up!)
    • Determining What’s over There That’s Not over Here
    • A Word About Strategy
  • PEELING, PARSING, AND SEGMENTING
    • Extracting the First Name (Using Flash Fill)
    • Splitting by a Single Delimiter: Separating the City from the Name
    • Splitting into Rows: Getting Those People Out of There!
  • IDENTIFYING DUPLICATE RECORDS: FUZZY MATCHING
    • Excel’s Duplicate Remover: The Hazard!
    • Reality, Context, and Strategy: Flagging Records for Review Instead of Clearing Duplicates
  • MERGING AND APPENDING MULTIPLE WORKBOOKS
  • FROM USELESS TO USEFUL: FLATTENING A REPORT
    • Let’s Flatten Some Stuff!
  • FINAL THOUGHTS

About The 'Straight to the Point' Series

Books in this series are designed to thoroughly cover one targeted aspect of Excel.



Where to Buy

MrExcel Bookstore

eBook from MrExcel.

Add to Cart »

Hear the Figures

If you are using assistive reading technology (for low-vision, dyslexia, mobility impairments, attention issues, or if you need to read hands free), this e-book is "born accessible", with a complete description attached to each screenshot so you can follow along with the text.

Related Products


M is for (DATA) MONKEY

December 2015

Despite the moniker "data monkey", we information workers are often more like data magicians. Our data seldom enters our world in a ready-to-consume format; it can take hours of cleaning, filtering, and reshaping to get things ready to go. Power Query will make this process faster the first time and reduce it to a single button click every subsequent time.


Guerrilla Data Analysis 2nd Edition

May 2015

This book includes step-by-step examples and case studies that teach users the many power tricks for analyzing data in Excel. These tips have been honed by Oz du Soleil, a former financial analyst charged with taking mainframe data and turning it into useful information quickly, and "MrExcel" himself, Bill Jelen.


Cleaning Excel Data With Power Query Straight To The Point

March 2019

This book provides a quick Straight-to-the-Point introduction to Power Query. There’s a lot here, and there’s also a lot that’s not here. Reading this book will be like sitting down at a café and striking up a conversation with the person at the next table, where you learn enough random stuff about that person to decide if you want to know more and keep in touch.