Hackers and Slackers: Data Science for Badasses

Excel

16 Posts
Professor Snacks reveals Excel secrets.

I Owe My Job to Mr. Robot

Entering the Dataverse

What's up data gang? If you've been reading along throughout this journey, you'll realize that all of my posts have something in common (other than Excel...smartass), they all assume that you're already working with data...even if it's just cursory exposure. Well, every journey of a thousand worksheets begins with a single lookup, and hopefully this story will help you position yourself to inner-join the fraternity of functions.

For the two of you who went out of the way

Data Author imageMax Mileaf August 14
Read

Importing Excel Datetimes Into Pandas

Pandas & Excel, Part 1

Different file formats are different!  For all kinds of reasons!

A few months back, I had to import some Excel files into a database. In this process I learned so much about the delightfully unique way Excel stores dates & times!  

The basic datetime will be a decimal number, like 43324.909907407404.  The number before the decimal is the day, the number afterwards is the time.  So far, so good - this is pretty common for computers.  The date is

Pandas Author imageMatthew Alhonte August 13
Read

Active-X Gonna Give it to Ya

Named Ranges and the Active Xs who Love Them

Hi there dear reader, we've now reached a crossroads in your ability to use Excel as a means of data analysis. If you've been following along, you've learned how to use basically every tool that an Excel analyst would need to have in the front of their minds. For those just joining us, these tools are as follows:

  • Pivot Tables (both the report layout and the tabular layout)
  • Lookup Functions
  • IF + AND + OR statements
  • And the combination of these three
Excel Author imageMax Mileaf August 10
Read

Your Invitation to the Pivot Party

Adventures in Excel

I know its been a while, but you'd be surprised how little time an energy you have when you're building a plane while you're flying it...or perhaps, if you've been reading along with this series, you won't.

Before jumping right into the deep end and showing you some lesser known (but supremely useful) tricks to save even more time (which you'll no doubt use to implement new tricks to save more time, it's a vicious cycle), allow me to

Excel Author imageMax Mileaf July 21
Read

The Way of The Knife

Adventures in Excel

If you've been following along with the posts in this series, and you've been putting some of the scenarios into practice, you've now crossed a crucial juncture in your never ending quest to master the bloated beast that is Excel. To put things in perspective, if you've conquered all the quests that I've set you upon, you should now be able to:

* Open up and add macro code into an Excel Worksheet
* Utilize lookup functions (particularly Index(Match)) to compare

Excel Author imageMax Mileaf July 05
Read

Yes...If(And))

Adventures in Excel

If you've been following along on our proverbial Hogwarts for budding Excel wizardry, you would know that we recently crossed an important Rubicon upon which all Excel, and indeed all computer programming languages are built: The mighty IF statement. Remember, the IF statement allows the wielder to fork reality at their whim, to bend the code on a lark, to be the Franklin Richards of the spreadsheet just by asking the computer a simple true/false question. Considering the gravity

Excel Author imageMax Mileaf June 20
Read

Getting Iffy With it

Adventures in Excel

If you've been following along, we discussed in the last several posts of this series how, if you're not working in a very "tech forward" organization (like my two compatriots on his site), but you have the same title, you're probably obtaining your data from another department (or it might be a sentient sponge, or a gang of squirrels with dreams of world domination, you'll actually have no idea) who you will have no contact with. As a

Excel Author imageMax Mileaf June 10
Read

Taking Out the Trash

Dealing With Dirty Data Pt 2

In my last post, we explored the organizational structure of many large companies and how this pertains to one's duties as a fledgling data analyst. I highly recommend you go back and read the first post on "dirty" data, but just in case you're one of those rebels who thinks that they're too cool to read part 1, here's a quick refresher to put you back in the analytical mindset (which is the perfect combination tactical laziness, ADHD,

Excel Author imageMax Mileaf June 05
Read

Dealing with Dirty Data

Adventures in Excel

In my last post, we discussed what separates a true analyst (read: technical) from a project manager wearing the mask of an analyst like some Scott Snyder era Joker (I figure that there's a solid overlap between fans of comic books and fans of the real world application of data. Note that this is a study with an N = 1 so it bares no statistical significance, but I have a funny feeling...call it spidey sense).

Full disclosure, this post

Excel Author imageMax Mileaf May 31
Read

Doing the Data Dance

Adventures in Excel

If you've been reading along, over the last several posts you've learned the two major skills that any self-respecting Excel jockey counts as their go-tos: the ability to lookup (remember, I'm partial to index-match, but if you learned VH lookup, ride that until you crash your system) and the ability to pivot.

Now here's something really interesting: until we pierce the veil on doing some VB macros, by truly mastering just those two skills, you can handle basically any job

Excel Author imageMax Mileaf May 29
Read