Excel Data Cleansing Straight to the Point

By Oz du Soleil

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 headersDeleting summary rowsFilling in gapsFlattening a reportMerging and appending data from multiple sourcesPulling data from source X to complete data in source YSplitting names from addressesIdentifying and deleting duplicate recordsConverting units of measurement in multiple sources

• Language: English
• Publisher: Independent Publishers Group
• Release date: Jul 23, 2019
• ISBN: 9781615471508

Book preview

Straight to the Point

The Straight to the Point e-books are designed to thoroughly cover one targeted aspect of Excel.

Excel Data Cleansing

Straight to the Point

Oz du Soleil

Holy Macro! Books

PO Box 541731, Merritt Island FL 32953

Excel Data Cleansing Straight to the Point

© 2019 by Tickling Keys, Inc.

All rights reserved. No part of this book may be reproduced or transmitted in any form or by any means, electronic or mechanical, including photocopying, recording, or by any information or storage retrieval system without written permission from the publisher.

All terms known in this book known to be trademarks have been appropriately capitalized. Trademarks are the property of their respective owners and are not affiliated with Holy Macro! Books

Every effort has been made to make this book as complete and accurate as possible, but no warranty or fitness is implied. The information is provided on an as is basis. The authors and the publisher shall have neither liability nor responsibility to any person or entity with respect to any loss or damages arising from the information contained in this book.

First Published: July 2019

Author: Oz du Soleil

Copyeditor: Kitty Wilson

Cover Design: Suat M. Ozgur

Cover Illustration: Shannon Mattiza, 6'4 Productions

Indexer: Nellie Jay

Tech Editor: Bill Jelen

Screen Reader Captions: Deb Govern

Compositor: Jill Cabot

Published by: Holy Macro! Books, PO Box 541731, Merritt Island, FL 32953

Distributed by Independent Publishers Group, Chicago, IL

ISBN 978-1-61547-150-8

Table of Contents

About the Author

Acknowledgments

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

Index

This page intentionally left blank. The Introduction starts on the next page.

About the Author

Oz du Soleil is an Excel MVP who’s been working with Excel since 2001. He’s co-author, along with Bill Jelen, of Guerrilla Data Analysis, 2nd edition. Oz has several Excel courses on the LinkedIn Learning

Reviews for Excel Data Cleansing Straight to the Point

[Dania · Rating: 4 out of 5 stars]

Straight to the point tips for intermediate users.. a quick and resourceful read.

[wtc · Rating: 5 out of 5 stars]

Impressive range of techniques for cleanup and use of data. The focus on what you’re asking rather than just what you have makes it easier to solve your own real world problems.

Guidance at the end (for example, use a copy of your data) should go without saying but really needs to be kept in mind.

An excellent introduction to build on your skills.