Guerrilla Data Analysis Using Microsoft Excel E-Book

Oz du Soleil

• Herausgeber: Packt Publishing
• Kategorie: Wissenschaft und neue Technologien
• Sprache: Englisch

Seitenzahl: 326

Veröffentlichungsjahr: 2024

Beschreibung

Unlock Microsoft Excel's hidden potential with this dynamic guide designed for data professionals and enthusiasts. You'll start by reviewing Excel basics before advancing to powerful tools like Excel Tables, Pivot Tables, and Power Query. Each chapter enhances your ability to analyze and visualize data efficiently, from complex lookups and dynamic arrays to essential data validation techniques that ensure accuracy and integrity in your spreadsheets.
As you progress, you'll learn how to protect your work with advanced sheet protection methods and collaboration tools for seamless teamwork. The book also covers sophisticated functions like INDIRECT, OFFSET, and LET, preparing you to tackle complex data challenges. Additionally, you'll receive critical advice on avoiding the pitfalls of machine learning-driven features and maintaining clean, organized data.
By the end of the guide, you'll have mastered Excel's advanced capabilities, empowering you to streamline workflows, optimize data processes, and make confident, data-driven decisions. This guide is your comprehensive resource for transforming your approach to data analysis with Excel.

Leseprobe

by

Oz Du Soleil & Bill Jelen

Holy Macro! Books

PO Box 541731

Merritt Island, FL 32953

Guerrilla Data Analysis 3rd 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 sys­tem 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 re­spect 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 who’ve taken my courses on LinkedIn, and anyone who’s learned anything from me about Excel. We’re 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. He’s 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 who’s 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é mochas—many days from open to close, 10 a.m. to 8 p.m.

Thank you to several people who’ve 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!

About This Book

Blindsided by Data

Small, Stupid Stuff and Big, Complicated Stuff

Chapter 1: Reviewing the Basics

Overview of Excel Functions and Formulas

Relative, Absolute, and Mixed References

Text Manipulation Functions

IF Statements

Developing Dynamic Spreadsheets

Concatenating Names and Changing Formulas to Values

Linking Worksheets and Workbooks

Helper Columns

Sorting and Filtering

Chapter 2: Excel Tables: The Glue in Dynamic Spreadsheet Development

Converting a Data Range to a Table

Using a Total Row

Naming a Table

Using Tables to Make Dynamic Dropdown Lists

Tables Functions and Cell References

Some Warnings About Working with Tables

Excel Tables Conclusion

Chapter 3: Collaboration Tools

How to Share a Workbook

The Awesome Part of Collaboration: Sheet Views

Chapter 4: Summing and Counting with Criteria

Chapter 5: VLOOKUP and XLOOKUP

VLOOKUP: What Does It Do?

XLOOKUP

Chapter 6: Pivot Tables: The Turning Point!

What Is a Pivot Table, and What Can It Do?

Getting to Know the Pivot Table Interface

Building a Pivot Table to Sum and Count Values

Summing and Counting Side-by-Side … and a Filter

Filtering with the Pivot Table

Grouping Dates in the Pivot Table

Using the Pivot Table to Get the Percentage of the Total

Pivot Table Percentages Without Totals

Using the Pivot Table to Drill Down for Isolated Details

Deleting a Pivot Table

Saving Your Favorite Pivot Table Settings Using Pivot Table Defaults

Creating a Year-over-Year Report in a Pivot Table

Counting Distinct Values in a Pivot Table

Pivot Table Conclusions

Chapter 7: Power Query

Power Query: A Little Background

Filling Down and Splitting Columns by Delimiter

Splitting Column into Rows, Grouping By, and Duplicating a Query

Data Types and Power Query

Sorting in Power Query

The Query Settings Pane

Adding More Source Data

Unpivoting and Filtering

Blanks, Nulls, and Zeros: They Aren’t the Same in Power Query

Joins and Merges in Power Query

Appending (aka Stacking Stuff Up)

Importing from a File or from a Folder

Transformation Tables

Fuzzy Matching

Chapter 8: Conditional Formatting

Using Conditional Formatting to Find Duplicates

Using Icons with Conditional Formatting

Chapter 9: De-duping in Excel

De-duping with Advanced Filter

De-duping Gets Ugly!

Using IF to “LOOK”

De-duping with an Assembled ID

Chapter 10: Dynamic Arrays

SORT

FILTER

RANDARRAY

UNIQUE

The @ Operator, Briefly Known as SINGLE

The Spill Indicator

Chapter 11: Data Is Never 100% Clean (Not for Very Long)

Chapter 12: Data Validation: Controlling Inputs and Maintaining Data Integrity

Data Validation Overview

Implementing Dropdown Lists

