How To Beat An Excel Expert At Excel

So I’ve been using Excel for many years and gotten really good at it.

So good in fact that when I challenged myself to complete all the exams for a three month Excel course over a weekend, I did it.

Now I’m not trying to brag (much), I’m mainly trying to get the point across that I am very good with Excel. Index/Match, Pivots, VBA, all that good stuff.

Now, as an expert level Excel user, trust me when I say this.

You can beat an Excel expert by learning to use Power Query and Power Pivot.

And the crazy thing is, the big stuff that you can do with these tools is EASIER than Excel formulas!

Case in point, who loves VLOOKUP, raise your hands?

Nobody does.

In Power Pivot, there is an awesome formula called RELATED that absolutely blows VLOOKUP out of the water, and even INDEX/MATCH.

Just follow these steps:

  • All you have to do is add your two sets of data (as tables) to the data model
  1. Turn your data sets into tables (CTRL+T) and add them to the data model
  2. Go to the Data tab, select Manage Data Model
  3. Select the Diagram view (it’s an option on the right of the Home tab)
  4. Drag a line between your two related columns
  5. Go back to the Data View
  6. Scroll to the right, select the last column (says Add Column at the top)
  7. Type RELATED and the related tables and columns of those tables will show up
  8. Simply pick from that list whichever columns you want

That is Power Pivot’s answer to VLOOKUP and INDEX/MATCH and once it’s set up, it’s so much better!

I like to add calculated columns like:

=IF(RELATED(table[some_column])="special value",TRUE) 

And now I’ve got myself a simple filter that I can name anything I want, ideally something user friendly.

This is just one use case but I demonstrated it to two different colleagues this week and blew their minds.

Upgrade your Excel game at warp speed with Power Query and Power Pivot.

Seriously, whatever level you are, you won’t regret it.

Tell me what you think

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: