Max Mileaf

How's it going readers? If you've been paying attention and/or have the basic ability to count, you'll notice that this is the third post in a series about using Excel's secret Weapon of Math Destruction: PowerPivot. I highly suggest that if you haven't read the previous two posts, that you go ahead and do that. If you had, you would already:

1. Have enabled Powerpivot through the COM add-ins.
2. Know what Powerpivot is.
3. Understand why powerpivot can make your life as an analyst a great deal easier.
4. Be ready to power-up some ginormous flat files.

I'm going to assume that

Hey there budding Excel wizards, this post picks up RIGHT where my previous post left off, so if you haven't enabled Powerpivot yet, I highly recommend that you read the previous post and enable the add-in before moving forward. Don't worry, we'll wait...

Good to see you again! If you've gotten this far, you've already won half the battle by enabling Powerpivot. To recap, technically PowerPivot is the desktop version of one of the cornerstones of Microsoft's Business Intelligence cloud platform: Power BI. Functionally however, PowerPivot is the answer to dealing with enormous files in a way that they're still

Well, hello again dear reader, it's been a while. I've been hard at work moving around and summarizing ever expanding datasets using nothing but the wonderful tools offered to me in the Microsoft office suite (and occasionally using SAS Enterprise guide for a very unintended purpose, but that's for a later post...and hopefully, a never post).  I know I promised a post about hardware, and maybe I'll include something about that in my next post. However, there are much more pertinent and useful things at foot, and the truth is, you're probably working on a laptop anyways.

Those of

Hi there dear reader, so, I know that in the previous article that I penned, I stated that I'd be showing you how to create buttons that you can interact with (and I know you know that because you're reading these in order...right?) but I recently had to create something that was so cool, I had to write about it (once you've drank enough data Kool-Aid, you too will find solutions to data manipulation problems cool).

Here's the scene: we've got a list of people, each with a particular issue, however, due to why I needed to put the

First things first, 10 points to anyone who understands what the title is referring to (that's either Charles Atlas' workout philosophy, or one of core tenants of Bokononism in Kurt Vonnegut's standout Cat's Cradle).

Now, with my obscure reference quota filled for the day, let's assume that you've been working as an analyst for some time now. As stated in a previous post, generally you're likely to get promoted into a management position, where you no longer need your formidable skills of analysis, and they too, like all good things, will atrophy to nothingness. However, for those of you who

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 to read our bios, you may have seen something interesting: I started out as a therapist...a job that does

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 tools (and if you're particularly efficient, you may have dabbled in some VBA here and there)

However, if you have

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 introduce the downside of dabbling in the mystic arts:

One of the issues that nearly any overly competent technical analyst

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 and validate data sets
* Build a complex formula
* Utilize IF/AND/and OR statements in order to manipulate data and

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 of this momentous revelation, I implore you to read the previous statement in order to join us in enlightenment.

As

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 side effect of this...rather strange situation (ya know, like being a child and being abandoned briefly by your mother in a

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, and a complete disregard for downside risk).

Essentially, the reason why most data analysts exist within a company (remember, NOT a tech

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 comes mostly out of my inability to sleep in my hotel room in Chicago following a grueling day of doing

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 that Excel can actually handle, anything more advanced is just a matter of doing it faster (and faster, and faster)