New in 2022: AutoComplete in Validation Dropdown Lists

Controlling Dates

Reasonable Numbers

Data Validation Cautions

Data Validation Conclusions

Chapter 13: Protecting Sheets and Cells

Locking Down an Entire Sheet

Locking and Unlocking Cells

Unprotecting a Sheet

Chapter 14: Octopus Spreadsheets

Chapter 15: INDIRECT

Chapter 16: OFFSET

Using OFFSET to Sum a Range

Chapter 17: Recognizing Patterns

Chapter 18: Data Types and Stock History

Original Release Data Types

Second Release Data Types: Wolfram

Data Types: Navigating the Data Card

Custom Data Types: Features Needed

Custom Data Types: The Choices Available Today

Chapter 19: Graphing

Graphing a Histogram Using the FREQUENCY Function

Using Chart Features

Chapter 20: The Dangers of Just Diving In

Chapter 21: The LET Function

LET for Reusing Parts of Formulas

LET for Easier Readability

Chapter 22: Warnings About Machine Learning–Driven Features in Excel and Power Query

Chapter 23: Avoid Working on Your Source Data

Chapter 24: Using Slicers

Using Slicers with Tables

Pivot Tables and Slicers

Chapter 25: Data Models and Relationships

Foreign and Primary Keys

Why a Data Model vs. Power Query?

Chapter 26: People, Processes, and Tools

Chapter 27: Keeping Your Data in as Few Places as Possible

Chapter 28: Rough-and-Tumble Tips and Insights

Unhiding Column A

Formula Triggers

Adding Emojis to Cells and Formulas

Hiding Unnecessary Zeros

Forcing a Report to Fit on One Page

Setting the Print Area to Print a Section of a Worksheet

Alt+Enter for an Extra Line in a Cell

Handling Dates

Connecting Cell Values to Shapes or Objects

Useful Excel Functions

Integrity Checks and Troubleshooting

Error-Handling Functions: IFNA vs. IFERROR

Row Counts

Chapter 29: Spreadsheet Layout and Development

A Final Word About Spreadsheet Layout and Development

Index

Introduction: Welcome to the World of Guerrilla Data Analysis!

Over the years that I’ve been consulting, teaching workshops, writing a blog, and creating videos for my YouTube channel Excel on Fire, it’s gotten clear to me that there are a lot of people who are in data-driven roles but don’t have a data background. They aren’t 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 didn’t 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 who’ve been trained to work with data. Consider a person who’s 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 isn’t at the firm, but they have Excel. And the level of data cleansing wasn’t part of his formal training. Flattening a file (page 109) wasn’t part of the curriculum. He didn’t expect to have to break data out of a PDF or compile inconsistent data that’s sent to him in Word documents, Excel files, Google Sheets, and pasted into Excel from emails. This is guerrilla data analysis!

If you’re reading this book, you’re probably a guerrilla analyst, and hopefully you’ll 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 don’t do anything.

Think about what you set out to accomplish when you pick up a tool—a pen, a roll of tape, a USB cable, a car key, or Excel. You have a mission. You’re 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 I’m empowering you—by showing you the news tools to continue this mission of ridding the world of the misery caused by crap data.

About This Book

We couldn’t 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, here’s what guided the decisions:

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

When someone needs to do something in Excel, there aren’t partitions between beginner, intermediate, and advanced skills. There’s 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.

You need all of those tasks in order to create a tasty pie. You don’t need to be an expert or a pastry chef. You need to be functional. It’s the same with Excel: You usually don’t need to be an expert, but you do need to be functional. This book strives to show you the parts of Excel that’ll make you functional in most situations.

We go beyond the typical standalone Excel parlor tricks in this book and offer warnings, context, and nuance. Most sections of this book are short and designed to give quick insights with practical examples.

Some images in the book do not show entire datasets because they’re too large. However, if you want to see a full dataset, you can use the workbooks that accompany the book. In fact, to work along with any of the examples in this book, grab the Excel files from https://www.mrexcel.com/download-center/guerrilla-data-analysis-3rd-edition-71223/.

🐉 What We Mean by Excel Skirmishes

Skirmish

This book is especially meaningful to me because it gives me an opportunity to share stories and insights that we’re calling skirmishes.

I’ve often said that when you have a project, the Excel work can be the easy part, and the hard stuff is outside of Excel. That outside stuff is the skirmishes. Things like:

Dealing with octopus spreadsheets—where they come from and what to do with them (see page 222)Identifying whether your problem is a people problem, a process problem, or a tool problem (see page 281)Avoiding working on your source data (see page 271)Accepting that data is never 100% clean and determining when it’s clean enough (see page 210)

