Jelen Bill - Guerrilla Data Analysis Using Microsoft Excel

by

Oz Du Soleil & Bill Jelen

Holy Macro! Books

PO Box 541731

Merritt Island, FL 32953

Guerrilla Data Analysis 3 rd Edition

2022 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 permission from the publisher. 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.

Authors: Oz Du Soleil & Bill Jelen

Layout: Bronkella Publishing

Copyediting: Kitty Wilson

Cover Design: Shannon Travise

Indexing: Nellie Jay

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

Distributed by: Independent Publishers Group, Chicago, IL

First Printing: June 2022

Revision: 202206051010

ISBN: 978-1-61547-074-7 Print, 978-1-61547-160-7 e-Book

Library of Congress Control Number: 2022938519

Dedications

To my mother, Maere Floyd

To all of my followers at Excel on Fire, students whove taken my courses on LinkedIn, and anyone whos learned anything from me about Excel. Were in a battle here, and I appreciate you for being committed to a world free of crap data.

Oz

To Ron Luther, the first guerrilla data analyst I ever knew.

Bill

About the Authors

Oz du Soleil was first awarded the Excel MVP Award in January 2015. Hes an author, a popular instructor on the LinkedIn Learning Library, and the host of the YouTube channel Excel on Fire. Oz is also a storyteller whos told stories for the Risk! podcast and live shows, as well as many shows around Portland, Oregon.

Bill Jelen is the author of 66 books and the host of MrExcel.com. He has been awarded the Microsoft MVP in Excel for more than 15 years.

Acknowledgments

Thanks to Bill Jelen for allowing me to be part of this Guerrilla Data Analysis mission that he started in 2002, invited me to update in 2014, and continued in 2022 with this third edition of the book.

Thanks to Helena Bouchez, who coached me in getting the second edition completed. The lessons were with me throughout the writing of this edition.

Huge thanks to the owners of Cascade Cigar & Tobacco in Happy Valley, Oregon, where much of this book was written over cigars and caf mochasmany days from open to close, 10 a.m. to 8 p.m.

Thank you to several people whove been crucial in this happy life that I get to live: Raymond Christian, Deirdre Gruendler, Terri Knight, Mike Land, Happy Little, Andrea Mize, Precious Molyneux, and Charlie Vlahogiannis.

Thank you to the Microsoft MVP community.

Oz

Thank you, Oz, for being a great co-author and keeping me writing. Thank you, Kitty Wilson, for copyediting. Thanks to Bob Umlas for tech editing. Thank you, Mary Ellen Jelen, for keeping me focused on writing.

Bill

Oz at Cascade Cigar & Tobacco

Table of Contents

Introduction: Welcome to the World of Guerrilla Data Analysis!

Over the years that Ive been consulting, teaching workshops, writing a blog, and creating videos for my YouTube channel Excel on Fire, its gotten clear to me that there are a lot of people who are in data-driven roles but dont have a data background. They arent sure what Excel can really do, but spreadsheets keep showing up in their inboxes. One of my students complained that she got a promotion, more money, and the title Social Media Strategist. However, instead of getting more social media activity, she got a mountain of data and was directed to find something interesting in this. She had become an unwitting data analyst who didnt know where to start.

Other students and clients have told stories about taking a week to manually compare lists that were thousands of rows long, retyping data that came to them in ALL CAPS, and spending days creating summaries without knowing that Pivot Tables are designed to make such summaries in seconds.

That is the world of guerrilla data analysis : You find yourself in the heat of data conflict, without formal training, and you need to make something happen.

Some aspects of data even blindside people whove been trained to work with data. Consider a person whos studied marketing in college and learned all the analytics and A/B testing and whatnot. He graduates and takes a job in a small boutique marketing firm and holy moly ! The expensive software that he used in college isnt at the firm, but they have Excel. And the level of data cleansing wasnt part of his formal training. Flattening a file () wasnt part of the curriculum. He didnt expect to have to break data out of a PDF or compile inconsistent data thats sent to him in Word documents, Excel files, Google Sheets, and pasted into Excel from emails. This is guerrilla data analysis!

If youre reading this book, youre probably a guerrilla analyst, and hopefully youll get useful tips and insights from this book, as well as solutions that end unnecessary misery. The examples here are practical and cover a wide variety of areas, including nonprofits, accounting, and event planning and retail. The goal is to get your ideas churning by exposing you to a variety of ways to use Excel.

This third edition is more than just an update of the second edition. Yes, it includes the newest brilliant features in Excel, like XLOOKUP, Power Query, dynamic arrays, and LET. But those are just tools, and in and of themselves, the tools dont do anything.

Think about what you set out to accomplish when you pick up a toola pen, a roll of tape, a USB cable, a car key, or Excel. You have a mission. Youre trying to move something in the real world.

My mission has been to battle crap data in all its forms: duplicate entries, incomplete entries, sloppy cut-and-paste jobs, data in 20 different places needing to be consolidated in a single place all the ways that data can be messy, wrong, or unusable. The consequences of all this chaos are real, and I want to empower others to use the new Excel tools to minimize and eliminate chaos. Creating this third edition is one way Im empowering youby showing you the news tools to continue this mission of ridding the world of the misery caused by crap data.

About This Book

We couldnt include everything in Excel in this book, and we made some hard decisions about what to include and how deep to go on some topics. So, heres what guided the decisions:

What would a person need when theyre thrown into the fire and need to work with Excel and data?

When someone needs to do something in Excel, there arent partitions between beginner, intermediate, and advanced skills. Theres data, a need, and people counting on the work to be done and accurate. The user needs essentials .

Think about baking a pie. There are easy tasks, like measuring a teaspoon of salt. There are intermediate tasks, such as chopping apples into similar-sized chunks. And there are advanced tasks, like knowing how to roll out the crust.