Excel Data Cleansing Straight to the Point E-Book

MrExcel's Holy Macro! Books

• Herausgeber: Packt Publishing
• Kategorie: Lebensstil
• Sprache: Englisch

Seitenzahl: 39

Veröffentlichungsjahr: 2024

Beschreibung

This book offers a deep dive into data cleansing using Excel. The content is built for professionals who frequently encounter messy, unstructured, or inconsistent data and need a systematic approach to clean and organize it. Readers will explore strategies to correct names, manage duplicates, parse data, and merge multiple datasets effectively.
Practical examples guide the reader through the intricacies of Excel’s functions, focusing on actionable solutions and enhancing productivity. Whether it’s about removing duplicates, segmenting datasets, or flattening complex reports, this book provides easy-to-follow methods to ensure data becomes useful and accessible.
By combining clear explanations with real-world applications, this book equips readers to handle diverse data challenges with confidence. Professionals will be empowered to make better decisions and streamline their workflows through cleaner, more reliable data.

Leseprobe

Straight to the Point

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

Excel Data CleansingStraight 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 2019Author: 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 platform. He’s best known for the dramatic and colorful Excel tutorials on his YouTube channel Excel on Fire.

Data cleansing is Oz’s area of specialization in Excel. From his earliest days with Excel, he has found himself constantly needing to fix names that are ALLCAPS, peel addresses away from phone numbers, clean up the messes that result from extracting data from PDF files, and fix all the many other things that prevent data from being useful.

Oz has presented Excel topics and master classes at conferences in Amsterdam; Sofia, Bulgaria; São Paulo, Brazil; Toronto, Canada; and cities around the United States.

When Oz isn’t elbows-deep in the guts of a spreadsheet, he does storytelling around Portland, Oregon. He has told stories onstage for Risk!, Pants on Fire, Seven Deadly Sins, Pickathon, The Moth, and other storytelling shows.

Acknowledgments

Thanks to MrExcel, Bill Jelen, for the opportunity to share one of my favorite Excel topics.

INTRODUCTION

A newspaper reporter once described to me the problem she had in getting the public school system to turn over data that it was legally required to turn over. In the face of school closings and outraged parents, the mayor and school administrators kept repeating things like “the data say…” and “the data tell us…”—and the reporter wanted to see this omniscient data.

She eventually did get the data, and it was a mess. The law didn’t require the data to be free of duplicates and ready to be sorted, filtered, and analyzed. And that’s where the investigation ended because the reporter didn’t have the skill or resources to cleanse the data.

Lawyers have complained about similar problems. The opposing side is required to provide data—but that data doesn’t have to be immediately useful. Someone has to take emails, PDFs, and reports from various banks, PayPal, QuickBooks, etc.; get the formats all the same; and then merge them together before any analysis can happen.

Without clean data, no analysis can happen.

This Straight to the Point guide provides an introduction to data cleansing, which also goes by names such as data munging and data wrangling