These things can make an analyst’s work extremely and unnecessarily difficult. Imagine gathering data from five different sources, getting it all compiled, doing all the Pivot Tables, using dynamic arrays, and writing complex calculations. Then you delete some rows you don’t need and save the workbook. But then you realize that you needed those rows of data you deleted, and you can’t undo your way back. You’re deep in a guerrilla data skirmish now!

If you have access to your source data, at least you can start all over again. But, if you originally worked on your source data, woe be unto you. The source data is gone. If someone else compiled the data for you, now you’ve got to go back and humbly request, “ummm … can you get me that data again?”

The skirmishes in this book are included to help you in real-life data situations and circumstances that often can’t be ameliorated with extreme Excel sorcery. You’ll recognize a skirmish when you see this dragon symbol: 🐉

Blindsided by Data

When we wrote the second edition of this book, big data was a big deal. It isn’t such a hot topic anymore (thank goodness). Today’s buzzwords include:

Data-driven decision makingData visualizationDashboards

Whatever the lingo or fashionable topics, what hasn’t changed is the increasing number of unsuspecting people who find themselves working with data and Excel. It might be the person who was trained in college to analyze market trends, or the newbie podcaster who has to manage a budget and review her analytics to determine what listeners like and don’t like and with what frequency she should post new episodes. I even met a guy who regularly volunteered to go to a stream and track how many turtles he saw over several hours. That data would be compiled to monitor the turtle population over time, as a measure of the health of the ecosystem.

The turtle tracker, the marketer, and the podcaster can all be blindsided by data. In the case of the marketer, they can be blindsided by the level of data cleansing, and properly structuring the data inside a workbook, that has to be done before all the slick analytics that they were trained to execute.

Small, Stupid Stuff and Big, Complicated Stuff

Data analysis involves both small, stupid stuff and big, complicated stuff. Let us tell you what we mean.

One afternoon in 2005, I needed to print certificates on expensive paper. It was late in the afternoon, and the certificates absolutely had to ship that day. I did the Excel–Word mail merge, and the certificates were coming out of the printer with weirdo numbers instead of dates: 38491, 38464, and 38478 instead of 5/19/2005, 4/22/2005, and 5/6/2005. C’mon! Really?! Now?!

It took me two hours to learn that the Short Date formatting in Excel had been changed to General, and I only had to change it back and redo the merge. But the glitch had already cost me at least 50 sheets of fancy paper, the afternoon was gone, and I’d done a lot of worrying that I’d have to tell a lot of already anxious people that the certificates weren’t going to ship on time.

That afternoon I didn’t really have an analysis problem, but the story is a good example of an analyst having done all the data cleansing, merging, analysis, investigations, etc. to get to a final result—then, BANG! 💣—something weird happens just when it seemed like the hard work was done. Small, stupid details turned the process upside down.

Another reason for telling this story is to let you know that you’re not the only one who’s been stopped by small things. Students ask about these types of disruptions and start their questions with “This may seem like a small thing, but …” I say it’s not small when you’re under pressure and a whole process has stopped. Guerrilla data conflict is guerrilla data conflict.

In addition to small, stupid stuff, there is big, complicated stuff. I had a client who hired me several times to build prototypes that his in-house team would convert into web-based applications. I asked him, “Why pay me when you’ve already got the developers?” He described how he knew a little Excel and could guide me in creating what he was looking for, but, he said, “when those guys open their editors and I see all that code, I don’t know what the 😠 is going on. So, I get you to build what I want, in Excel, with all the calculations working right, and then I can tell them, ‘Here. Build this.’”

One time he and I spent two hours on a video call, working on a single formula while I shared my screen and took his instructions as he watched. Some of the formula details:

His clients would pay quarterly fees based on their annual revenue. My formula had to use the annual revenue to retrieve the fee from a fee schedule.If a client started mid-quarter, the formula had to prorate the fee. If they started one-third into the quarter, the fee would be two-thirds.If a client left within a year, the full fee for the quarter was due. If the client left after a year and left mid-quarter, the fee would be prorated. This meant my calculations had to look at the client’s start date and exit date to determine if a full fee or prorated fee was due.

All this might sound like a pretty small task, but my client was dealing with millions of dollars from his clients. So, I felt the pressure to get it right. I asked lots of questions about possible scenarios, and that spurred him into thinking of even more scenarios that we then tested. We tweaked the formula until the results were accurate.

Big and complicated can be a complex project that takes months to complete. It can also be the pressure of writing a single formula that accurately reflects a contractual agreement between multi-million-dollar businesses. Pressure is pressure. Guerrilla data conflict is guerrilla data conflict.

Note: If you have any questions, notice errors, or want to share how the book has helped you, please be in touch. I would love to hear from you. Find me at [email protected].