Excel

Pro Excel secrets and magic. The kind of industry knowledge to put junior finance guys out of business.
Dealing with Dirty Data in Excel

Dealing with Dirty Data in Excel

Cleaning data Excel in the absence of tools designed to do so.

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

Doing the Excel Data Dance

Doing the Excel Data Dance

Taking a step back to reflect on the problems Excel intends to solve.

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)

Adventures in Excel: Power to the Pivot

Adventures in Excel: Power to the Pivot

Power up your Excel Pivot Tables with these pro moves.

During the last discussion, you've (hopefully) learned how to generate a pivot table, and learned about the four "buckets" that can house your columns:

  • Filter
  • Row
  • Column
  • Value

I'm also going to make the wild assumption that you've played around with your newly birthed pivot table, taking your column headings from your "raw" data (in the lingua franca of the numerically inclined, this means the data just as you've received it, before you add formulae or engage in any other manipulation.) Hopefully this gave you a feel of how a pivot table works, and if your synapses are wired in

The Art of Excel Pivot Tables

The Art of Excel Pivot Tables

Pivot complex Excel spreadsheets to summarize your data.

As previously discussed, the entry level data professional (coming from a background other than statistics, math, or computer science) consists of two keywords: The ability to lookup and the ability to pivot.

Dear reader, this is not an oversimplification or hyperbole, the hard truth is that if someone has a reasonable familiarity with a lookup function (remember, cool kids use Index-Match), and equal familiarity with how to use pivot tables, you can get most "day to day" work done on the job, and if you get really good, by utilizing both of these tools in creative ways you can essentially

The One Excel Formula to Rule Them All

The One Excel Formula to Rule Them All

Unlock the secrets of Excel's Index Match.

In my last entry, we discussed how to write a formula, and you've been armed with what each piece of the formula represents (the command, the variables, and the definition of an array). With this knowledge, you've actually been armed with the keys to the kingdom, and you're finally going to learn how to do something fancy.

There comes a time in every person's life where just by learning one piece of information, suddenly everything else you've learned clicks into place. For those who utilize Excel, learning this formula WILL BE THAT FOR YOU. In our last entry, I stated

Excel Showdown: Index Match vs. "The Lookups"

Excel Showdown: Index Match vs. "The Lookups"

Learn to hate V-Lookups and H-Lookups in favor of Index Match.

You may have picked up in my last post that some fellow wizards (of which I now consider you if you've mastered Index-Match, and you've activated developer mode) who may have started learning Excel prior to 2007 may drop the phrase "lookup;" generally in the context of "V-Lookup" (or perhaps "H-Lookup" if they're working with data that was formatted by savages).

The question is, if people "in the know" are always talking about and using these two formulas, then what do they do, and why haven't we learned it? The short answer to this question is "Index Match was introduced

How I Stopped Worrying and Learned to Love the Formula

How I Stopped Worrying and Learned to Love the Formula

Learn to define and utilize Excel formulas.

In my last entry, I took you on a journey to unlock the secrets of Excel; essentially making you a spreadsheet wizard. However, one important thing to remember at work is that a company can't function if it's comprised of only wizards (tell me to my face that Hogwarts was a functioning school!)

That begs the question, if I'm not doing wizardry, how can I still increase my Excel productivity? (that and the fact that macros can be scary at first!) The answer lies in the utilization of wild combinations of Formulae to achieve results that the program was never

Excel Data Manipulation: a Guide For Everyone

Excel Data Manipulation: a Guide For Everyone

Introduction to Excel Data Manipulation using TEXTJOIN and Macros.

Alright, so unlike the other two guys, I work in corporate, which means I work with people who only use their computers to send emails, schedule meetings they'll cancel, and occasionally write a memo plus, because they're terrified of anyone associated with IT, they'll never ask for help. As such, I'm forced to do a similar job as my comrades using the tool that everyone has: Excel. Luckily, Excel has nearly infinite functionality for those that seek to explore further... Join me in this first of a series of indefinite length: Excel data manipulation, where we get you hooked on