Jelen Bill - Guerilla Data Analysis Using Microsoft Excel

GUERRILLA DATA ANALYSIS
USING MICROSOFT EXCEL
2nd Edition

Oz du Soleil & Bill Jelen

Holy Macro! Books
PO Box 82
Uniontown OH 44685

GUERRILLA DATA ANALYSIS USING MICROSOFT EXCEL 2nd Edition

2015 Holy Macro! Books

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.

Printed in USA by Hess Print Solutions

First Printing: March 2015

Authors: Oz du Soleil & Bill Jelen

Copy Editor: Kitty Wilson

Indexer: Nellie Jay

Cover Design: Shannon Mattiza, 6Ft4 Productions

Published by: Holy Macro! Books, PO Box 82, Uniontown OH 44685

Distributed by Independent Publishers Group, Chicago, IL

ISBN 978-1-61547-033-4 (Print), 978-1-61547-228-4 (PDF)

Library of Congress Control Number: 2014959001

Table of Contents

Dedications

To my mother, Maere Floyd, who seemed like the meanest mother in the world. Id ask her a question like, Who was the president when I was born? Her response would be, Did you try to look it up? Eventually, I learned that the only right response was Yes. I had to have at least tried.

We had a World Book Encyclopedia set in the living room, and those books were meant for use, not decoration.

All these many years later, one lesson has stuck with me and has served me as an analyst, consultant, and Excel power user: If theres a fact in the world, make an effort to look for it. Dont make stuff up. Dig, probe, and pick until you get your answer.

As a high school teachers aide preparing for retirement, my mother has remained committed to young folks and how they learn. Thanks, Mom!

Oz du Soleil

To Dan Bricklin, Bob Frankston, and Dan Fylstra.

Bill Jelen

About the Authors

Oz du Soleil

Biographies tend to be written in the third-person, but let me speak directly to you.

I grew up in the city of North Chicago, Illinois. I joined the US Navy in 1985 and spent six years there. In that time, I served on the fast attack submarine USS Sturgeon (SSN-637) and the fast frigate USS Joseph Hewes (FF-1078).

After the Navy, I graduated from University of Illinois at Chicago (UIC) with a BA in philosophy and a minor in economics and two years of Russian language study. My coursework in philosophy involved logic and decision theory, which offers some insight into my passion for Excel and datalots of strategy, symbols, variables, and parentheses.

After UIC, I spent two years at Harvards Kennedy School of Government. I didnt finish the masters in public administration. Public policy just wasnt my thing, although while I was there, I did help with legal research for the New York Civil Liberties Union in a successful case against Time-Warner Cable.

Then I was back to Chicagoand Long Beach, California; and Maplewood, New Jersey; and New York City; and Chicago again where I eventually ended up in a job where I used Excel to investigate complaints. Duplicate orders were shipping out, test results werent showing up, accounts were assigned to the wrong sales reps, the wrong people were getting termination warning letters. In all those cases, the culprit turned out to be bad data. Some of those problems werent mine to solve, but management allowed me to dig into the data, get an understanding of the processes, work with the stakeholders, clean the data, and revise the processes. I loved putting out those fires!

For seven years I put out such fires: uncovering flaws in reports, exposing how calculations in reports didnt match business rules, modifying processes, cleaning data, and generally reducing the unnecessary misery caused by crap data. And then, at a mandatory meeting in January 2008, it was announced that the company was moving to LaCrosse, Wisconsin, and all 300 of us would be laid off over the next 12 months. I was laid off in July 2008 and decided to freelance my Excel skills.

While I took on Excel clients and taught workshops, I also founded a nonprofit and was its unpaid executive director for three years. And I developed a ghost pepper peanut butter cookie that sold quite successfully.

Today I run a blog, DataScopic.net, that focuses on data and data management, with Excel at the root of it all. After seeing so much data in my own jobs, in working with clients and students, and in discussions with other data folks, my perspective is that Excel is just a tool. We need data-savvy people and solid processesin addition to the right toolsin order to keep our data clean...and keep termination warning letters from going to the wrong people.

I love freelancing and taking projects, but I love teaching and speaking even more. That led me to take improv and stand-up comedy courses at Second City. Its been great! Theyre wonderful for anyone who regularly gets up in front of people.

Here are a few more things Id like to say about myself:

• I always carry Sriracha with me. (Gotta be prepared if friends want to go eat Italian or some other cuisine void of capsaicin.)
• I wrestled for 13 yearsfrom junior high school, high school, college and a few Navy tournaments.
• I prefer bow ties and custom-made hats.
• Ive visited Gdansk, Poland, and Budapest, Hungary, and I got tattoos in both places.
• I am in a film called Sriracha: A Documentary Film by Griffin Hammond .
• The writing of this book was briefly interrupted by my move from Chicago, IL to Portland, OR.

Thanks for buying this book! I welcome your feedback, at Oz@DataScopic.net.

Bill Jelen

I wrote the first edition of this book in 2002. Since then, I have written 44 other books about Microsoft Excel including Pivot Table Data Crunching, Excel Gurus Gone Wild, and Power Excel with MrExcel. You can find 1800 episodes of my Learn Excel podcast on YouTube. My favorite activity is to wow a room full of accountants with my half-day Power Excel seminar. Microsoft has awarded me an Excel MVP award for 10 years. I have run the MrExcel.com website since 1998.

Acknowledgements

Thanks to Bill Jelen, Mr. Excel, for the opportunity to write this book, and his leadership in the Excel community.

Thanks to Troy Berry for seeing that I was an analyst before I thought of myself as an analyst. Through his prodding and advice, I began to see this Excel thing as a powerful toolfar more than a fun toy for solving problems.

Thanks to Helena Bouchez for her real-deal coaching that kept me on track and got this book done on time!

Thanks to Keidra Chaney for many many conversations about data and the needs that non-data people have as they are thrust into data-driven roles. Thanks for your shared commitment in empowering those people in understanding the strengths and limitations of data.

Thanks to Monica Johnson, Lupe Miranda, Drew Alexander, and Nancy Migalla. These former co-workers were incredibly supportive not only as co-workers but as teachers who showed me the world of Pivot Tables, the importance of data quality, and that being an analyst goes beyond the data. It includes managing people who dont like the results and owning up to it when someone finds mistakes in the analysis.